Дни между покупками в SQL: почему один час — это 0 или 1 день?

Заказ в 23:30 и следующий в 00:30. SQL показывает ноль дней, хотя дата уже другая. Посчитаем два разных интервала и проверим среднее.

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;
CAST обрезает интервал в один час до 0 дней
КлиентЗаказПредыдущая дата UTCПолных суток
1101NULLNULL
11022026-04-01 23:30:000
11032026-04-02 00:30:003
2201NULLNULL
22022026-04-03 12:00:000
3302NULLNULL

С заказом №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;
Проверка: час через полночь даёт один календарный день
КлиентЗаказКалендарных днейПрошло часов
1101NULLNULL
110211
1103372
2201NULLNULL
220200
3302NULLNULL

Две особенности: между №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;
Средние считаются по 2 и 1 интервалам, не по 3 и 2 заказам
КлиентИнтерваловСреднее календарных днейСреднее часов
12236.5
2100

У клиента №1 календарные промежутки — 1 и 3 дня, среднее 2; фактические длительности — 1 и 72 часа, среднее 36,5 часа. Клиент №3 исключён не потому, что купил «0 дней спустя», а потому, что у него нет повторной доставки. Для общей средней по всем покупателям сначала решите, кому дать вес: каждой паузе или каждому человеку.

Маленькая проверка

Переставьте дату доставки №102 на 2026-04-01 23:45:00. Что будет с двумя видами интервала? Затем уберите order_id из ORDER BY окна и объясните, почему две доставки клиента №2 теряют устойчивый порядок. В SQL-песочнице все исходные события уже включены в каждый пример.

Не путайте интервалы с долей повторных покупателей. При равных датах полезен детерминированный выбор записи; синтаксис LAG разбирается в оконных функциях. Локальные календарные дни отдельно показаны в разборе часовых поясов SQLite.

Семантика дат и числовых значений: SQLite Date And Time Functions; оконные функции SQLite.

Потренируйте порядок покупок в задаче P108 «Номер покупки клиента». Это подготовительный шаг: для интервалов из статьи к порядку событий понадобится добавить время предыдущего заказа через LAG.


Примеры выше выполняются в бесплатной SQL-песочнице на SQLite. Для последовательных упражнений на MySQL 8 есть отдельный курс SQL для аналитика на Stepik. Функции дат в этих двух системах отличаются — переносите смысл, а не синтаксис буквально.