Window functions

By using window functions, you can enable your users to create analytic business queries more efficiently. Window functions operate on a partition or "window" of a result set, and return a value for every row in that window. In contrast, nonwindowed functions perform their calculations with respect to every row in the result set. Unlike group functions that aggregate result rows, all rows in the table expression are retained.

The values returned are calculated by using values from the sets of rows in that window. For each row in the table, the window defines a set of rows that is used to compute additional attributes. A window is defined using a window specification (the OVER clause), and is based on three main concepts:

• Window partitioning, which forms groups of rows (PARTITION clause)

• Window ordering, which defines an order or sequence of rows within each partition (ORDER BY clause)

• Window frames, which are defined relative to each row to further restrict the set of rows (ROWS specification)

Window functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the window functions are processed. Therefore, window functions can appear only in the select list or ORDER BY clause. You can use multiple window functions within a single query with different frame clauses. You can also use window functions in other scalar expressions, such as CASE.

Amazon Redshift supports two types of window functions: aggregate and ranking.

These are the supported aggregate functions:

• AVG

• COUNT

• CUME_DIST

• FIRST_VALUE

• LAG

• LAST_VALUE

• LEAD

• MAX

• MEDIAN

• MIN

• NTH_VALUE

• PERCENTILE_CONT

• PERCENTILE_DISC

• RATIO_TO_REPORT

• STDDEV_POP

• STDDEV_SAMP (synonym for STDDEV)

• SUM

• VAR_POP

• VAR_SAMP (synonym for VARIANCE)

These are the supported ranking functions:

• DENSE_RANK

• NTILE

• PERCENT_RANK

• RANK

• ROW_NUMBER

Topics

• Window function syntax summary
• Unique ordering of data for window functions
• Overview example for window functions
• AVG window function
• COUNT window function
• CUME_DIST window function
• DENSE_RANK window function
• FIRST_VALUE and LAST_VALUE window functions
• LAG window function
• LEAD window function
• LISTAGG window function
• MAX window function
• MEDIAN window function
• MIN window function
• NTH_VALUE window function
• NTILE window function
• PERCENT_RANK window function
• PERCENTILE_CONT window function
• PERCENTILE_DISC window function
• RANK window function
• RATIO_TO_REPORT window function
• ROW_NUMBER window function
• STDDEV_SAMP and STDDEV_POP window functions
• SUM window function
• VAR_SAMP and VAR_POP window functions

Overview example for window functions

Following, you can find an overview example demonstrating how to work with the window functions. You can also find specific code examples with each function description.

Some of the window function examples use a table named WINSALES, which contains 11 rows, as shown following.

SALESID	DATEID	SELLERID	BUYERID	QTY	QTY_SHIPPED
30001	8/2/2003	3	B	10	10
10001	12/24/2003	1	C	10	10
10005	12/24/2003	1	A	30
40001	1/9/2004	4	A	40
10006	1/18/2004	1	C	10
20001	2/12/2004	2	B	20	20
40005	2/12/2004	4	A	10	10
20002	2/16/2004	2	C	20	20
30003	4/18/2004	3	B	15
30004	4/18/2004	3	B	20
30007	9/7/2004	3	C	30

The following script creates and populates the sample WINSALES table.

create table winsales(
salesid int,
dateid date,
sellerid int,
buyerid char(10),
qty int,
qty_shipped int);

insert into winsales values
(30001, '8/2/2003', 3, 'b', 10, 10),
(10001, '12/24/2003', 1, 'c', 10, 10),
(10005, '12/24/2003', 1, 'a', 30, null),	
(40001, '1/9/2004', 4, 'a', 40, null),	
(10006, '1/18/2004', 1, 'c', 10, null),	
(20001, '2/12/2004', 2, 'b', 20, 20),
(40005, '2/12/2004', 4, 'a', 10, 10),
(20002, '2/16/2004', 2, 'c', 20, 20),
(30003, '4/18/2004', 3, 'b', 15, null),
(30004, '4/18/2004', 3, 'b', 20, null),	
(30007, '9/7/2004', 3, 'c', 30, null);