问题描述

数据结构与规模

存款明细表 deposit

字段名称	字段类型	字段注释	示例数据
dt	Date	日期	2023-11-23
curr	String	币种	1 人民币，0 其他
cust_no	String	客户号	2889412
dept	Number	部门号	1-500
code11	Number	代码 11	1、0
code12	Number	代码 12	1、0
amt	Number	金额	32499.90
…

十几个字段，每天 2 千万，共 30 天 6 亿条记录。

客户表 cust

字段名称	字段类型	字段注释	示例数据
dt	Date	日期	2023-11-23
cust_no	String	客户号	2889412
cust_sub	Number	客户性质	0 农户、1 非农
…

300 万客户，每日存全量，共 30 天 9000 万条记录。

存款表和客户表通过字段日期 dt、客户号 cust_no 关联。

环境和期望

日指标是指按天统计的指标，过滤条件中必须有日期。存款表和客户表关联后，使用不同过滤条件可以组合出各种日指标，比如统计 2023 年 11 月 22 日的：

1. 各个部门人民币存款的农户，总金额、平均金额、总笔数等指标，还有一个条件是代码 11（code11）为 1。SQL 写出来大致是这样的。

select d.dept,sum(amt),avg(amt),count(1)… 
	from deposit d inner join cust c 
		on d.cust_no=d.cust_no and d.dt=c.dt  
	where d.dt=to_date('20231122','yyyymmdd')
		and d.curr=1 and c.cust_sub=1 and d.code11=1
	group by d.dept

2. 按照代码 11 分组，人民币存款的农户去重客户数、总金额、平均金额等指标，还有一个条件是代码 12（code12）为 0。

select  d.code11,count(distinct c.cust_no),sum(d.amt),avg(d.amt)… 
	from deposit d inner join cust c 
		on d.cust_no=c.cust_no and d.dt=c.dt 
	where d.dt=to_date('20231122','yyyymmdd') 
		and d.curr=1 and c.cust_sub=1 and d.code12=0
	group by code11

3. 按照代码 12 分组人民币存款农户的总金额、总笔数等指标。

select d.ocde12, sum(d.amt),count(d.amt),… 
	from deposit d  inner join cust c 
		on d.cust_no=d.cust_no and d.dt=c.dt  
	where d.dt=to_date('20231122','yyyymmdd') 
		and d.curr=1 and c.cust_sub=1 
	group by code12

指标种类非常多，在应用的每个页面上需要同时展现上百个指标。这些页面还要满足几千人的查询，即使采用了缓存方案以避免相同内容的页面被重复计算，早高峰的集中访问期间仍会有数十个不同的页面任务要并发，相当于同时要计算出几千个指标。虽然一个结果集可以计算出约 10 到 20 个指标，但几千指标的计算量仍然很大。

现状：用关系数据库实时计算指标性能达不到要求，只能选择少部分指标预先算好后存入数据库供查询，对业务的支撑作用较差。

期望：能实现指标的实时计算，且满足并发查询的需要。

问题分析

多指标实时计算涉及的明细数据量很大，要同时计算的指标数量也非常多，现有的数据库难以达到秒级响应速度。

而采用预计算也面临严重的问题：全部指标的总数非常大，要预先计算出来的结果太多，后台根本无法全部存下。只能预先调研需求，计算并保存少量指标供查询。

快速去重

去重本质上是分组运算，需要遍历原数据。如果去重的结果较小，可以在内存中保持去重后的结果集，遍历原数据时用去重字段查找结果集，以决定丢弃还是添加。如果结果集超出内存容量，一般就要用 HASH 分堆，把原数据拆成几部分缓存到外存中，再分别做内存结果集的去重。这样会多出外存读写动作，性能就很差。

观察多指标计算的特征可以发现：要做去重和去重计数的是比较确定的字段（客户号）。可以利用 SPL有序存储机制，将数据按照日期和客户号排序后存储，这样，在同一天内客户号就是确定有序的。

确定客户号有序后，去重就变得简单了，在遍历过程中只要把与上一条不相同的客户号逐步保存下来就可以了，即使内存放不下也不必做分堆处理。而去重计数时，则只要在客户号发生不同时加 1 即可，连结果集都不必存储下来。这就是 SPL 的快速有序去重机制。

大表关联

采用 SPL 的有序存储机制，也能很好的解决存款表和客户表之间大表关联的性能问题。

存款表和客户表都按照关联字段（字段日期、客户号）排序后，就可以看成是主子表关系：客户是主表、存款是子表。我们可以利用 SPL有序归并机制，将两个表的数据按顺序归并，复杂度是 M+N。

关系数据库采用哈希法实现存款和客户两个大表的连接，复杂度 SUM(Ni*Mi)。相比之下，SPL 有序归并性能要好很多。

遍历复用

多指标计算主要是对外存大明细表进行遍历，假如每组指标的计算都要遍历一次大明细表，那么硬盘 IO 就会成为瓶颈，众多指标的计算就不可能做到秒级响应了。SPL 的管道功能实现了遍历复用机制，能够做到一次遍历计算多个指标，从而有效提升多指标计算性能。

