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
Original: https://www.cnblogs.com/daydreamer-fs/p/16739784.html
Author: fogey
Title: 统计本周每天的数据
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/715185/
转载文章受原作者版权保护。转载请注明原作者出处！