Window Functions

Window functions apply aggregate and ranking functions over a particular window (set of rows). It achieves the following objective.

• Partition rows into sets of rows. (PARTITION BY clause)

• Order rows within those partitions into a particular order. (ORDER BY clause)

Count

Count function applied to calculate count over a particular window (set of rows) .

Rolling Sum

Sum function applied to calculate sum over a particular window (set of rows) .

Rolling Average

Average function applied to calculate mean over a particular window (set of rows) .

Rolling Min

Min function applied to calculate min over a particular window (set of rows) .

Rolling Max

Function applied to calculate max over a particular window (set of rows) .

Standard Deviation

Standard deviation function applied to calculate standard deviation over a particular window (set of rows) .

Rolling Number

It assigns consecutive integers to all the rows within the partition. Within a partition, no two rows can have the same row number.

Rank

Rank function assigns rank to all the rows within each partition.

De-Duplicate

Function removes duplicate values from all the rows within each partition.

First Value

This function can be used to retrieve the first entry in an ordered group of values.

Last Value

Function can be used to retrieve the last entry in an ordered group of values

Next

Function can be used to retrieve the next entry in an ordered group of values

Previous

Function can be used to retrieve the previous entry in an ordered group of values