求基于最新日期计算过往3日rec_cnt平均值的更优SQL方案

需求说明 #

• 按tbl_nm分组，计算每组最新rundt日期之前3个日期的rec_cnt平均值
• 基于该平均值计算上浮10%（avg_plus_10%）和下浮10%（avg_minus_10%）的值
• 判断每条记录的rec_cnt是否处于上述两个值之间，结果存入is_matched（TRUE/FALSE）

输入数据集 #

tbl_nm | rundt     | rec_cnt
emp    | 2025/04/23| 100
emp    | 2025/04/16| 125
emp    | 2025/04/09| 110
emp    | 2025/04/02| 135
sal    | 2025/04/23| 210
sal    | 2025/04/16| 230
sal    | 2025/04/09| 200
sal    | 2025/04/02| 215

预期输出 #

tbl_nm | rundt     | rec_cnt| avg_cnt | avg_minus_10%| avg_plus_10%| is_matched
emp    | 2025/04/23| 100    | 123.33  |      111.0   |      135.66 | FALSE
emp    | 2025/04/16| 125    | 123.33  |      111.0   |      135.66 | TRUE
emp    | 2025/04/09| 110    | 123.33  |      111.0   |      135.66 | FALSE
emp    | 2025/04/02| 135    | 123.33  |      111.0   |      135.66 | TRUE
sal    | 2025/04/23| 210    | 215.00  |      193.5   |      236.50 | TRUE
sal    | 2025/04/16| 230    | 215.00  |      193.5   |      236.50 | TRUE
sal    | 2025/04/09| 200    | 215.00  |      193.5   |      236.50 | TRUE
sal    | 2025/04/02| 215    | 215.00  |      193.5   |      236.50 | TRUE

已实现的SQL方案 #

WITH calc_avg AS (
SELECT tbl_nm,
       rundt,
       rec_cnt,
       AVG(rec_cnt) OVER (PARTITION BY tbl_nm ORDER BY rundt DESC
                          ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS avg_cnt
FROM audit_tbl
)
SELECT
    tbl_nm,
    rundt,
    rec_cnt,
    (FIRST_VALUE(avg_cnt) OVER (avg_win)) AS avg_cnt,
    (FIRST_VALUE(avg_cnt) OVER (avg_win)) * 0.9 AS avg_minus_10,
    (FIRST_VALUE(avg_cnt) OVER (avg_win)) * 1.1 AS avg_plus_10,
    CASE WHEN rec_cnt BETWEEN ((FIRST_VALUE(avg_cnt) OVER (avg_win)) * 0.9) 
                              AND
                              ((FIRST_VALUE(avg_cnt) OVER (avg_win)) * 1.1)
         THEN 'TRUE'
         ELSE 'FALSE'
    END AS is_matched
FROM calc_avg
WINDOW avg_win AS (PARTITION BY tbl_nm ORDER BY rundt DESC)
ORDER BY 1, 2 DESC;

优化后的SQL方案 #

原方案存在多次重复调用窗口函数的冗余，优化后减少计算开销，逻辑更清晰：

WITH group_avg AS (
    -- 按分组计算最新日期之外的前3条记录的平均值
    SELECT 
        tbl_nm,
        AVG(rec_cnt) AS avg_cnt
    FROM audit_tbl
    WHERE rundt < (SELECT MAX(rundt) FROM audit_tbl t WHERE t.tbl_nm = audit_tbl.tbl_nm)
    QUALIFY ROW_NUMBER() OVER (PARTITION BY tbl_nm ORDER BY rundt DESC) <= 3
    GROUP BY tbl_nm
)
SELECT 
    a.tbl_nm,
    a.rundt,
    a.rec_cnt,
    ROUND(g.avg_cnt, 2) AS avg_cnt,
    ROUND(g.avg_cnt * 0.9, 1) AS avg_minus_10%,
    ROUND(g.avg_cnt * 1.1, 2) AS avg_plus_10%,
    CASE 
        WHEN a.rec_cnt BETWEEN g.avg_cnt * 0.9 AND g.avg_cnt * 1.1 
        THEN TRUE 
        ELSE FALSE 
    END AS is_matched
FROM audit_tbl a
JOIN group_avg g ON a.tbl_nm = g.tbl_nm
ORDER BY a.tbl_nm, a.rundt DESC;

优化点说明 #

1. 明确数据范围：直接筛选出最新日期之外的记录，再取前3条，逻辑更直观
2. 减少冗余计算：分组平均值仅计算一次，避免原方案中多次调用FIRST_VALUE的重复运算
3. 格式对齐：用ROUND函数统一控制小数位数，匹配预期输出格式
4. 可读性提升：将平均值计算与结果输出分层，逻辑结构更清晰

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