基于前日记录更新：MySQL同表同站点两日记录求平均查询需求

没问题，我来帮你搞定这个查询需求——刚好匹配你说的场景：同一站点跨两个工作日的记录对比，还要计算两日平均值，而且适合每天早上数据更新后执行。

首先，我先假设你的表结构大概是这样的（你可以替换成自己实际的表名和字段）：

• 表名：site_daily_data
• 核心字段：
  • site_id：站点唯一标识（比如站点编号）
  • record_date：记录日期（DATE类型，每个工作日一条）
  • metric_value：需要计算平均值的数值字段（比如站点的某个指标值）

方案1：自连接（适合日期连续的工作日） #

如果你的数据是每个工作日连续更新（没有周末或假期间隔），用自连接的方式最直接：

SELECT
    t1.site_id,
    t1.record_date AS current_date,
    t2.record_date AS previous_date,
    t1.metric_value AS current_value,
    t2.metric_value AS previous_value,
    ROUND((t1.metric_value + t2.metric_value) / 2, 2) AS two_day_average
FROM
    site_daily_data t1
INNER JOIN
    site_daily_data t2 
    ON t1.site_id = t2.site_id
    AND t2.record_date = DATE_SUB(t1.record_date, INTERVAL 1 DAY)
-- 每天早上执行时，只取最新的当天数据
WHERE
    t1.record_date = CURDATE();

这个查询的逻辑： #

1. 把表自己和自己连接（t1是当天的记录，t2是前一天的同站点记录）
2. 关联条件确保是同一站点，且t2的日期是t1的前一天
3. 计算两日平均值时，用(当前值+前一天值)/2，再用ROUND保留2位小数（你可以调整位数）
4. 最后用CURDATE()过滤出当天的记录，这样每天执行时只会看到最新的对比结果

方案2：窗口函数（适合有日期间隔的情况） #

如果你的数据会跳过周末或假期（比如周一的前一条记录是周五），自连接的INTERVAL 1 DAY就不适用了，这时候用LAG()窗口函数更靠谱：

WITH site_daily_with_prev AS (
    SELECT
        site_id,
        record_date,
        metric_value,
        -- 取出同一站点的上一条记录的数值
        LAG(metric_value) OVER (PARTITION BY site_id ORDER BY record_date) AS previous_value,
        -- 取出上一条记录的日期
        LAG(record_date) OVER (PARTITION BY site_id ORDER BY record_date) AS previous_date
    FROM
        site_daily_data
)
SELECT
    site_id,
    record_date AS current_date,
    previous_date,
    metric_value AS current_value,
    previous_value,
    ROUND((metric_value + previous_value) / 2, 2) AS two_day_average
FROM
    site_daily_with_prev
WHERE
    previous_value IS NOT NULL -- 排除没有前一天记录的（比如站点的第一条数据）
    AND record_date = CURDATE(); -- 只取当天的记录

这个查询的逻辑： #

1. 用CTE（公共表表达式）先给每条记录加上同一站点的上一条记录的数值和日期
2. PARTITION BY site_id确保只在同一个站点内找前一条记录，ORDER BY record_date保证按时间顺序取
3. 后续查询过滤掉没有前一天数据的记录，只保留当天的结果，计算平均值

一些注意事项： #

• 记得把代码里的表名和字段替换成你实际使用的名称
• 如果你的日期字段是DATETIME类型，需要用DATE(record_date)来转换（比如DATE(t1.record_date)）
• 如果你想查看所有站点的历史两日平均，去掉WHERE里的record_date = CURDATE()即可
• ROUND函数的第二个参数可以根据需求调整小数位数（比如改成0就是整数）

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