SQL--统计日期期间内每天的数据量，没有补0

需求：

在某些业务后台看板中，需要统计某日期范围内每天的数据量，以及总的数据量，如果某一天没有数据，则补0。

分析：

1、在数据库中规定日期范围内的日期可能不全，但是最后需要统计并展示每天的数据，所以要考虑日期补全
2、不存在的日期没有数据，要设置为0
3、最后需要对每天的数据量进行累加，统计每天的总数据量

实现：

第一步：统计结果

SELECT DATE_FORMAT(create_time, '%Y-%m-%d') date, count(id) num
FROM tb_clue
WHERE create_time BETWEEN #{startTime}
        AND #{endTime}
GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d')

DATE_FORMAT(date,format)函数用于以不同的格式显示日期/时间数据。date 参数是合法的日期表达式，format参数是日期/时间数据格式。此例中格式为yyyy-MM-dd

第二步：生成一个日期表

SELECT @cdate := DATE_ADD(@cdate, INTERVAL -1 DAY) date, 0 AS cou
FROM (
       SELECT @cdate := DATE_ADD(#{endTime}, INTERVAL + 1 DAY)
       FROM [任意有数据的表,但表的数据量尽量大，能包含开始时间到结束时间所有的数据]
   ) t1
WHERE @cdate > #{startTime}
ORDER BY date

@cdate := 是定义名为cdate的局部变量并赋值（select 后面必须用：=）。没有"@"的字段代表是列名

DATE_ADD(date,INTERVAL expr type)表示对时间添加指定的时间间隔。date 参数是合法的日期表达式，expr 参数是您希望添加的时间间隔数值，可+可-，type参数时间单位。

第三步：通过外连接的方式将统计结果的表并入到日期表中

SELECT t1.date date,t2.num num
FROM (
         SELECT @cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) date
         FROM (
                  SELECT @cdate := DATE_ADD(#{endTime}, INTERVAL + 1 DAY)
                  FROM tb_clue
              ) t1
         WHERE @cdate > #{startTime}
         ORDER BY date
     ) t1
LEFT JOIN (
            SELECT DATE_FORMAT(create_time, '%Y-%m-%d') date,count(id) num
            FROM tb_clue
            WHERE create_time BETWEEN #{startTime} AND #{endTime}
            GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d')
          ) t2
ON t1.date = t2.date
ORDER BY t1.date ASC

第四步：处理统计结果，将Null值设置为0

SELECT t1.date date,IFNULL(t2.num, 0) num
FROM (
         SELECT @cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) date
         FROM (
                  SELECT @cdate := DATE_ADD(#{endTime}, INTERVAL + 1 DAY)
                  FROM tb_clue
              ) t1
         WHERE @cdate > #{startTime}
         ORDER BY date
     ) t1
LEFT JOIN (
            SELECT DATE_FORMAT(create_time, '%Y-%m-%d') date,count(*) num
            FROM tb_clue
            WHERE create_time BETWEEN #{startTime} AND #{endTime}
            GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d')
          ) t2
ON t1.date = t2.date
ORDER BY t1.date ASC

IFNULL(UnitsOnOrder,0)函数用于规定如何处理 NULL 值，是MySQL的方言。如果UnitsOnOrder为Null，则返回0

第五步：计算每天的总数据量（根据业务的实际情况）

SELECT t3.date,(SELECT count(t4.create_time) FROM tb_clue t4 WHERE t4.create_time<=t3.date) num FROM
(
  SELECT t1.date date,IFNULL(t2.num, 0) num
  FROM (
         SELECT @cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) date
         FROM (
                  SELECT @cdate := DATE_ADD(#{endTime}, INTERVAL + 1 DAY)
                  FROM tb_clue
              ) t1
         WHERE @cdate > #{startTime}
         ORDER BY date
     ) t1
  LEFT JOIN (
            SELECT DATE_FORMAT(create_time, '%Y-%m-%d') date,count(*) num
            FROM tb_clue
            WHERE create_time BETWEEN #{startTime} AND #{endTime}
            GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d')
          ) t2
  ON t1.date = t2.date
  ORDER BY t1.date ASC
) t3