"11 时间日期序列的计算这类任务是指成员是时间和日期的有序集合的计算。除了常见的困难比如有序计算之外，SQL在生成时间日期序列方面也不方便，通常要借助辅助表或递归语句来实现，结构复杂代码繁琐 .."

jinxing
乾学院 53 号会员
39 浏览 • 2 个月前

SPL 查询与报表计算实战指南 -11 时间日期序列的计算

计算＆AI

11 时间日期序列的计算

这类任务是指成员是时间和日期的有序集合的计算。除了常见的困难比如有序计算之外，SQL在生成时间日期序列方面也不方便，通常要借助辅助表或递归语句来实现，结构复杂代码繁琐。

例1：以5分钟为区间每分钟统计一次电商订单表

数据源：电商订单表（11/Orders_online.txt）每分钟会产生多个订单。

目标：每分钟统计一次订单金额，每次统计5分钟的时间区间，即当前分钟和之后第4分钟之间的所有订单。

Picture1png

SPL代码：

	A
1	$select * from 11/Orders_online.txt
2	=A1.groups(datetime@m(OrderTime):min;sum(Amount):minTotal)
3	=A2.new(min:from, elapse@s(from,300):to, minTotal[0:4].sum(): 5minTotal)
4	=A3.m(:-5)

A1：加载数据。

A2：以分钟为单位对订单分组汇总。函数datetime可以调整时间的精度，@m表示精确到分，也就是计算出时间所在的分钟数。

Picture2png
A3：根据 A2 生成新二维表，以 300 秒（5 分钟）为区间对每条记录（每分钟）统计一次。from 取自当前记录的时间，to 取自当前记录 5 分钟后的时间，payload 汇总自当前记录到之后第 4 条记录之间的区间。函数 elapse 用于计算时间增减后的新时间，默认单位为天，@s 表示单位为秒。[0:4] 是 SPL 的相对区间表示语法。

Picture3png
A4：A3 的最后 4 条记录无意义，因此只取第 1 条至倒数第 5 条。函数 m 按绝对位置取成员。

Picture4png

例2：对客户的售后服务之间有重叠的日期区间进行合并

数据源：客户的售后服务表（11/after_sales_service.txt），每个客户同时有多次售后服务，售后服务之间的起止日期（区间）可能有重叠。

目标：对于每个客户，合并有重叠的日期区间。

Picture5png

SPL代码：

	A
1	$select * from 11/after_sales_service.txt
2	=A1.group(Client)
3	=A2.(~.(periods(StartDate,EndDate)).merge@u() .group@i(~!=~[-1]+1).new(A2.Client,~1:StartDate,~.m(-1):EndDate))
4	=A3.conj()

A1：加载数据。

A2：按客户分组但不汇总，每组是1个集合。第1组如图。

Picture6png
A3=A2.(~.(periods(StartDate,EndDate))) 处理 A2 的每组数据，分为多个步骤，先循环当前组的每条记录，按起止日期生成日期序列。第 1 组的第 1 个日期序列如图：

Picture7png
.merge@u() 继续处理，对当前组的各个序列求并集，生成不重复的日期序列。函数 merge 用于有序集合的归并，@u 表示归并时求并集。第 1 组前 2 个日期序列归并后如图：
Picture8png

.group@i(~!=~[-1]+1) 继续处理，分组但不汇总，将连续的日期序列分到同一个小组。函数group@i用于有序集合的条件分组，~!=~[-1]+1表示当前成员不等于上一个成员加1时，开始新的分组。第1组的前2个小组如图。

Picture10png

.new(A2.Client,~1:StartDate,~.m(-1):EndDate) 继续处理，根据当前组生成二维表，每个小组1条记录，起始日期取自组内第1个成员，终止日期取自组内倒数第1个成员。

Picture11png

A3：合并各组成员。

例3：计算各帐户每月余额，补齐缺失月份

数据源：账户资金流水表（11\transactions.txt），保存着多个账户的资金借入和贷出情况，日期不连续。

目标：统计从期初（2021-01）到期末（2024-04）每个账户每个月的余额，缺失的月份要补齐。

Picture12png

SPL代码：

	A
1	$select Name,YM,sum(Credit - Debit) as Change from(select Name, date(NUMTOCHAR(year(Date))+'-'+NUMTOCHAR(month(Date))+'-01')as ym,Credit,Debit from 11\transactions.txt) group by Name,ym
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):Year,month(YM):Month,Change+if(Name==Name[-1] , Balance[-1]):Balance)