[
https://issues.apache.org/jira/browse/CALCITE-4000?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17107665#comment-17107665
]
Viliam Durina commented on CALCITE-4000:
----------------------------------------
Few more notes:
1. The parameter names, unlike Java, are part of the contract in SQL. These
functions are not yet a part of standard SQL, but we should think of good
names.
2. We must add a second parameter of type {{DESCRIPTOR}}. We need to specify
based on which time column to calculate the windows.
3. The window size argument to the {{HOP}} function should come before the
{{shift}} argument (as it is in the example)
4. The intervals are day-to-second intervals - the year-month-intervals don't
have a fixed length in seconds.
The One SQL to Rule Them All paper proposes this:
{code:sql}
TUMBLE(
table TABLE,
timecol DESCRIPTOR,
dur INTERVAL DAY TO SECOND,
offset INTERVAL DAY TO SECOND DEFAULT INTERVAL '0' SECONDS);
HOP(
table TABLE,
timecol DESCRIPTOR,
dur INTERVAL DAY TO SECOND,
hopsize INTERVAL DAY TO SECOND,
offset INTERVAL DAY TO SECOND DEFAULT INTERVAL '0' SECONDS);
{code}
However I propose different names. Those above are more in C style, SQL tends
to not use abbreviations. I propose these: {{table, time_column, size,
hop_size, offset}}. All these points also apply to {{SESSION}}.
I'm extending the scope of this issue, maybe I should have created a new issue,
but that issue will include {{offset}}, so I don't know..
> Support OFFSET parameter in TUMBLE/HOP table functions
> -------------------------------------------------------
>
> Key: CALCITE-4000
> URL: https://issues.apache.org/jira/browse/CALCITE-4000
> Project: Calcite
> Issue Type: Sub-task
> Reporter: Rui Wang
> Assignee: Rui Wang
> Priority: Major
>
> TUMBLE/HOP have an optional "OFFSET" parameter to indicate how many time off
> from 0 for windowing. The OFFSET parameter is of interval type.
> {code:java}
> TUMBLE(table, size [, offset])
> HOP(table, shift, size, [, offset])
> {code}
> One example:
> Say here is a TUMBLE call with the following output
> TUMBLE(table, INTERNVAL '5' MINUTE)
> ||rowtime||window_start||window_end||
> |2020-06-20 12:23:40|2020-06-20 12:20:00|2020-06-20 12:25:00|
> |2020-06-20 12:21:40|2020-06-20 12:20:00|2020-06-20 12:25:00|
> if using the optional offset parameter
> TUMBLE(table, INTERNVAL '5' MINUTE, INTERNVAL '2' MINUTE)
> ||rowtime||window_start||window_end||
> |2020-06-20 12:23:40|2020-06-20 12:22:00|2020-06-20 12:27:00|
> |2020-06-20 12:21:40|2020-06-20 12:17:00|2020-06-20 12:22:00|
> OFFSET is applied per window basis, so the example above will also apply for
> HOP.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
