Mysql、SUM列和按小时分组

Question

情况(简化)：

tableA

id |   date               | val
------------------------------
0    2018-02-19 00:01:00  | 10
1    2018-02-19 00:02:00  | 10
2    2018-02-19 00:03:00  | 10
..   2018-02-19 23:59:00  | 10

我需要做一个查询，每小时返回列val的和。

这是查询

SELECT `AllHours`.`hour` , COALESCE(SUM(`A`.`val`),0) AS `A`.`total`
FROM `tableA` AS `A`

RIGHT JOIN (
    SELECT  0 AS `hour`
    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
         UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
         UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
         UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18
         UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21
         UNION ALL SELECT 22 UNION ALL SELECT 23
  )      AS `AllHours` ON HOUR(`A`.`date`) = `AllHours`.`hour`


WHERE `A`.`date` BETWEEN '2018-02-19 00:00:00' AND '2018-02-19 23:59:59' OR `A`.`date` IS NULL

GROUP BY `AllHours`.`hour`
ORDER BY `AllHours`.`hour`

结果

​

​

查询正常，但缺少11小时。

重要注意事项我需要所有的时间，如果没有数据的话。否则，我就不会添加正确的联接，按小时(日期)计算，它就足够了。

Answer 1

考虑使用数字表-参见下面的链接。这将为您提供当前所需的输出：

Testdata：

CREATE TABLE T   (`id` int, `dt` datetime, `val` int);        
INSERT INTO T    (`id`, `dt`, `val`) 
VALUES
    (0, '2018-02-19 00:01:00', 10),
    (1, '2018-02-19 00:02:00', 10),
    (2, '2018-02-19 00:03:00', 10),
    (4, '2018-02-19 01:01:00', 10),
    (5, '2018-02-19 01:02:00', 10),
    (6, '2018-02-19 02:03:00', 10)
;

Sql：

select 
  lpad(cast(HH as char(2)),2,'0') as hour,  
  sum(val) as sumVal from
(
    select 
      EXTRACT(HOUR from dt) AS HH,
      val
    from T 
    WHERE dt >= '2018-02-19 0:0:0' and dt < '2018-02-20 0:0:0'

    UNION ALL SELECT  0,0
    UNION ALL SELECT  1,0 UNION ALL SELECT  2,0  UNION ALL SELECT  3,0
    UNION ALL SELECT  4,0 UNION ALL SELECT  5,0  UNION ALL SELECT  6,0
    UNION ALL SELECT  7,0 UNION ALL SELECT  8,0 UNION ALL SELECT  9,0
    UNION ALL SELECT 10,0 UNION ALL SELECT 11,0 UNION ALL SELECT 12,0
    UNION ALL SELECT 13,0 UNION ALL SELECT 14,0 UNION ALL SELECT 15,0
    UNION ALL SELECT 16,0 UNION ALL SELECT 17,0 UNION ALL SELECT 18,0
    UNION ALL SELECT 19,0 UNION ALL SELECT 20,0 UNION ALL SELECT 21,0
    UNION ALL SELECT 22,0 UNION ALL SELECT 23,0
) as m
group by lpad(cast(HH as char(2)),2,'0')

获取的输出为：

hour    sumVal
00  30
01  20
02  10
03  0
04  0
05  0
06  0
07  0
08  0
09  0
10  0
11  0
12  0
13  0
14  0
15  0
16  0
17  0
18  0
19  0
20  0
21  0
22  0
23  0

如果您经常需要这样做，请创建一个numbers表，并使用您已经拥有的联接语法。没有一个数字表，你也可以联合所有没有加入。

所以-准备：

• MYSQL: Sequential Number Table
• Creating a "Numbers Table" in mysql

有了一个数字表，上面所有的大联盟都可以重写为

UNION ALL SELECT  num, 0 from numbers where num between 0 and 23

或者您可以在它和合并语法上使用一个连接。

Answer 2

试一试：

SELECT
`AllHours`.`tmp_hour`,
COALESCE(SUM(`val`),0) AS `total`
    FROM
      tableA
    RIGHT JOIN (
    SELECT  0 AS `tmp_hour`
    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
         UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
         UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
         UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18
         UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21
         UNION ALL SELECT 22 UNION ALL SELECT 23
  )      AS `AllHours` ON DATE_FORMAT(dt, '%H') = `AllHours`.`tmp_hour`

    WHERE  `dt` BETWEEN '2018-02-19 00:00:00' AND '2018-02-19 23:59:59' OR `dt` IS NULL

    GROUP BY `AllHours`.`tmp_hour`
    ORDER BY `AllHours`.`tmp_hour`