MYSQL按照小时、按天、按月分组统计，无数据补0

在开发过程中，写统计接口时，总会遇到按时、按天、按月统计，无数据进行补零的业务需求

现在市面既有补0的做法；也有不补0的做法：

不补0对于数据统计来说简洁明了,一个单表分组查询完事。（也就是在前端展示时，某个时间没有数据则不展示该时间，对于用户来说，其实可以接受）
补0的做法在市面相对更广泛一些，就是某个时间没有数据也展示出来该时间且对应数据补0。

以下为根据时间序列进行关联查询

   目的：按小时、天、月进行分组统计，没有数据的时间段自动补零 （只能实现一个时间段内的数据条数统计）
   实现方式：
     基于左连接的方式进行查询
        左表的数据为根据时间序列建立的时间段（比如：一天24小时，00-23  一个月：05-01 ~ 05-31）
          右表的数据为业务表根据指定查询条件、对时间进行指定格式化后分组得到（分组目的：和左表生成的时间进行对应，以做关联）
     左右两表按照时间进行关联 使用IFNULL方法进行为null补0

按小时分组，统计指定某天的数据，无数据补0

获取某天每小时的数据，若是想获取24小时的话，则小时设为23，
若是想获取当天动态的话，可以用sysdate()代替CONCAT('2022-12-12',' 23:59:59')SET@i=-1;SELECT  DATE_FORMAT(DATE_SUB(CONCAT('2022-12-12',' 23:59:59'),INTERVAL((@i:=@i+1))HOUR),'%H')ASdateTimeFROM  mysql.help_category 
    WHERE@i<23orderbydateTimeasc

效果如下：

关联要查询的表后

SELECT t1.date,IFNULL(t2.count,0)AS count FROM(SELECT@a:=@a+1AS`index`, DATE_FORMAT(ADDDATE( sysdate(),INTERVAL@aHOUR),'%H')ASdateFROM mysql.help_topic,(SELECT@a:=0)tempLIMIT24)AS t1
LEFTJOIN(SELECT date_format(td.create_time,'%H')AS hours,count(*)as count
    FROM test td
    WHERE
    td.create_time BETWEEN'2023-06-19 00:00:00'AND'2023-06-19 23:59:59'groupby date_format(td.create_time,'%H')) t2 ON t1.date= t2.hours ORDERBY t1.dateasc

结果如下：

按天分组，统计指定某月的数据，无数据补0

利用Mysql服务器自带的系统表，进行获取某段日期的每天时间
2022-12-12 ：开始日期；
2022-12-25 ：结束日期；

SELECTRIGHT( date_add( date_sub('2022-12-12',INTERVAL1DAY),INTERVAL( cast( help_topic_id AS signed INTEGER)+1)DAY),5)dateTimeFROM
    mysql.help_topic 
WHERE
    help_topic_id < DATEDIFF('2022-12-15',
    date_sub('2022-12-12',INTERVAL1DAY))

结果如下：

关联业务表数据后：

SELECT t1.dateTime,IFNULL(t2.count,0)FROM(SELECTRIGHT( date_add( DATE_SUB('2023-06-01',INTERVAL1DAY),INTERVAL( cast( help_topic_id AS signed INTEGER)+1)DAY),5)dateTimeFROM mysql.help_topic WHERE help_topic_id < DATEDIFF('2023-06-30',date_sub('2023-06-01',INTERVAL1DAY))) t1
LEFTJOIN(SELECT date_format(td.create_time,'%m-%d')ASmonth,count(*)as count FROM test td
    WHERE
        td.create_time  BETWEEN'2023-06-01 00:00:00'AND'2023-06-30 23:59:59'groupby date_format(td.create_time,'%m-%d')) t2 ON t1.dateTime= t2.monthORDERBY t1.dateTimeasc

执行结果：

注意！！！ 在mybatis中需动态查询则需要进行格式化处理(不处理则查出所有日期都为0)

SELECT t1.dateTime,IFNULL(t2.count,0)FROM(SELECTRIGHT( date_add( DATE_SUB(date_format(#{queryStartTime,jdbcType=TIMESTAMP}, '%Y-%m-%d')    , INTERVAL 1 DAY ), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 1 ) DAY ), 5 ) dateTimeFROM mysql.help_topic WHERE help_topic_id < DATEDIFF(#{queryEndTime,jdbcType=TIMESTAMP},date_sub(#{queryStartTime,jdbcType=TIMESTAMP}, INTERVAL 1 DAY ))) t1
LEFTJOIN(SELECT date_format(td.create_time,'%m-%d')ASmonth,count(*)as count FROM test td
    WHERE
        td.create_time  BETWEEN'2023-06-01 00:00:00'AND'2023-06-30 23:59:59'groupby date_format(td.create_time,'%m-%d')) t2 ON t1.dateTime= t2.monthORDERBY t1.dateTimeasc

按月分组，统计指定某年的数据，无数据补0

SELECT@a:=@a+1AS`index`FROM mysql.help_topic,(SELECT@a:=0)tempLIMIT12

结果如下：

关联业务表后：

SELECT t1.index,IFNULL(t2.count,0)FROM(SELECT@a:=@a+1AS`index`FROM mysql.help_topic,(SELECT@a:=0)tempLIMIT12) t1
LEFTJOIN(SELECT date_format(td.create_time,'%m')ASmonth,count(*)as count FROM test td
    WHERE
    td.create_time  BETWEEN'2023-01-01 00:00:00'AND'2023-12-31 23:59:59'groupby date_format(td.create_time,'%m')) t2 ON t1.index= t2.monthORDERBY t1.indexasc

结果如下：