聚合财务交易为月度余额：SQL优化与无间隙日期处理问询

交易数据 #

我有如下财务交易数据（包含客户名称、交易日期、借方金额、贷方金额），同一客户在同一日期可有多笔交易：

Name   Date    Debit   Credit
A  2021-01-01 00:00:00.0000000 10  0
A  2021-01-01 00:00:00.0000000 9   0
A  2021-02-01 00:00:00.0000000 11  0
A  2021-03-01 00:00:00.0000000 0   50
A  2021-04-01 00:00:00.0000000 30  0
B  2021-01-01 00:00:00.0000000 10  0
B  2022-02-01 00:00:00.0000000 0   12
B  2022-03-01 00:00:00.0000000 0   50
B  2024-04-01 00:00:00.0000000 3   0

需求 #

需要生成每个客户的月度余额：无交易的年月可缺失，但理想状态下结果无日期间隙。

存在日期间隙的结果示例 #

（如客户B的2021年1月与2022年2月间存在间隙）

Name    Year    Month   balance
A   2021    1   -19
A   2021    2   -30
A   2021    3   20
A   2021    4   -10
B   2021    1   -10
B   2022    2   2
B   2022    3   52
B   2024    4   49

无日期间隙的结果示例 #

（省略部分无交易年月）

Name    Year    Month   balance
A   2021    1   -19 -- 从第一笔交易开始
A   2021    2   -30
A   2021    3   20
A   2021    4   -10
A   2021    5   -10 -- 2021年5月无交易，沿用上期余额
...                -- 展示至2024年3月的所有年月
A   2024    4   -10 -- 直至当前月份

B   2021    1   -10
B   2021    2   -10 -- 无交易
B   2021    3   -10 -- 无交易
...                 -- 展示至2021年12月的所有年月
B   2022    1   -10 -- 无交易
B   2022    2   2   
B   2022    3   52  
B   2022    4   52  -- 无交易
B   2022    5   52  -- 无交易
...                 -- 展示至2024年3月的所有年月
B   2024    3   49  -- 无交易
B   2024    4   49

现有实现 #

我已通过两步实现该需求：

1. 先按客户、年份、月份分组；
2. 对每组累加所有前置交易以计算余额。

对应的SQL代码如下：

DECLARE @MyTransactions TABLE (
    [Name] nvarchar(10),
    [Date] datetime2,
    [Debit] decimal,
    [Credit] decimal
 )

 INSERT INTO @MyTransactions ([Name],[Date],[Debit],[Credit]) VALUES ('A','2021-01-01',10,0)
 INSERT INTO @MyTransactions ([Name],[Date],[Debit],[Credit]) VALUES ('A','2021-02-01',11,0)
 INSERT INTO @MyTransactions ([Name],[Date],[Debit],[Credit]) VALUES ('A','2021-03-01',0,50)
 INSERT INTO @MyTransactions ([Name],[Date],[Debit],[Credit]) VALUES ('A','2021-04-01',30,0)

 INSERT INTO @MyTransactions ([Name],[Date],[Debit],[Credit]) VALUES ('B','2021-01-01',10,0)
 INSERT INTO @MyTransactions ([Name],[Date],[Debit],[Credit]) VALUES ('B','2022-02-01',0,12)
 INSERT INTO @MyTransactions ([Name],[Date],[Debit],[Credit]) VALUES ('B','2022-03-01',0,50)
 INSERT INTO @MyTransactions ([Name],[Date],[Debit],[Credit]) VALUES ('B','2024-04-01',3,0)


 DECLARE @MyGroups TABLE (
    [Name] nvarchar(10),
    [Year] int,
    [Month] int
 )


 -- 1. 先按客户、年份、月份分组
 INSERT INTO @MyGroups ([Name],[Year],[Month])
 SELECT [Name], DATEPART(YEAR, [Date]), DATEPART(MONTH, [Date])
 FROM @MyTransactions
 GROUP BY [Name],DATEPART(YEAR, [Date]), DATEPART(MONTH, [Date])

 -- 2. 对每组累加所有前置交易以计算余额
 SELECT 
    G.[Name], G.[Year], G.[Month],  SUM(ISNULL(T.Credit,0) - ISNULL(T.Debit,0))             
 FROM @MyGroups G
 JOIN @MyTransactions T ON 
    T.[Name] = G.[Name] 
    AND 
    (
    (DATEPART(YEAR, T.[Date]) = G.[Year] AND DATEPART(MONTH, T.[Date]) <= G.[Month])
    OR
    (DATEPART(YEAR, T.[Date]) < G.[Year])
    )
 GROUP BY G.[Name], G.[Year], G.[Month]

