如何在MySQL中按日期计算同工作日历史均值并生成透视表？

需求明确：针对表中每个日期的每个表，计算过去两周内相同工作日的count均值（例如2023-01-06是周五，取前两个周五的count值求平均），并生成透视表展示每日count及对应均值。

原SQL的核心问题 #

原查询的子查询t2计算的是每个表过去14天所有数据的平均值，没有区分相同工作日，完全不符合需求逻辑。

修正后的SQL #

假设日期列实际名为table_date（与你SQL中的字段名保持一致），以下是适配需求的查询：

WITH date_workday AS (
    -- 为每条数据标记所属星期几（MySQL语法：1=周日，2=周一...7=周六，可根据数据库调整）
    SELECT 
        table_date,
        table_name,
        `count`,
        DAYOFWEEK(table_date) AS workday
    FROM tbl
    WHERE table_name NOT IN ('table7')
),
historical_avg AS (
    -- 计算每个日期+表对应的过去两周同工作日均值
    SELECT 
        d1.table_date,
        d1.table_name,
        AVG(d2.`count`) AS same_workday_avg
    FROM date_workday d1
    LEFT JOIN date_workday d2 
        ON d1.table_name = d2.table_name
        AND d1.workday = d2.workday
        -- 取过去两周内的同工作日（前7天到前14天的区间，正好两个同工作日）
        AND d2.table_date BETWEEN DATE_SUB(d1.table_date, INTERVAL 14 DAY) AND DATE_SUB(d1.table_date, INTERVAL 7 DAY)
    GROUP BY d1.table_date, d1.table_name
)
-- 生成透视表，展示最近4天的count及对应同工作日均值
SELECT 
    t.table_name,
    MAX(CASE WHEN t.table_date = DATE_SUB(CURDATE(), INTERVAL 4 DAY) THEN t.`count` END) AS `4天前count`,
    MAX(CASE WHEN t.table_date = DATE_SUB(CURDATE(), INTERVAL 4 DAY) THEN h.same_workday_avg END) AS `4天前同工作日均值`,
    MAX(CASE WHEN t.table_date = DATE_SUB(CURDATE(), INTERVAL 3 DAY) THEN t.`count` END) AS `3天前count`,
    MAX(CASE WHEN t.table_date = DATE_SUB(CURDATE(), INTERVAL 3 DAY) THEN h.same_workday_avg END) AS `3天前同工作日均值`,
    MAX(CASE WHEN t.table_date = DATE_SUB(CURDATE(), INTERVAL 2 DAY) THEN t.`count` END) AS `2天前count`,
    MAX(CASE WHEN t.table_date = DATE_SUB(CURDATE(), INTERVAL 2 DAY) THEN h.same_workday_avg END) AS `2天前同工作日均值`,
    MAX(CASE WHEN t.table_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN t.`count` END) AS `1天前count`,
    MAX(CASE WHEN t.table_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN h.same_workday_avg END) AS `1天前同工作日均值`
FROM date_workday t
LEFT JOIN historical_avg h 
    ON t.table_date = h.table_date 
    AND t.table_name = h.table_name
WHERE t.table_date >= DATE_SUB(CURDATE(), INTERVAL 4 DAY)
GROUP BY t.table_name;

关键逻辑说明 #

1. 工作日标记：用DAYOFWEEK()（MySQL）标记每条数据的星期属性，确保只匹配相同工作日的历史数据（其他数据库可替换为对应函数：PostgreSQL用EXTRACT(DOW FROM table_date)，SQL Server用DATEPART(WEEKDAY, table_date)）。
2. 同工作日均值计算：通过自连接关联当前日期前14天至前7天的同工作日数据，计算这两个日期的count平均值，正好对应过去两周的同工作日数据。
3. 透视表生成：用CASE WHEN将行数据转为列结构，每个表占一行，清晰展示最近4天的count值及对应的同工作日均值。

补充说明 #

如果表中历史数据不足（比如目标日期前两周没有对应工作日的数据），均值会返回NULL，可按需用COALESCE(same_workday_avg, 0)将空值替换为0或其他默认值。

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