Aggregate window functions

They enable users to perform a wide range of analytical tasks, from ranking and partitioning data to calculating moving averages, cumulative sums, and differences between consecutive rows. Оконная функция как и аггрегирующая функция работает с набором записей, но в отличие от нее возвращает не одно значение, а набор (столько же записей сколько получила на вход).

SELECT *,
       AVG(revenue) OVER () AS total_avg
FROM online_store.orders
LIMIT 5;
-- выведет строки исходной таблицы с добавлением столбца с average значением
--
-- нужно разделить записи на разделы-окна по параметрам в выражении OVER
-- затем посчитать среднюю выручку по записям в каждом окне

то же что и

SELECT *,
       (SELECT AVG(revenue) AS total_avg FROM online_store.orders)
FROM online_store.orders
LIMIT 5;

PARTITION BY

SELECT *,
       AVG(revenue) OVER (PARTITION BY event_dt) AS date_avg
FROM online_store.orders;
-- партицируем по дате, для каждой даты считаем среднее revenue
-- дополняем исходную таблицу колонкой date_avg

то же что и

SELECT *
FROM online_store.orders AS ord
LEFT JOIN (SELECT event_dt,
                  AVG(revenue) AS date_avg
           FROM online_store.orders
           GROUP BY event_dt) AS dt ON ord.event_dt = dt.event_dt;

-- по каждому пользователю ищет дату последнего заказа
SELECT *,
       MAX(event_dt) OVER (PARTITION BY user_id) AS last_order_dt
FROM online_store.orders;

-- количество заказов по пользователям
SELECT *,
       COUNT(*) OVER (PARTITION BY user_id) AS orders_cnt
FROM online_store.orders;

Оконные функции бывают:

• агрегирующие AVG, MIN, MAX, SUM и COUNT
• функции общего назначения RAW_NUMBER, RANK, DENSE_RANK, NTILE, 
• функции смещения LEAD, LAG

SELECT *,
       AVG(revenue) OVER (PARTITION BY user_id) AS user_avg
FROM online_store.orders;

Можно партицировать сразу по нескольким полям перечисленным через запятую, тогда в окно войдет каждое уникальное сочетание указанных полей