OVER (оконные функции)

Ключевое слово, превращающее агрегатную функцию в оконную — вычисляет значение без схлопывания строк.

Синтаксис

function() OVER ([PARTITION BY col] [ORDER BY col] [frame])

Объяснение

Оконные функции вычисляют значение для каждой строки с учётом «окна» соседних строк. В отличие от GROUP BY, строки не группируются. Синтаксис: функция OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE BETWEEN ...) Выполняются после WHERE, GROUP BY, HAVING — поэтому нельзя фильтровать по их результату в WHERE. Нужен подзапрос или CTE.

Пример

-- Нарастающая сумма продаж по дате
SELECT
  date,
  sales,
  SUM(sales) OVER (ORDER BY date) AS running_total
FROM daily_sales;

Связанные термины

LAG / LEADLAG возвращает значение из предыдущей строки окна, LEAD — из следующей.PARTITION BYДелит строки на разделы для оконной функции. Аналог GROUP BY, но без схлопывания строк.RANKПрисваивает ранг строкам. При одинаковых значениях ранги совпадают, следующий ранг пропускается.ROW_NUMBER / RANK / DENSE_RANKОконные функции нумерации строк. Различаются поведением при одинаковых значениях.

Анекдоты по теме

Разработчик: — У меня запрос на 8 таблиц через JOIN, подзапросы, оконные функции внутри WHERE и GROUP BY на хэш с миллиардом строк. Он идёт 3 дня. Как оптимизировать? DBA: — TRUNCATE TABLE карьера_разработчика. И иди в менеджеры.

FIRST_VALUE и LAST_VALUE: — FIRST_VALUE(price) OVER (PARTITION BY product ORDER BY date) — первая цена продукта. — LAST_VALUE(price) OVER (...) — осторожно! По умолчанию frame заканчивается на текущей строке. — Чтобы получить реально последнее значение: ...OVER (PARTITION BY product ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

— Какая оконная функция самая печальная? — LAG(). Потому что она всегда оглядывается на прошлое и никогда не смотрит вперёд.

Все анекдоты →

Читать статью →Потренироваться →← Все термины