Оконные агрегаты и нарастающий итог

Оконные агрегаты и ORDER BY в окне: нарастающий итог SUM, скользящее среднее AVG, ROWS BETWEEN — аналитические вычисления по «рамке».

Когда внутри OVER добавляют ORDER BY, окно получает рамку: по умолчанию — от начала партиции до текущей строки. Это позволяет считать нарастающие итоги и скользящие агрегаты.

Нарастающий итог SUM

Отсортируем сотрудников по дате приёма и посчитаем накопленный фонд зарплаты:

SELECT
  emp_name,
  hire_date,
  salary,
  SUM(salary) OVER (
    ORDER BY hire_date
  ) AS running_total
FROM employees
ORDER BY hire_date;

Результат:

emp_name     | hire_date  | salary | running_total
-------------+------------+--------+--------------
Ethan Hunt   | 2001-05-01 | 5000   | 5000
Tony Montana | 2002-07-15 | 6500   | 11500
Sarah Connor | 2005-10-18 | 8000   | 19500
Rick Deckard | 2007-01-03 | 7200   | 26700
Martin Blank | 2008-06-24 | 5600   | 32300

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

Нарастающий итог внутри отдела

Добавляем PARTITION BY — нарастающий итог сбрасывается в каждом отделе:

SELECT
  e.emp_name,
  e.dept_id,
  e.salary,
  SUM(e.salary) OVER (
    PARTITION BY e.dept_id
    ORDER BY e.hire_date
  ) AS dept_running_total
FROM employees AS e
WHERE e.dept_id IS NOT NULL
ORDER BY e.dept_id, e.hire_date;

Результат:

emp_name     | dept_id | salary | dept_running_total
-------------+---------+--------+-------------------
Tony Montana | 1       | 6500   | 6500
Rick Deckard | 3       | 7200   | 7200
Ethan Hunt   | 4       | 5000   | 5000
Sarah Connor | 5       | 8000   | 8000

AVG с нарастающим окном

-- Среднее зарплат по хронологии (нарастающее)
SELECT
  emp_name,
  hire_date,
  salary,
  ROUND(AVG(salary) OVER (ORDER BY hire_date), 0) AS running_avg
FROM employees
ORDER BY hire_date;

Результат:

emp_name     | hire_date  | salary | running_avg
-------------+------------+--------+------------
Ethan Hunt   | 2001-05-01 | 5000   | 5000
Tony Montana | 2002-07-15 | 6500   | 5750
Sarah Connor | 2005-10-18 | 8000   | 6500
Rick Deckard | 2007-01-03 | 7200   | 6675
Martin Blank | 2008-06-24 | 5600   | 6460

ROWS BETWEEN — явная рамка

Ключевое слово ROWS BETWEEN задаёт точные границы рамки:

-- Скользящее среднее по 2 предыдущим строкам + текущей
SELECT
  emp_name,
  hire_date,
  salary,
  ROUND(AVG(salary) OVER (
    ORDER BY hire_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ), 0) AS moving_avg_3
FROM employees
ORDER BY hire_date;

Результат:

emp_name     | hire_date  | salary | moving_avg_3
-------------+------------+--------+-------------
Ethan Hunt   | 2001-05-01 | 5000   | 5000
Tony Montana | 2002-07-15 | 6500   | 5750
Sarah Connor | 2005-10-18 | 8000   | 6500
Rick Deckard | 2007-01-03 | 7200   | 7233
Martin Blank | 2008-06-24 | 5600   | 6933

«Скользящее» означает, что при каждой новой строке окно «сдвигается вперёд», включая не более 3 строк.

Итоговая сумма по всей партиции

Чтобы в каждой строке видеть итог по всему окну (а не нарастающий), используем ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING или просто OVER (PARTITION BY ...) без ORDER BY:

SELECT
  e.emp_name,
  e.salary,
  SUM(e.salary) OVER () AS total_all
FROM employees AS e
ORDER BY e.salary DESC;

Результат:

emp_name     | salary | total_all
-------------+--------+----------
Sarah Connor | 8000   | 32300
Rick Deckard | 7200   | 32300
Tony Montana | 6500   | 32300
Martin Blank | 5600   | 32300
Ethan Hunt   | 5000   | 32300

Коротко

• SUM(...) OVER (ORDER BY col) — нарастающий итог по порядку.
• Добавление PARTITION BY сбрасывает итог в каждой группе.
• ROWS BETWEEN N PRECEDING AND CURRENT ROW — скользящее окно фиксированного размера.
• Без ORDER BY в окне агрегат считается по всей партиции сразу (итог, а не нарастающий).