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

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

拓展：近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` title,
		IFNULL( b.num, 0 ) num
FROM
		(
				SELECT
						DATE_FORMAT(DATE_ADD(DATE(#{timeHorizon[0]}),INTERVAL (seq.seq - 1) MONTH), '%Y-%m') AS `date`
				FROM (
								 SELECT
										 h.num AS seq
								 FROM
										-- 这里需要一个辅助表
										 system_num h
								 WHERE
										 h.num < PERIOD_DIFF(DATE_FORMAT(DATE(#{timeHorizon[1]}), '%Y%m'), DATE_FORMAT(DATE(#{timeHorizon[0]}), '%Y%m')) + 1
						 ) seq
				ORDER BY `date`) a
				LEFT JOIN (
				-- 业务表
				SELECT DATE_FORMAT( report_time, '%Y-%m' ) AS reportYearMonth, COUNT( id ) num
				FROM xxx
				WHERE report_time BETWEEN DATE_FORMAT(#{timeHorizon[0]}, '%Y-%m-%d' ) AND DATE_FORMAT( #{timeHorizon[1]}, '%Y-%m-%d' ) GROUP BY reportYearMonth
		) b ON a.`date` = reportYearMonth ORDER BY title ASC

参考链接：

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

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