Window
Overview
Window functions is a group of SQL functions, that operate on a partition or “window” of a result set, returning values for every row within that window. The following window functions and clauses are currently supported by Oxla:
Window Functions
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 |
Ranking Functions
| 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 |
Distribution Functions
| 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 |
Value Functions
| 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 |
Window Clause
| 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 |
Important Notes
There are a few essential things to remember when using window functions in Oxla:
- Verify that you can effectively use window functions alongside the
PARTITION BY,ORDER BYandFRAMEclauses as part of your window specification - Ensure the window specification chaining is supported by executing the following command:
SELECT SUM(i0) OVER w2 FROM tb1 WINDOW w1 AS (PARTITION BY i1), w2 AS (w1 ROWS CURRENT ROW) - The
FRAMEclause of the window specification is restricted to theROWSclause and does not include frame exclusion