List of window functions in YQL
The syntax for calling window functions is detailed in a separate article.
Aggregate functions
All the aggregate functions can also be used as window functions. In this case, each row includes an aggregation result obtained on a set of rows from the window frame.
Examples
SELECT
SUM(int_column) OVER w1 AS running_total,
SUM(int_column) OVER w2 AS total,
FROM my_table
WINDOW
w1 AS (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
w2 AS ();
ROW_NUMBER
Row number within a partition. Without arguments.
Signature
ROW_NUMBER()->Uint64
Examples
SELECT
ROW_NUMBER() OVER w AS row_num
FROM my_table
WINDOW w AS (ORDER BY key);
LAG / LEAD
Accessing a value from the partition row that is behind (LAG
) or ahead (LEAD
) of the current partition row by a fixed number of rows. The first argument specifies the expression to be accessed, and the second argument specifies the offset in rows. You may omit the offset. By default, the neighbor row is used: the previous or next, respectively (hence, 1 is assumed by default). For the rows having no neighbors at a given distance (for example, LAG(expr, 3)
in the first and second rows of the partition), NULL
is returned.
Signature
LEAD(T[,Int32])->T?
LAG(T[,Int32])->T?
Examples
SELECT
int_value - LAG(int_value) OVER w AS int_value_diff
FROM my_table
WINDOW w AS (ORDER BY key);
SELECT item, odd, LAG(item, 1) OVER w as lag1 FROM (
SELECT item, item % 2 as odd FROM (
SELECT AsList(1, 2, 3, 4, 5, 6, 7) as item
)
FLATTEN BY item
)
WINDOW w As (
PARTITION BY odd
ORDER BY item
);
/* Output:
item odd lag1
--------------------
2 0 NULL
4 0 2
6 0 4
1 1 NULL
3 1 1
5 1 3
7 1 5
*/
FIRST_VALUE / LAST_VALUE
Accessing the values from the first and last rows of the window border (in the order of the window's ORDER BY
). The only argument is the expression that you need to access.
Optionally, OVER
can be preceded by the additional modifier IGNORE NULLS
. It changes the behavior of functions to the first or last non-empty (i.e., non-NULL
) value among the window frame rows. The antonym of this modifier is RESPECT NULLS
: it's the default behavior that can be omitted.
Signature
FIRST_VALUE(T)->T?
LAST_VALUE(T)->T?
Examples
SELECT
FIRST_VALUE(my_column) OVER w
FROM my_table
WINDOW w AS (ORDER BY key);
SELECT
LAST_VALUE(my_column) IGNORE NULLS OVER w
FROM my_table
WINDOW w AS (ORDER BY key);
NTH_VALUE
Accessing values from the specified row within the window frame (in the order of the window's ORDER BY
). The arguments are the expression to access and the row number starting with 1.
Optionally, OVER
can be preceded by the additional modifier IGNORE NULLS
, which lets you skip rows with NULL in the first argument's value. The antonym of this modifier is RESPECT NULLS
: it's the default behavior that can be omitted.
Signature
NTH_VALUE(T,N)->T?
Examples
SELECT
NTH_VALUE(my_column, 2) OVER w
FROM my_table
WINDOW w AS (ORDER BY key);
SELECT
NTH_VALUE(my_column, 3) IGNORE NULLS OVER w
FROM my_table
WINDOW w AS (ORDER BY key);
RANK / DENSE_RANK / PERCENT_RANK
Number the groups of neighboring partition rows having the same expression value in the argument. DENSE_RANK
numbers the groups one-by-one, and RANK
skips (N - 1)
values, with N
being the number of rows in the previous group. PERCENT_RANK
returns the relative rank of the current row: (RANK - 1)/(number of rows in the partition - 1)
.
If there is no argument, it uses the order specified in the ORDER BY
section in the window definition.
If the argument is omitted and ORDER BY
is not specified, then all rows are considered equal to each other.
Note
Passing an argument to RANK
/DENSE_RANK
/PERCENT_RANK
is a non-standard extension in YQL.
Signature
RANK([T])->Uint64
DENSE_RANK([T])->Uint64
PERCENT_RANK([T])->Double
Examples
SELECT
RANK(my_column) OVER w
FROM my_table
WINDOW w AS (ORDER BY key);
SELECT
DENSE_RANK() OVER w
FROM my_table
WINDOW w AS (ORDER BY my_column);
SELECT
PERCENT_RANK() OVER w
FROM my_table
WINDOW w AS (ORDER BY my_column);
NTILE
Distributes the rows of an ordered partition into a specified number of groups. The groups are numbered starting with 1. For each row, the NTILE function returns the number of the group the row belongs to.
Signature
NTILE(Uint64)->Uint64
Examples
SELECT
NTILE(10) OVER w AS group_num
FROM my_table
WINDOW w AS (ORDER BY key);
CUME_DIST
Returns the relative position (> 0 and <= 1) of a row within a partition. Without arguments.
Signature
CUME_DIST()->Double
Examples
SELECT
CUME_DIST() OVER w AS dist
FROM my_table
WINDOW w AS (ORDER BY key);
SessionState()
A non-standard window function SessionState()
(without arguments) lets you get the session calculation status from SessionWindow for the current row.
It's allowed only if SessionWindow()
is present in the PARTITION BY
section in the window definition.