Отображение сводных данных за час

У меня есть база данных MySQL с таблицей, содержащей логины с отметками времени, и я хотел бы получить количество логинов за каждый час за последние 24 часа.

Сначала я попробовал что-то очевидное:

SELECT
  timestamp AS Hour,
  COUNT(*) AS Logins
FROM auth
WHERE
  timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY HOUR(timestamp)
ORDER BY timestamp DESC

Это приводит к выводу, подобному следующему:

+---------------------+--------+
| Hour                | Logins |
+---------------------+--------+
| 2017-10-08 17:00:05 |    272 |
| 2017-10-08 16:00:02 |    323 |
| 2017-10-08 15:00:34 |    301 |
| 2017-10-08 14:55:10 |     30 |
| 2017-10-08 11:04:27 |    107 |
| 2017-10-08 10:06:26 |    115 |
| 2017-10-08 09:00:11 |     92 |
| 2017-10-08 08:02:34 |    195 |
| 2017-10-08 07:03:15 |    171 |
| 2017-10-08 06:03:06 |    133 |
| 2017-10-08 05:00:20 |    102 |
| 2017-10-08 04:03:23 |    198 |
| 2017-10-08 03:00:23 |    345 |
| 2017-10-08 02:01:39 |    318 |
| 2017-10-08 01:01:22 |    205 |
| 2017-10-08 00:00:24 |    334 |
| 2017-10-07 23:00:00 |    501 |
| 2017-10-07 22:00:10 |    377 |
| 2017-10-07 21:00:02 |    482 |
| 2017-10-07 20:00:04 |    349 |
| 2017-10-07 19:00:54 |    298 |
| 2017-10-07 18:13:06 |    438 |
+---------------------+--------+
22 rows in set (0,02 sec)

Есть две проблемы с этим выводом. Во-первых, временные метки не равны целому часу, потому что первый вход в систему произошел через несколько минут/секунд после точного часа. Кроме того, мне действительно не нужна дата в выводе. Я исправил эту проблему, выполнив следующие действия:

SELECT
  DATE_FORMAT(DATE_ADD(timestamp, INTERVAL 30 MINUTE),'%H:00:00') AS Hour,
  COUNT(*) AS Logins
FROM auth
WHERE
  timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY HOUR(timestamp)
ORDER BY timestamp DESC

и выход сейчас

+----------+--------+
| Hour     | Logins |
+----------+--------+
| 17:00:00 |    272 |
| 16:00:00 |    323 |
| 15:00:00 |    301 |
| 15:00:00 |     30 |
| 11:00:00 |    107 |
| 10:00:00 |    115 |
| 09:00:00 |     92 |
| 08:00:00 |    195 |
| 07:00:00 |    171 |
| 06:00:00 |    133 |
| 05:00:00 |    102 |
| 04:00:00 |    198 |
| 03:00:00 |    345 |
| 02:00:00 |    318 |
| 01:00:00 |    205 |
| 00:00:00 |    334 |
| 23:00:00 |    501 |
| 22:00:00 |    377 |
| 21:00:00 |    482 |
| 20:00:00 |    349 |
| 19:00:00 |    298 |
| 18:00:00 |    452 |
+----------+--------+
22 rows in set (0,00 sec)

Вопрос 1. Это хороший способ сделать это (отображение всего часа) или есть лучший способ?

Вторая проблема, которую я не знаю, как решить. Видите ли, приведенная выше команда была выполнена в 18:19 по местному времени. Обратите внимание, что количество входов в систему между 18:00 и 18:19 отсутствует. Да, я понимаю, что последний целый час еще не закончился, но все же хотелось бы отобразить данные, которые накопились за прошедшую его часть.

Вопрос 2. Как это сделать?


mysql datetime group-by hour
person bontchev    schedule 08.10.2017    source источник
comment
Я склонен считать плохой практикой группировать по чему-либо, кроме того, что вы выбираете. Проблемы с отображением должны обрабатываться в коде приложения.   -  person Strawberry    schedule 08.10.2017
comment
@Клубничка, не знаю, что ты имеешь в виду. Я выбираю и группирую по timestamp. Вы имеете в виду, что я должен использовать HOUR(DATE_ADD(timestamp, INTERVAL 30 MINUTE)) вместо DATE_FORMAT(DATE_ADD(timestamp, INTERVAL 30 MINUTE),'%H:00:00')? Хорошо, как угодно, но проблема №2 этим не решается.   -  person bontchev    schedule 08.10.2017
comment
Почему вы добавляете 30 минут к timestamp?   -  person kmoser    schedule 09.10.2017
comment
@kmoser, ты прав, это не обязательно; просто HOUR(timestamp) подойдет. Это осталось от того, когда я пытался преобразовать метку времени в целый час. Но его удаление не решает проблему отсутствия данных за последний (неполный) час.   -  person bontchev    schedule 09.10.2017
comment
@bontchev В своем исходном сообщении вы написали, что количество входов в систему с 18:00 до 18:19 отсутствует. Это неправильно. В строке 18:00 суммируются все входы в систему с 18:00 до 18:59:59. Если вы запустите запрос в 18:19, он будет включать входы с 18:00 до 18:19 (т.е. текущее время).   -  person kmoser    schedule 09.10.2017
comment
@kmoser, да, ты прав. Проблема в группировке. Он группирует данные за последний час с данными за первый час 24-часового периода. Я понял, как это решить; Я опубликую ответ.   -  person bontchev    schedule 09.10.2017

Ответы (2)


arrow_upward
2
arrow_downward

Как указал @kmoser, проблема связана с тем, что данные за последний (неполный) час 24-часового периода группируются с первым его часом, потому что номер часа тот же. Чтобы решить эту проблему, нам нужно сгруппировать по чему-то, что отличает одни и те же часы в разные дни. Вот решение, которое сработало для меня:

SELECT
  DATE_FORMAT(timestamp, '%Y-%m-%d %H:00:00') AS Hour,
  COUNT(*) AS Logins
FROM auth
WHERE
  timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY DATE_FORMAT(timestamp, '%Y-%m-%d %H:00:00')
ORDER BY timestamp DESC
person bontchev    schedule 09.10.2017

arrow_upward

arrow_downward

person    schedule
comment
Этот ответ ужасно, совершенно неправильный. Вы хоть вопрос поняли? - person bontchev; 09.10.2017