Теория: SUM, COUNT и AVG

Функции SUM(), COUNT(), AVG() в SQL используются для агрегации данных в столбцах таблицы. В оконных версиях, эти функции вычислят значения в пределах указанного окна, группы.

Например, обычный подсчет среднего и суммы по регионам будет выглядеть так:

SELECT
    sh.region,
    SUM(s.price * s.quantity) AS sum_value,
    AVG(s.price * s.quantity) AS avg_value
FROM sales AS s
LEFT JOIN customer AS c ON s.customer_id = c.customer_id
LEFT JOIN shop AS sh ON s.shop_id = sh.shop_id
GROUP BY sh.region;

View on DB Fiddle

Проблема запроса выше, что мы не можем сравнить значение строки с расчитанным средним или суммой. В таком случае, нам помогут оконные функции:

SELECT
    id,
    sale_date,
    price,
    quantity,
    (price * quantity) as value,
    region,
    c.category,
    SUM(s.price * s.quantity) OVER (PARTITION BY region) AS sum_value,
    AVG(s.price * s.quantity) OVER (PARTITION BY region) AS avg_value
FROM sales AS s
LEFT JOIN customer AS c ON s.customer_id = c.customer_id
LEFT JOIN shop AS sh ON s.shop_id = sh.shop_id
ORDER BY s.id;

View on DB Fiddle

Агрегирующие функции вычисляют значения для окон, указанных в OVER (PARTITION BY region). Теперь рядом с каждой строкой таблицы мы также получим вычисленное значение по группе, в которой есть эта строка. Эти данные позволяют нам провести анализ, например была ли сумма продажи выше или ниже среднего чека по региону.

Выводы

1. Оконные функции позволяют выполнять агрегатные вычисления не только по всей таблице, но и в пределах определенного окна, что делает их мощным инструментом для аналитики данных
2. При использовании оконной функции версии функции каждая строка будет иметь доступ к количеству строк в заданном окне.
3. Для использования оконных функций SUM(), COUNT(), AVG() в SQL необходимо указать OVER() с PARTITION BY для определения окна, в пределах которого будет выполняться вычисление функции.