Суммирование продаж по месяцам и годам в PostgreSQL

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

Мастерство агрегации данных по временным интервалам в PostgreSQL можно освоить с помощью функции DATE_TRUNC. Этот SQL-метод позволит вам без проблем группировать данные:

SELECT
  DATE_TRUNC('month', your_date_field) AS month_year,
  SUM(your_sales_field) AS total_sales
FROM
  your_table
GROUP BY
  month_year
ORDER BY
  month_year;

Примените эти выражения к вашим полям your_date_field, your_sales_field и your_table. Так, вы получите хронологически упорядоченный отчет о продажах по месяцам и годам.

Разбираем код: объяснение

Хотите узнать больше? Рассмотрим детали и полезные особенности.

Использование to_char для форматирования дат

Преобразуем даты так, чтобы они были понятны даже для вашего руководителя.

SELECT
  to_char(DATE_TRUNC('month', date_col), 'Mon-YYYY') AS month_year, -- Янв-2020, Фев-2020 и т.д. Более наглядно!
  product,
  SUM(sales) AS total_sales
FROM
  sales_table
GROUP BY
  month_year, product
ORDER BY
  month_year, product;

Не забывайте: стандартный SQL-формат дат не всегда удобен для восприятия. Функция to_char преобразует даты для лучшей читаемости.

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

Заглядываем внутрь “case”

Проблема чувствительности к регистру в именах таблиц и столбцов решается добавлением кавычек:

SELECT
  to_char(DATE_TRUNC('month', "date_col"), 'Mon-YYYY') AS month_year,
  "product",
  SUM("sales") AS total_sales
FROM
  "sales_table"
GROUP BY
  month_year, "product"
ORDER BY
  month_year;

Группируем с умом

Применяем продуманную группировку:

GROUP BY
  to_char(DATE_TRUNC('month', date_col), 'Mon-YYYY'), product

Умная группировка схожа с успешно организованными общественными собраниями.

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

Визуальная аналогия поможет вам понять структуру данных.

🗓️ 2020          🗓️ 2021          🗓️ 2022
├── 📅 Янв      ├── 📅 Янв      ├── 📅 Янв
|   ├──📷📷📷   |   ├──📷📷     |   ├──📷📷📷📷
├── 📅 Фев      ├── 📅 Фев      ├── 📅 Фев
|   ├──📷📷     |   ├──📷📷📷   |   ├──📷📷
├── 📅 Мар      ├── 📅 Мар      ├── 📅 Мар
|   ├──📷       |   ├──📷📷     |   ├──📷📷📷
...
# Каждый символ 📷 олицетворяет запись, группированную по месяцу и году.

SQL помогает организовать данные так же легко, как сильный кофе бодрит вас утром.

Извлекаем значение: год и месяц

Для извлечения года и месяца составьте следующий запрос:

SELECT
  EXTRACT(YEAR FROM date_col) AS year,
  EXTRACT(MONTH FROM date_col) AS month,
  SUM(sales) AS total_sales
FROM
  sales_table
GROUP BY
  year, month
ORDER BY
  year, month;

Таким образом, вы получите отдельные столбцы для года и месяца.

Решаем проблемы с нестандартными случаями

Проблемы с нулевыми значениями или аномалиями можно исправить с использованием COALESCE и CASE:

SELECT
  to_char(DATE_TRUNC('month', date_col), 'Mon-YYYY') AS month_year,
  COALESCE(product, 'Неопознанный продукт') AS product,
  SUM(COALESCE(sales, 0)) AS total_sales
FROM
  sales_table
GROUP BY
  month_year, product
ORDER BY
  month_year, product;

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

1. Документация PostgreSQL: 9.9. — подробное описание работы с типами данных даты и времени в PostgreSQL.
2. SQL GROUP BY | Продвинутый SQL – Mode — детальное изучение группировок в SQL.
3. Оптимизация SQL-запросов для временных данных — советы и методы по оптимизации запросов, связанных с временными данными.