MYSQL 统计近七天（N天）数据，缺少天数时以0补齐

MYSQL 统计日期时 缺少天数时补齐（2022-01-22）

1、背景

描述：做图表时，可能会遇到查询某一段日期数据，其中一天没有数据，但是必须统计前N天的数据， 使用此条SQL 可以生成数据为空的天数

方法一：创建一个临时表a，里面内置了七天的数据，然后根据临时表去匹配实际数据，但是如果统计十天、N天，那这个方案就会比较繁琐

注：
click_date 填充后的日期
count 日期下的数据

// An highlighted block
SELECT
	a.click_date,
	IFNULL( b.count, 0 ) AS count 
FROM
	(
	SELECT
		CURDATE() AS click_date UNION ALL
	SELECT
		DATE_SUB( CURDATE(), INTERVAL 1 DAY ) AS click_date UNION ALL
	SELECT
		DATE_SUB( CURDATE(), INTERVAL 2 DAY ) AS click_date UNION ALL
	SELECT
		DATE_SUB( CURDATE(), INTERVAL 3 DAY ) AS click_date UNION ALL
	SELECT
		DATE_SUB( CURDATE(), INTERVAL 4 DAY ) AS click_date UNION ALL
	SELECT
		DATE_SUB( CURDATE(), INTERVAL 5 DAY ) AS click_date UNION ALL
	SELECT
		DATE_SUB( CURDATE(), INTERVAL 6 DAY ) AS click_date 
	) a
	LEFT JOIN ( SELECT DATE( create_time ) date, create_time, "统计值" AS count FROM "表名" WHERE DATEDIFF( NOW(), create_time )<= 7 ) b ON a.click_date = b.date;

配图：

方法二：在数据库中找一张表作为连续天数表（这里新建了一张表continuity_date），以continuity_date表为基础左连接left join到要查询的数据表

注：这里仅作为测试实现。
continuity_date表，无所谓数据是什么，这里只是需要用到数据的总量，如果查询连续七天那要保证“连续天数表”中数据大于等于七条，查询十天、N天同理。

此处举例查询七天内用户注册数：用户表

生成最近七天的日期，不包括当天

SQL分析：
1）=
只有在set和update时才是和:=一样，赋值的作用，其它都是等于的作用。
2）:=
不只在set和update时时赋值的作用，在select也是赋值的作用。
3）@cdate := 是定义名为cdate的变量并赋值
4）@cdate := date_add(@cdate, INTERVAL - 1 DAY) 把定义的cdate变量天数-1（自减）
5）@cdate := CURDATE() FROM continuity_date 以continuity_date表数据为基础，为@cdate赋值为当前日期

SELECT
	a.click_date,
	IFNULL( b.count, 0 ) AS count 
FROM
	(
		SELECT
			@cdate := date_add( @cdate, INTERVAL - 1 DAY ) AS click_date 
		FROM
			( SELECT @cdate := CURDATE() FROM continuity_date ) t LIMIT 7
	) a
	LEFT JOIN ( SELECT DATE( create_time ) date, create_time, COUNT(id) AS count FROM user_info GROUP BY date
 ) b ON a.click_date = b.date;

查询结果

生成最近七天的日期，包括当天

SQL分析：
1）@cdate := date_add(CURDATE(), INTERVAL + 1 DAY ) FROM continuity_date 以continuity_date表数据为基础，为@cdate赋值为在当前日期基础上加一天

SELECT
	a.click_date,
	IFNULL( b.count, 0 ) AS count 
FROM
	(
		SELECT
			@cdate := date_add( @cdate, INTERVAL - 1 DAY ) AS click_date 
		FROM
			( SELECT @cdate := date_add(CURDATE(), INTERVAL + 1 DAY ) FROM continuity_date ) t LIMIT 7
	) a
	LEFT JOIN ( SELECT DATE( create_time ) date, create_time, COUNT(id) AS count FROM user_info GROUP BY date
 ) b ON a.click_date = b.date;

查询结果：

生成最近七天的日期，指定某天

SQL分析：
CURDATE() 获取当前时间 格式：yyyy-MM-dd
将 CURDATE() 替换为自己需要的日期即可：此处举例 2023-03-20

 SELECT
	a.click_date,
	IFNULL( b.count, 0 ) AS count 
FROM
	(
		SELECT
			@cdate := date_add( @cdate, INTERVAL - 1 DAY ) AS click_date 
		FROM
			( SELECT @cdate := date_add('2023-03-20', INTERVAL + 1 DAY ) FROM continuity_date ) t LIMIT 7
	) a
	LEFT JOIN ( SELECT DATE( create_time ) date, create_time, COUNT(id) AS count FROM user_info GROUP BY date
 ) b ON a.click_date = b.date; 

查询结果：

参考文章：
MYSQL中用sql语句按照天数统计最近7天（或者十天，一个月）的报表，如果当天没有数据则填充0
MySQL获取周、月、天日期，生成排序号