Hi,
I am also working on a similar topic and encountered a problem with the window
definition and parsing it's syntax.
I am following the example to define the windows with the OVER clause. And I am
trying to use also the Partition BY clause when defining the window. Can I get
some help to find the proper syntax to define the window. It seems that the
example syntax did not work for me as shown below.
SELECT CLIENT_NAME,
SUM(AMOUNT) OVER prodW (RANGE INTERVAL '10' MINUTE PRECEDING) AS m10
FROM inputdata
WINDOW prodW AS (ORDER BY ETIME PARTITION BY ID)
However this leads to a parsing error
Encountered "(" at line 1, column 46.
Was expecting one of:
"FROM" ...
"," ...
"AS" ...
...
at
org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:388)
at
org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:119)
at
org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:131)
at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:156)
at org.apache.calcite.prepare.PlannerImpl.parse(PlannerImpl.java:167)
at org.huawei.demo.sqlparsercsv.LocalParser3.main(LocalParser3.java:68)
Alternatively I tried to define the window completely
SELECT CLIENT_NAME,
SUM(AMOUNT) OVER prodW AS m10
FROM inputdata
WINDOW prodW AS (ORDER BY ETIME RANGE INTERVAL '10' MINUTE PRECEDING
PARTITION BY ID)
Which leads to a different error:
Exception in thread "main" org.apache.calcite.sql.parser.SqlParseException:
Encountered "PARTITION" at line 1, column 158.
Was expecting one of:
")" ...
"ALLOW" ...
"DISALLOW" ...
at
org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:388)
at
org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:119)
at
org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:131)
at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:156)
at org.apache.calcite.prepare.PlannerImpl.parse(PlannerImpl.java:167)
at org.huawei.demo.sqlparsercsv.LocalParser3.main(LocalParser3.java:74)
-----Original Message-----
From: Julian Hyde [mailto:[email protected]]
Sent: Wednesday, November 02, 2016 7:14 PM
To: [email protected]
Subject: Re: Window Semantics for Streams
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, Γιώργος Θεοδωράκης <[email protected]>
> 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 Γιώργος Θεοδωράκης <[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.
>>>
>>
>>
