SQL Studio / Разборы SQL / Почему февраль и апрель исчезли из отчёта
В отчёте за январь–май почему-то три строки вместо пяти: февраль и апрель исчезли. Обычный GROUP BY не создаёт месяцы, в которых нет ни одной продажи. В SQLite можно построить ряд первых чисел месяцев через WITH RECURSIVE, поставить этот календарь слева от LEFT JOIN и заменить отсутствие суммы нулём через COALESCE. Важно: фильтр по статусу продаж должен остаться в ON, иначе пустые месяцы снова пропадут.
Разберём искусственную, но воспроизводимую витрину: одна строка — заказ; created_at хранит дату и время в ISO-порядке, amount_kopecks — целое число копеек. Два заказа в мае, один в январе и один в марте доставлены. Мартовская отмена не считается продажей, июньский заказ лежит за верхней границей отчёта.
1. Запрос выполняется, но уничтожает календарь
Сначала создаём пять месяцев рекурсивно: якорь — 2026-01-01, следующее значение — date(month_start, '+1 month'). Условие month_start < '2026-05-01' прекращает рекурсию после мая. Ошибка спрятана ниже — в WHERE s.status = 'delivered'.
WITH RECURSIVE sales(order_id, created_at, status, amount_kopecks) AS (
VALUES
(1, '2026-01-15 10:00:00', 'delivered', 12000),
(2, '2026-03-02 12:00:00', 'delivered', 9000),
(3, '2026-03-28 09:00:00', 'cancelled', 3000),
(4, '2026-05-01 00:00:00', 'delivered', 5000),
(5, '2026-05-30 23:59:59', 'delivered', 7000),
(6, '2026-06-01 00:00:00', 'delivered', 8000)
),
months(month_start) AS (
SELECT '2026-01-01'
UNION ALL
SELECT date(month_start, '+1 month')
FROM months
WHERE month_start < '2026-05-01'
)
SELECT m.month_start, COUNT(s.order_id) AS orders_n,
COALESCE(SUM(s.amount_kopecks), 0) AS amount_kopecks
FROM months AS m
LEFT JOIN sales AS s
ON s.created_at >= m.month_start
AND s.created_at < date(m.month_start, '+1 month')
WHERE s.status = 'delivered'
GROUP BY m.month_start
ORDER BY m.month_start;
| Месяц | Доставлено | Копейки |
|---|---|---|
| 2026-01-01 | 1 | 12000 |
| 2026-03-01 | 1 | 9000 |
| 2026-05-01 | 2 | 12000 |
У февраля и апреля в результате LEFT JOIN столбец s.status равен NULL. Проверка NULL = 'delivered' не истинна, и WHERE удаляет обе календарные строки. Запрос не падает и возвращает правдоподобные деньги — именно поэтому ошибка опасна.
2. Сохраняем пустые месяцы, фильтруя в ON
Переносим s.status = 'delivered' в условие соединения. У каждого месяца теперь остаётся строка даже без доставленных заказов. Для количества используем COUNT(s.order_id), а не COUNT(*): пустая строка после LEFT JOIN не должна считаться заказом. Сумму приводим к нулю только после агрегирования.
WITH RECURSIVE sales(order_id, created_at, status, amount_kopecks) AS (
VALUES
(1, '2026-01-15 10:00:00', 'delivered', 12000),
(2, '2026-03-02 12:00:00', 'delivered', 9000),
(3, '2026-03-28 09:00:00', 'cancelled', 3000),
(4, '2026-05-01 00:00:00', 'delivered', 5000),
(5, '2026-05-30 23:59:59', 'delivered', 7000),
(6, '2026-06-01 00:00:00', 'delivered', 8000)
),
months(month_start) AS (
SELECT '2026-01-01'
UNION ALL
SELECT date(month_start, '+1 month')
FROM months
WHERE month_start < '2026-05-01'
)
SELECT m.month_start, COUNT(s.order_id) AS orders_n,
COALESCE(SUM(s.amount_kopecks), 0) AS amount_kopecks
FROM months AS m
LEFT JOIN sales AS s
ON s.created_at >= m.month_start
AND s.created_at < date(m.month_start, '+1 month')
AND s.status = 'delivered'
GROUP BY m.month_start
ORDER BY m.month_start;
| Месяц | Заказов | Выручка, коп. |
|---|---|---|
| 2026-01-01 | 1 | 12000 |
| 2026-02-01 | 0 | 0 |
| 2026-03-01 | 1 | 9000 |
| 2026-04-01 | 0 | 0 |
| 2026-05-01 | 2 | 12000 |
Контрольная сумма: 12 000 + 9 000 + 5 000 + 7 000 = 33 000 копеек по четырём доставленным заказам. В июне есть ещё 8 000 копеек, но они не относятся к отчёту: интервал для мая заканчивается строго раньше 2026-06-01. Строка 2026-05-30 23:59:59 включена. Это проверка не только суммы, но и двух границ диапазона.
3. Если начало совпало с концом — и если конец раньше начала
Проверим рекурсию независимо от таблицы продаж. Когда обе границы — май, календарь содержит ровно один месяц:
WITH RECURSIVE bounds(first_month, last_month) AS (
VALUES ('2026-05-01', '2026-05-01')
),
months(month_start) AS (
SELECT first_month FROM bounds WHERE first_month <= last_month
UNION ALL
SELECT date(month_start, '+1 month')
FROM months, bounds
WHERE month_start < last_month
)
SELECT COUNT(*) AS months_n,
MIN(month_start) AS first_value,
MAX(month_start) AS last_value
FROM months;
| months_n | first_value | last_value |
|---|---|---|
| 1 | 2026-05-01 | 2026-05-01 |
Теперь поменяем last_month на апрель — нижняя граница станет позже верхней. Условие в якорном SELECT не создаст ни одной календарной строки:
WITH RECURSIVE bounds(first_month, last_month) AS (
VALUES ('2026-05-01', '2026-04-01')
),
months(month_start) AS (
SELECT first_month FROM bounds WHERE first_month <= last_month
UNION ALL
SELECT date(month_start, '+1 month')
FROM months, bounds
WHERE month_start < last_month
)
SELECT COUNT(*) AS months_n,
MIN(month_start) AS first_value,
MAX(month_start) AS last_value
FROM months;
| months_n | first_value | last_value |
|---|---|---|
| 0 | NULL | NULL |
Эта защита полезнее попытки «починить» диапазон после соединения. Для пользовательских дат дополнительно проверяйте формат и требуйте границы именно первого числа месяца. generate_series() в SQLite существует как отдельное расширение, но его наличие нельзя гарантировать в каждом WASM-окружении — здесь достаточно стандартного рекурсивного CTE.
Задание на две минуты
Поменяйте конец первого отчёта на 2026-04-01: сколько строк и сколько копеек останется? Затем добавьте в sales ещё одну доставку 28 февраля и предскажите изменение ровно одной строки результата. Чтобы проверить гипотезу, откройте бесплатную SQL-песочницу и выполните запрос целиком: CTE уже содержит исходные данные.
Ограничения рекурсивного CTE и модель UNION ALL: официальная документация SQLite. Функции и смещения календаря: SQLite Date And Time Functions.
Примеры выше выполняются в бесплатной SQL-песочнице на SQLite. Для последовательных упражнений на MySQL 8 есть отдельный курс SQL для аналитика на Stepik. Функции дат в этих двух системах отличаются — переносите смысл, а не синтаксис буквально.