# eXtremeSQL Window Functions

> A “Window function” is a variation on an aggregation function. Where an aggregation function, like `SUM()` and `AVG()`, takes `n` inputs and returns a single value, a window function returns `n` values.

> For example, consider the simple table defined as:

>         CREATE TABLE test(i int);
>         INSERT INTO test VALUES(1),(2),(3),(4),(5);

> The `SUM()` and `AVG()`aggregate functions return the following values:

>         SELECT SUM(i), AVG(i) FROM test;
>         #1      #2
>         ------------------------------------------------------------------------------
>         15      3
>         Selected records: 1

> In an SQL `SELECT` statement, the `OVER` keyword indicates that the preceding function is to be applied to a `window` representing a group of rows relative to the current row in the result set of the query. For example, the window functions `SUM()` and `AVG()` return the following values:

>         SELECT i, SUM(i) OVER(), AVG(i) OVER() FROM test;
>         i       #2      #3
>         ------------------------------------------------------------------------------
>         1       1       1
>         2       3       1.5
>         3       6       2
>         4       10      2.5
>         5       15      3
>         Selected records: 5

> The output of a window function depends on a number of input values (function arguments, computed values from other rows in the frame, etc.), so they don’t include functions that work element-wise, like + or `ROUND()`. Window functions include variations on aggregate functions, like `SUM()`, `AVG()`, `MIN()` and `MAX()`, functions for ranking and ordering, like `RANK()`, and functions for taking offsets, like `LEAD()` and `LAG()`.

> Note that these SQL window functions are not to be confused with the “Window_Agg” functions in the Vector-based Analytics Library. The “Window_Agg” functions operate only on sequences (i.e. fields of type sequence) where the “window” is specified as a fixed number of elements (i.e. interval) to perform computations on.

> ## Window function syntax

> A window function call always contains an `OVER` clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a regular function or aggregate function. The `OVER` clause determines exactly how the rows of the query are to be split up for processing by the window function. A `PARTITION BY` list within the `OVER` clause can be used for dividing the rows into groups, or partitions that share the same values specified in the `PARTITION BY` expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row.

> For a given window we can specify:

> - the order of the rows in the window (the `ORDER BY` clause)
> - a subset of the table data to operate on (the `PARTITION BY` clause)
> - the window defined by the previous two clauses can be further narrowed down into frames of rows by use of the `ROWS BETWEEN` clause which can be:
>   - bounded – eg. `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW`
>   - partially bounded – eg. `ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING`
>   - unbounded – eg. `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`
> - “logical” `frames` can also be defined through the `RANGE BETWEEN` option (see “Specifying window frames” below)

> The following window functions are supported. Note that those below “for ordered rows” require an `ORDER BY` clause in the `OVER` specification:

> - aggregate functions

> > - `count` – the cumulative row count
> > - `row_number` – the row number
> > - `min` – the minimum value
> > - `max` – the maximum value
> > - `sum` – the cumulative sum of this and preceding rows
> > - `avg` – the average of this row and all previous rows
> > - `median` - the median of this row and all previous rows

> - for ordered rows

> > - `first_value` – the first value
> > - `last_value` – the last value
> > - `nth_value` – the specified nth value
> > - `lead` – access a row at a specified offset after the current row
> > - `lag` – access a row at a specified offset before the current row
> > - `rank` – the rank of the value in this row (there can be gaps in the sequence of ranks)
> > - `dense_rank` – the rank of the value in this row (with no gaps in the sequence of ranks)
> > - `percent_rank` – used for computing the percentile of this row
> > - `ntile` - distributes the rows into a specified number of groups

> ## Specifying window frames

> The window frame for each row of the result set is determined by specifying `BETWEEN` bounds. These `BETWEEN` bounds can be specified by either `ROWS` or `RANGE` options. The `ROWS` option defines a fixed number of rows preceding and following the current row; and the rows that will comprise the frame will be determined by the `ORDER BY` clause. If no bounds are explicitly specified, the default frame is from the first row (`UNBOUNDED PRECEDING`) to the current row.

> Note that the `BETWEEN` bounds must be specified with explicit constant values, not with parameters. For example, the following statement is not valid:

> >         SELECT stamp, SUM(price*quantity) OVER w AS pq1, SUM(quantity) OVER w AS vol, pq1/vol AS VWAP
> >         FROM Quotes
> >         WINDOW w AS (PARTITION BY pkid ORDER BY stamp RANGE BETWEEN ? PRECEDING AND 0 FOLLOWING)

