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, но без схлопывания строк.ROW_NUMBER / RANK / DENSE_RANKОконные функции нумерации строк. Различаются поведением при одинаковых значениях.

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

RANK() и DENSE_RANK() поспорили о справедливости. RANK(): — Если двое на первом месте, следующий — третий. DENSE_RANK(): — Если двое на первом месте, следующий — второй. ROW_NUMBER(): — Мне всё равно. Каждый получает уникальный номер.

— Доктор, у меня шизофрения. — Это лечится. Расскажите подробно. — WITH моей_личности AS (SELECT боль, радость, тревогу, ROW_NUMBER() OVER (PARTITION BY день_недели ORDER BY кофеин DESC) FROM психика)... Ой, кажется, я только что создал три новых партиции самосознания.

Задача: найти разницу продаж между текущим и предыдущим месяцем. Решение: SELECT month, sales, sales - LAG(sales) OVER (ORDER BY month) AS diff FROM monthly_sales; Программист: одна строка вместо самостоятельного JOIN! DBA: вот за это любят оконные функции.

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

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