Hello,
Can someone inform me if we can define the bounds of sliding windows with
OVER in Calcite at this moment? I am trying to define sliding windows
according to the examples given in https://calcite.apache
.org/docs/stream.html and I keep getting wrong results. Some examples and
the plans (I use ProjectToWindowRUle) they generate are :
1)
"select s.orders.productid, SUM(units) over pr " + "from
s.orders " + "window pr as (ORDER BY productid ROWS BETWEEN 5 PRECEDING AND
10 FOLLOWING)"
==>
LogicalProject(productid=[$0], EXPR$1=[CASE(>($2, 0), CAST($3):INTEGER,
null)]) LogicalWindow(window#0=[window(partition {} order by [0] rows
between $2 PRECEDING and $3 FOLLOWING aggs [COUNT($1), $SUM0($1)])])
LogicalProject(productid=[$1], units=[$2]) LogicalTableScan(table=[[s,
orders]])
in which, the numbers I have used are "converted" to columns.
2)
"select s.orders.productid, SUM(units) over (ORDER BY productid ROWS
BETWEEN 5 PRECEDING AND 10 FOLLOWING) " + "from s.orders "
For this I get the same plan as before.
3)The same goes for RANGE :
"select s.orders.productid, SUM(units) over (ORDER BY productid
RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING) "
+ "from s.orders "
4)
"select s.orders.productid, SUM(units) over (ORDER BY productid
RANGE 3600 PRECEDING) "
+ "from s.orders "
==>
LogicalProject(productid=[$0], EXPR$1=[CASE(>($2, 0), CAST($3):INTEGER,
null)])
LogicalWindow(window#0=[window(partition {} order by [0] range between $2
PRECEDING and CURRENT ROW aggs [COUNT($1), $SUM0($1)])])
LogicalProject(productid=[$1], units=[$2])
LogicalTableScan(table=[[s, orders]])
Can someone provide me a working example?
Thanks,
George
2016-10-18 20:33 GMT+03:00 Γιώργος Θεοδωράκης <[email protected]>:
> Also this query in Calcite :
>
> "select * from ("
> + "select s.orders.productid , avg(units) OVER (product
> ROWS BETWEEN 10 PRECEDING and 5 FOLLOWING)" + " as m10, "
> + "AVG(units) OVER (product RANGE INTERVAL '7' DAY
> PRECEDING) AS d7 "
> + "from s.orders "
> + " WINDOW product AS (PARTITION BY productId)) "
> + "where m10>d7 "
> );
>
> gives me after optimization the following plan, that doesn't have any
> window boundaries :
>
> LogicalFilter(condition=[>($1, $2)])
> LogicalProject(productid=[$0], m10=[CAST(/($1, $2)):INTEGER NOT NULL],
> d7=[CAST(/($3, $4)):INTEGER NOT NULL])
> LogicalProject(productid=[$0], $1=[$2], $2=[$3], $3=[$4], $4=[$5])
> LogicalWindow(window#0=[window(partition {0} order by [] rows
> between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [SUM($1),
> COUNT($1)])], window#1=[window(partition {0} order by [] range between
> UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [SUM($1), COUNT($1)])])
> LogicalProject(productid=[$1], units=[$2])
> LogicalTableScan(table=[[s, orders]])
>
> 2016-10-18 20:23 GMT+03:00 Γιώργος Θεοδωράκης <[email protected]>:
>
>> Hi,
>>
>> I was wondering if there is any possible way to define windows with SQl
>> in Calcite for queries that don't have an aggregate function? For example,
>> I want to define the queries from Linear Road Benchmark of the STREAM
>> project (http://infolab.stanford.edu/stream/cql-benchmark.html):
>>
>> 1)
>> SELECT DISTINCT car_id
>> FROM CarSegStr [RANGE 30 SECONDS];
>>
>> 2)
>> SELECT car_id, exp_way, dir, seg
>> FROM CarSegStr [PARTITION BY car_id ROWS 1], CurActiveCars
>> WHERE CarSegStr.car_id = CurActiveCars.car_id;
>>
>> Thank you in advance,
>> George.
>>
>
>
