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`参考链接：
https://blog.csdn.net/zjh19961213/article/details/105240167
https://blog.csdn.net/csdnlaiyanqi/article/details/121407363
标签：WEEKDAY,SUB,每天,INTERVAL,本周,统计,DATE,SELECT,CURDATE From： https://www.cnblogs.com/daydreamer-fs/p/16739784.html