The documentation of windowed aggregates is minimal, because Calcite
just follows the SQL standard. What works in standards-compliant
databases, such as Oracle and PostgreSQL, will typically work in
Calcite.
Here is an example of a time window query:
SELECT deptno,
empno,
COUNT(*) OVER (ORDER BY hiredate
RANGE INTERVAL '1' MONTH PRECEDING
PARTITION BY deptno) AS c1,
COUNT(*) OVER (ORDER BY hiredate
RANGE INTERVAL '1' MONTH PRECEDING ) AS c2
FROM Emp
It shows, for each employee, how many employees were hired in the same
department in the previous month, and how many were hired in the whole
company in the previous month.
Note that you use ORDER BY in the window to specify the column that
you wish to take a range over, then the RANGE clause to specify the
lower and/or upper bounds. Subtracting two datetime values yields an
interval, so the range uses an interval literal.
You can specify both bounds using BETWEEN:
COUNT(*) OVER ( ...
RANGE BETWEEN INTERVAL '2' MONTH PRECEDING
AND '1' YEAR FOLLOWING)
I don't have a particular implementation of streaming SQL to
recommend. Calcite's reference implementation is pretty basic and lags
behind the SQL examples. I hope to fix that soon. SamzaSQL, Storm and
Flink have released implementations of streaming SQL; give them a try.
Hope that helps.
Julian
On Thu, Jun 9, 2016 at 3:21 PM, Chinmay Soman <[email protected]> wrote:
> Hi all,
>
> Just wanted to know if there is any documentation regarding window
> functions ? Specifically if I have to do something like : "count(*) over
> window", I somehow can't find any examples on the Calcite docs.
>
> More specifically,
> * Is there a way to specify a time window range ?
> * Assuming I have a timestamp column, can I use that to define the window
> range ?
> * Is there any implementation of Streaming SQL out there that I can use ?
>
> Any help would be much appreciated.
>
>
> --
> Thanks and regards
>
> Chinmay Soman
