求MySQL按Price_Start_Date计算各Id最新有效Price平均值的脚本

需求说明 #

需要按Price_Start_Date字段计算Price的平均值，核心要求：

• 计算某一日期的平均值时，仅纳入每个Id在该日期及之前最后一次更新的价格（旧价格不再生效则排除）
• 保留UUID和Size的分组维度

示例数据 #

-- 表结构及测试数据
CREATE TABLE t1 (
    UUID VARCHAR(50),
    Size VARCHAR(10),
    Price_Start_Date DATE,
    Id VARCHAR(20),
    Price INT
);

INSERT INTO t1 VALUES
('b2c944f4-a7b9-11ed-afbe-024291230288', '1.00', '1900-01-01', '7128311000001100', 783),
('b2c944f4-a7b9-11ed-afbe-024291230288', '1.00', '1900-01-01', '7128711000001101', 1010),
('b2c944f4-a7b9-11ed-afbe-024291230288', '1.00', '1900-01-01', '7129611000001101', 1147),
('b2c944f4-a7b9-11ed-afbe-024291230288', '1.00', '2019-05-01', '7063411000001102', 1007),
('b2c944f4-a7b9-11ed-afbe-024291230288', '1.00', '2023-04-01', '7063411000001102', 1032);

期望结果 #

UUID                                   Size  Price_Start_Date  Average_Price
b2c944f4-a7b9-11ed-afbe-024291230288  1.00  1900-01-01        980
b2c944f4-a7b9-11ed-afbe-024291230288  1.00  2019-05-01        986.76
b2c944f4-a7b9-11ed-afbe-024291230288  1.00  2023-04-01        993

原查询问题分析 #

你提供的原查询未过滤每个Id的历史旧价格，导致同一Id的所有旧记录都会被纳入计算（比如2023-04-01时，Id=7063411000001102的2019-05-01价格也被统计），不符合"每个Id仅取最新Price"的要求。

解决方案 #

方案1：MySQL 8.0+ 窗口函数版本（推荐）

利用窗口函数标记每个Id的最新价格记录，再关联日期列表计算平均值：

WITH price_records AS (
    SELECT 
        UUID,
        Size,
        Price_Start_Date,
        Id,
        Price,
        -- 按Id分组，按日期倒序排序，标记最新记录
        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Price_Start_Date DESC) AS rn
    FROM t1
),
date_list AS (
    -- 提取所有需要计算的日期（原表中出现的所有Price_Start_Date）
    SELECT DISTINCT UUID, Size, Price_Start_Date FROM t1
),
valid_prices AS (
    -- 为每个日期匹配所有Id的最新有效价格
    SELECT 
        dl.UUID,
        dl.Size,
        dl.Price_Start_Date,
        pr.Price
    FROM date_list dl
    LEFT JOIN price_records pr 
        ON pr.UUID = dl.UUID 
        AND pr.Size = dl.Size 
        AND pr.Price_Start_Date <= dl.Price_Start_Date
    WHERE pr.rn = 1
)
-- 分组计算平均值，保留两位小数
SELECT 
    UUID,
    Size,
    Price_Start_Date,
    ROUND(AVG(Price), 2) AS Average_Price
FROM valid_prices
GROUP BY UUID, Size, Price_Start_Date
ORDER BY Price_Start_Date;

方案2：兼容MySQL 5.x版本（无窗口函数）

通过子查询先获取每个Id的最新价格记录，再关联日期列表计算：

SELECT 
    dl.UUID,
    dl.Size,
    dl.Price_Start_Date,
    ROUND(AVG(pr.Price), 2) AS Average_Price
FROM (
    -- 提取所有需要计算的日期
    SELECT DISTINCT UUID, Size, Price_Start_Date FROM t1
) dl
LEFT JOIN (
    -- 获取每个Id的最新价格记录
    SELECT 
        t1.UUID,
        t1.Size,
        t1.Id,
        t1.Price,
        t1.Price_Start_Date
    FROM t1
    INNER JOIN (
        SELECT Id, MAX(Price_Start_Date) AS latest_date FROM t1 GROUP BY Id
    ) latest 
        ON t1.Id = latest.Id 
        AND t1.Price_Start_Date = latest.latest_date
) pr 
    ON pr.UUID = dl.UUID 
    AND pr.Size = dl.Size 
    AND pr.Price_Start_Date <= dl.Price_Start_Date
GROUP BY dl.UUID, dl.Size, dl.Price_Start_Date
ORDER BY dl.Price_Start_Date;

结果验证 #

• 1900-01-01：仅统计3个Id的价格，平均值为(783+1010+1147)/3 = 980
• 2019-05-01：新增第4个Id的最新价格1007，平均值为(783+1010+1147+1007)/4 = 986.76
• 2023-04-01：第4个Id的最新价格更新为1032，平均值为(783+1010+1147+1032)/4 = 993

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