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. >> > >