> > Here the parameter substitution "`BETWEEN ? PRECEDING`" is not allowed.

> > Instead the following statement using constant value `5` is valid:

> >         SELECT stamp, SUM(price*quantity) OVER w AS pq1, SUM(quantity) OVER w AS vol, pq1/vol AS VWAP
> >         FROM Quotes
> >         WINDOW w AS (PARTITION BY pkid ORDER BY stamp RANGE BETWEEN 5 PRECEDING AND 0 FOLLOWING)

> If the `RANGE` option is used, then all rows within the frame that have the same `ORDER BY` value as the current row will be included in that row’s function computation. To illustrate the difference between `physical` `ROWS BETWEEN` frames and `logical` `RANGE BETWEEN` frames, we will add a column to the example table test and insert values as follows:

>         ALTER TABLE test ADD val int;
>         UPDATE test SET val=100 WHERE i=1;
>         UPDATE test SET val=200 WHERE i=2;
>         UPDATE test SET val=200 WHERE i=3;
>         UPDATE test SET val=200 WHERE i=4;
>         UPDATE test SET val=300 WHERE i=5;
>         SELECT * FROM test;
>         i       val
>         ------------------------------------------------------------------------------
>         1       100
>         2       200
>         3       200
>         4       200
>         5       300
>         Selected records: 5

> Now to compute the sum over the physical frame of `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` (the default frame) we could perform a query like the following:

>         SELECT i, val, SUM(val) OVER(ORDER BY val ROWS BETWEEN UNBOUNDED PRECEDING AND
>         CURRENT ROW) AS Row_Sum FROM test;
>         i       val     Row_Sum
>         ------------------------------------------------------------------------------
>         1       100     100
>         2       200     300
>         3       200     500
>         4       200     700
>         5       300     1000
>         Selected records: 5

> Note that `SUM(val)` is calculated as the sum of the value val for the current row plus its value for all preceding rows. To compute the sum over the logical frame `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` we could perform a query like the following:

>         SELECT i, val, SUM(val) OVER(ORDER BY val RANGE BETWEEN UNBOUNDED PRECEDING AND
>         CURRENT ROW) AS Range_Sum FROM test;
>         i       val     Range_Sum
>         ------------------------------------------------------------------------------
>         1       100     100
>         2       200     700
>         3       200     700
>         4       200     700
>         5       300     1000
>         Selected records: 5

> Note that the `ORDER BY` value of column val is the same for rows 2, 3 and 4; so `SUM(val)` here is calculated for these rows as `100 + 200 + 200 + 200 = 700`.

> ## Examples

> The following examples use data from Washington DC’s Capital Bikeshare Program, which publishes detailed trip-level historical data on their website. Each row represents one ride. The following import script was used to create the table bikes, the index `bike_num_idx` to speed up lookups by `bike_num`, and then insert the sample data:

>         CREATE TABLE bikes(duration int, start_date string, end_date string, start_st_num int,
>         start_st string, end_st_num int, end_st string, bike_num string,
>         member_type string);
>         INSERT INTO bikes SELECT * from foreign table(path='2016-Q1-Trips-History-Data.csv', delimiter=',', skip=1) AS bikes;
>         CREATE INDEX bike_num_idx ON bikes(bike_num);

> A simple practical example of a Window function is calculating a running total. For example the following query creates an aggregation (`running_total`) as the cumulative sum of field duration (note that this is without using a `GROUP BY` clause):

>         SELECT duration, start_date, SUM(duration) OVER (ORDER BY start_date) AS running_total
>         FROM bikes WHERE bike_num = 'W22196' AND start_date >= '3/31/2016';

> The first part of the above aggregation, `SUM(duration)`, looks similar to any other aggregation. Adding the keyword `OVER` designates it as a window function. So the above aggregation could be read as “take the sum of duration over the entire result set, in order of `start_date`”. Here, for brevity of output, we have specified a `WHERE` clause for a `bike_num (W22196)` and a single day (3/31/2016) with a limited number of trips. The result set shows how the `running_total` displays the cumulative sum:

>         duration        start_date        running_total
>         ------------------------------------------------------------------------------
>         456465  	 3/31/2016 0:05    456465
>         300889  	 3/31/2016 14:25   757354
>         1001144 	 3/31/2016 23:51   1758498
>         577035  	 3/31/2016 8:20    2335533
>         Selected records: 4

