Window#
Function |
Description |
|---|---|
Returns the fraction of rows in the window partition that come before or are tied with the current row. |
|
Returns the rank of each row within a window partition, without gaps for ties. |
|
Returns the first value in an ordered set of values with respect to the window declared in |
|
Returns the value of a column at a given offset before the current row within a window partition. |
|
Returns the last value in an ordered set of values with respect to the window declared in |
|
Returns the value of a column at a given offset after the current row within a window partition. |
|
Returns the value at a given row of the window frame. |
|
Splits the rows of a window partition into a number of buckets and returns the bucket number. |
|
Define a window (a set of rows) within which a function is applied. |
|
Returns the relative rank of each row within a window partition, from 0 to 1. |
|
Returns the rank of each row within a window partition, with gaps for ties. |
|
Returns the sequential row number within a window partition, starting from 1. |
Note
Without a frame clause, a window with ORDER BY uses the frame
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, as in other databases: rows with equal
ORDER BY values (peers) get the same result. Without ORDER BY, the frame is the whole
partition. See OVER for the supported frames.
Note
Rows that have equal values for the ORDER BY of a window (peers) may be processed in any
order. Functions such as ROW_NUMBER, LAG, LEAD, FIRST_VALUE, LAST_VALUE,
NTH_VALUE and aggregates with a ROWS frame can then give tied rows different results
between runs. Add columns to the ORDER BY to make
the order unique.
Note
In a query with GROUP BY, or with aggregates and no GROUP BY, window functions run on
the aggregated rows. Their arguments, PARTITION BY and ORDER BY can use group keys and
aggregates, as in RANK() OVER (ORDER BY SUM(x) DESC) or SUM(COUNT(*)) OVER ().
CUME_DIST#
Returns the fraction of rows in the window partition that come before the current row or are
tied with it: (rows up to and including the last tied row) / (rows in partition).
Requirements:
Must be used with an
OVERclause.Without
ORDER BYin the window specification all rows are tied, so the result is 1.
Example:
df = pl.DataFrame({
"id": [1, 2, 3, 4, 5, 6],
"category": ["A", "A", "A", "B", "B", "B"],
"score": [85, 90, 90, 75, 80, 80]
})
df.sql("""
SELECT
id,
category,
score,
PERCENT_RANK() OVER (PARTITION BY category ORDER BY score DESC) AS pct_rank,
CUME_DIST() OVER (PARTITION BY category ORDER BY score DESC) AS cume_dist
FROM self
ORDER BY category, score DESC, id
""")
# shape: (6, 5)
# ┌─────┬──────────┬───────┬──────────┬───────────┐
# │ id ┆ category ┆ score ┆ pct_rank ┆ cume_dist │
# │ --- ┆ --- ┆ --- ┆ --- ┆ --- │
# │ i64 ┆ str ┆ i64 ┆ f64 ┆ f64 │
# ╞═════╪══════════╪═══════╪══════════╪═══════════╡
# │ 2 ┆ A ┆ 90 ┆ 0.0 ┆ 0.666667 │
# │ 3 ┆ A ┆ 90 ┆ 0.0 ┆ 0.666667 │
# │ 1 ┆ A ┆ 85 ┆ 1.0 ┆ 1.0 │
# │ 5 ┆ B ┆ 80 ┆ 0.0 ┆ 0.666667 │
# │ 6 ┆ B ┆ 80 ┆ 0.0 ┆ 0.666667 │
# │ 4 ┆ B ┆ 75 ┆ 1.0 ┆ 1.0 │
# └─────┴──────────┴───────┴──────────┴───────────┘
DENSE_RANK#
Returns the rank of each row within a window partition, without gaps for ties. Rows with equal values receive the same rank, and the next rank number is consecutive (no gaps).
Requirements:
Must be used with an
OVERclause.Without
ORDER BYin the window specification all rows are tied, so every rank is 1.
Example:
df = pl.DataFrame({
"id": [1, 2, 3, 4, 5, 6],
"category": ["A", "A", "A", "B", "B", "B"],
"score": [85, 90, 90, 75, 80, 80]
})
df.sql("""
SELECT
id,
category,
score,
RANK() OVER (PARTITION BY category ORDER BY score DESC) AS rank,
DENSE_RANK() OVER (PARTITION BY category ORDER BY score DESC) AS dense_rank
FROM self
ORDER BY category, score DESC
""")
# shape: (6, 5)
# ┌─────┬──────────┬───────┬──────┬────────────┐
# │ id ┆ category ┆ score ┆ rank ┆ dense_rank │
# │ --- ┆ --- ┆ --- ┆ --- ┆ --- │
# │ i64 ┆ str ┆ i64 ┆ i64 ┆ i64 │
# ╞═════╪══════════╪═══════╪══════╪════════════╡
# │ 2 ┆ A ┆ 90 ┆ 1 ┆ 1 │
# │ 3 ┆ A ┆ 90 ┆ 1 ┆ 1 │
# │ 1 ┆ A ┆ 85 ┆ 3 ┆ 2 │
# │ 5 ┆ B ┆ 80 ┆ 1 ┆ 1 │
# │ 6 ┆ B ┆ 80 ┆ 1 ┆ 1 │
# │ 4 ┆ B ┆ 75 ┆ 3 ┆ 2 │
# └─────┴──────────┴───────┴──────┴────────────┘
FIRST_VALUE#
Returns the value at the first row of the window frame, or NULL if the frame is empty. See NTH_VALUE for an example.
LAG#
Returns the value of a column at a given offset before the current row within a window partition. If the offset goes beyond the partition boundary, NULL is returned.
Syntax:
LAG(expr) OVER (...)- offset defaults to 1.LAG(expr, n) OVER (...)- offset ofnrows.ncan be 0 (the current row) or negative (rows after the current row).LAG(expr, n, default) OVER (...)- returnsdefaultinstead of NULL when the offset goes beyond the partition boundary.
Requirements:
Must be used with an
OVERclause.That clause must have
ORDER BYin the window specification.
Example:
df = pl.DataFrame({
"id": [1, 2, 3, 4, 5, 6],
"category": ["A", "A", "A", "B", "B", "B"],
"value": [10, 20, 30, 40, 50, 60],
})
df.sql("""
SELECT
id,
category,
value,
LAG(value) OVER (PARTITION BY category ORDER BY id) AS prev_value,
LAG(value, 2) OVER (PARTITION BY category ORDER BY id) AS prev2_value
FROM self
ORDER BY category, id
""")
# shape: (6, 5)
# ┌─────┬──────────┬───────┬────────────┬─────────────┐
# │ id ┆ category ┆ value ┆ prev_value ┆ prev2_value │
# │ --- ┆ --- ┆ --- ┆ --- ┆ --- │
# │ i64 ┆ str ┆ i64 ┆ i64 ┆ i64 │
# ╞═════╪══════════╪═══════╪════════════╪═════════════╡
# │ 1 ┆ A ┆ 10 ┆ null ┆ null │
# │ 2 ┆ A ┆ 20 ┆ 10 ┆ null │
# │ 3 ┆ A ┆ 30 ┆ 20 ┆ 10 │
# │ 4 ┆ B ┆ 40 ┆ null ┆ null │
# │ 5 ┆ B ┆ 50 ┆ 40 ┆ null │
# │ 6 ┆ B ┆ 60 ┆ 50 ┆ 40 │
# └─────┴──────────┴───────┴────────────┴─────────────┘
LAST_VALUE#
Returns the value at the last row of the window frame, or NULL if the frame is empty. With
ORDER BY and no frame clause, the frame ends at the last row tied with the current row. See
NTH_VALUE for an example.
LEAD#
Returns the value of a column at a given offset after the current row within a window partition. If the offset goes beyond the partition boundary, NULL is returned.
Syntax:
LEAD(expr) OVER (...)- offset defaults to 1.LEAD(expr, n) OVER (...)- offset ofnrows.ncan be 0 (the current row) or negative (rows before the current row).LEAD(expr, n, default) OVER (...)- returnsdefaultinstead of NULL when the offset goes beyond the partition boundary.
Requirements:
Must be used with an
OVERclause.That clause must have
ORDER BYin the window specification.
Example:
df = pl.DataFrame({
"id": [1, 2, 3, 4, 5, 6],
"category": ["A", "A", "A", "B", "B", "B"],
"value": [10, 20, 30, 40, 50, 60],
})
df.sql("""
SELECT
id,
category,
value,
LEAD(value) OVER (PARTITION BY category ORDER BY id) AS next_value,
LEAD(value, 2) OVER (PARTITION BY category ORDER BY id) AS next2_value
FROM self
ORDER BY category, id
""")
# shape: (6, 5)
# ┌─────┬──────────┬───────┬────────────┬─────────────┐
# │ id ┆ category ┆ value ┆ next_value ┆ next2_value │
# │ --- ┆ --- ┆ --- ┆ --- ┆ --- │
# │ i64 ┆ str ┆ i64 ┆ i64 ┆ i64 │
# ╞═════╪══════════╪═══════╪════════════╪═════════════╡
# │ 1 ┆ A ┆ 10 ┆ 20 ┆ 30 │
# │ 2 ┆ A ┆ 20 ┆ 30 ┆ null │
# │ 3 ┆ A ┆ 30 ┆ null ┆ null │
# │ 4 ┆ B ┆ 40 ┆ 50 ┆ 60 │
# │ 5 ┆ B ┆ 50 ┆ 60 ┆ null │
# │ 6 ┆ B ┆ 60 ┆ null ┆ null │
# └─────┴──────────┴───────┴────────────┴─────────────┘
NTH_VALUE#
Returns the value at row n of the window frame, counting from 1, or NULL if the frame has
fewer than n rows.
Syntax:
NTH_VALUE(expr, n) OVER (...)-nmust be a positive integer literal.
Example:
df = pl.DataFrame({
"id": [1, 2, 3, 4, 5, 6],
"category": ["A", "A", "A", "B", "B", "B"],
"value": [10, 20, 30, 40, 50, 60],
})
df.sql("""
SELECT
id,
category,
value,
FIRST_VALUE(value) OVER w AS first_val,
LAST_VALUE(value) OVER w AS last_val,
NTH_VALUE(value, 2) OVER w AS second_val
FROM self
WINDOW w AS (
PARTITION BY category ORDER BY id
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)
ORDER BY category, id
""")
# shape: (6, 6)
# ┌─────┬──────────┬───────┬───────────┬──────────┬────────────┐
# │ id ┆ category ┆ value ┆ first_val ┆ last_val ┆ second_val │
# │ --- ┆ --- ┆ --- ┆ --- ┆ --- ┆ --- │
# │ i64 ┆ str ┆ i64 ┆ i64 ┆ i64 ┆ i64 │
# ╞═════╪══════════╪═══════╪═══════════╪══════════╪════════════╡
# │ 1 ┆ A ┆ 10 ┆ 10 ┆ 20 ┆ 20 │
# │ 2 ┆ A ┆ 20 ┆ 10 ┆ 30 ┆ 20 │
# │ 3 ┆ A ┆ 30 ┆ 20 ┆ 30 ┆ 30 │
# │ 4 ┆ B ┆ 40 ┆ 40 ┆ 50 ┆ 50 │
# │ 5 ┆ B ┆ 50 ┆ 40 ┆ 60 ┆ 50 │
# │ 6 ┆ B ┆ 60 ┆ 50 ┆ 60 ┆ 60 │
# └─────┴──────────┴───────┴───────────┴──────────┴────────────┘
NTILE#
Splits the rows of a window partition into n buckets of near-equal size, in the order of the
window’s ORDER BY, and returns the bucket number of each row, starting from 1. When the rows
don’t split evenly, the first buckets get one more row.
Syntax:
NTILE(n) OVER (...)-nmust be a positive integer literal.
Example:
df = pl.DataFrame({
"id": [1, 2, 3, 4, 5, 6, 7],
"value": [10, 20, 30, 40, 50, 60, 70],
})
df.sql("""
SELECT
id,
value,
NTILE(3) OVER (ORDER BY value) AS bucket
FROM self
ORDER BY id
""")
# shape: (7, 3)
# ┌─────┬───────┬────────┐
# │ id ┆ value ┆ bucket │
# │ --- ┆ --- ┆ --- │
# │ i64 ┆ i64 ┆ i64 │
# ╞═════╪═══════╪════════╡
# │ 1 ┆ 10 ┆ 1 │
# │ 2 ┆ 20 ┆ 1 │
# │ 3 ┆ 30 ┆ 1 │
# │ 4 ┆ 40 ┆ 2 │
# │ 5 ┆ 50 ┆ 2 │
# │ 6 ┆ 60 ┆ 3 │
# │ 7 ┆ 70 ┆ 3 │
# └─────┴───────┴────────┘
PERCENT_RANK#
Returns the relative rank of each row within a window partition:
(rank - 1) / (rows in partition - 1), or 0 when the partition has one row. Tied rows get the
same value. See CUME_DIST for an example.
Requirements:
Must be used with an
OVERclause.Without
ORDER BYin the window specification all rows are tied, so the result is 0.
RANK#
Returns the rank of each row within a window partition, with gaps for ties. Rows with equal values receive the same rank, and the next rank skips numbers (creating gaps).
Requirements:
Must be used with an
OVERclause.Without
ORDER BYin the window specification all rows are tied, so every rank is 1.
Example:
df = pl.DataFrame({
"id": [1, 2, 3, 4, 5, 6],
"category": ["A", "A", "A", "B", "B", "B"],
"score": [85, 90, 90, 75, 80, 80]
})
df.sql("""
SELECT
id,
category,
score,
DENSE_RANK() OVER (PARTITION BY category ORDER BY score DESC) AS dense_rank,
RANK() OVER (PARTITION BY category ORDER BY score DESC) AS rank
FROM self
ORDER BY category, score DESC
""")
# shape: (6, 5)
# ┌─────┬──────────┬───────┬────────────┬──────┐
# │ id ┆ category ┆ score ┆ dense_rank ┆ rank │
# │ --- ┆ --- ┆ --- ┆ --- ┆ --- │
# │ i64 ┆ str ┆ i64 ┆ i64 ┆ i64 │
# ╞═════╪══════════╪═══════╪════════════╪══════╡
# │ 2 ┆ A ┆ 90 ┆ 1 ┆ 1 │
# │ 3 ┆ A ┆ 90 ┆ 1 ┆ 1 │
# │ 1 ┆ A ┆ 85 ┆ 2 ┆ 3 │
# │ 5 ┆ B ┆ 80 ┆ 1 ┆ 1 │
# │ 6 ┆ B ┆ 80 ┆ 1 ┆ 1 │
# │ 4 ┆ B ┆ 75 ┆ 2 ┆ 3 │
# └─────┴──────────┴───────┴────────────┴──────┘
ROW_NUMBER#
Returns the sequential row number, optionally within a window partition, starting from 1. Unlike
RANK and DENSE_RANK, ROW_NUMBER always returns unique numbers even when values are tied.
Example:
df = pl.DataFrame({
"id": [1, 2, 3, 4, 5, 6],
"category": ["A", "A", "A", "B", "B", "B"],
"value": [100, 200, 200, 150, 300, 150]
})
df.sql("""
SELECT
ROW_NUMBER() AS x,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY id) AS y,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY id DESC) AS z,
category,
value
FROM self
ORDER BY category, id
""")
# shape: (6, 5)
# ┌─────┬─────┬─────┬──────────┬───────┐
# │ x ┆ y ┆ z ┆ category ┆ value │
# │ --- ┆ --- ┆ --- ┆ --- ┆ --- │
# │ i64 ┆ i64 ┆ i64 ┆ str ┆ i64 │
# ╞═════╪═════╪═════╪══════════╪═══════╡
# │ 1 ┆ 1 ┆ 3 ┆ A ┆ 100 │
# │ 2 ┆ 2 ┆ 2 ┆ A ┆ 200 │
# │ 3 ┆ 3 ┆ 1 ┆ A ┆ 200 │
# │ 4 ┆ 1 ┆ 3 ┆ B ┆ 150 │
# │ 5 ┆ 2 ┆ 2 ┆ B ┆ 300 │
# │ 6 ┆ 3 ┆ 1 ┆ B ┆ 150 │
# └─────┴─────┴─────┴──────────┴───────┘
OVER#
Used to define a window (a set of rows) within which a function is applied.
Notes:
Without a frame clause, a window with ORDER BY uses the frame
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: rows with equal ORDER BY
values (peers) get the same result. Without ORDER BY, the frame is the whole partition.
SUM, COUNT, MIN, MAX, AVG and TOTAL support these frames, with
ROWS, RANGE or GROUPS:
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWBETWEEN CURRENT ROW AND UNBOUNDED FOLLOWINGBETWEEN CURRENT ROW AND CURRENT ROWBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGROWS BETWEEN <n> PRECEDING AND CURRENT ROW(not forMINandMAXof strings, orSUM,MINandMAXof decimals)
FIRST_VALUE, LAST_VALUE and NTH_VALUE support these frames, and ROWS frames
that start or end <n> PRECEDING or <n> FOLLOWING. With RANGE or GROUPS,
FIRST_VALUE also supports BETWEEN UNBOUNDED PRECEDING AND <n> FOLLOWING and
BETWEEN CURRENT ROW AND <n> FOLLOWING, and LAST_VALUE supports
BETWEEN <n> PRECEDING AND CURRENT ROW and BETWEEN <n> PRECEDING AND UNBOUNDED FOLLOWING.
Other frames raise an error.
Example:
df = pl.DataFrame(
{
"idx": [0, 1, 2, 3, 4, 5, 6],
"label": ["aaa", "aaa", "bbb", "bbb", "aaa", "ccc", "aaa"],
"value": [10, 20, 30, 40, 50, -5, 0],
}
)
df.sql("""
SELECT
*,
FIRST_VALUE(value) OVER (
PARTITION BY label ORDER BY idx
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS first_val,
LAST_VALUE(value) OVER (
PARTITION BY label ORDER BY idx
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS last_val,
SUM(value) OVER (
PARTITION BY label ORDER BY idx
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total_by_label
FROM self
ORDER BY label, idx
""")
# shape: (7, 6)
# ┌─────┬───────┬───────┬───────────┬──────────┬────────────────────────┐
# │ idx ┆ label ┆ value ┆ first_val ┆ last_val ┆ running_total_by_label │
# │ --- ┆ --- ┆ --- ┆ --- ┆ --- ┆ --- │
# │ i64 ┆ str ┆ i64 ┆ i64 ┆ i64 ┆ i64 │
# ╞═════╪═══════╪═══════╪═══════════╪══════════╪════════════════════════╡
# │ 0 ┆ aaa ┆ 10 ┆ 10 ┆ 10 ┆ 10 │
# │ 1 ┆ aaa ┆ 20 ┆ 10 ┆ 20 ┆ 30 │
# │ 4 ┆ aaa ┆ 50 ┆ 10 ┆ 50 ┆ 80 │
# │ 6 ┆ aaa ┆ 0 ┆ 10 ┆ 0 ┆ 80 │
# │ 2 ┆ bbb ┆ 30 ┆ 30 ┆ 30 ┆ 30 │
# │ 3 ┆ bbb ┆ 40 ┆ 30 ┆ 40 ┆ 70 │
# │ 5 ┆ ccc ┆ -5 ┆ -5 ┆ -5 ┆ -5 │
# └─────┴───────┴───────┴───────────┴──────────┴────────────────────────┘