Windowing Functions

Windowing functions operate on a group of related rows. We support the following SQL windowing functions:

CUME_DIST#

Syntax

CUME_DIST()

Description

Finds the cumulative distribution of a value with regard to other values within the same window partition.

Example

select
  O_CUSTKEY,
  O_ORDERSTATUS,
  CUME_DIST() OVER (PARTITION BY O_ORDERSTATUS order by O_TOTALPRICE)
as cume_dist
from
  ORDERS;

gives the following output:

O_CUSTKEY	O_ORDERSTATUS	CUME_DIST
723919	P	0.0011947431302270011
415031	P	0.0015929908403026682
1426144	P	0.001991238550378335
483598	P	0.0023894862604540022
226072	P	0.0027877339705296694

DENSE_RANK#

Syntax

DENSE_RANK()

Description

Returns the rank of a value within a group of values, without gaps in the ranks. The rank value starts at 1 and continues up sequentially. If two values are the same, they have the same rank.

Example

SELECT
  O_CUSTKEY,
  O_TOTALPRICE,
DENSE_RANK() OVER (ORDER BY O_TOTALPRICE ASC)
as dense_rank
from
  ORDERS;

gives the following output:

O_CUSTKEY	O_TOTALPRICE	DENSE_RANK
605161	921.58	1
1436941	927.13	2
337630	932.35	3
511	948.72	4
867835	974.76	5

FIRST_VALUE#

Syntax

FIRST_VALUE(<expr>)

Description

Returns the first value within an ordered group of values.

Example

SELECT
  COLUMN_1,
  COLUMN_2,
FIRST_VALUE() OVER (PARTITION BY COLUMN_1 ORDER BY COLUMN_2 nulls last)
as first_value
from values
  (1,10),(1,11),(1,12)
  (2,20),(2,21)
order by
  COLUMN_1,
  COLUMN_2;

gives the following output:

COLUMN_1	COLUMN_2	FIRST_VALUE
1	10	10
1	11	10
1	12	10
2	20	20
2	21	20

LAG#

Syntax

LAG(<expr> [, <offset>, <default>])

Description

Accesses data in a previous row in the same result set without having to join the table to itself.

LAST_VALUE#

Syntax

LAST_VALUE(<expr>)

Description

Returns the last value within an ordered group of values.

Example

SELECT
  COLUMN_1,
  COLUMN_2,
LAST_VALUE() OVER (PARTITION BY COLUMN_1 ORDER BY COLUMN_2)
as last_value
from values
(1,10),(1,11),(1,12)
(2,20),(2,21)
as last_value
from
  ORDERS;

gives the following output:

COLUMN_1	COLUMN_2	LAST_VALUE
1	10	12
1	11	12
1	12	12
2	20	21
2	21	21

LEAD#

Syntax

LEAD(<expr> [, <offset>, <default>])

Description

Accesses data in a subsequent row in the same result set.

Example

SELECT
  ID,
  YEAR,
  REVENUE,
LEAD(REVENUE) OVER (PARTITION BY ID ORDER BY YEAR)
as difference_to_next
from
  SALES;

ID	YEAR	DIFFERENCE_TO_NEXT
1	2010	500.00
2	2011	-1,000.00
3	2012	NULL
4	2013	250.00
5	2014	300.00

NTH_VALUE#

Syntax

NTH_VALUE(<expr>, <n>)

Description

Returns the nth value (up to 1000) within an ordered group of values.

Example

SELECT
  COLUMN_1,
  COLUMN_2,
NTH_VALUE (COLUMN_2, 2) OVER (PARTITION BY COLUMN_1 ORDER BY COLUMN_2)
from values
(1,10),(1,11),(1,12)
(2,20),(2,21)
as nth_value
from
  ORDERS;

COLUMN_1	COLUMN_2	NTH_VALUE
1	10	11
1	11	11
1	12	11
2	20	21
2	21	21

NTILE#

Syntax

NTILE(<constant_n>)

Description

Divides an ordered data set equally into the number of buckets. Buckets are sequentially numbered starting with 1.

Example

SELECT
  EXCHANGE,
  SYMBOL,
NTILE(4) OVER (PARTITION BY EXCHANGE ORDER BY SHARES)
as ntile
from
  DATA;

EXCHANGE	SYMBOL	NTILE
C	SPY	1
C	AAPL	2
C	AAPL	3
N	SPY	1
N	AAPL	1

PERCENT_RANK#

Syntax

PERCENT_RANK()

Description

Returns the relative rank of a value within a group of values, specified as a percentage ranging from 0.0 to 1.0.

Example

SELECT
  EXCHANGE,
  SYMBOL,
PERCENT_RANK() OVER (PARTITION BY EXCHANGE ORDER BY PRICE)
as percent_rank
from
  DATA;

EXCHANGE	SYMBOL	PERCENT_RANK
C	SPY	0.0
C	AAPL	0.5
C	AAPL	1.0
N	YHOO	0.0
N	QQQ	0.2

RANK#

Syntax

RANK()

Description

Returns the rank of a value within an ordered group of values. The rank value starts at 1 and continues up sequentially. If two values are the same, they have the same rank.

Example

SELECT
  O_CUSTKEY,
  O_TOTALPRICE,
RANK() OVER (ORDER BY O_TOTALPRICE DESC)
as rank
from
  ORDERS;

gives the following output:

O_CUSTKEY	O_TOTALPRICE	RANK
605161	921.58	1
1436941	927.13	2
337630	932.35	3
511	948.72	4
867835	974.76	5

ROW_NUMBER#

Syntax

ROW_NUMBER()

Description

Returns a unique row number for each row within a window partition. The row number starts at 1 and continues up sequentially.

Example

SELECT
  O_CUSTKEY,
  O_ORDERSTATUS,
RANK() OVER (ORDER BY O_ORDERSTATUS DESC)
as row_number
from
  ORDERS;

gives the following output:

O_CUSTKEY	O_ORDERSTATUS	ROW_NUMBER
605161	F	1
1436941	F	2
337630	F	3
511	F	4
867835	F	5