基于MySQL 5.5.31的table_1表，请求计算过去四个完整周的周平均值

嘿，我来帮你搞定这个计算过去四个完整周平均值的问题！先理清楚你的表结构和核心需求：

你的表结构 #

首先，你的table_1表完整结构大概是这样的：

mysql> desc table_1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col_1 | varchar(50) | NO   | PRI | NULL    |       |
| col_2 | varchar(50) | NO   | PRI | NULL    |       |
| col_3 | date        | NO   | PRI | NULL    |       |
| col_4 | int(11)     | NO   |     | NULL    |       |
| col_5 | int(11)     | NO   |     | NULL    |       |
| col_6 | float       | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

这是一张带有**复合主键（col_1+col_2+col_3）**的表，我们需要基于col_3的日期维度，计算过去四个完整周的数值平均值。

核心实现思路 #

要完成需求，我们需要解决两个关键问题：

• 精准筛选出过去四个完整周的数据（排除当前正在进行的不完整周）
• 按周分组计算平均值，同时避免跨年时周数重复的问题

1. 确定完整周的时间范围 #

假设业务中周的定义是周一到周日（最常用的业务周规则），我们可以用MySQL的日期函数算出四个完整周的起止时间：

• 当前周的周一：DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY)
• 四个完整周的起始日期：DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 28 DAY)
• 四个完整周的结束日期：DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 1 DAY)

如果你的业务周是周日到周六，只需要把WEEKDAY()换成DAYOFWEEK()-1即可（因为DAYOFWEEK()中周日是1，周六是7）。

2. 分组计算周平均值 #

为了避免跨年时周数重复（比如每年的第52/53周可能和下一年的第1周混淆），我们使用YEARWEEK()函数来标识唯一的周，第二个参数1指定周从周一开始。

完整SQL语句 #

下面是针对你的表的查询语句，这里默认按col_1、col_2维度分别计算各周的平均值，如果不需要维度分组，直接去掉col_1, col_2即可：

SELECT
    col_1,
    col_2,
    YEARWEEK(col_3, 1) AS year_week, -- 唯一标识周，格式为YYYYWW
    DATE_FORMAT(MIN(col_3), '%Y-%m-%d') AS week_start, -- 周起始日期（周一）
    DATE_FORMAT(MAX(col_3), '%Y-%m-%d') AS week_end, -- 周结束日期（周日）
    AVG(col_4) AS avg_col4,
    AVG(col_5) AS avg_col5,
    AVG(col_6) AS avg_col6
FROM
    table_1
WHERE
    col_3 BETWEEN 
        DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 28 DAY)
        AND
        DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 1 DAY)
GROUP BY
    col_1, col_2, YEARWEEK(col_3, 1)
ORDER BY
    year_week DESC;

关键细节说明 #

• MySQL 5.5兼容性：上述用到的DATE_SUB()、WEEKDAY()、YEARWEEK()等函数，在MySQL 5.5.31中都是完全支持的，不用担心版本适配问题。
• 周定义调整：如果你的业务周是周日开始，把YEARWEEK(col_3, 1)改成YEARWEEK(col_3, 2)，同时时间范围计算里的WEEKDAY()替换为DAYOFWEEK()-1。
• 空值处理：虽然你的表中所有字段都是NOT NULL，如果后续业务调整允许空值，可以用AVG(IFNULL(col_4, 0))来避免空值对平均值的影响。

内容的提问来源于stack exchange，提问作者300