计算帐户每月余额，补齐缺失日期：从 SQL 到 SPL

MSSQL 数据库有个资产账户的流水表，日期不连续。

现在要统计从期初 2021 年 1 月到期末 2024 年 4 月每个账户每个月的余额，缺失的月份要补齐。

SQL 解法：

WITH Accounts AS (
    SELECT DISTINCT Name FROM trans
),
Months AS (
    SELECT DATEADD(MONTH, n, '2021-01-01') AS MonthStart
    FROM (
        SELECT TOP (DATEDIFF(MONTH, '2021-01-01', '2024-04-01') + 1) 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
        FROM master.dbo.spt_values
    ) AS Numbers
),
AccountMonths AS (
    SELECT a.Name, m.MonthStart
    FROM Accounts a
    CROSS JOIN Months m
),
Changes as (
    SELECT Name, DATEADD(MONTH, DATEDIFF(MONTH, 0, Date), 0) as ym,
    sum(Credit - Debit) as change 
    FROM trans group by Name, DATEADD(MONTH, DATEDIFF(MONTH, 0, Date), 0)
),
fullChanges as (
    SELECT A.Name,A.MonthStart,c.change 
    FROM AccountMonths a left join Changes c 
    on a.Name=c.Name and a.MonthStart=c.ym
)
SELECT 
   Name,YEAR(MonthStart) AS Y,MONTH(MonthStart) AS M,
   SUM(change) OVER (PARTITION BY Name ORDER BY MonthStart) AS balance
   FROM fullChanges

SQL没有方便的方法生成月份序列，要用嵌套查询+窗口函数，代码非常复杂。

SPL提供了生成日期序列的函数，包括连续月份。

A1：查询数据库，按账户、每月第1天的日期分组，统计每月金额变化。

A2：生成每月第一天组成的连续序列。perionds生成日期序列，@m表示间隔单位为月份。

A3：将账户和日期序列进行叉乘。

A4：将叉乘结果和A1左关联。

A5：当前账号与上一条记录相比不变时，当月余额=当月金额变化+上个月的余额；账号变化时，当月余额重置为当月金额变化。

SPL已开源免费，欢迎前往乾学院了解更多！

源码地址

免费下载