技术问题 #

1. 是否存在更简洁的实现方式，例如无需临时分组表的单步实现？
2. 如何确保结果中的日期无间隙？

解答 #

问题1：更简洁的单步实现 #

可以利用窗口函数SUM() OVER()直接计算累计余额，无需临时分组表。先按月度聚合每个客户的当月收支变化，再基于聚合结果计算累计余额：

WITH MonthlyTransactions AS (
    SELECT
        [Name],
        DATEPART(YEAR, [Date]) AS [Year],
        DATEPART(MONTH, [Date]) AS [Month],
        SUM(ISNULL(Credit, 0) - ISNULL(Debit, 0)) AS MonthlyChange
    FROM @MyTransactions
    GROUP BY [Name], DATEPART(YEAR, [Date]), DATEPART(MONTH, [Date])
)
SELECT
    [Name],
    [Year],
    [Month],
    SUM(MonthlyChange) OVER (PARTITION BY [Name] ORDER BY [Year], [Month]) AS balance
FROM MonthlyTransactions
ORDER BY [Name], [Year], [Month];

这段代码用CTE聚合每月收支变化，再通过窗口函数按客户分组、年月排序累加，直接得到月度累计余额，省去了临时分组表，逻辑更紧凑。

问题2：生成无日期间隙的结果 #

要实现无间隙的月度序列，需要先生成每个客户的完整年月范围（从第一笔交易年月到当前年月），再关联交易数据填充余额，无交易月份沿用上月值。完整代码如下：

-- 生成日期维度表（覆盖需要的年月范围）
DECLARE @StartDate DATE = (SELECT MIN([Date]) FROM @MyTransactions);
DECLARE @EndDate DATE = GETDATE();

WITH DateSeries AS (
    SELECT
        DATEPART(YEAR, @StartDate) AS [Year],
        DATEPART(MONTH, @StartDate) AS [Month]
    UNION ALL
    SELECT
        CASE WHEN [Month] = 12 THEN [Year] + 1 ELSE [Year] END,
        CASE WHEN [Month] = 12 THEN 1 ELSE [Month] + 1 END
    FROM DateSeries
    WHERE DATEFROMPARTS([Year], [Month], 1) < @EndDate
),
-- 获取每个客户的交易起止年月
CustomerDateRange AS (
    SELECT
        [Name],
        MIN(DATEFROMPARTS(DATEPART(YEAR, [Date]), DATEPART(MONTH, [Date]), 1)) AS FirstTransactionMonth,
        @EndDate AS LastMonth
    FROM @MyTransactions
    GROUP BY [Name]
),
-- 生成每个客户的完整年月序列
CustomerMonthSeries AS (
    SELECT
        c.[Name],
        ds.[Year],
        ds.[Month]
    FROM CustomerDateRange c
    JOIN DateSeries ds
        ON DATEFROMPARTS(ds.[Year], ds.[Month], 1) BETWEEN c.FirstTransactionMonth AND c.LastMonth
),
-- 聚合每月交易变化
MonthlyTransactions AS (
    SELECT
        [Name],
        DATEPART(YEAR, [Date]) AS [Year],
        DATEPART(MONTH, [Date]) AS [Month],
        SUM(ISNULL(Credit, 0) - ISNULL(Debit, 0)) AS MonthlyChange
    FROM @MyTransactions
    GROUP BY [Name], DATEPART(YEAR, [Date]), DATEPART(MONTH, [Date])
)
-- 关联并计算累计余额
SELECT
    cms.[Name],
    cms.[Year],
    cms.[Month],
    SUM(ISNULL(mt.MonthlyChange, 0)) OVER (PARTITION BY cms.[Name] ORDER BY cms.[Year], cms.[Month]) AS balance
FROM CustomerMonthSeries cms
LEFT JOIN MonthlyTransactions mt
    ON cms.[Name] = mt.[Name]
    AND cms.[Year] = mt.[Year]
    AND cms.[Month] = mt.[Month]
ORDER BY cms.[Name], cms.[Year], cms.[Month];

这段代码先递归生成完整年月序列，匹配每个客户的交易时间范围得到无间隙的客户-年月列表，最后左连接交易数据，通过窗口函数累加得到每个月份的余额，无交易月份的收支变化为0，累计余额自然沿用上月值。

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