Как заполнить пустоту в интервале mysql?

Question

[rinaz22]

Всем привет. Нужно получить количество записей в БД, сгруппировав по часам или по дням.
Для этого делаю такой запрос:

SELECT
  3600 * FLOOR(`time` / 3600) as `interval`,
  COUNT(`id`) AS `count`

FROM
  `visitor_stat`

WHERE
  `date` >= $sTime
  AND `date <= $eTime

GROUP BY
  1

ORDER BY
  `interval`

Проблемка в том, что например если в базе есть записи с временем 13:00, 14:00 и 16:00, то соответственно выведет 3 записи, а 15:00 не будет учтен, т.к. записей с этим временным диапазоном нет. Как можно добавить тот самый пустой элемент, в примере это 15:00 со значением 0?
Или это лучше сделать после получения и уже в процессе обработки данных?

Comments

[Akina]

В MySQL вот этот фрагмент:

3600 * FLOOR(`time` / 3600) as `interval`,

даст заведомый бред, ибо time будет преобразован из литерала времени в числовую форму (и это ни разу не количество секунд. как кто-то надеется).

Как можно добавить тот самый пустой элемент, в примере это 15:00 со значением 0?

Укажите точную версию MySQL - в данном случае это критичная информация.

[rinaz22]

Akina, `time` содержит время в Unix формате

[Akina]

rinaz22, это как? емнип UNIXTIME хранит точку даты-времени... то есть именно точку времени (и обязательно с компонентом даты), а не длительность интервала (который может иметь компоненту дней, но не даты).

Answer 1

[Rsa97]

WITH RECURSIVE `cte` (`from`, `to`) AS (
  SELECT CAST('00:00' AS TIME), CAST('01:00' AS TIME)
  UNION SELECT `to`, `to` + INTERVAL 1 HOUR
    FROM `cte`
    WHERE `to` < '24:00'
)
SELECT `cte`.`from` AS `interval`, COUNT(*) AS `count`
  FROM `cte`
  LEFT JOIN `visitor_stat` AS `v`
    ON `v`.`time` >= `cte`.`from` AND `v`.`time` < `cte`.`to`
  WHERE `date` >= :sTime AND `date <= :eTime
  GROUP BY `cte`.`from`
  ORDER BY `cte`.`from`

Comments

[Akina]

1. Лучше UNION ALL - конечно, на таком количестве записей дополнительная сортировка на каждом витке рекурсии незаметна, но лучше не привыкать.

2. WHERE `to` < '24:00' предполагает, что контекст выполнения - время. Оно, конечно, так, но на неявные установки лучше не полагаться, и указать тип данных литерала явно: WHERE `to` < TIME '24:00'.

3. При наличии записи с нулевым количеством минут условие ON `v`.`time` BETWEEN `cte`.`from` AND `cte`.`to` посчитает запись дважды. Следует использовать два неравенства, одно строгое, второе нет:

ON `v`.`time` >= `cte`.`from` AND  `v`.`time` < `cte`.`to`

.

[Rsa97]

Akina, Почему-то всегда считал, что BETWEEN справа не включает границу. Был не прав, однако. Действительно, надо два неравенства писать.

[rinaz22]

Rsa97, Увидев такой запрос, подумал может лучше обрабатывать уже на php)

[Rsa97]

rinaz22, Дык запрос то примитивный. В принципе, его можно и для старых версий MySQL переписать, только он длиннее станет.

