As you know, streams.html is a specification. We do not claim that it is all 
implemented.

Did you do a search of the existing tests? JdbcTest.testWinAgg2 features 
windows that have a variety of bounds, and produces the correct results. There 
are also tests in winagg.iq.

I suspect that the “constants” field of Window is not output as part of the 
explain for Window (or LogicalWindow). The $2 and $3 refer to those hidden 
constants.

Julian

> On Nov 2, 2016, at 10:53 AM, Γιώργος Θεοδωράκης <giwrgosrth...@gmail.com> 
> wrote:
> 
> 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 Γιώργος Θεοδωράκης <giwrgosrth...@gmail.com>:
> 
>> 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 Γιώργος Θεοδωράκης <giwrgosrth...@gmail.com>:
>> 
>>> 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.
>>> 
>> 
>> 

Reply via email to