统计一年每月/本周每天的数据
MySQL5.7
目前有两个思路:
- 穷举本周每天的日期，left join 数据表
- 指定统计的时间范围、按照时间间隔进行统计（类似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
