一行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'))

DATE_FORMAT(CURDATE() - INTERVAL (DAY(CURDATE()) - 1) DAY, '%Y-%m-01') 表示取当月的1号
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，例子如下：