统计一年每月/本周每天的数据

MySQL5.7

目前有两个思路:

1. 穷举本周每天的日期，left join 数据表
2. 指定统计的时间范围、按照时间间隔进行统计（类似ES桶聚合date_histogram）

思路一(UNION)实现

SELECT
	IFNULL(AVG(c.res_value),0) avg
FROM
(
	SELECT
	a.thisweek,b.res_value
	FROM
	(
		SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 0 DAY) AS thisweek
		UNION
		SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(curdate()) - 1 DAY)
		UNION
		SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(curdate()) - 2 DAY)
		UNION
		SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(curdate()) - 3 DAY)
		UNION
		SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(curdate()) - 4 DAY)
		UNION
		SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(curdate()) - 5 DAY)
		UNION
		SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(curdate()) - 6 DAY)
	) a
	LEFT JOIN (SELECT res_value, update_date FROM xxx) b ON a.thisweek = DATE_FORMAT(b.update_date,'%Y-%m-%d')
) c
GROUP BY c.thisweek
/* 结果如下：
avg	thisweek
0	2022-09-26
7.5	2022-09-27
0	2022-09-28
0	2022-09-29
0	2022-09-30
0	2022-10-01
0	2022-10-02
*/

思路一(用户变量)实现本周日期

SELECT
	DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - @s:=@s+1 DAY) `thisweek`
FROM
	mysql.help_topic,
	(SELECT @s := -1) a
WHERE
	@s < 6
ORDER BY
	`thisweek`

拓展：近7天日期

# 不包括当天
SELECT
	DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + @s:=@s+1 DAY) `date`
FROM
	mysql.help_topic,
	(SELECT @s := 0) a
WHERE
	@s < 7
ORDER BY
	`date`

拓展：统计当年每月的数据

SELECT
a.`date`,
	IFNULL(b.num,0) num
FROM
(
# 今年每月月份
SELECT
	CONCAT(YEAR(CURDATE()),"-",LPAD(@s:=@s+1,2,"0"))`date`
FROM
mysql.help_topic,
(SELECT @s := 0) a
WHERE
@s < 12
ORDER BY
`date`
) a LEFT JOIN 
(
SELECT
	DATE_FORMAT(F_CREATORTIME,'%Y-%m') AS releaseYearMonth,
	COUNT(F_Id) num
FROM
	wyexam_train_qu_answer
	WHERE F_UserId = "349057407209541"
	GROUP BY releaseYearMonth
)  b ON a.`date` = releaseYearMonth

参考链接：

https://blog.csdn.net/zjh19961213/article/details/105240167

https://blog.csdn.net/csdnlaiyanqi/article/details/121407363