在SQL Server中使用LAG和LEAD函数计算期末总数量

我来帮你搞定这个计算需求，SQL Server的窗口函数（尤其是LAG）刚好能完美解决这类前后行关联的计算问题。先理清楚你的需求：基于给定的账户交易记录，生成包含期初数量、净变动、零期初数量和期末数量的结果，其中第一行的期初数量和净变动显示为'n/a'。

输入数据 #

首先把你的输入数据整理成清晰的表格：

解决方案代码 #

下面是完整的SQL代码，我会逐部分解释逻辑：

WITH 交易记录 AS (
    -- 这里替换成你的实际表名，或者直接用VALUES子句测试
    SELECT 
        CONVERT(DATE, 日期) AS 日期, -- 转换为标准日期格式，确保排序准确
        账户,
        类型,
        数量
    FROM (
        VALUES 
            ('12/28/2007', 'A', '2N', 719),
            ('3/28/2008', 'A', '2N', 806),
            ('6/27/2008', 'A', '2N', 622),
            ('9/26/2008', 'A', '2N', 748),
            ('12/26/2008', 'A', '2N', 757)
    ) AS t(日期, 账户, 类型, 数量)
),
累计计算 AS (
    SELECT 
        日期,
        账户,
        类型,
        数量,
        -- 按账户+类型分组，按日期排序，累计计算期末总数量
        SUM(数量) OVER (PARTITION BY 账户, 类型 ORDER BY 日期) AS 期末数量
    FROM 交易记录
)
SELECT 
    日期,
    账户,
    类型,
    数量,
    -- 取上一行的期末数量作为当期期初，首行无数据显示'n/a'
    CASE 
        WHEN LAG(期末数量) OVER (PARTITION BY 账户, 类型 ORDER BY 日期) IS NULL 
        THEN 'n/a' 
        ELSE CAST(LAG(期末数量) OVER (PARTITION BY 账户, 类型 ORDER BY 日期) AS VARCHAR(20)) 
    END AS 期初数量,
    -- 首行净变动显示'n/a'，其余行用当期数量作为净变动（如果你的净变动是差值可以修改为 数量 - 期初数量）
    CASE 
        WHEN LAG(期末数量) OVER (PARTITION BY 账户, 类型 ORDER BY 日期) IS NULL 
        THEN 'n/a' 
        ELSE CAST(数量 AS VARCHAR(20)) 
    END AS 净变动,
    -- 零期初数量：这里逻辑是当初期数量为0时显示当期数量，否则为0，可根据实际需求调整
    CASE 
        WHEN LAG(期末数量) OVER (PARTITION BY 账户, 类型 ORDER BY 日期) = 0 
        THEN 数量 
        ELSE 0 
    END AS 零期初数量,
    期末数量
FROM 累计计算
ORDER BY 日期;

代码逻辑解释 #

1. 交易记录CTE：主要是整理输入数据，把日期转换为标准的DATE类型，避免字符串排序出错。如果你的数据已经在正式表中，直接替换成FROM 你的表名即可。
2. 累计计算CTE：使用SUM() OVER (PARTITION BY 账户, 类型 ORDER BY 日期)这个窗口函数，实现按账户和类型分组，按日期顺序累计数量，得到每行的期末总数量。
3. 主查询字段说明：
   • 期初数量：用LAG(期末数量) OVER (...)获取上一行的期末数量作为当期的期初值，首行没有上一行，所以用CASE语句把NULL替换成'n/a'。
   • 净变动：这里默认用当期的数量作为净变动（假设输入的数量是本期发生额），如果你的净变动是“当期数量 - 期初数量”，可以把CAST(数量 AS VARCHAR(20))改成CAST(数量 - LAG(期末数量) OVER (...) AS VARCHAR(20))。
   • 零期初数量：按照“期初为0时显示当期数量，否则为0”的逻辑处理，如果你有不同的需求，直接修改CASE的条件即可。
   • 期末数量：直接使用累计计算得到的结果。

执行结果 #

运行上面的代码后，会得到符合你要求的输出：

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