> The following query demonstrates the use of a `PARTITION BY` clause to calculate the cumulative sum for blocks of result set rows grouped by the start terminal number `start_st_num` and ordered by `start_date`:

>         SELECT start_st_num, duration, start_date, SUM(duration)
>         OVER (PARTITION BY start_st_num ORDER BY start_date) AS running_total
>         FROM bikes WHERE bike_num = 'W22196' AND start_st_num BETWEEN 31610 and 31625;
>         start_st_num    duration   start_date        running_total
>         ------------------------------------------------------------------------------
>         31624   	 1375776    1/12/2016 18:28   1375776
>         31624   	 396755     1/21/2016 8:47    1772531
>         31624   	 988750     1/7/2016 19:38    2761281
>         31624   	 203844     2/29/2016 10:26   2965125
>         31624   	 987070     3/11/2016 16:30   3952195
>         31624   	 372239     3/11/2016 18:09   4324434
>         31623   	 602551     1/14/2016 7:02    602551
>         31623   	 691224     1/16/2016 21:03   1293775
>         31623   	 372176     1/18/2016 12:40   1665951
>         31623   	 278196     1/20/2016 13:48   1944147
>         31623   	 389189     1/7/2016 7:42     2333336
>         31623  		 387578     3/29/2016 18:44   2720914
>         31622   	 382357     1/18/2016 8:24    382357
>         31622   	 797607     1/19/2016 8:36    1179964
>         31622   	 508194     3/30/2016 9:09    1688158
>         31621   	 837364     1/13/2016 17:37   837364
>         31621   	 143947     3/1/2016 8:52     981311
>         31620   	 188661     3/27/2016 12:22   188661
>         31620   	 300889     3/31/2016 14:25   489550
>         31618 		 3336939    3/30/2016 14:09   3336939
>         31615   	 216014     1/6/2016 16:36    216014
>         31615   	 577035     3/31/2016 8:20    793049
>         31613   	 1493051    3/30/2016 10:18   1493051
>         Selected records: 23

> This query groups the results by `start_st_num`. Note that within each value of `start_st_num`, the running total sums duration across the current row and all previous rows of that window. This is the effect of the `PARTITION BY` clause. Also note that the optional `ORDER BY` clause simply orders by the designated column(s) the same way that an `ORDER BY` clause would for the `SELECT` statement, except that it treats every partition separately.

> Note that you cannot use window functions and standard aggregations in the same query. More specifically, you can’t include window functions in a `GROUP BY` clause.

> As the `SUM()` function calculates the cumulative sum, similarly the `COUNT()`and `AVG()` functions calculate the cumulative number of rows in the partition and their average. For example:

>         SELECT start_st_num, duration, start_date,
>         SUM(duration) OVER (PARTITION BY start_st_num ORDER BY start_date) AS running_total,
>         COUNT(duration) OVER (PARTITION BY start_st_num ORDER BY start_date) AS running_count,
>         AVG(duration) OVER (PARTITION BY start_st_num ORDER BY start_date) AS running_avg
>         FROM bikes WHERE bike_num = 'W22196' and start_st_num BETWEEN 31610 and 31625;
>         start_st_num    duration start_date  running_total running_count  running_avg
>         ------------------------------------------------------------------------------
>         31624   1375776 1/12/2016 18:28 	1375776 	1       	1375776
>         31624   396755  1/21/2016 8:47  	1772531 	2       	886265.5
>         31624   988750  1/7/2016 19:38  	2761281 	3       	920427
>         31624   203844  2/29/2016 10:26 	2965125 	4       	741281.25
>         31624   987070  3/11/2016 16:30 	3952195 	5       	790439
>         31624   372239  3/11/2016 18:09 	4324434 	6       	720739
>         31623   602551  1/14/2016 7:02  	602551  	1       	602551
>         31623   691224  1/16/2016 21:03 	1293775 	2       	646887.5
>         31623   372176  1/18/2016 12:40 	1665951 	3       	555317
>         31623   278196  1/20/2016 13:48 	1944147 	4       	486036.75
>         31623   389189  1/7/2016 7:42   	2333336 	5       	466667.2
>         31623   387578  3/29/2016 18:44 	2720914 	6       	453485.666666667
>         31622   382357  1/18/2016 8:24  	382357  	1       	382357
>         31622   797607  1/19/2016 8:36  	1179964 	2       	589982
>         31622   508194  3/30/2016 9:09  	1688158 	3       	562719.333333333
>         31621   837364  1/13/2016 17:37 	837364  	1       	837364
>         31621   143947  3/1/2016 8:52   	981311  	2       	490655.5
>         31620   188661  3/27/2016 12:22 	188661  	1       	188661
>         31620   300889  3/31/2016 14:25 	489550  	2       	244775
>         31618   3336939 3/30/2016 14:09 	3336939 	1       	3336939
>         31615   216014  1/6/2016 16:36  	216014  	1       	216014
>         31615   577035  3/31/2016 8:20  	793049  	2       	396524.5
>         31613   1493051 3/30/2016 10:18 	1493051 	1       	1493051
>         Selected records: 23

