vtlim commented on code in PR #15902: URL: https://github.com/apache/druid/pull/15902#discussion_r1501236644
########## docs/querying/sql-window-functions.md: ########## @@ -214,6 +309,45 @@ GROUP BY channel, __time, delta </details> +### Example with window frames + +The following query uses a few different window frames to calculate overall activity by channel: + +```sql +SELECT + channel, + TIME_FLOOR(__time, 'PT1H') AS time_hour, + SUM(delta) AS hourly_channel_changes, + SUM(SUM(delta)) OVER cumulative AS cumulative_activity_in_channel, + SUM(SUM(delta)) OVER moving5 AS csum5, + COUNT(1) OVER moving5 AS count5 +FROM "wikipedia" +WHERE channel = '#en.wikipedia' + AND __time BETWEEN '2016-06-27' AND '2016-06-28' +GROUP BY 1, TIME_FLOOR(__time, 'PT1H') +WINDOW cumulative AS ( + PARTITION BY channel + ORDER BY TIME_FLOOR(__time, 'PT1H') + ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW + ) + , + moving5 AS ( + PARTITION BY channel + ORDER BY TIME_FLOOR(__time, 'PT1H') + ROWS BETWEEN 4 PRECEDING AND CURRENT ROW + ) +``` + +The example defines multiple window specifications in the WINDOW clause that you can use for various window function calculations. + +The query uses two windows: +- cumulative is partitioned by channel and includes all rows from the beginning of partition up to the current row as ordered by `__time` to enable cumulative aggregation +- `moving5` is also partitioned by channel but only includes up to the last 4 rows and the current row as ordered by time + +The number of rows considered for the `moving5` window for the count 5 column: +- starts at 1 because there are no rows before the current one +- grows up to 5 as defined by ROWS BETWEEN 4 PRECEDING AND CURRENT ROW Review Comment: See earlier comment on if it's `4 PRECEDING` or `4 ROWS PRECEDING` https://github.com/apache/druid/pull/15902#discussion_r1501234899 -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
