如何编写每日运行的SQL查询，按7天分段计算温度平均值？

场景说明 #

现有表daily_temperatures，包含date（日期）和temperature（温度）字段，存储2022-11-28至2022-12-22的每日温度数据。需要编写每日运行的SQL查询，计算以运行当日为结尾的7天分段平均温度；若剩余天数不足7天，则按实际存在的天数计算平均值，week_ending_in_date字段随运行日期动态更新。

方案1：每日运行输出当日截止的7天平均 #

适用于每日执行，仅输出当天作为截止日期的统计结果：

-- MySQL 版本
SELECT
    CURDATE() AS week_ending_in_date,
    ROUND(AVG(temperature), 2) AS 7_day_avg_temperature,
    COUNT(*) AS actual_days_count
FROM daily_temperatures
WHERE date BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 DAY) AND CURDATE();

-- PostgreSQL 版本
SELECT
    CURRENT_DATE AS week_ending_in_date,
    ROUND(AVG(temperature)::numeric, 2) AS 7_day_avg_temperature,
    COUNT(*) AS actual_days_count
FROM daily_temperatures
WHERE date BETWEEN CURRENT_DATE - INTERVAL '6 days' AND CURRENT_DATE;

逻辑说明 #

• CURDATE()/CURRENT_DATE自动获取运行当日的日期，作为week_ending_in_date
• 筛选条件会取当日及往前6天的所有数据，若数据库中最早日期晚于这个起始时间，会自动只取实际存在的日期数据
• AVG(temperature)自动忽略NULL值（如果存在无温度数据的日期），COUNT(*)统计实际参与计算的天数

方案2：生成所有日期对应的截止7天平均 #

若需要一次性输出所有日期作为截止点的统计结果（每日运行时会输出全量数据），可使用窗口函数：

-- 兼容 MySQL 8.0+、PostgreSQL、SQL Server 等支持窗口函数的数据库
SELECT
    date AS week_ending_in_date,
    ROUND(AVG(temperature) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2) AS 7_day_avg_temperature,
    COUNT(temperature) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS actual_days_count
FROM daily_temperatures
ORDER BY date DESC;

逻辑说明 #

• 窗口函数OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)会为每个日期，计算它及往前6天范围内的温度平均值
• 对于早期日期（如2022-12-01），因为往前不足6天，会自动取从最早日期（2022-11-28）到当前日期的所有数据计算平均
• COUNT(temperature)统计该范围内有有效温度数据的天数

注意事项 #

• 如果date字段包含时间戳，需用DATE(date)（MySQL）或date::date（PostgreSQL）截断时间，避免筛选错误
• 若存在日期缺失的情况（某天无温度数据），如果需要将缺失日期计入天数（比如按0填充或标记为NULL），需先生成连续日期序列，再与温度表关联计算

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