Function Name | Description |
---|---|
COUNT | Counts all the rows or those specified by the given expression |
AVG | Calculates the average (arithmetic mean) of a set of numeric values within a window |
SUM | Calculates and returns the sum of values from the input column or expression values |
MIN | Computes the minimum value of an expression across a set of rows |
MAX | Computes the maximum value of an expression across a set of rows |
BOOL_AND | Evaluates whether all values within a specified window of rows are true |
BOOL_OR | Evaluates whether at least one value within a specified window of rows is true |
Function Name | Description |
---|---|
ROW_NUMBER | Returns the current row index within its partition (beginning with 1) |
RANK | Calculates and returns the rank of a value within a specified group of values |
DENSE_RANK | Calculates the percent rank of a value within a group and returns the result |
NTILE | Divides an ordered data set into a specified number of approximately equal groups |
Function Name | Description |
---|---|
CUME_DIST | Calculates the cumulative distribution of a value within a set of values |
PERCENT_RANK | Calculates and returns the percent rank of a value within a specified group of values |
Function Name | Description |
---|---|
FIRST_VALUE | Returns the first value in an ordered set of values within a specified partition |
LAST_VALUE | Returns the last value in an ordered set of values within a specified partition |
NTH_VALUE | Returns a value from the nth row in an ordered partition of a result set |
LAG | Returns the values for a row located at a defined offset, either above or below the current row within the partition |
LEAD | Returns the values for a row located at a defined offset, either above or below the current row within the partition |
Clause Name | Description |
---|---|
OVER | Defines the window specification and is mandatory for window functions |
WINDOW | Optional clause that defines one or more named window specifications |
PARTITION BY
, ORDER BY
and FRAME
clauses as part of your window specificationSELECT SUM(i0) OVER w2 FROM tb1 WINDOW w1 AS (PARTITION BY i1), w2 AS (w1 ROWS CURRENT ROW)
FRAME
clause of the window specification is restricted to the ROWS
clause and does not include frame exclusion