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

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

NameDateDebitCredit
A2021-01-01100
A2021-01-0190
A2021-02-01110
A2021-03-01050
A2021-04-01300
B2021-01-01100
B2022-02-01012
B2022-03-01050
B2024-04-0130

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

NameymBlance
A20211-19
A20212-30
A2021320
A20214-10
A20215-10
A20243-10
A20244-10
B20211-10
B20212-10
B20221-10
B202222
B2022352
B2022452
B2024352
B2024449

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已开源免费,欢迎前往乾学院了解更多!

源码地址

免费下载

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/500715.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

【AimRT】现代机器人通信中间件 AimRT

目录 一、什么是AimRT二、AimRT与ROS22.1 定位与设计2.2 组成与通信方式对比 三、AimRT基本概念3.1 Node、Pkg 和 Module3.2 Protocol、Channel、Rpc 和 Filter3.3 App模式 和 Pkg模式3.4 Executor3.5 Plugin 一、什么是AimRT AimRT 是智元机器人公司自主研发的一款机器人通信…

SSM-Spring-AOP

目录 1 AOP实现步骤(以前打印当前系统的时间为例) 2 AOP工作流程 3 AOP核心概念 4 AOP配置管理 4-1 AOP切入点表达式 4-1-1 语法格式 4-1-2 通配符 4-2 AOP通知类型 五种通知类型 AOP通知获取数据 获取参数 获取返回值 获取异常 总结 5 …

idea( 2022.3.2)打包报错总结

一 报错 class lombok.javac.apt.LombokProcessor (in unnamed module 0x4fe64d23) cannot access class com.sun.tools.javac.processing.JavacProcessingEnvironment (in module jdk.compiler) because module jdk.compiler does not export com.sun.tools.javac.processing …

攻防靶场(29):目录权限和文件权限 ICMP

目录 1. 侦查 1.1 收集目标网络信息:IP地址 1.2 主动扫描:扫描IP地址段 1.3 搜索目标网站 2. 初始访问 2.1 利用面向公众的应用 3. 权限提升 3.1 有效账户:本地账户 3.2 滥用特权控制机制:Sudo和Sudo缓存 靶场下载地址&#xff1a…

C++ 面向对象编程:多态、虚函数原理

多态的通用描述便是,使用父类指针调用函数,可以根据对象类型来调用对应类型函数,我们分几个步骤来理解,先看下类的占用空间,然后拓展到虚函数对应数组,最后理解多态的原理。 我们先来看下在多态中没有任何…

王老吉药业SRM系统上线 携手隆道共启战略合作新篇章

12月27日,广州王老吉药业股份有限公司(简称“王老吉药业”)SRM项目上线启动会,在王老吉科普教育基地——“吉园”隆重举行。广药集团纪委主任陈耕、王老吉药业总工程师黄晓丹、隆道公司总裁吴树贵、项目经理赵耀、供应商代表郭伟及…

JavaScript基础 -- 变量、作用域与内存

1 原始值与引用值 原始值就是最简单的数据,引用值则是由多个值构成的对象。在把一个值赋给变量时,JavaScript引擎必须要确定这个值是原始值还是引用值 原始值大小固定,保存在栈内存上;引用值是对象,存储在堆内存上 它…

SQL—替换字符串—replace函数用法详解

SQL—替换字符串—replace函数用法详解 REPLACE() 函数——查找一个字符串中的指定子串,并将其替换为另一个子串。 REPLACE(str, old_substring, new_substring)str:要进行替换操作的原始字符串。old_substring:要被替换的子串。new_substri…

[极客大挑战 2019]Http 1

进入环境: 检查源码发现有一个链接,但是这里没有绑定,需要手动跳转,打开后,发现提示: 这里就是需要我们从https://Sycsecret.buuoj.cn来访问它 因此我们抓包,使用referer:服务器伪造…

吾杯网络安全技能大赛——Misc方向WP

吾杯网络安全技能大赛——Misc方向WP Sign 题目介绍: 浅浅签个到吧 解题过程: 57754375707B64663335376434372D333163622D343261382D616130632D3634333036333464646634617D 直接使用赛博橱子秒了 flag为 WuCup{df357d47-31cb-42a8-aa0c-6430634ddf4a} 原神启动…

