SQL window functions

This list includes window functions currently supported by Network Reporting.

avg

Returns the average (arithmetic mean) of the input expression values. The AVG function works with numeric values and ignores NULL values.

AVG ( [ALL ] expression ) OVER ( [ PARTITION BY expr_list ] )

count

Counts the rows defined by the expression. The COUNT function has two variations. COUNT(*) counts all the rows in the target table whether they include nulls or not. COUNT(expression) computes the number of rows with non-NULL values in a specific column or expression.

COUNT ( * | [ ALL ] expression) OVER ( [ PARTITION BY expr_list ] )

dense_rank

Determines the rank of a value in a group of values, based on the ORDER BY expression in the OVER clause. If the optional PARTITION BY clause is present, the rankings are reset for each group of rows. Rows with equal values for the ranking criteria receive the same rank. The DENSE_RANK function differs from RANK in one respect: If two or more rows tie, there is no gap in the sequence of ranked values. For example, if two rows are ranked 1, the next rank is 2.

DENSE_RANK () OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list ] )

first_value

Given an ordered set of rows, FIRST_VALUE returns the value of the specified expression with respect to the first row in the window frame.

FIRST_VALUE ( expression [ IGNORE NULLS | RESPECT NULLS ] ) OVER ( [ PARTITION BY expr_list ] )

lag

Returns the values for a row at a given offset above (before) the current row in the partition.

LAG (value_expr [, offset ]) [ IGNORE NULLS | RESPECT NULLS ] OVER ( [ PARTITION BY window_partition ] ORDER BY window_ordering )

last_value

Given an ordered set of rows, the LAST_VALUE function returns the value of the expression with respect to the last row in the frame.

LAST_VALUE ( expression [ IGNORE NULLS | RESPECT NULLS ] ) OVER ( [ PARTITION BY expr_list ] )

lead

Returns the values for a row at a given offset below (after) the current row in the partition.

LEAD (value_expr [, offset ]) [ IGNORE NULLS | RESPECT NULLS ] OVER ( [ PARTITION BY window_partition ] ORDER BY window_ordering )

max

The MAX window function returns the maximum of the input expression values. The MAX function works with numeric values and ignores NULL values.

MAX ( [ ALL ] expression ) OVER ( [ PARTITION BY expr_list ] )

median

Calculates the median value for the range of values in a window or partition. NULL values in the range are ignored. MEDIAN is an inverse distribution function that assumes a continuous distribution model.

MEDIAN ( median_expression ) OVER ( [ PARTITION BY partition_expression ] )

min

The MIN window function returns the minimum of the input expression values. The MIN function works with numeric values and ignores NULL values.

MIN ( [ ALL ] expression ) OVER ( [ PARTITION BY expr_list ] )

percent_rank

Calculates the percent rank of a given row. The percent rank is determined using this formula:(x - 1) / (the number of rows in the window or partition - 1) where x is the rank of the current row.

PERCENT_RANK () OVER ( [ PARTITION BY partition_expression ] [ ORDER BY order_list ] )

rank

Determines the rank of a value in a group of values, based on the ORDER BY expression in the OVER clause. If the optional PARTITION BY clause is present, the rankings are reset for each group of rows. Rows with equal values for the ranking criteria receive the same rank. Amazon Redshift adds the number of tied rows to the tied rank to calculate the next rank and thus the ranks might not be consecutive numbers. For example, if two rows are ranked 1, the next rank is 3.

RANK () OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list ] )

ratio_to_report

Calculates the ratio of a value to the sum of the values in a window or partition. The ratio to report value is determined using the formula:

value of ratio_expression argument for the current row / sum of ratio_expression argument for the window or partition

RATIO_TO_REPORT ( ratio_expression ) OVER ( [ PARTITION BY partition_expression ] )

row_number

Determines the ordinal number of the current row within a group of rows, counting from 1, based on the ORDER BY expression in the OVER clause. If the optional PARTITION BY clause is present, the ordinal numbers are reset for each group of rows. Rows with equal values for the ORDER BY expressions receive the different row numbers nondeterministically.

ROW_NUMBER () OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list ] )

sum

The SUM window function returns the sum of the input column or expression values. The SUM function works with numeric values and ignores NULL values.

SUM ( [ ALL ] expression ) OVER ( [ PARTITION BY expr_list ] )