[
https://issues.apache.org/jira/browse/PHOENIX-154?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15176283#comment-15176283
]
Maryann Xue edited comment on PHOENIX-154 at 3/2/16 7:18 PM:
-------------------------------------------------------------
Calcite has full support for the window function OVER clause as illustrated
below, so I suppose we don't need to anything in calcite for now.
{code}
<window function> OVER
([PARTITION BY <expression list>]
[ORDER BY <expression [ASC|DESC] list>]
[ROWS|RANGE <window frame>])
{code}
Still, we have to implement the runtime in Phoenix and the translation of
Phoenix window function operator for the integration part, which is a
considerable amount of work I think. Right now the difficulties include:
1) Handle the boundaries of sliding windows if the table is ordered on the
partition key. Not sure if we'll have a solution that do not have to return all
row-level information to the client for region boundary rows.
2) When the table is not ordered on the partition key. In this case, I think it
will basically be a server-side aggregation (what we do for GROUP BY right now)
plus a pure client side window building.
A window with only ORDER BY key and no PARTITION key will be similar to case 2)
for unordered case and will be somewhat straightforward for ordered case.
3) Multiple windows. Things can get much more complicated with the following
example (copied from calcite test case), thus I suggest we leave it for future
improvement.
{code}
select
"deptno",
"empid",
sum("salary" + "empid") over w as s,
min("salary") over w as m,
count(*) over w as c,
count(*) over w2 as c2,
count(*) over w11 as c11,
count(*) over w11dept as c11dept
from "hr"."emps"
window w as (order by "empid" rows 1 preceding),
w2 as (order by "empid" rows 2 preceding),
w11 as (order by "empid" rows between 1 preceding and 1 following),
w11dept as (partition by "deptno" order by "empid" rows between 1
preceding and 1 following)
{code}
Think it might make sense to start with the simplest case: sliding window with
ORDER BY key and ROWS/RANGE only, so that we can get all the basic facilities
for sliding windows ready before we move onto more difficult problems with
PARTITION. What do you think, [~jamestaylor] and [~RCheungIT] ?
was (Author: maryannxue):
Calcite has full support for the window function OVER clause as illustrated
below, so I suppose we don't need to anything in calcite for now.
{code}
<window function> OVER
([PARTITION BY <expression list>]
[ORDER BY <expression [ASC|DESC] list>]
[ROWS|RANGE <window frame>])
{code}
Still, we have to implement the runtime in Phoenix and the translation of
Phoenix window function operator for the integration part, which is a
considerable amount of work I think. Right now the difficulties include:
1) Handle the boundaries of sliding windows if the table is ordered on the
partition key. Not sure if we'll have a solution that do not have to return all
row-level information to the client for region boundary rows.
2) When the table is not ordered on the partition key. In this case, I think it
will basically be a server-side aggregation (what we do for GROUP BY right now)
plus a pure client side window building.
A window with only ORDER BY key and no PARTITION key will be similar to case 2)
for unordered case and will be somewhat straightforward for ordered case.
3) Multiple windows. Things can get much more complicated with the following
example (copied from calcite test case), thus I suggest we leave it for future
improvement.
{code}
select
\"deptno\",
\"empid\",
sum(\"salary\" + \"empid\") over w as s,
min(\"salary\") over w as m,
count(*) over w as c,
count(*) over w2 as c2,
count(*) over w11 as c11,
count(*) over w11dept as c11dept
from \"hr\".\"emps\"
window w as (order by \"empid\" rows 1 preceding),
w2 as (order by \"empid\" rows 2 preceding),
w11 as (order by \"empid\" rows between 1 preceding and 1
following),
w11dept as (partition by \"deptno\" order by \"empid\" rows
between 1 preceding and 1 following)
{code}
Think it might make sense to start with the simplest case: sliding window with
ORDER BY key and ROWS/RANGE only, so that we can get all the basic facilities
for sliding windows ready before we move onto more difficult problems with
PARTITION. What do you think, [~jamestaylor] and [~RCheungIT] ?
> Support SQL OLAP extensions
> ---------------------------
>
> Key: PHOENIX-154
> URL: https://issues.apache.org/jira/browse/PHOENIX-154
> Project: Phoenix
> Issue Type: New Feature
> Reporter: James Taylor
> Labels: gsoc2016
>
> Support the WINDOW, PARTITION OVER, GROUPING, RANK, DENSE RANK, ORDER BY etc.
> functionality.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