如何查看下载到本地的大模型的具体大小?占了多少存储空间:Llama-3.1-8B下载到本地大概15GB

这里介绍一下tree命令,可以方便的查看文件目录结构和文件大小。 命令行tree的具体使用,请参考笔者的另一篇博客:深入了解 Linux tree 命令及其常用选项:Linux如何显示目录结构和文件大小,一言以蔽之,sudo a…

【Java回顾】Day2 正则表达式----异常处理

参考资料:菜鸟教程 https://www.runoob.com/java/java-exceptions.html 正则表达式 有一部分没看完 介绍 字符串的模式搜索、编辑或处理文本java.util.regex包,包含了pattern和mathcer类,用于处理正则表达式的匹配操作。 捕获组 把多个字符…

招银网路Java后端一面,难度有点大!

这是一位武汉理工大学同学的招银网络一面面经,同样附带超详细的参考答案。大家可以用来查漏补缺,针对性地补短板。 招银网络一面还是比较简单的,基本都是一些比较重要且高频的常规八股,项目问的不多。到了二面的时候, 会开始主要考察你的项目。 1、自我介绍 自我介绍一般…

xadmin后台首页增加一个导入数据按钮

xadmin后台首页增加一个导入数据按钮 效果 流程 1、在添加小组件中添加一个html页面 2、写入html代码 3、在urls.py添加导入数据路由 4、在views.py中添加响应函数html代码 <!DOCTYPE html> <html lang

arcgis模版空库怎么用(一)

这里以某个项目的数据为例&#xff1a; 可以看到&#xff0c;属性表中全部只有列标题&#xff0c;无数据内容 可能有些人会认为空库是用来往里面加入信息的&#xff0c;其实不是&#xff0c;正确的用法如下&#xff1a; 一、下图是我演示用的数据&#xff0c;我们可以看到其中…

GJB系统设计说明模板

GJB系统设计说明模板及详解 1 范围 1.1 标识 1.2 系统概述 1.3 文档概述 2 引用文档 GJB XXX XXX XXX&#xff1b; XXX XXX。 前2章通用不再赘述 3 系统级设计决策 系统设计决策的目的:对系统规格说明中的关键需求(包括功能、质量属性和设计约束)进行分析,得到系统级概念性架构…

某小程序sign签名参数逆向分析

文章目录 1. 写在前面2. 接口分析3. 分析还原 【&#x1f3e0;作者主页】&#xff1a;吴秋霖 【&#x1f4bc;作者介绍】&#xff1a;擅长爬虫与JS加密逆向分析&#xff01;Python领域优质创作者、CSDN博客专家、阿里云博客专家、华为云享专家。一路走来长期坚守并致力于Python…

2000-2020年各省财政一般预算支出面板数据

2000-2020年各省财政一般预算支出面板数据 1、时间&#xff1a;2000-2020年 2、来源&#xff1a;国家统计局 3、指标&#xff1a;年份、省份、地方财政一般预算支出 4、范围&#xff1a;31省 指标解释&#xff1a;地方财政一般预算支出‌是指地方ZF根据预算安排&#xff0…

[羊城杯 2024]1z_misc

得到FL4G.zip和天机不可泄露.txt文件&#xff0c;其中压缩包需要解压密码&#xff1a; 二十八星宿&#xff1a; 东方苍龙七宿&#xff1a;角、亢、氐、房、心、尾、箕 南方朱雀七宿&#xff1a;鬼、井、柳、星、张、翼、轸 西方白虎七宿&#xff1a;奎、娄、胃、昴、毕、觜、…

右值引用全面剖析

为什么要有右值引用&#xff0c;右值引用出现前程序员们的困境&#xff1a; 在右值引用出现以前&#xff0c;想要把一块内存空间里的内容放到另一块内存空间&#xff0c;只能再开辟一块内存&#xff0c;然后将原来内存里的内容复制到新开辟的内存里&#xff0c;然后再把原来的…