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