[ 
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)

Reply via email to