在数据库应用开发中,我们经常需要面对各种复杂的SQL计算,多层固定分组就是其中一种。实现该算法的思路是用left join语句将源数据按照固定的依据对齐,但由于该算法往往涉及分组汇总、行间计算、填补缺失数据,而且层次较多,因此相应的SQL语句会非常复杂。
本文将介绍一种相对简单易懂的方法,也就是用SPL实现多层固定分组。下面用一个实例进行说明:
表stocklog存储着每天多种货物的多次出入库记录,表中部分数据如下:
![5f194e1cc6b34bf3be96d616f0815672.png](https://i-blog.csdnimg.cn/blog_migrate/b9c070a79eba3cc646240224adcb6a1e.jpeg)
我们期望的结果是列出指定时间段内,每天每种货物的库存状态。其中,库存状态是指每天每种产品的开库时数量(Open)、入库数量(Enter)、最高库存(Total)、出库数量(Issued)、闭库时数量(Close)。如下图所示:
![ba4266e5deef86f9d661d814ada9e971.png](https://i-blog.csdnimg.cn/blog_migrate/7ae635aa8b70208905bdeea56f6ea128.jpeg)
在原表数据中,如果Indicator的值为空,则表示该记录是入库动作,如果为ISSUE,则表示出库。同时需要注意的是,实际记录日期也许有缺失,即某几天完全没有出入库记录,但对于统计的库存状态来说必须包含完整连续的日期。而且满足以下规则:当日的“Open”应该等于前一日的“Close”,“Enter”和“Issued”来自于表stocklog,“Total”等于“Open+Enter”,“Close”等于“Open+Enter-Issued”或者是“Total-Issued”。
SPL代码如下:
![62e04820f18894b97aea579817b57d9f.png](https://i-blog.csdnimg.cn/blog_migrate/ef93f47b3a5ad821a651fc949dbfb73f.jpeg)
A1:查询数据库,根据表stocklog计算出每种产品每天总的入库数量和出库数量。这里只需要对数据进行分组汇总,计算上没有难度,可以交给SQL语句去完成。值得注意的是,A1中有两个参数start和end,分别对应SQL语句中的两个问号,代表外部传入的时间段,可以来自于JAVA或报表工具。假设start和end的值分别为 2014-04-01和2014-04-10,则A1的计算结果如下:
![b51ba5542b0ba637014dd21703f02dc9.png](https://i-blog.csdnimg.cn/blog_migrate/24f38c76cc1bdafc9b620440edddb704.jpeg)
A2=A1.group(INAME)
这句代码将A1按照INAME分组,每组数据是一个产品各天的总出入库数量。值得注意的是,这里无需对分组后的数据进行汇总计算,也就是说是一个纯粹的分组操作。A2的计算结果如下图左侧,右侧是每组数据的明细。
![d999c33b89757047bf570c245009a952.png](https://i-blog.csdnimg.cn/blog_migrate/49bba9c7277f33559d5a9cfe0a6222de.jpeg)
关于分组,集算器SPL有两个函数:groups和group。函数groups类似于SQL中的group by语句,可以在分组的同时进行汇总。而group函数只分组,不做汇总,这种纯粹的分组功能是SQL所缺乏的。
最终需要的计算结果是start到end之间每一天的库存状态,而源数据并非每天都有出入库记录,因此还要把A2按照连续的时间序列对齐。下面先生成这个时间序列。
B2=periods(start,end,1)
函数periods可以生成时间序列,有三个参数:起始时间、终止时间、间隔。缺省将生成日期序列,使用选项还可以生成年、季、月、旬的时间序列。A3的计算结果如下:
![4e75921d4c8441f3b298ea7b247d2c53.png](https://i-blog.csdnimg.cn/blog_migrate/df67206bfd5cd7aa4aa0f7d5f25809f6.png)
A3=for A2,这是循环语句,表示对A2进行循环,每次计算一个产品。
B3-B6是循环体,具体算法是先将该产品的出入库记录与B2中的时间序列对齐,然后计算每个产品每天的库存状态,最后将计算结果追加到B6中。值得注意的是,集算器使用直观的缩进来表示循环体,而不是括号或begin/end等标识符。B3-B6就是循环语句A3的循环体。
B3=A3.align(B2,IDATE)
这句代码将当前产品的出入库记录与B2中的时间序列对齐。注意,A3既是循环语句,也是循环变量,即当前产品的出入库记录。以产品Item3为例,下图左侧是对齐前的数据,右侧是对齐后的数据:
![977ae5afccec753b1a3e26a52fbc3345.png](https://i-blog.csdnimg.cn/blog_migrate/c69054f5b164f3335b546a7146695579.jpeg)
B4>c=0
这句代码用来给变量c赋初值0。c代表当前产品每条库存状态的OPENING字段,初始日期的OPENING字段为0,c会在B5中不断被修改。
B5=B3.new(A3.INAME:INAME,B2(#):IDATE, c:OPENING, ENTER,(b=c+ENTER):TOTAL,ISSUE,(c=b-ISSUE):CLOSE)
这句代码用来计算库存状态,是整个计算过程的核心。B3.new(…)表示以B3为基础新建一个序表,即当前产品的库存状态。新序表中有7个字段,分别是:
A3. INAME: INAME ----从当前产品的出入库记录A3取出INAME字段,新字段名为INAME。
B2 (#):IDATE ----将时间序列B2按顺序插入新序表,作为新字段IDATE。注意,#表示A3的记录序号,B2(N)表示B2的第N条记录,因此B2(#)表示按A3的序号将B2插入新序表。
c: OPENING ----将变量c作为OPENING的字段值,第一条记录时,这个值为0。
ENTER----将B3中的字段ENTER直接当做新字段。由于新序表是基于B3的,因此无需像INAME字段那样重命名。
(b=c+ENTER):TOTAL----按公式OPENING+ENTER计算出字段TOTAL,为了清晰起见,这里用括号把表达式括起来。
ISSUE---将B3中的字段ISSUE直接当做新字段。
(c=b-ISSUE):CLOSE---按公式TOTAL-ISSUE计算CLOSE字段。注意,这里的c已经被修改了,在计算下一条记录时,c会作为OPENING字段的值,从而满足业务规则:当日的“Open”等于前一日的“Close”。
以Item3为例,B5的计算结果如下:
![24a3a3a6307245b96e7bb82f67e21120.png](https://i-blog.csdnimg.cn/blog_migrate/c9588fae89c3486ecd22c00622494a9c.jpeg)
B6=@|B5
这句代码将B5不断地追加到当前格中,@表示当前格B6,最终计算结果如下:
![c295cd5bba0f6ab1cd1a4e6920722770.png](https://i-blog.csdnimg.cn/blog_migrate/299e757ce09c17ec7d9326b53bd636e6.jpeg)
B6就是本案例的最终计算结果。
>file("stocklog.xlsx").xlsexport@t(B6)
这句代码将计算结果导出到"stocklog.xlsx"文件,可以通过excel工具来查看:
![d8a8ba50520149783469062331a609a1.png](https://i-blog.csdnimg.cn/blog_migrate/70217e4c95b7d6af6e20ee5d6bafebf2.jpeg)
另外,集算器可被报表工具或java程序调用,调用的方法也和普通数据库相似,使用它提供的JDBC接口即可向java主程序返回ResultSet形式的计算结果,具体方法可参考相关文档。【Java如何调用SPL脚本】