SQL Studio / Разборы SQL / Один час между заказами — это 0 или 1 день?
У клиента две покупки: 1 апреля в 23:30 и 2 апреля в 00:30. В отчёте почему-то «0 дней между заказами», хотя календарная дата успела смениться. Это не ошибка LAG: разница между полными 24-часовыми интервалами, сменой календарной даты и точным прошедшим временем — три разных вопроса. В SQLite предыдущую покупку находим через LAG(...), а длительность — через julianday(); для календарных дней сначала берём date() обеих отметок.
Возьмём семь событий трёх клиентов. Строка — один заказ, время — UTC в текстовом формате YYYY-MM-DD HH:MM:SS. У клиента №2 две доставки ровно в один момент, а у клиента №3 есть отмена, которую нельзя считать предшествующей покупкой. Поэтому статус фильтруется до оконной функции, а при равном времени сортировка определяется дополнительным order_id.
1. Правдоподобные нули: CAST обрезает дробную часть
Этот запрос отвечает на узкий вопрос «сколько полных суток прошло», но будет ошибкой в отчёте по календарным дням. Первая покупка каждого человека закономерно имеет previous_at = NULL; у неё нет интервала.
WITH purchases(customer_id, order_id, ordered_at, status) AS (
VALUES
(1, 101, '2026-04-01 23:30:00', 'delivered'),
(1, 102, '2026-04-02 00:30:00', 'delivered'),
(1, 103, '2026-04-05 00:30:00', 'delivered'),
(2, 201, '2026-04-03 12:00:00', 'delivered'),
(2, 202, '2026-04-03 12:00:00', 'delivered'),
(3, 301, '2026-04-02 09:00:00', 'cancelled'),
(3, 302, '2026-04-04 09:00:00', 'delivered')
),
sequenced AS (
SELECT customer_id, order_id, ordered_at,
LAG(ordered_at) OVER (
PARTITION BY customer_id
ORDER BY ordered_at, order_id
) AS previous_at
FROM purchases
WHERE status = 'delivered'
)
SELECT customer_id, order_id, previous_at,
CAST(julianday(ordered_at) - julianday(previous_at) AS INTEGER)
AS full_24h_days
FROM sequenced
ORDER BY customer_id, ordered_at, order_id;
| Клиент | Заказ | Предыдущая дата UTC | Полных суток |
|---|---|---|---|
| 1 | 101 | NULL | NULL |
| 1 | 102 | 2026-04-01 23:30:00 | 0 |
| 1 | 103 | 2026-04-02 00:30:00 | 3 |
| 2 | 201 | NULL | NULL |
| 2 | 202 | 2026-04-03 12:00:00 | 0 |
| 3 | 302 | NULL | NULL |
С заказом №102 прошёл один час, то есть 1/24 суток; после CAST(... AS INTEGER) остаётся ноль. Для первого заказа julianday(NULL) тоже возвращает NULL. Не подменяйте это значение нулём через COALESCE, иначе средний интервал искусственно сократится.
2. Календарные даты и прошедшие часы рядом
Исправим метрику: разница julianday(date(...)) считает число смен полуночи в одной календарной системе. Отдельный столбец показывает действительное прошедшее время в часах (для этого примера, где все события уже в UTC). Если вам важна продолжительность в долях суток — делите столбец часов на 24, а не используйте CAST.
WITH purchases(customer_id, order_id, ordered_at, status) AS (
VALUES
(1, 101, '2026-04-01 23:30:00', 'delivered'),
(1, 102, '2026-04-02 00:30:00', 'delivered'),
(1, 103, '2026-04-05 00:30:00', 'delivered'),
(2, 201, '2026-04-03 12:00:00', 'delivered'),
(2, 202, '2026-04-03 12:00:00', 'delivered'),
(3, 301, '2026-04-02 09:00:00', 'cancelled'),
(3, 302, '2026-04-04 09:00:00', 'delivered')
),
sequenced AS (
SELECT customer_id, order_id, ordered_at,
LAG(ordered_at) OVER (
PARTITION BY customer_id
ORDER BY ordered_at, order_id
) AS previous_at
FROM purchases
WHERE status = 'delivered'
)
SELECT customer_id, order_id,
CAST(julianday(date(ordered_at)) -
julianday(date(previous_at)) AS INTEGER) AS calendar_days,
ROUND((julianday(ordered_at) -
julianday(previous_at)) * 24, 2) AS elapsed_hours
FROM sequenced
ORDER BY customer_id, ordered_at, order_id;
| Клиент | Заказ | Календарных дней | Прошло часов |
|---|---|---|---|
| 1 | 101 | NULL | NULL |
| 1 | 102 | 1 | 1 |
| 1 | 103 | 3 | 72 |
| 2 | 201 | NULL | NULL |
| 2 | 202 | 0 | 0 |
| 3 | 302 | NULL | NULL |
Две особенности: между №101 и №102 календарный разрыв равен 1, но прошло лишь 1,00 часа; между №201 и №202 две отдельные строки заказов с одинаковым временем — разрыв 0. Идентификатор заказа выступает tie-breaker только для устойчивого порядка, не добавляет времени. Если бизнес не считает две оплаты в один момент разными покупками, сначала зафиксируйте правило дедупликации.
3. Правильный знаменатель средней паузы
Для среднего считаем не заказы и не дни с регистрации, а интервалы между соседними доставленными заказами. У трёх покупок клиента №1 всего два интервала; у единственной доставки клиента №3 ни одного. Используем уже вычисленный previous_at и явно исключаем NULL:
WITH purchases(customer_id, order_id, ordered_at, status) AS (
VALUES
(1, 101, '2026-04-01 23:30:00', 'delivered'),
(1, 102, '2026-04-02 00:30:00', 'delivered'),
(1, 103, '2026-04-05 00:30:00', 'delivered'),
(2, 201, '2026-04-03 12:00:00', 'delivered'),
(2, 202, '2026-04-03 12:00:00', 'delivered'),
(3, 301, '2026-04-02 09:00:00', 'cancelled'),
(3, 302, '2026-04-04 09:00:00', 'delivered')
),
sequenced AS (
SELECT customer_id, order_id, ordered_at,
LAG(ordered_at) OVER (
PARTITION BY customer_id
ORDER BY ordered_at, order_id
) AS previous_at
FROM purchases
WHERE status = 'delivered'
)
SELECT customer_id, COUNT(*) AS repeat_intervals,
ROUND(AVG(julianday(date(ordered_at)) -
julianday(date(previous_at))), 2) AS avg_calendar_days,
ROUND(AVG((julianday(ordered_at) -
julianday(previous_at)) * 24), 2) AS avg_elapsed_hours
FROM sequenced
WHERE previous_at IS NOT NULL
GROUP BY customer_id
ORDER BY customer_id;
| Клиент | Интервалов | Среднее календарных дней | Среднее часов |
|---|---|---|---|
| 1 | 2 | 2 | 36.5 |
| 2 | 1 | 0 | 0 |
У клиента №1 календарные промежутки — 1 и 3 дня, среднее 2; фактические длительности — 1 и 72 часа, среднее 36,5 часа. Клиент №3 исключён не потому, что купил «0 дней спустя», а потому, что у него нет повторной доставки. Для общей средней по всем покупателям сначала решите, кому дать вес: каждой паузе или каждому человеку.
Маленькая проверка
Переставьте дату доставки №102 на 2026-04-01 23:45:00. Что будет с двумя видами интервала? Затем уберите order_id из ORDER BY окна и объясните, почему две доставки клиента №2 теряют устойчивый порядок. В SQL-песочнице все исходные события уже включены в каждый пример.
Семантика дат и числовых значений: SQLite Date And Time Functions; оконные функции SQLite.
Примеры выше выполняются в бесплатной SQL-песочнице на SQLite. Для последовательных упражнений на MySQL 8 есть отдельный курс SQL для аналитика на Stepik. Функции дат в этих двух системах отличаются — переносите смысл, а не синтаксис буквально.