SELECT `t`.`from` AS `interval`, COUNT(*) AS `count`
  FROM (
    SELECT CAST('00:00' AS TIME) AS `from`, CAST('01:00' AS TIME) AS `to`
    UNION ALL SELECT CAST('01:00' AS TIME), CAST('02:00' AS TIME)
    ...
    UNION ALL SELECT CAST('23:00' AS TIME), CAST('24:00' AS TIME)
  ) AS `t`
  LEFT JOIN `visitor_stat` AS `v`
    ON `v`.`time` >= `t`.`from` AND `v`.`time` < `t`.`to`
  WHERE `date` >= :sTime AND `date <= :eTime
  GROUP BY `t`.`from`
  ORDER BY `t`.`from`

[Дмитрий]

rinaz22, а у вас именно MySQL? А какой версии?

[rinaz22]

Дмитрий, Да, у меня MySQL, версия 7.4.30

[rinaz22]

Rsa97, в обоих вариантах выходит ошибка:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RECURSIVE `cte` (`from`, `to`) AS ( SELECT CAST('00:00' AS TIME), CAST('01:00' at line 1

[Rsa97]

rinaz22, В варианте для старой версии MySQL такой ошибки быть не может, там нет cte.

[Akina]

у меня MySQL, версия 7.4.30

Забавно, но сам MySQL даже не подозревает о существовании такой версии.
Существует MySQL NDB Cluster 7.4.30 - но он основан на MySQL версии 5.6.50.

Выполните запрос SELECT VERSION(); и выложите полный ответ сервера.

в обоих вариантах выходит ошибка

Такая ошибка характерна для старых (5.х) версий MySQL, не подозревающих о существовании рекурсивного (да вообще любого) CTE.

[rinaz22]

Akina, 5.7.36

[rinaz22]

Rsa97, Для старой версии другая ошибка. Имел ввиду оба варианта выдают ошибку

[rinaz22]

Rsa97, В любом случае, я бы не хотел усложнить запрос, если это возможно.
Сейчас я обрабатываю и заполняю пустые значения с помощью PHP, но проблемка в том, что запрос группирует по времени ближайшего count.
Т.е. если вместо 3600 поставить 7200(интервал 2 часа), то в ответ возвращаются значения не кратные 2. Т.е. не 00:00, 02:00, а например 01:00, 09:00

[Rsa97]

rinaz22, Какой тип поля `time`?

[rinaz22]

Rsa97, bigint

[Дмитрий]

rinaz22, тогда проехали. В mariadb и кажется 8 было попроще создавать последовательности на лету

[Rsa97]

rinaz22, И что в этом поле? Там точно секунды с начала суток? Или обычный unix timestamp?

[Akina]

rinaz22,

5.7.36

Для этой версии вместо рекурсивного CTE нужно использовать синтетическую таблицу.

Например, если в поле time хранится UNIX TIMESTAMP, а дискретность выборки должна быть час, то в качестве опорной таблицы может использоваться, например, такое:

...
JOIN ( SELECT 0 AS `hour` UNION ALL SELECT 1 UNION ALL SELECT 2 .. UNION ALL SELECT 23) AS hourslist
...

А связывание выполнять просто по значению часа в дате

... 
LEFT JOIN visitor_stat ON HOUR(visitor_stat.`time`) = hourslist.`hour`
...

[rinaz22]

Rsa97, unix timestamp

[Rsa97]

rinaz22, Тогда ваш метод вычисления часа не годится. Он не учитывает часовой пояс и прочие сдвиги времени. Нужно переводить в DATETIME и брать HOUR или предварительно вычитать timestamp начала суток.

[rinaz22]

Rsa97, Полученные unix time я конвертирую с помощью php функции date, так что с часовым поясом проблем нет

[Rsa97]

rinaz22, Если у вас `time` равно 0, то это 00:00:00Z или 03:00:00MSK.
А ещё смотрите на фокус:
354920399 => 1981-03-31 23:59:59 MSK
354920400 => 1981-04-01 01:00:00 MSK
То есть, прибавили секунду, а прошёл час. Сейчас в России перехода на летнее/зимнее время нет, но не факт, что снова не сделают в будущем.

[rinaz22]

Rsa97, в данный момент часовой пояс не так сильно волнует, как группировка по часам или по дням.
Я пробовал конвертировать unix time в date и использовать hour(), но он также группирует примерно также как и мой код в начале вопроса, т.е. без 0

[Rsa97]

rinaz22, Советую создать тестовую таблицу на https://www.db-fiddle.com/ с небольшим набором реальных данных, на которых возникает проблема, и привести пример вашего запроса с проблемой.

[rinaz22]

Rsa97, https://www.db-fiddle.com/f/hxitUakrSPjgBUXND5U6nk/0
Выводит:
03.09.2022, 09:00:00 - 5
03.09.2022, 17:00:00 - 1

А нужно чтобы время соответствовала начальному WHERE time (1662152400) и кратные 2 часам(7200).
Т.е. должно быть:
03.09.2022, 08:00:00 - 5
03.09.2022, 16:00:00 - 1

Вместо 7200 может быть разные интервалы, например 3600(1 час) или 86400(1 день) и т.п.

[Rsa97]

rinaz22, Теперь смотрите.

SET time_zone = 'UTC';
SELECT `id`,  FROM_UNIXTIME(`time`),  FROM_UNIXTIME(7200 * FLOOR(`time` / 7200))
  FROM `visitor_stat`
| id  | FROM_UNIXTIME(`time`) | interval            |
| --- | --------------------- | ------------------- |
| 9   | 2022-09-03 07:00:57   | 2022-09-03 06:00:00 |
| 14  | 2022-09-03 15:17:51   | 2022-09-03 14:00:00 |
| 15  | 2022-09-03 21:47:50   | 2022-09-03 20:00:00 |
| 17  | 2022-09-03 23:16:40   | 2022-09-03 22:00:00 |

Всё красиво, всё совпадает. Теперь поставим другую зону.

SET time_zone = 'Europe/Moscow';
SELECT `id`,  FROM_UNIXTIME(`time`),  FROM_UNIXTIME(7200 * FLOOR(`time` / 7200))
  FROM `visitor_stat`
| id  | FROM_UNIXTIME(`time`) | interval            |
| --- | --------------------- | ------------------- |
| 9   | 2022-09-03 10:00:57   | 2022-09-03 09:00:00 |
| 14  | 2022-09-03 18:17:51   | 2022-09-03 17:00:00 |
| 15  | 2022-09-04 00:47:50   | 2022-09-03 23:00:00 |
| 17  | 2022-09-04 02:16:40   | 2022-09-04 01:00:00 |

Что мы видим? Поскольку зона у нас +03:00, то всё время сдвинулось на нечётное количество часов. Значит работать надо не с математическими операциями, а с функциями времени.

SET time_zone = 'Europe/Moscow';
SELECT CAST(CONCAT(`t`.`hour`, ':00') AS TIME) AS `interval`, COUNT(`id`) as `count`
  FROM (
    SELECT 0 AS `hour`
    UNION ALL SELECT 2
    UNION ALL SELECT 4
    UNION ALL SELECT 6
    UNION ALL SELECT 8
    UNION ALL SELECT 10
    UNION ALL SELECT 12
    UNION ALL SELECT 14
    UNION ALL SELECT 16
    UNION ALL SELECT 18
    UNION ALL SELECT 20
    UNION ALL SELECT 22
  ) AS `t`
  LEFT JOIN `visitor_stat` AS `v`
  	ON HOUR(FROM_UNIXTIME(`v`.`time`)) BETWEEN `t`.`hour` AND `t`.`hour` + 1
  GROUP BY `t`.`hour`

| interval | count |
| -------- | ----- |
| 00:00:00 | 2     |
| 02:00:00 | 2     |
| 04:00:00 | 0     |
| 06:00:00 | 0     |
| 08:00:00 | 0     |
| 10:00:00 | 5     |
| 12:00:00 | 0     |
| 14:00:00 | 0     |
| 16:00:00 | 0     |
| 18:00:00 | 1     |
| 20:00:00 | 0     |
| 22:00:00 | 0     |

Правда, запрос получится небыстрый, поскольку в условии JOIN'a функция и индекс неприменим,

[rinaz22]

Rsa97, Возможно неправильно объяснил, 7200 это лишь для примера, т.е. группировать нужно будет и по часам и по 2 часа и по дням и т.д. В соответствии с WHERE.
Т.е. если группировать по 3 дня, то значения будут не четными, а если по 2 дня, то четными.
Уже какой день бьюсь над этой задачей, все никак не могу найти логический конец