vtlim commented on code in PR #15902: URL: https://github.com/apache/druid/pull/15902#discussion_r1501214326
########## docs/querying/sql-window-functions.md: ########## @@ -88,40 +88,135 @@ You can use the OVER clause to treat other Druid aggregation functions as window Window functions support aliasing. -## Define a window with the OVER clause +## Window function syntax + +In general, Druid Window functions use the following syntax: + + +```sql +window_function() OVER ( + [PARTITION BY partitioning expression] + [ORDER BY order expression] + [[ROWS, RANGE] BETWEEN range start AND range end]) +FROM table +GROUP BY dimensions +``` + +```sql +window_function() OVER w +FROM table +WINDOW w AS ([PARTITION BY ] [ORDER BY] + [[ROWS, RANGE] BETWEEN range start AND range end] ) +GROUP BY dimensions +``` The OVER clause defines the query windows for window functions as follows: - PARTITION BY indicates the dimension that defines the rows within the window -- ORDER BY specifies the order of the rows within the windows. +- ORDER BY specifies the order of the rows within the windows + +An empty OVER clause or the absence of a PARTITION BY clause indicates that all data belongs to a single window. + +For example, the following OVER clause example sets the window dimension to `channel` and orders the results by the absolute value of `delta` ascending: + +```sql +... +RANK() OVER (PARTITION BY channel ORDER BY ABS(delta) ASC) +... +``` + +Window frames, set in ROWS and RANGE expressions, limit the set of rows used for the windowed aggregation. The general syntax is: + +ROWS AND RANGE accept the following values start and end : +- UNBOUND PRECEDING - from the beginning of the partition as order by the order expression +- N ROWS PRECEDING - N rows before the current row as ordered by the order expression +- CURRENT ROW - the current row +- N ROWS FOLLOWING - N rows after the current row as ordered by the order expression +- UNBOUNDED FOLLOWING - to the end of the partition as ordered by the order expression Review Comment: For the SQL value, I assume N is a placeholder here so we should denote it as a placeholder somehow like `<N> ROWS PRECEDING` ########## docs/querying/sql-window-functions.md: ########## @@ -88,40 +88,135 @@ You can use the OVER clause to treat other Druid aggregation functions as window Window functions support aliasing. -## Define a window with the OVER clause +## Window function syntax + +In general, Druid Window functions use the following syntax: + + +```sql +window_function() OVER ( + [PARTITION BY partitioning expression] + [ORDER BY order expression] + [[ROWS, RANGE] BETWEEN range start AND range end]) +FROM table +GROUP BY dimensions +``` + +```sql +window_function() OVER w +FROM table +WINDOW w AS ([PARTITION BY ] [ORDER BY] + [[ROWS, RANGE] BETWEEN range start AND range end] ) +GROUP BY dimensions +``` The OVER clause defines the query windows for window functions as follows: - PARTITION BY indicates the dimension that defines the rows within the window -- ORDER BY specifies the order of the rows within the windows. +- ORDER BY specifies the order of the rows within the windows + +An empty OVER clause or the absence of a PARTITION BY clause indicates that all data belongs to a single window. + +For example, the following OVER clause example sets the window dimension to `channel` and orders the results by the absolute value of `delta` ascending: + +```sql +... +RANK() OVER (PARTITION BY channel ORDER BY ABS(delta) ASC) +... +``` + +Window frames, set in ROWS and RANGE expressions, limit the set of rows used for the windowed aggregation. The general syntax is: + +ROWS AND RANGE accept the following values start and end : +- UNBOUND PRECEDING - from the beginning of the partition as order by the order expression +- N ROWS PRECEDING - N rows before the current row as ordered by the order expression +- CURRENT ROW - the current row +- N ROWS FOLLOWING - N rows after the current row as ordered by the order expression +- UNBOUNDED FOLLOWING - to the end of the partition as ordered by the order expression Review Comment: ```suggestion - `UNBOUND PRECEDING`: from the beginning of the window as ordered by the order expression - `N ROWS PRECEDING`: _N_ rows before the current row as ordered by the order expression - `CURRENT ROW`: the current row - `N ROWS FOLLOWING`: _N_ rows after the current row as ordered by the order expression - `UNBOUNDED FOLLOWING`: to the end of the window as ordered by the order expression ``` For the description we should italicize N since it's a variable (https://developers.google.com/style/text-formatting) -- 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]
