Window

SQL Window Functions

Index

Row Number Functions

• ROW_NUMBER

Rank Functions

• RANK

• PERCENT_RANK

• DENSE_RANK

• CUME_DIST

Lead or Lag Functions

• LEAD

• LAG

Nth Value Functions

• FIRST_VALUE

• LAST_VALUE

• NTH_VALUE

Other Window Functions

• NTILE

• RATIO_TO_REPORT

Details

Row Number Functions

ROW_NUMBER

ROW_NUMBER() OVER windowNameOrSpecification

Return the number of the current row starting with 1. Window frame clause is not allowed for this function.

Example:

SELECT ROW_NUMBER() OVER (), * FROM TEST;
SELECT ROW_NUMBER() OVER (ORDER BY ID), * FROM TEST;
SELECT ROW_NUMBER() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;

Rank Functions

RANK

RANK() OVER windowNameOrSpecification

Returns the rank of the current row. The rank of a row is the number of rows that precede this row plus 1. If two or more rows have the same values in ORDER BY columns, these rows get the same rank from the first row with same values. It means that gaps in ranks are possible. This function requires window order clause. Window frame clause is not allowed for this function.

See Rank aggregate for a hypothetical set function with the same name.

Example:

SELECT RANK() OVER (ORDER BY ID), * FROM TEST;
SELECT RANK() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;

DENSE_RANK

RANK() OVER windowNameOrSpecification

Return the dense rank of the current row. The rank of a row is the number of groups of rows with the same values in ORDER BY columns that precede group with row plug 1. If two or more rows have the same values in ORDER BY columns, these rows get the same rank. Gaps in ranks are not possible. This function requires window order clause. Window frame clause is not allowed for this function.

See DENSE_RANK aggregate for a hypothetical set function with the same name.

Example:

SELECT DENSE_RANK() OVER (ORDER BY ID), * FROM TEST;
SELECT DENSE_RANK() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;

PERCENT_RANK

PERCENT_RANK() OVER windowNameOrSpecification

Returns the relative rank of the current row. The relative rank is calculated as (RANK - 1) / (NR - 1), where RANK is a rank of the row and NR is a number of rows in window partition with this row. Note that result is always 0 if window order clause is not specified. Window frame clause is not allowed for this function.

See PERENT_RANK aggregate for a hypothetical set function with the same name.

Example:

SELECT PERCENT_RANK() OVER (ORDER BY ID), * FROM TEST;
SELECT PERCENT_RANK() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;

CUME_DIST

CUME_DIST() OVER windowNameOrSpecification

Returns the relative rank of the current row. The relative rank is calculated as NP / NR where NP is a number of rows that precede the current row or have the same values in ORDER BY columns and NR is a number of rows in window partition with this row. Note that result is always 1 if window order clause is not specified. Window frame clause is not allowed for this function.

Example:

SELECT CUME_DIST() OVER (ORDER BY ID), * FROM TEST; 
SELECT CUME_DIST() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;

Lead or Lag Functions

LEAD

LEAD(value [, offsetInt [, defaultValue]]) [{RESPECT|IGNORE} NULLS]
OVER windowNameOrSpecification

TODO

LAG

LAG(value [, offsetInt [, defaultValue]]) [{RESPECT|IGNORE} NULLS]
OVER windowNameOrSpecification

TODO

Nth Value Functions

FIRST_VALUE

FIRST_VALUE(value) [{RESPECT|IGNORE} NULLS]
OVER windowNameOrSpecification

TODO

LAST_VALUE

LAST_VALUE(value) [{RESPECT|IGNORE} NULLS]
OVER windowNameOrSpecification

TODO

NTH_VALUE

NTH_VALUE(value, nInt) [FROM {FIRST|LAST}] [{RESPECT|IGNORE} NULLS]
OVER windowNameOrSpecification

TODO

Other Window Functions

NTILE

NTILE(long) OVER WindowNameOrSpecification

Distributes the rows into a specified number of groups. The Number of groups should be a positive long value. NTLE returns the 1-based number of the group to which the current row belongs. First groups will have more rows if the number of rows is not divisible by number of groups. For example, if 5 rows are distributed into 2 groups this function returns 1 for the first 3 rows and 2 for the last rows. This function requires window order clause. Window frame clause is not allowed for this function.

Example:

SELECT NTILE(10) OVER (ORDER BY ID), * FROM TEST;
SELECT NTILE(10) OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;

RATIO_TO_REPORT

RATIO_TO_REPORT(VALUE)
OVER WindowNameOrSpecification

Returns the ratio of a value to the sum of all values. If the argument is NULL or the sum of all values is 0, then the value of the function is NULL. Window ordering and window frame clauses are not allowed for this function.

Note: Window function in Guinsoo may require a lot of memory for large queries.

Example:

SELECT X, RATIO_TO_REPORT(X) OVER (PARTITION BY CATEGORY), CATEGORY FROM TEST;