> To demonstrate the next set of functions we will use a simple table and dataset created by the following script:

>         CREATE TABLE test(i int);
>         INSERT INTO test VALUES(1),(2),(3),(4),(5);

> The `LAG()` and `LEAD()`functions return the value for the specified row following or preceding the current row, respectively:

>         SELECT i,
>         LAG(i,1) OVER (ORDER BY i ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
>         LEAD(i,1) OVER (ORDER BY i ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM test;
>         i       #2      #3
>         ------------------------------------------------------------------------------
>         1       null    2
>         2       1       3
>         3       2       4
>         4       3       5
>         5       4       null
>         Selected records: 5

> The `MIN()` and `MAX()`functions return the minimal and the maximum values respectively for the specified frames. For example, when using the default bounds (`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`), the `MIN()` and `MAX()`are determined by the ever increasing frame. For example:

>         SELECT i, MIN(i) OVER (ORDER BY i), MAX(i) OVER (ORDER BY i) FROM test;
>         i       #2      #3
>         ------------------------------------------------------------------------------
>         1       1       1
>         2       1       2
>         3       1       3
>         4       1       4
>         5       1       5
>         Selected records: 5

> But when an unbounded frame is specified (`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`) the minimum and maximum values are for the entire window:

>         SELECT i,
>         MIN(i) OVER (ORDER BY i ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
>         MAX(i) OVER (ORDER BY i ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
>         FROM test;
>         i       #2      #3
>         ------------------------------------------------------------------------------
>         1       1       5
>         2       1       5
>         3       1       5
>         4       1       5
>         5       1       5
>         Selected records: 5

> The functions `LAST_VALUE()`, `FIRST_VALUE()`, and `NTH_VALUE()` with no boundaries specified and ordering by the field `i` will produce 5 frames and the value of `LAST_VALUE()` will be the value of `i` for the current row. This is because the default frame for window functions is `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`. So by not specifying a frame explicitly, `FIRST_VALUE()` returns the value of the first row in the table and `LAST_VALUE()`returns the value in the current row. The `NTH_VALUE()` function returns the value of the `nth` row (zero-based) of the current frame or `NULL` if there is no `nth` row in the current frame. For example:

>         SELECT i, FIRST_VALUE(i) OVER (ORDER BY i), LAST_VALUE(i) OVER (ORDER BY i),
>         NTH_VALUE(i, 1) OVER (ORDER BY i) FROM test;
>         i       #2      #3      #4
>         ------------------------------------------------------------------------------
>         1       1       1       null
>         2       1       2       2
>         3       1       3       2
>         4       1       4       2
>         5       1       5       2
>         Selected records: 5

> If we specify an unbounded window the value of `LAST_VALUE()` will be the last value in the entire window:

>         SELECT i,
>         FIRST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
>         LAST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
>         NTH_VALUE(i, 1) OVER (ORDER BY i ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM test;
>         i       #2      #3      #4
>         ------------------------------------------------------------------------------
>         1       1       5       2
>         2       1       5       2
>         3       1       5       2
>         4       1       5       2
>         5       1       5       2
>         Selected records: 5

> And if we specify a bounded window the value of `LAST_VALUE()` will be the last value in the specified frame (range of rows):

>         SELECT i, FIRST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
>         LAST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
>         NTH_VALUE(i, 2) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM test;
>         i       #2      #3      #4
>         ------------------------------------------------------------------------------
>         1       1       2       null
>         2       1       3       3
>         3       2       4       4
>         4       3       5       5
>         5       4       5       null
>         Selected records: 5

> To demonstrate ranking window functions we will use another simple table and dataset created by the following script:

>         CREATE TABLE t(v string);
>         INSERT INTO t(v) VALUES ('a'),('a'),('a'),('b'),('c'),('c'),('d'),('e');

> The `ROW_NUMBER()` function assigns unique numbers to each row within the partition. For example:

>         SELECT v, ROW_NUMBER() OVER(ORDER BY v) FROM t;
>         v       #2
>         ------------------------------------------------------------------------------
>         a       1
>         a       2
>         a       3
>         b       4
>         c       5
>         c       6
>         d       7
>         e       8
>         Selected records: 8

> The `RANK()` function behaves like `ROW_NUMBER()` except that “equal” rows are ranked the same. For example:

>         SELECT v, RANK() OVER(ORDER BY v) FROM t;
>         v       #2
>         ------------------------------------------------------------------------------
>         a       1
>         a       1
>         a       1
>         b       4
>         c       5
>         c       5
>         d       7
>         e       8
>         Selected records: 8

> The `DENSE_RANK()` function calculates a rank with no gaps, i.e. “dense”. For example:

>         SELECT v, DENSE_RANK() OVER(ORDER BY v) FROM t;
>         v       #2
>         ------------------------------------------------------------------------------
>         a       1
>         a       1
>         a       1
>         b       2
>         c       3
>         c       3
>         d       4
>         e       5
>         Selected records: 8

> The `PERCENT_RANK()` function calculates the relative rank of the current row to others in the partition. This function is used in statistical analysis to determine the percentile for a group of values. The actual formula used to calculate the `percent_rank` is:

>         PERCENT_RANK() = ( RANK() – 1 ) / ( TotalRows - 1 )

> To demonstrate this we create yet another table with the following script:

>         CREATE TABLE salaries(DepartmentID int, Salary int);
>         INSERT INTO salaries VALUES (1, 15000), (1, 18000), (1, 23000), (1, 23000), (1, 25000);

> The `RANK()` and `PERCENT_RANK()` functions are applied to the `Salary` column in the following query:

>         SELECT DepartmentID, Salary,
>         ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary ROWS BETWEEN UNBOUNDED
>         PRECEDING AND UNBOUNDED FOLLOWING) AS RowNumber,
>         RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary ROWS BETWEEN UNBOUNDED
>         PRECEDING AND UNBOUNDED FOLLOWING) AS RowRank,
>         PERCENT_RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary ROWS BETWEEN UNBOUNDED
>         PRECEDING AND UNBOUNDED FOLLOWING) AS PercentRank
>         FROM salaries;
>         DepartmentID    Salary  RowNumber RowRank PercentRank
>         ------------------------------------------------------------------------------
>         1       	 15000   1		1       0
>         1       	 18000   2		2       0.25
>         1       	 23000   3		3       0.5
>         1       	 23000   4		3       0.5
>         1       	 25000   5		5       1
>         Selected records: 5

> Here we have 4 different `Salary` values in 5 rows. The values returned by `PERCENT_RANK()` are computed as follows:

>         1.	RANK = 1 so PERCENT_RANK = ( 1 – 1 ) / 4 = 0
>         2.	RANK = 2 so PERCENT_RANK = ( 2 – 1 ) / 4 = 0.25
>         3.	RANK = 3 so PERCENT_RANK = ( 3 – 1 ) / 4 = 0.5
>         4.	RANK = 3 so PERCENT_RANK = ( 3 – 1 ) / 4 = 0.5
>         5.	RANK = 5 so PERCENT_RANK = ( 5 – 1 ) / 4 = 1

> The `NTILE()`function distributes result set rows into a specified number of groups. To demonstrate we will create yet another table with the following script:

>         CREATE TABLE students(StudentID char(2), Marks integer);
>         INSERT INTO students VALUES('S1', 75);
>         INSERT INTO students VALUES('S2', 83);
>         INSERT INTO students VALUES('S3', 91);
>         INSERT INTO students VALUES('S4', 83);
>         INSERT INTO students VALUES('S5', 93);

> We can apply the `NTILE()`function to this table with a script like the following:

>         SELECT StudentID, Marks, NTILE(2) OVER(ORDER BY Marks ROWS BETWEEN UNBOUNDED PRECEDING
>         AND UNBOUNDED FOLLOWING) FROM students;
>         StudentID      Marks   #3
>         ------------------------------------------------------------------------------
>         S1      	75      1
>         S2      	83      1
>         S4      	83      1
>         S3      	91      2
>         S5      	93      2
>         Selected records: 5

> Note that the group number `1` has three rows while group `2` has two simply because there is an uneven number of result rows.