mysql 查询一天中每个小时的数据量

需求查询某一天中每个小时的订单数量, 如果某个时间段没有数据, 就填充 0. 效果如下:建表语句-- 一张简化过的 xx 订单表CREATE TABLE `mod_work_order` (`id` int(11) NOT NULL,`create_time` datetime NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=u

nimo10050

7251人浏览 · 2021-03-22 16:06:43

nimo10050 · 2021-03-22 16:06:43 发布

需求

查询某一天中每个小时的订单数量, 如果某个时间段没有数据, 就填充 0. 效果如下:
在这里插入图片描述

建表语句

-- 一张简化过的 xx 订单表
CREATE TABLE `mod_work_order` (
  `id` int(11) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

模拟2021年3月22日当天的数据

INSERT INTO `mod_work_order` VALUES (1, '2021-03-22 14:26:31');
INSERT INTO `mod_work_order` VALUES (2, '2021-03-22 14:26:31');
INSERT INTO `mod_work_order` VALUES (3, '2021-03-22 07:26:31');
INSERT INTO `mod_work_order` VALUES (4, '2021-03-22 14:26:31');
INSERT INTO `mod_work_order` VALUES (5, '2021-03-22 14:26:31');
INSERT INTO `mod_work_order` VALUES (6, '2021-03-22 08:26:31');
INSERT INTO `mod_work_order` VALUES (7, '2021-03-22 14:26:31');
INSERT INTO `mod_work_order` VALUES (8, '2021-03-22 14:26:31');
INSERT INTO `mod_work_order` VALUES (9, '2021-03-22 19:26:31');

实现步骤

第一步

一开始很容易就能想到如下的 sql, 就是按小时分组嘛, 然后按时间点排序

SELECT HOUR(create_time) AS order_hour, count(id) AS num 
FROM mod_work_order 
WHERE create_time >= str_to_date('2021-03-22 00:00:00','%Y-%m-%d %T')    
  AND create_time <= str_to_date('2021-03-22 23:59:59','%Y-%m-%d %T')   
GROUP BY order_hour ORDER BY order_hour;

而且如果你的测试环境刚好有这一天 24 小时的数据的话, 稍微不细心就觉得这样就 ok 了. 但实际上如果一旦哪个时间段没数据, 就会是下面的结果:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-satSscJq-1616400071892)(/Users/zhanggaopei/Library/Application Support/typora-user-images/image-20210322151239707.png)]

第二步

查询时, 构建一张临时表, 里面包含了 24 小时, 作为默认数据.

 SELECT 0 AS order_hour UNION ALL SELECT 1 AS order_hour UNION ALL
 SELECT 2 AS order_hour UNION ALL SELECT 3 AS order_hour UNION ALL 
 SELECT 4 AS order_hour UNION ALL SELECT 5 AS order_hour UNION ALL 
 SELECT 6 AS order_hour UNION ALL SELECT 7 AS order_hour UNION ALL 
 SELECT 8 AS order_hour UNION ALL SELECT 9 AS order_hour UNION ALL 
 SELECT 10 AS order_hour UNION ALL SELECT 11 AS order_hour UNION ALL 
 SELECT 12 AS order_hour UNION ALL SELECT 13 AS order_hour UNION ALL 
 SELECT 14 AS order_hour UNION ALL SELECT 15 AS order_hour UNION ALL 
 SELECT 16 AS order_hour UNION ALL SELECT 17 AS order_hour UNION ALL 
 SELECT 18 AS order_hour UNION ALL SELECT 19 AS order_hour UNION ALL 
 SELECT 20 AS order_hour UNION ALL SELECT 21 AS order_hour UNION ALL 
 SELECT 22 AS order_hour UNION ALL SELECT 23 AS order_hour

在这里插入图片描述

第三步

将第一步和第二步的查询结果, 进行左连接.

select a.order_hour as order_hour, ifnull(b.num, 0) as num

from 第二步的查询结果 as a

LEFT JOIN 第一步的查询结果 as b
ON a.order_hour=b.order_hour

ORDER BY order_hour;

最终结果

select a.order_hour as order_hour, ifnull(b.num, 0) as num 

from (
 SELECT 0 AS order_hour UNION ALL SELECT 1 AS order_hour UNION ALL
 SELECT 2 AS order_hour UNION ALL SELECT 3 AS order_hour UNION ALL 
 SELECT 4 AS order_hour UNION ALL SELECT 5 AS order_hour UNION ALL 
 SELECT 6 AS order_hour UNION ALL SELECT 7 AS order_hour UNION ALL 
 SELECT 8 AS order_hour UNION ALL SELECT 9 AS order_hour UNION ALL 
 SELECT 10 AS order_hour UNION ALL SELECT 11 AS order_hour UNION ALL 
 SELECT 12 AS order_hour UNION ALL SELECT 13 AS order_hour UNION ALL 
 SELECT 14 AS order_hour UNION ALL SELECT 15 AS order_hour UNION ALL 
 SELECT 16 AS order_hour UNION ALL SELECT 17 AS order_hour UNION ALL 
 SELECT 18 AS order_hour UNION ALL SELECT 19 AS order_hour UNION ALL 
 SELECT 20 AS order_hour UNION ALL SELECT 21 AS order_hour UNION ALL 
 SELECT 22 AS order_hour UNION ALL SELECT 23 AS order_hour 
) as a

LEFT JOIN 

(
	SELECT HOUR(create_time) AS order_hour, count(id) AS num FROM mod_work_order 
  WHERE create_time >= str_to_date('2021-03-22 00:00:00','%Y-%m-%d %T')    
  AND create_time <= str_to_date('2021-03-22 23:59:59','%Y-%m-%d %T')   
  GROUP BY order_hour
) b ON a.order_hour=b.order_hour

ORDER BY order_hour;