关系数据库和大数据平台普遍采用的 SQL 语言没有遍历复用语法，无法写出这样的运算。只能遍历多次，或者寄希望于数据库引擎的自动优化功能。但实测证明，即使是优化引擎做的较好的 Oracle 数据库，对于比较简单的多种分组，也还是会对数据表遍历多次，显然没有实现遍历复用机制。

新增数据

存款和客户表每天都会产生新数据，包括新的存款明细和新一天的全量客户数据。

按照上述分析，存款和客户表都按照日期和客户号有序，所以每天的新增数据都按照客户号排序后，在现有的数据最后追加就可以了。

实践过程

准备数据

	A	B	C
1	=file("deposit.txt")	=file("cust.txt")
2	>movefile(A1),movefile(B1)
3	=periods(date(2023,11,1),date(2023,11,30))
4	for A3	=to(3000000).new(A4:dt,~:cust_no,rand(2):cust_sub)
5		=B1.export@at(B4)
6		for 100	=to(200000).new(A4:dt,rand(2):curr,rand(3000000)+1:cust_no,rand(500)+1:dept,rand(2):code11,rand(2):code12,rand()*10000:amt)
7			=A1.export@at(C6)

这段代码生成文本文件 deposit.txt 包含 2023 年 11 月的 6 亿条明细记录，cust.txt 包含 9000 万条客户记录（2023 年 11 月每天的全量），用来模拟从数据库或其他数据源导出的原始数据。

实际应用中，存款明细一般是按照数据产生的顺序存储的，所以 deposit.txt 按照日期有序。

数据预处理

	A	B
1	=file("deposit.txt").cursor@t(dt,cust_no,curr,dept,code11,code12,amt).sortx(dt,cust_no)
2	=file("cust.txt").cursor@t(dt,cust_no,cust_sub).sortx(dt,cust_no)
3	=file("deposit.ctx").create@y(#dt,#cust_no,curr,dept,code11,code12,amt)
4	=file("cust.ctx").create@y(#dt,#cust_no,cust_sub)
5	=A3.append@i(A1)	=A4.append@i(A2)

A1、A2 将存款明细数据和客户数据都按照日期、客户号排序。

A3、A4 建立两个组表，定义好字段并声明 dt、cust_no 有序。

A5、B5 将两表数据存入组表。

多指标计算

	A	B
1	=file("deposit.ctx").open()	=file("cust.ctx").open()
2	=B1.cursor(dt,cust_no;dt==date(2023,11,22) && cust_sub==1)
3	=A1.cursor(dt,cust_no,code11,code12,dept,amt;dt==date(2023,11,22) && curr==1)
4	=joinx(A2:c,dt,cust_no;A3:d,dt,cust_no)
5	cursor A4	=A5.select(d.code11==1)
6		=B5.groups(d.dept;sum(d.amt),avg(d.amt),count(1))
7	cursor	=A7.select(d.code12==0)
8		=B7.groups(d.code11;icount@o(d.cust_no),sum(d.amt),avg(d.amt))
9	cursor	=A9.groups(d.code12;sum(d.amt),count(1))
10	return A5,A7,A9

A1-A3 建立两组表游标，因为有并发需求，所以没有采用多线程游标。

A4：两组表都对日期和客户号有序，按照这两个字段有序归并实现关联。

A5、A7、A9 分别建立 A4 游标的管道，定义了不同指标计算。不同指标的过滤条件、分组字段、聚合函数不同。

A10：管道的定义结束，SPL 自动开始从 A4 游标中分批取数，推送给 3 个管道计算。取数结束后，计算也都完成，结果写入 A5、A7、A9 格。这样就完成了遍历复用。

需要特别说明的是，日期相同的时候，存款明细和客户都是对客户号有序的。所以在 B8 中去重计数 icount 函数可以加上 @o 选项，实现了有序快速去重。

使用管道时，要将过滤条件基本相同的指标，放到同一个游标的不同管道中，这样才能发挥管道对同一批数据遍历复用的作用。

比如上边的例子过滤条件都包括 cust_sub==1、dt==date(2023,11,22) 、curr==1，可以合并到游标的过滤条件中。

如果再增加一个 cust_sub==0、dt==date(2023,11,23) 、curr==0 的指标，实际上和上边例子遍历的数据完全不同，不适合放在一个遍历中计算。可以放到和这个指标条件基本相同的其他游标、管道中。

实践效果

单服务器 64 核 CPU、256G 内存。单任务计算 200 个指标，10 并发任务共 2000 个指标，所有任务都能在 3 秒内返回。

后记

多指标计算面临的难题是明细数据量大、同时计算的指标多，传统技术无法达到秒级响应的要求。如果预先计算又面临指标组合数量超大的难题，只能计算出一小部分指标供查询。

采用 SPL 的遍历复用、快速有序去重、大表有序归并关联技术，可以高性能计算指标，变预先计算为实时计算，而且能保证秒级响应。

这次实践中，客户表每天存储全量数据的方式，一般称为快照表。

不过，客户表每天数据变动通常不会太大，快照表会产生大量数据冗余。可以考虑采用 SPL 时间键的方式存储客户表，进一步提高性能，详细参见：SPL 时间键。

多指标计算还可以采用 SPL 企业版的列式计算加上 pjoin 函数，性能还可以有较大提升。详细参见：SPL 的新关联计算。