Суммирование продаж по месяцам и годам в 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;
Полезные материалы
- Документация PostgreSQL: 9.9. — подробное описание работы с типами данных даты и времени в PostgreSQL.
- SQL GROUP BY | Продвинутый SQL – Mode — детальное изучение группировок в SQL.
- Оптимизация SQL-запросов для временных данных — советы и методы по оптимизации запросов, связанных с временными данными.