Как создать список секунд в MySQL

Question

У меня есть следующий запрос:

  SELECT timestamp,
         COUNT(*)
    FROM table
GROUP BY timestamp

Но некоторые временные метки не отображаются, потому что нет данных. Вот пример

1:00:00 |  3
1:00:02 | 17
1:00:03 |  2

Обратите внимание, что 1:00:01 отсутствует. Есть ли способ сделать 1:00:01 | 0 появиться в результате?

Answer 1

MySQL не имеет рекурсивной функциональности, поэтому вам нужно использовать табличный трюк NUMBERS -

1. Создайте таблицу, которая содержит только увеличивающиеся числа - это легко сделать с помощью auto_increment:

   DROP TABLE IF EXISTS `example`.`numbers`;
   CREATE TABLE  `example`.`numbers` (
     `id` int(10) unsigned NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

2. Заполните таблицу, используя:

   INSERT INTO NUMBERS
     (id)
   VALUES
     (NULL)
   

   ... столько ценностей, сколько вам нужно.

3. Используйте DATE_ADD для составления списка раз, увеличивая секунды на основе значения NUMBERS.id:

   SELECT x.*
     FROM (SELECT DATE_FORMAT(DATE_ADD('2010-01-01', INTERVAL n.id - 1 SECOND), '%T') 
             FROM numbers n) x
   

4. СЛЕДУЮЩИЙ ПРИСОЕДИНЯЙТЕСЬ к своей таблице данных, основанной на временной части:

      SELECT x.ts AS timestamp,
             COALESCE(COUNT(y.timestamp), 0) AS cnt
        FROM (SELECT DATE_FORMAT(DATE_ADD('2010-01-01', INTERVAL n.id - 1 SECOND), '%T') AS ts
                FROM numbers n) x
   LEFT JOIN TABLE y ON y.timestamp = x.ts
    GROUP BY x.ts