mysql - mysql如何统计一个组中的记录数

Question

比如我有一个表1:

+---------------------+
| StartTime           |
+---------------------+
| 2014-04-09 09:47:53 |
| 2014-04-09 09:47:53 |
| 2014-04-09 09:47:55 |
| 2014-04-09 09:47:56 |
| 2014-04-09 09:47:57 |
| 2014-04-09 09:47:59 |
+---------------------+

表格没有连续的时间。
例如，它没有

2014-04-09 09:47:54,
2014-04-09 09:47:58

等然后我使用:

select count(*),StartTime from this_tables group by StartTime;

我得到另一个表 2:

+-------+---------------------+
| count | StartTime           |
+-------+---------------------+
|     2 | 2014-04-09 09:47:53 |
|     1 | 2014-04-09 09:47:55 |
|     1 | 2014-04-09 09:47:56 |
|     1 | 2014-04-09 09:47:57 |
|     1 | 2014-04-09 09:47:59 |
+-------+---------------------+

但我想要一个 table3 看起来像:

+-------+---------------------+
| count | StartTime           |
+-------+---------------------+
|     2 | 2014-04-09 09:47:53 |
|     0 | 2014-04-09 09:47:54 |
|     1 | 2014-04-09 09:47:55 |
|     1 | 2014-04-09 09:47:56 |
|     1 | 2014-04-09 09:47:57 |
|     0 | 2014-04-09 09:47:58 |
|     1 | 2014-04-09 09:47:59 |
+-------+---------------------+

对于不存在的 StartTime，表 3 可以包含“0”。我怎么能用mysql呢？

Answer 1

为此，您需要一个数字(理货)表。您可以通过以下方式创建和填充此类表(在本例中有 100 行)

CREATE TABLE tally (n INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

INSERT INTO tally
SELECT a.N + b.N * 10 + 1 n
 FROM 
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
ORDER BY n;

您需要在此表中拥有与查询的最大可能间隔中的秒数一样多的行。

现在您正在寻找的查询(为了这个示例的一天 2014-04-09)可能看起来像这样

SELECT b.starttime, COALESCE(count, 0) count
  FROM
(
  SELECT min_dt + INTERVAL n-1 SECOND starttime
    FROM tally t CROSS JOIN 
  (
    SELECT MIN(starttime) min_dt, MAX(starttime) max_dt
      FROM table1
     WHERE starttime >= '2014-04-09'
       AND starttime < '2014-04-09' + INTERVAL 1 DAY
  ) i 
   WHERE t.n-1 <= TIMESTAMPDIFF(SECOND, min_dt, max_dt)
) b LEFT JOIN 
(
  SELECT starttime, COUNT(*) count
    FROM table1
   WHERE starttime >= '2014-04-09'
     AND starttime < '2014-04-09' + INTERVAL 1 DAY
   GROUP BY starttime
) q
    ON b.starttime = q.starttime

输出:

|                    STARTTIME | COUNT ||------------------------------|-------|| April, 09 2014 09:47:53+0000 |     2 || April, 09 2014 09:47:54+0000 |     0 || April, 09 2014 09:47:55+0000 |     1 || April, 09 2014 09:47:56+0000 |     1 || April, 09 2014 09:47:57+0000 |     1 || April, 09 2014 09:47:58+0000 |     0 || April, 09 2014 09:47:59+0000 |     1 |

这是一个 SQLFiddle 演示