Расчет накопительного итога в Postgresql: решение с COUNT и GROUP BY

Быстрый ответ

SELECT date, 
       value, 
       SUM(value) OVER (ORDER BY date) AS cumulative_total
FROM your_table;

Для расчёта накопительного итога в PostgreSQL используйте обозначение SUM() OVER, совмещённое с ORDER BY, что позволяет суммировать значения в столбце value. Произведите замену your_table и необходимых имен столбцов, чтобы получить желаемые результаты.

Углубление и оптимизация

Нихиле обstat, вышеприведенный пример отлично подойдёт для начального этапа использования, однако здесь мы рассмотрим более сложные сценарии.

Удаление дубликатов в ежедневных значениях

Если необходимо подсчитать количество уникальных записей по дням, как, например, число пользователей, можно воспользоваться DISTINCT:

SELECT date, 
       COUNT(DISTINCT email) OVER (ORDER BY date) AS unique_email_count
FROM user_table;

Таким образом, наш пользователь «Джон Доу» будет учитываться всего однажды за день, сохраняя свою индивидуальность.

Подробнее об этом расскажет наш спикер на видео

Исключение повторений на протяжении всей выборки

Для более глобального подсчёта уникальных записей можно применить DISTINCT ON следующим образом:

SELECT date, 
       COUNT(email) OVER (ORDER BY date) AS cumulative_unique_emails
FROM (
  SELECT DISTINCT ON (email) email, date
  FROM user_table
  ORDER BY email, date ASC
) as unique_emails;

Теперь каждый пользователь встретится в данных только однажды, независимо от своей активности.

Не забывайте про индекс по (email, date), который способствует значительному ускорению выполнения запроса на больших объёмах данных.

Заполнение пробелов в данных с помощью генерации дат

Для учёта "нулевых" дней используется взаимодействие функции генерации и LEFT JOIN:

WITH date_series AS (
  SELECT generate_series(MIN(date)::date, MAX(date)::date, '1 day'::interval)::date as date
  FROM user_table
)
SELECT ds.date, 
       COALESCE(COUNT(ut.email), 0) OVER (ORDER BY ds.date) AS cumulative_total
FROM date_series ds
LEFT JOIN user_table ut ON ds.date = ut.date
GROUP BY ds.date
ORDER BY ds.date;

Благодаря этому обеспечивается непрерывность и корректность накопительных данных, даже за "пустые" дни.

Учтите поддержку оконных функций в вашей версии PostgreSQL и предельную важность применения правильных типов данных.

Визуализация

Накопительный итог так же захватывающ, как горный восход:

Базовый лагерь: 🏕 (Начальная точка)

Каждый шаг записи вносит новый этап:

Тропа 1: 🏕➡️🌲
Тропа 2: 🌲➡️🏞
Тропа 3: 🏞➡️⛰ (И мы туда, все выше)

На SQL-языке, отслеживание шагов осуществляется с помощью SUM():

SELECT
  trail_id,
  SUM(elevation) OVER (ORDER BY trail_id ASC) AS cumulative_elevation
FROM trails;

В итоге мы получаем следующий разбор:

| trail_id | Высота    |
| -------- | --------- |
| 🏕       | 0         |
| 🌲       | 100       |
| 🏞       | 200       |
| ⛰        | 300       |

То есть накопительный итог — это подсчёт пройденного пути.

Расширенные техники

В более сложных случаях крайне полезными являются дополнительные возможности PostgreSQL.

Применение ROLLUP для вывода промежуточных итогов

При помощи ROLLUP мы можем выводить сводные данные по различным уровням:

SELECT date, 
       value, 
       SUM(value) OVER (ORDER BY date) AS cumulative_total
FROM your_table
GROUP BY ROLLUP(date, value);

Такой подход обеспечивает комплексное представление данных по дням и значениям.

Сравнение накопленных данных с помощью INNER JOIN

Если вам требуется сопоставить накопительные итоги за разные дни, то можно воспользоваться INNER JOIN:

SELECT a.date, 
       COUNT(b.email) AS cumulative_count
FROM (SELECT DISTINCT date FROM user_table) a
INNER JOIN user_table b ON a.date >= b.date
GROUP BY a.date
ORDER BY a.date;

Это позволяет визуализировать рост количества пользовательских входов со временем.

Подсчёт накопительного итога с учетом пустых дат

Одни из ключевых моментов — это запись в расчёт дней без данных:

WITH recursive cte AS (
  SELECT MIN(date)::date as date FROM user_table
  UNION ALL
  SELECT date + 1 FROM cte WHERE date < CURRENT_DATE
)
SELECT cte.date,
       COALESCE(SUM(value), 0) OVER (ORDER BY cte.date) AS cumulative_total
FROM cte
LEFT JOIN user_table ut ON cte.date = ut.date
GROUP BY cte.date;

Рекурсивная CTE обеспечивает создание полной последовательности дат, что необходимо для точного подсчёта итогов.

Полезные материалы

1. PostgreSQL: Documentation: 9.21. Aggregate Functions — подробная документация PostgreSQL по агрегатным функциям.
2. Database agnostic. ANSI SQL or ORM? – Stack Overflow — обширный набор решений сообщества.
3. Gapless Sequences – PostgreSQL wiki — указания по работе с беспрерывными последовательностями.
4. db<>fiddle — сервис для оценки SQL запросов на практике.
5. PostgreSQL: Documentation: 3.5. Window Functions — обзор оконных функций в PostgreSQL.
6. Five ways to paginate in Postgres, from the basic to the exotic — обзор методов пагинации для использования с накопительными итогами.
7. Category:Snippets – PostgreSQL wiki — подборка полезных методик и примеров кода для PostgreSQL.