如何用SQL动态计算各供应商连续两期金额的平均值？

核心思路 #

先按供应商+月份聚合每月的总金额，再用窗口函数LAG()获取上一月的总金额，最后计算两月平均值。这种方式无需硬编码月份，完全适配动态日期。

分步实现 #

1. 聚合每月供应商总金额 #

先将原始数据按供应商和月份分组，计算每个供应商每月的金额总和：

WITH monthly_vendor_totals AS (
    SELECT
        Date_Col,
        Vendor_col,
        -- 处理示例中带千分位和逗号小数点的金额格式，数值类型可直接用SUM(Amt_col)
        SUM(REPLACE(REPLACE(Amt_col, '.', ''), ',', '.')) AS monthly_total
    FROM your_table_name
    GROUP BY Date_Col, Vendor_col
)

2. 计算连续两期平均值 #

基于聚合结果，用LAG()窗口函数按供应商分组，获取上一月的总金额后计算平均值：

SELECT
    Date_Col,
    Vendor_col,
    CASE
        -- 第一个月无前置月份，直接返回当月总和
        WHEN LAG(monthly_total) OVER (PARTITION BY Vendor_col ORDER BY Date_Col) IS NULL
            THEN monthly_total
        -- 后续月份计算当月+上月的平均值
        ELSE (monthly_total + LAG(monthly_total) OVER (PARTITION BY Vendor_col ORDER BY Date_Col)) / 2
    END AS Amt_col
FROM monthly_vendor_totals
ORDER BY Date_Col, Vendor_col;

测试结果验证 #

用你提供的测试数据运行上述代码，会得到与期望一致的结果（可按需用FORMAT()调整金额显示格式）：

Date_Col  Vendor_col   Amt_col
202201   ABC          226468.30
202201   DEF          845678.45
202201   GHI          450657.43
202202   ABC          138581.36  -- (226468.30 + 50694.45)/2
202202   DEF          552276.73  -- (845678.45 + 258875.01)/2
202202   GHI          450328.72  -- (450657.43 + 450000.00)/2
202203   ABC          31647.48   -- (50694.45 + 12600.50)/2
202203   DEF          159737.81  -- (258875.01 + 60600.60)/2

方案优势 #

• 无需硬编码任何月份分支，完全动态适配任意时间范围的历史数据
• 窗口函数实现逻辑清晰，性能优于多次CASE WHEN判断
• 聚合+窗口函数的分层写法易于维护和扩展

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