Оконные функции

Функция	Описание
DENSE_RANK	Возвращает ранг каждой строки в разделе окна без пропусков при совпадении значений.
FIRST_VALUE	Возвращает первое значение в упорядоченном наборе значений относительно окна, объявленного в `OVER`.
LAG	Возвращает значение столбца со смещением относительно текущей строки назад в разделе окна.
LAST_VALUE	Возвращает последнее значение в упорядоченном наборе значений относительно окна, объявленного в `OVER`.
LEAD	Возвращает значение столбца со смещением относительно текущей строки вперёд в разделе окна.
OVER	Определяет окно (набор строк), в пределах которого применяется функция.
RANK	Возвращает ранг каждой строки в разделе окна с пропусками при совпадении значений.
ROW_NUMBER	Возвращает порядковый номер строки в разделе окна, начиная с 1.

Примечание

В качестве движка DataFrame Polars по умолчанию использует семантику кадрирования ROWS для оконных функций, если явная спецификация окна не указана; в частности, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Это отличается от семантики кадрирования RANGE по умолчанию, обычно используемой в движках баз данных.

DENSE_RANK

Возвращает ранг каждой строки в разделе окна без пропусков при совпадении значений. Строки с одинаковыми значениями получают один и тот же ранг, а следующий номер ранга идёт подряд (без пропусков).

Требования:

Должна использоваться с предложением OVER.
Это предложение должно содержать ORDER BY в спецификации окна.

Пример:

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   ┆ u32  ┆ u32        │
# ╞═════╪══════════╪═══════╪══════╪════════════╡
# │ 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

Возвращает первое значение в упорядоченном наборе значений относительно окна, объявленного в OVER.

LAG

Возвращает значение столбца со смещением относительно текущей строки назад в разделе окна. Если смещение выходит за границы раздела, возвращается NULL.

Синтаксис:

LAG(expr) OVER (...) — смещение по умолчанию равно 1.
LAG(expr, n) OVER (...) — смещение на n строк.

Требования:

Должна использоваться с предложением OVER.
Это предложение должно содержать ORDER BY в спецификации окна.

Пример:

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

Возвращает последнее значение в упорядоченном наборе значений относительно окна, объявленного в OVER.

LEAD

Возвращает значение столбца со смещением относительно текущей строки вперёд в разделе окна. Если смещение выходит за границы раздела, возвращается NULL.

Синтаксис:

LEAD(expr) OVER (...) — смещение по умолчанию равно 1.
LEAD(expr, n) OVER (...) — смещение на n строк.

Требования:

Должна использоваться с предложением OVER.
Это предложение должно содержать ORDER BY в спецификации окна.

Пример:

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        │
# └─────┴──────────┴───────┴────────────┴─────────────┘

RANK

Возвращает ранг каждой строки в разделе окна с пропусками при совпадении значений. Строки с одинаковыми значениями получают один и тот же ранг, а следующий номер ранга пропускает числа (образуя пропуски).

Требования:

Должна использоваться с предложением OVER.
Это предложение должно содержать ORDER BY в спецификации окна.

Пример:

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   ┆ u32        ┆ u32  │
# ╞═════╪══════════╪═══════╪════════════╪══════╡
# │ 2   ┆ A        ┆ 90    ┆ 1          ┆ 1    │
# │ 3   ┆ A        ┆ 90    ┆ 1          ┆ 1    │
# │ 1   ┆ A        ┆ 85    ┆ 2          ┆ 3    │2)
# │ 5   ┆ B        ┆ 80    ┆ 1          ┆ 1    │
# │ 6   ┆ B        ┆ 80    ┆ 1          ┆ 1    │
# │ 4   ┆ B        ┆ 75    ┆ 2          ┆ 3    │2)
# └─────┴──────────┴───────┴────────────┴──────┘

ROW_NUMBER

Возвращает порядковый номер строки, при необходимости в пределах раздела окна, начиная с 1. В отличие от RANK и DENSE_RANK, ROW_NUMBER всегда возвращает уникальные номера, даже если значения совпадают.

Пример:

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 │
# │ --- ┆ --- ┆ --- ┆ ---      ┆ ---   │
# │ u32 ┆ u32 ┆ u32 ┆ 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

Используется для определения окна (набора строк), в пределах которого применяется функция.

Примечания: В качестве движка DataFrame Polars по умолчанию использует семантику кадрирования ROWS для оконных функций, если явная спецификация окна не указана; в частности, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Это отличается от семантики кадрирования RANGE по умолчанию, обычно используемой в движках баз данных.

Пример:

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                     │
# └─────┴───────┴───────┴───────────┴──────────┴────────────────────────┘

© 2020 Ritchie Vink
© 2022 Polars contributors
Licensed under the MIT License.
https://docs.pola.rs/api/python/stable/reference/sql/functions/window.html