mysql数据查询时间空缺的情况下回补时间数据

mysql数据查询时间空缺的情况下回补时间

一.应用案列

我要统计最近两天每小时的温度，但是我实际表中只有部分数据，而且每个小时的数据是随机有无的，但是再返回的数据中必须按时间排序返回满48条数据，因为前段要用时间画图，如果时间缺了就会导致画统计图有问题。

1.1 数据表结构：
 DROP TABLE IF EXISTS `tp_table`;
  CREATE TABLE `tp_table`  (
    `temperature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '温度度数',
    `addtime` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '时间（小时）',
    `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键',
    UNIQUE INDEX `index_id`(`id`) USING BTREE
  ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  ​

 

1.2 实际数据：
 INSERT INTO `tp_table` VALUES ('33℃', '2020-08-11 01', 1);
  INSERT INTO `tp_table` VALUES ('32℃', '2020-08-11 02', 2);
  INSERT INTO `tp_table` VALUES ('36℃', '2020-08-11 05', 3);
  INSERT INTO `tp_table` VALUES ('35℃', '2020-08-11 06', 4);
  INSERT INTO `tp_table` VALUES ('27℃', '2020-08-11 08', 5);
  INSERT INTO `tp_table` VALUES ('36℃', '2020-08-11 12', 6);
  INSERT INTO `tp_table` VALUES ('38℃', '2020-08-11 18', 7);
  INSERT INTO `tp_table` VALUES ('34℃', '2020-08-12 11', 8);
  INSERT INTO `tp_table` VALUES ('30℃', '2020-08-12 12', 9);
  INSERT INTO `tp_table` VALUES ('37℃', '2020-08-12 13', 10);
  INSERT INTO `tp_table` VALUES ('30℃', '2020-08-12 14', 11);
  INSERT INTO `tp_table` VALUES ('32℃', '2020-08-12 15', 12);
  INSERT INTO `tp_table` VALUES ('32℃', '2020-08-12 16', 13);
  INSERT INTO `tp_table` VALUES ('36℃', '2020-08-12 17', 14);
  INSERT INTO `tp_table` VALUES ('31℃', '2020-08-12 19', 15);
  INSERT INTO `tp_table` VALUES ('28℃', '2020-08-12 22', 16);
  INSERT INTO `tp_table` VALUES ('32℃', '2020-08-12 23', 17);
  INSERT INTO `tp_table` VALUES ('38℃', '2020-08-11 21', 18);
  INSERT INTO `tp_table` VALUES ('36℃', '2020-08-13 22', 19);

 

1.3 根据需求查询两天的数据，这里就查询（2020-08-11和2020-08-12）两天的数据
select * from test.tp_table where addtime>="2020-08-11 00" and addtime<="2020-08-12 23"

 

 

 

 

从图中可以看出实际两天的数据一共只有18条，但是我们需求是要返回两天的每个小时的24条数据，而且时间不能为空，因为前段要用时间画统计图啥的。

 

所以才会有接下来的步骤：

1.4 创建一张工具表，结构如下：
  DROP TABLE IF EXISTS `sys_timeutil`;
  CREATE TABLE `sys_timeutil`  (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增有序的主键，用来会不时间用',
    `addtime` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '添加时间，没有实际用处',
    UNIQUE INDEX `index_id`(`id`) USING BTREE
  ) ENGINE = InnoDB AUTO_INCREMENT = 29 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

 

1.5 表中添加数据，数据如下：

添加数据的条数取决于你需要回补时间的条数，两天是48小时，所以这里添加49条数据，具体用途，往下看。

 INSERT INTO `sys_timeutil` VALUES (1, '2020-08-14 10:31:45');
  INSERT INTO `sys_timeutil` VALUES (2, '2020-08-14 10:31:46');
  INSERT INTO `sys_timeutil` VALUES (3, '2020-08-14 10:31:46');
  INSERT INTO `sys_timeutil` VALUES (4, '2020-08-14 10:31:47');
  INSERT INTO `sys_timeutil` VALUES (5, '2020-08-14 10:31:48');
  INSERT INTO `sys_timeutil` VALUES (6, '2020-08-14 10:31:48');
  INSERT INTO `sys_timeutil` VALUES (7, '2020-08-14 10:31:48');
  INSERT INTO `sys_timeutil` VALUES (8, '2020-08-14 10:31:48');
  INSERT INTO `sys_timeutil` VALUES (9, '2020-08-14 10:31:49');
  INSERT INTO `sys_timeutil` VALUES (10, '2020-08-14 10:31:49');
  INSERT INTO `sys_timeutil` VALUES (11, '2020-08-14 10:31:49');
  INSERT INTO `sys_timeutil` VALUES (12, '2020-08-14 10:31:50');
  INSERT INTO `sys_timeutil` VALUES (13, '2020-08-14 10:31:51');
  INSERT INTO `sys_timeutil` VALUES (14, '2020-08-14 10:31:52');
  INSERT INTO `sys_timeutil` VALUES (15, '2020-08-14 10:31:52');
  INSERT INTO `sys_timeutil` VALUES (16, '2020-08-14 10:31:52');
  INSERT INTO `sys_timeutil` VALUES (17, '2020-08-14 10:31:52');
  INSERT INTO `sys_timeutil` VALUES (18, '2020-08-14 10:31:54');
  INSERT INTO `sys_timeutil` VALUES (19, '2020-08-14 10:31:54');
  INSERT INTO `sys_timeutil` VALUES (20, '2020-08-14 10:31:54');
  INSERT INTO `sys_timeutil` VALUES (21, '2020-08-14 10:31:58');
  INSERT INTO `sys_timeutil` VALUES (22, '2020-08-14 10:31:58');
  INSERT INTO `sys_timeutil` VALUES (23, '2020-08-14 10:31:58');
  INSERT INTO `sys_timeutil` VALUES (24, '2020-08-14 10:31:59');
  INSERT INTO `sys_timeutil` VALUES (25, '2020-08-14 10:31:59');
  INSERT INTO `sys_timeutil` VALUES (26, '2020-08-14 10:31:59');
  INSERT INTO `sys_timeutil` VALUES (27, '2020-08-14 10:31:59');
  INSERT INTO `sys_timeutil` VALUES (28, '2020-08-14 10:32:00');
  INSERT INTO `sys_timeutil` VALUES (29, '2020-08-14 10:32:00');
  INSERT INTO `sys_timeutil` VALUES (30, '2020-08-14 10:32:00');
  INSERT INTO `sys_timeutil` VALUES (31, '2020-08-14 10:32:00');
  INSERT INTO `sys_timeutil` VALUES (32, '2020-08-14 10:32:00');
  INSERT INTO `sys_timeutil` VALUES (33, '2020-08-14 10:32:01');
  INSERT INTO `sys_timeutil` VALUES (34, '2020-08-14 10:32:02');
  INSERT INTO `sys_timeutil` VALUES (35, '2020-08-14 10:32:02');
  INSERT INTO `sys_timeutil` VALUES (36, '2020-08-14 10:32:02');
  INSERT INTO `sys_timeutil` VALUES (37, '2020-08-14 10:32:02');
  INSERT INTO `sys_timeutil` VALUES (38, '2020-08-14 10:32:02');
  INSERT INTO `sys_timeutil` VALUES (39, '2020-08-14 10:32:03');
  INSERT INTO `sys_timeutil` VALUES (40, '2020-08-14 10:32:03');
  INSERT INTO `sys_timeutil` VALUES (41, '2020-08-14 10:32:03');
  INSERT INTO `sys_timeutil` VALUES (42, '2020-08-14 10:32:04');
  INSERT INTO `sys_timeutil` VALUES (43, '2020-08-14 10:32:04');
  INSERT INTO `sys_timeutil` VALUES (44, '2020-08-14 10:32:04');
  INSERT INTO `sys_timeutil` VALUES (45, '2020-08-14 10:32:05');
  INSERT INTO `sys_timeutil` VALUES (46, '2020-08-14 10:32:06');
  INSERT INTO `sys_timeutil` VALUES (47, '2020-08-14 10:32:06');
  INSERT INTO `sys_timeutil` VALUES (48, '2020-08-14 10:32:07');
  INSERT INTO `sys_timeutil` VALUES (49, '2020-08-14 10:32:07');
  ​

 

1.6 使用两张表结合，把数据补充完整

使用sys_timeutil这张表把两天48小时的所有时间都回补出来

  
  select 
          date_format(date_add("2020-08-10 23:00:00",INTERVAL id HOUR),'%Y-%m-%d %H') as time from test.sys_timeutil
          where date_format(date_add("2020-08-10  23:00:00",INTERVAL id HOUR),'%Y-%m-%d 00:00:00') <= date_format("2020-08-12",'%Y-%m-%d 23:00:00')

 

 

 

 

使用左连接以sys_timeutil为主表把数据查询出来，具体sql语句如下：

  SELECT * from
       (
           select date_format(date_add("2020-08-10 23:00:00",INTERVAL id HOUR),'%Y-%m-%d %H') as time from test.sys_timeutil
              where date_format(date_add("2020-08-10  23:00:00",INTERVAL id HOUR),'%Y-%m-%d 00:00:00') <= date_format("2020-08-12",'%Y-%m-%d 23:00:00')
       ) t1 left join 
       (
               select * from test.tp_table where addtime>="2020-08-11 00"  and addtime<="2020-08-12 23"
       ) t2 
       on t2.addtime=t1.time
       ORDER BY t1.time

 

部分结果截图如下：

数据该有的就有，没有的就为空，没有数据的时间也已经回补了，前段可以根据完整的时间画统计图

 

1.7 最简便的时间补全方式

使用mysql.help_topic这张表把需要的时间都回补出来

SELECT
DATE_ADD( date '2019-01-01', INTERVAL help_topic_id DAY ) AS time
FROM
mysql.help_topic
WHERE
help_topic_id < timestampdiff(
DAY,
FROM_UNIXTIME( 1609085517418 / 1000, '%Y-%m-%d' ),
FROM_UNIXTIME( 1612333890045 / 1000, '%Y-%m-%d' ))

效果

 

 

 此处结束这个问题的记录，这是工作中遇到的一个需求，以后也会遇到，只要时间的回补，不管单位是多少其实都是可以微调满足需求的，值得借鉴，记录供以后查看思路，

 

posted @ 2020-08-14 11:07  憨憨青年  阅读(536)  评论(0)  编辑  收藏  举报
// 侧边栏目录 // https://blog-static.cnblogs.com/files/douzujun/marvin.nav.my1502.css