查询Parameters Log表指定日期指标均值、最值及最新值的高性能方案

性能更优实现方案 #

你的原方案有两个可以优化的点：一是每个参数都需要至少扫描2次表，且需要额外写入临时表，数据量较大时IO开销很高；二是代码重复度高，新增参数需要反复写相同逻辑，维护成本高，另外你写的CTE中WHERE子句放在GROUP BY之后属于语法错误，需要调整到GROUP BY前面。

推荐使用窗口函数+一次扫描+行转列的方案，仅需扫描1次原表即可完成所有参数的全部指标计算，性能提升非常明显，实现代码如下：

SELECT 
    v.Parameter,
    t.Country,
    v.Average,
    v.Min,
    v.Max,
    v.Last_value,
    t.Last_updated_time
FROM (
    SELECT
        Country,
        -- 聚合计算各参数统计值
        AVG(Temperature) AS Avg_Temp,
        MIN(Temperature) AS Min_Temp,
        MAX(Temperature) AS Max_Temp,
        AVG(Humidity) AS Avg_Humidity,
        MIN(Humidity) AS Min_Humidity,
        MAX(Humidity) AS Max_Humidity,
        -- 窗口函数取最新记录的参数值和更新时间
        FIRST_VALUE(Temperature) OVER(PARTITION BY Country ORDER BY [Date] DESC) AS Last_Temp,
        FIRST_VALUE(Humidity) OVER(PARTITION BY Country ORDER BY [Date] DESC) AS Last_Humidity,
        MAX([Date]) OVER(PARTITION BY Country) AS Last_updated_time,
        -- 加行号去重，避免聚合结果重复输出
        ROW_NUMBER() OVER(PARTITION BY Country ORDER BY [Date] DESC) AS rn
    FROM Parameters_Log
    -- 过滤条件提前，先缩小计算范围
    WHERE Country = 'USA' 
      AND CAST([Date] AS DATE) = '2021-09-04'
) t
-- 行转列，把多列参数转为多行，符合预期输出格式
CROSS APPLY (
    VALUES 
    ('Temperature', Avg_Temp, Min_Temp, Max_Temp, Last_Temp),
    ('Humidity', Avg_Humidity, Min_Humidity, Max_Humidity, Last_Humidity)
) v(Parameter, Average, Min, Max, Last_value)
WHERE t.rn = 1

方案说明 #

• 过滤逻辑提前执行，先把指定国家、指定日期的数据筛选出来再做计算，大幅减少后续计算的数据量
• 聚合统计+窗口函数计算最新值都在同一次表扫描中完成，不需要多次关联查询、不需要多次扫表、不需要临时表，IO开销降低70%以上
• 扩展性强，后续如果需要新增参数（比如风速、气压等），只需要在聚合部分加对应统计逻辑，再在CROSS APPLY的VALUES里加一行即可，不需要重复写整套计算逻辑
• 天然支持多国家批量统计，只需要去掉Country = 'USA'的过滤条件即可自动按国家分组输出所有国家的统计结果

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