alexch2000 opened a new issue, #11775: URL: https://github.com/apache/pinot/issues/11775
# Create a Window HOP function similar to DATETIMECONVERT. The DATETIMECONVERTWINDOWHOP converts the value from a column that contains an epoch timestamp into another time unit and buckets based on the given time granularity and window hop size. A hopping time window has a fixed duration (hopWindow paramenter) and hops by a specified hop interval (outputGranularity parameter). If the hop interval is smaller than the window size, hopping windows are overlapping. Thus, rows can be assigned to multiple windows. For example, a hopping window of 15 minutes size and 5 minute hop interval assigns each row to 3 different windows of 15 minute size, which are evaluated in an interval of 5 minutes. This is why the function returns an array of timestamps, in this example the array would contain 3 elements. # Signature ``` DATETIMECONVERTWINDOWHOP(columnName, inputFormat, outputFormat, outputGranularity, hopWindow) inputFormat and outputFormat are defined using the following structure: <time size>:<time unit>:<time format>:<pattern> ``` # Usage Examples These examples are based on the [Batch JSON Quick Start](https://docs.pinot.apache.org/basics/getting-started/quick-start#batch-json). created_at_timestamp from milliseconds since epoch to seconds since epoch, bucketed to 1 hour window with 15 min granularity: ``` select id, created_at_timestamp, cast(created_at_timestamp AS long) AS timeInMs, DATETIMECONVERTWINDOWHOP( created_at_timestamp, '1:MILLISECONDS:EPOCH', '1:SECONDS:EPOCH', '15:MINUTES', '1:HOUR', ) AS windowHops from githubEvents WHERE id = 7044874134 ``` id | created_at_timestamp | timeInMs | windowHops -- | -- | -- | -- 7044874134 | 2018-01-01 11:00:00.0 | 1514804402000 | [1514804402, 1514803502, 1514802602, 1514801702] Moving window of unique user counts per hour with 15 min granularity: ``` select id, DATETIMECONVERTWINDOWHOP( created_at_timestamp, '1:MILLISECONDS:EPOCH', '1:SECONDS:EPOCH', '15:MINUTES', '1:HOUR', ) AS windowHops, DISTINCTCOUNT(userId) AS uniqueUsers from githubEvents group by 1 ``` <b id="docs-internal-guid-59ecfe58-7fff-ec29-a975-f4f68e3af7e5" style="caret-color: rgb(0, 0, 0); color: rgb(0, 0, 0); font-style: normal; font-variant-caps: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; font-weight: normal;"><div dir="ltr" align="left" style="margin-left: 0pt;"> windowHops | uniqueUsers -- | -- 7044874134 | 2018-01-01 11:00:00.0 </div></b> -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org For additional commands, e-mail: commits-h...@pinot.apache.org
