Блог

У меня есть приложение, которое получает sms-сообщения. Что я хочу сделать, так это составить статистику с помощью mysql, которая будет подсчитывать сообщения через час. Например, в 7 утра я получил 10 sms-сообщений, в 8 утра я получил 20 и т.д. В моей таблице есть следующие столбцы ID, smsText, smsDate … (другие не важны). Когда я запускаю этот скрипт:

 SELECT HOUR(smsDate), COUNT(ID) FROM SMS_MESSAGES GROUP BY HOUR(smsDate)
 

он показывает, сколько сообщений я получаю каждый час. Проблема в том, что когда я не получаю никакого сообщения, например, в 5 вечера, этот оператор не возвращает строку 17 со счетом 0, и у меня есть такой результат:

 Hour Count
...
15 10
16 5
18 2
...
 

и то, что я хочу получить, это

 Hour Count
...
15 10
16 5
17 0
18 2
...
 

Я искал решение в Интернете, что-то с UNION, но я не понимаю, как реализовать это в моем. Надеюсь, кто-нибудь сможет мне помочь.

Вы можете создать таблицу со всеми часами и объединить таблицы:

 CREATE TABLE IF NOT EXISTS `hours` (
  `hour` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `hours` (`hour`) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23);

SELECT hours.hour, count( SMS_MESSAGES.ID ) 
FROM hours
LEFT JOIN SMS_MESSAGES ON ( hours.hour = HOUR( SMS_MESSAGES.smsDate ) ) 
GROUP BY 1 
 

Поскольку hellocode ответил, что создание новой таблицы, содержащей значения часов, является хорошим подходом, вот еще один способ добиться этого с помощью объединения

 select t.`hour`,count(s.ID) from (
select 0 as `hour`
union
select 1 as `hour`
union
select 2 as `hour`
union
.
.
.
select 23 as `hour`
) t
left join SMS_MESSAGES s on(t.`hour` = hour(s.smsDate))
group by t.`hour`
 

Наблюдение: HOUR() просто извлекает час из метки времени. Возможно, вам нужны дата и час в вашем запросе. В этом ответе указаны дата и час.

Вам нужен способ получить виртуальную таблицу, содержащую все часовые временные метки в соответствующем диапазоне. Затем вам нужно присоединить эту таблицу к вашему агрегированному запросу.

Перво-наперво: вот запрос, который получит временные метки в диапазоне.

 SELECT mintime   INTERVAL seq.seq HOUR AS msghour
  FROM (
        SELECT MIN(DATE(smsDate)   INTERVAL HOUR(smsDate) HOUR) AS mintime,
               MAX(DATE(smsDate)   INTERVAL HOUR(smsDate) HOUR) AS maxtime
          FROM SMS_MESSAGES
       ) AS minmax
  JOIN seq_0_to_999999 AS seq ON seq.seq < TIMESTAMPDIFF(HOUR,mintime,maxtime)
 

Что здесь происходит? Три вещи.

Первое: DATE(smsDate) INTERVAL HOUR(smsDate) HOUR преобразует любую произвольную временную метку в временную метку в начале часа. Это позволяет нам извлекать первую и последнюю часовую временную метку в вашей таблице.

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

Во-вторых, у нас есть таблица с именем seq_0_to_999999. Он содержит последовательность кардинальных чисел: целых чисел, начинающихся с нуля. Подробнее об этом чуть позже.

Объединение этих двух таблиц вместе, затем с помощью выражения

 mintime   INTERVAL seq.seq HOUR AS msghour
 

мы можем получить таблицу, которая имеет непрерывный цикл часовых временных меток.

Затем мы присоединяем это к вашему запросу. Вот где это начинает выглядеть более сложным, чем оно есть. Мы делаем это в общих чертах:

  SELECT DATE(smsDate)   INTERVAL HOUR(smsDate) HOUR, COUNT(ID)
   FROM SMS_MESSAGES 
   JOIN ( /*the query above wit the sequence of timestamps*/) AS sq 
     ON DATE(smsDate)   INTERVAL HOUR(smsDate) HOUR = msghour
  GROUP BY DATE(smsDate)   INTERVAL HOUR(smsDate) HOUR
  ORDER BY DATE(smsDate)   INTERVAL HOUR(smsDate) HOUR
 

Собрав все это вместе, это выглядит так:

  SELECT DATE(smsDate)   INTERVAL HOUR(smsDate) HOUR, COUNT(ID)
   FROM SMS_MESSAGES 
   JOIN ( 
        SELECT mintime   INTERVAL seq.seq HOUR AS msghour
          FROM (
                SELECT MIN(DATE(smsDate)   INTERVAL HOUR(smsDate) HOUR) AS mintime,
                       MAX(DATE(smsDate)   INTERVAL HOUR(smsDate) HOUR) AS maxtime
                  FROM SMS_MESSAGES
               ) AS minmax
          JOIN seq_0_to_999999 AS seq ON seq.seq < TIMESTAMPDIFF(HOUR,mintime,maxtime)
       ) AS sq 
     ON DATE(smsDate)   INTERVAL HOUR(smsDate) HOUR = msghour
  GROUP BY DATE(smsDate)   INTERVAL HOUR(smsDate) HOUR
  ORDER BY DATE(smsDate)   INTERVAL HOUR(smsDate) HOUR
 

Это даст вам результирующий набор с меткой времени и количеством для каждого часа в диапазоне.

Наконец, что насчет этой seq_0_to_999999 таблицы последовательностей? Где мы получаем эти целые числа, начинающиеся с нуля? Ответ таков: мы должны договориться об этом; эти числа не встроены в MySQL (они есть в MariaDB v10 ).

Простой способ — создать таблицу с множеством целых чисел в ней. Однако это займет место в памяти, поэтому мы это пропустим.

Другой способ — создать короткую таблицу с целыми числами от 0 до 9 в ней, например:

 DROP TABLE IF EXISTS seq_0_to_9;
CREATE TABLE seq_0_to_9 AS
   SELECT 0 AS seq UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9;
 

Затем мы можем создать представление, которое объединяет эту таблицу с самим собой, чтобы сгенерировать 1000 таких комбинаций:

 DROP VIEW IF EXISTS seq_0_to_999;
CREATE VIEW seq_0_to_999 AS (
SELECT (a.seq   10 * (b.seq   10 * c.seq)) AS seq
  FROM seq_0_to_9 a
  JOIN seq_0_to_9 b
  JOIN seq_0_to_9 c
);
 

Наконец, мы можем объединить эту таблицу из 1000 чисел с самой собой, чтобы создать представление, которое будет генерировать миллион подобных комбинаций:

 DROP VIEW IF EXISTS seq_0_to_999999;
CREATE VIEW seq_0_to_999999 AS (
SELECT (a.seq   (1000 * b.seq)) AS seq
  FROM seq_0_to_999 a
  JOIN seq_0_to_999 b
);
 

Вот запись, предоставляющая дополнительную информацию обо всем этом. http://www.plumislandmedia.net/mysql/filling-missing-data-sequences-cardinal-integers /