如何使用LAG语法填充金额0值：继承最近非零历史金额

现有源数据包含Account、Period、Amount字段，其中多个月份的Amount值为0。使用包含LAG函数的CTE查询时，仅能将0值金额替换为紧邻的上一个非零金额，但连续0值的月份无法持续填充最近的非零历史金额。如何修改查询，确保所有0值金额都能填充最近的非零历史金额？

源数据 #

Account Period     Amount
AC100   January    100
AC100   February   0
AC100   March      0
AC100   April      0
AC100   May        0
AC100   June       600
AC100   July       700
AC100   August     0
AC100   September  0
AC100   October    1000
AC100   November   0
AC100   December   1200

当前查询 #

WITH CTE AS (
    SELECT
        Account,
        Period,
        Amount,
        LAG(Amount, 1, 0) OVER (PARTITION BY Account ORDER BY (SELECT NULL)) AS PreviousAmount
    FROM TableA
)
SELECT
    Account,
    Period,
    CASE WHEN Amount = 0 THEN PreviousAmount ELSE Amount END AS Amount
FROM CTE

原查询的LAG函数仅能获取紧邻上一行的值，连续0的情况下，第二行及之后的0无法追溯到更早的非零值。要解决这个问题，需要先给每个非零值对应的连续0区间分组，再在分组内取非零值填充。

可以通过窗口函数生成分组ID，让每个非零值开启一个新分组，后续连续的0归到同一分组，再用分组内的非零值填充所有0：

WITH GroupedData AS (
    SELECT
        Account,
        Period,
        Amount,
        -- 生成分组ID：每遇到非零金额，分组ID累加1
        SUM(CASE WHEN Amount != 0 THEN 1 ELSE 0 END) OVER (
            PARTITION BY Account 
            ORDER BY 
                CASE Period 
                    WHEN 'January' THEN 1
                    WHEN 'February' THEN 2
                    WHEN 'March' THEN 3
                    WHEN 'April' THEN 4
                    WHEN 'May' THEN 5
                    WHEN 'June' THEN 6
                    WHEN 'July' THEN 7
                    WHEN 'August' THEN 8
                    WHEN 'September' THEN 9
                    WHEN 'October' THEN 10
                    WHEN 'November' THEN 11
                    WHEN 'December' THEN 12
                END 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS GroupId
    FROM TableA
)
SELECT
    Account,
    Period,
    -- 取分组内的非零金额填充所有0值
    MAX(Amount) OVER (PARTITION BY Account, GroupId) AS Amount
FROM GroupedData
ORDER BY 
    CASE Period 
        WHEN 'January' THEN 1
        WHEN 'February' THEN 2
        WHEN 'March' THEN 3
        WHEN 'April' THEN 4
        WHEN 'May' THEN 5
        WHEN 'June' THEN 6
        WHEN 'July' THEN 7
        WHEN 'August' THEN 8
        WHEN 'September' THEN 9
        WHEN 'October' THEN 10
        WHEN 'November' THEN 11
        WHEN 'December' THEN 12
    END;

关键说明 #

1. 分组逻辑：通过窗口累加非零值的计数，每个非零金额会生成一个新的GroupId，后续连续的0会继承该GroupId，确保同一区间的0和前置非零值同组。
2. 排序修正：原查询中ORDER BY (SELECT NULL)是不稳定排序，必须按月份实际顺序排序，用CASE语句将月份转换为数字保证排序正确。
3. 填充逻辑：每个分组内仅有一个非零金额，用MAX(Amount)即可提取该值，替换分组内所有0值。

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