一行SQL语句实现统计未来7天、按月统计数据,无数据填充0
1 背景
由于业务需求,在项目的报表中心中需要未来7天、按月统计数据,且要求按天补全数据,补数据填为0。附实测SQL语句,请大家指正。
2 举例
2.1未来7天,按天补全数据,无数据填充0
sql语句:
select t1.lastDays as x, IFNULL(t2.count,0) as val from (SELECT date_format( @lastDay := date_add( @lastDay, INTERVAL 1 DAY ), '%Y-%m-%d' ) lastDays FROM ( SELECT @lastDay := date_add( curdate( ), INTERVAL - 1 DAY ) FROM mysql.help_topic LIMIT 7 ) a)as t1 left join (select DATE_FORMAT(maintenance_time, '%Y-%m-%d') as time_1, SUM(b.employee_number) as count from maintenance_plan a LEFT JOIN maintenance_strategy b ON (b.model_id = a.model_id) where a.maintenance_type=1 group by maintenance_time)t2 on t2.time_1=t1.lastDays
查询结果:
2.2 当月1-月底,按天补全数据,无数据填充0
sql语句:
select t1.lastDays as x, IFNULL(t2.count,0) as val from (SELECT date_format(date_add(@firstDayOfMonth := DATE_FORMAT(CURDATE() - INTERVAL (DAY(CURDATE()) - 1) DAY, '%Y-%m-01'), INTERVAL a.help_topic_id DAY), '%Y-%m-%d') AS lastDays FROM mysql.help_topic a WHERE a.help_topic_id BETWEEN 0 AND DATEDIFF(@lastDayOfMonth := LAST_DAY(CURDATE()), @firstDayOfMonth := DATE_FORMAT(CURDATE() - INTERVAL (DAY(CURDATE()) - 1) DAY, '%Y-%m-01')))as t1 left join (select DATE_FORMAT(maintenance_time, '%Y-%m-%d') as time_1, COUNT(x.id) as count from maintenance_plan x group by DATE_FORMAT(x.maintenance_time, '%Y-%m-%d'))t2 on t2.time_1=t1.lastDays
查询结果:还有2024-05-01和2024-05-02
3 SQL语句原理和拆解说明
原理:使用MySql的系统表help_topic,使用表中自增ID(help_topic_id),用于进行行表关联操作和计数。使用sql语句查询,发现help_topic累计有701条数据(包括help_topic_id=0),MySql版本为:8.0.27
3.1 拆解思路
3.1.1 第一步:通过help_topic取连续的数值段
查询未来7天SQL语句,如下:
SELECT date_format( @lastDay := date_add( @lastDay, INTERVAL 1 DAY ), '%Y-%m-%d' ) lastDays FROM ( SELECT @lastDay := date_add( curdate( ), INTERVAL - 1 DAY ) FROM mysql.help_topic LIMIT 7 ) a
date_add( @lastDay, INTERVAL 1 DAY ) 表示每次增加1天,
date_add( curdate( ), INTERVAL - 1 DAY ) 表示包括当天
LIMIT 7 表示取7条记录
执行结果:
查询当月每天SQL语句,如下:
SELECT date_format(date_add(@firstDayOfMonth := DATE_FORMAT(CURDATE() - INTERVAL (DAY(CURDATE()) - 1) DAY, '%Y-%m-01'), INTERVAL a.help_topic_id DAY), '%Y-%m-%d') AS lastDays FROM mysql.help_topic a WHERE a.help_topic_id BETWEEN 0 AND DATEDIFF(@lastDayOfMonth := LAST_DAY(CURDATE()), @firstDayOfMonth := DATE_FORMAT(CURDATE() - INTERVAL (DAY(CURDATE()) - 1) DAY, '%Y-%m-01'))
LAST_DAY(CURDATE()) 表示当前日期的当月最后一天
执行结果:
3.1.2 第二步:使用left join左连接业务表
有了第一步的日期字段,结合业务表时,需要使用left join进行左连接。
使用on,将业务表中日期字段格式化为%Y-%m-%d,与日期字段(lastDays)格式保持一致,并将统计的结果值命名为指定的字段名(count),用于使用别名t2.count取值。如下所示
select t1.lastDays as x, IFNULL(t2.count,0) as val from (SELECT date_format(date_add(@firstDayOfMonth := DATE_FORMAT(CURDATE() - INTERVAL (DAY(CURDATE()) - 1) DAY, '%Y-%m-01'), INTERVAL a.help_topic_id DAY), '%Y-%m-%d') AS lastDays FROM mysql.help_topic a WHERE a.help_topic_id BETWEEN 0 AND DATEDIFF(@lastDayOfMonth := LAST_DAY(CURDATE()), @firstDayOfMonth := DATE_FORMAT(CURDATE() - INTERVAL (DAY(CURDATE()) - 1) DAY, '%Y-%m-01')))as t1 left join (select DATE_FORMAT(maintenance_time, '%Y-%m-%d') as time_1, COUNT(x.id) as count from maintenance_plan x group by DATE_FORMAT(x.maintenance_time, '%Y-%m-%d'))t2 on t2.time_1=t1.lastDays
3.1.3 第三步:查询数据时,使用IFNULL校验数据值,当为NULL时,赋值为0
如果业务表中无该日期的数据,left join业务表中该日期的记录为null,故可以在查询结果中使用IFNULL校验t2.count,当统计天无数据时赋值为0。
4 拓展
统计未来7个月,将DAY换成MONTH,例子如下: