MSSQL 数据库有个资产账户的流水表,日期不连续。
Name | Date | Debit | Credit |
A | 2021-01-01 | 10 | 0 |
A | 2021-01-01 | 9 | 0 |
A | 2021-02-01 | 11 | 0 |
A | 2021-03-01 | 0 | 50 |
A | 2021-04-01 | 30 | 0 |
B | 2021-01-01 | 10 | 0 |
B | 2022-02-01 | 0 | 12 |
B | 2022-03-01 | 0 | 50 |
B | 2024-04-01 | 3 | 0 |
现在要统计从期初 2021 年 1 月到期末 2024 年 4 月每个账户每个月的余额,缺失的月份要补齐。
Name | y | m | Blance |
A | 2021 | 1 | -19 |
A | 2021 | 2 | -30 |
A | 2021 | 3 | 20 |
A | 2021 | 4 | -10 |
A | 2021 | 5 | -10 |
… | … | … | … |
A | 2024 | 3 | -10 |
A | 2024 | 4 | -10 |
B | 2021 | 1 | -10 |
B | 2021 | 2 | -10 |
… | … | … | … |
B | 2022 | 1 | -10 |
B | 2022 | 2 | 2 |
B | 2022 | 3 | 52 |
B | 2022 | 4 | 52 |
… | … | … | … |
B | 2024 | 3 | 52 |
B | 2024 | 4 | 49 |
SQL 解法:
WITH Accounts AS (SELECT DISTINCT Name FROM trans
),
Months AS (SELECT DATEADD(MONTH, n, '2021-01-01') AS MonthStartFROM (SELECT TOP (DATEDIFF(MONTH, '2021-01-01', '2024-04-01') + 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS nFROM master.dbo.spt_values) AS Numbers
),
AccountMonths AS (SELECT a.Name, m.MonthStartFROM Accounts aCROSS 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 balanceFROM fullChanges
SQL没有方便的方法生成月份序列,要用嵌套查询+窗口函数,代码非常复杂。
SPL提供了生成日期序列的函数,包括连续月份。
A | |
1 | =mssql.query("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)”) |
2 | =periods@m(date("2021-01-01"),date("2024-04-01"),1) |
3 | =xjoin(A1.id(Name):Name; A2:ym) |
4 | =A3.join(Name:ym, A1:Name:ym,change) |
5 | =A4.new(Name,year(ym):y,month(ym):m,change+if(Name==Name[-1] , Balance[-1]):Balance) |
A1:查询数据库,按账户、每月第1天的日期分组,统计每月金额变化。
A2:生成每月第一天组成的连续序列。perionds生成日期序列,@m表示间隔单位为月份。
A3:将账户和日期序列进行叉乘。
A4:将叉乘结果和A1左关联。
A5:当前账号与上一条记录相比不变时,当月余额=当月金额变化+上个月的余额;账号变化时,当月余额重置为当月金额变化。
SPL已开源免费,欢迎前往乾学院了解更多!
源码地址
免费下载