SPL 简化 SQL 案例详解：多层固定分组

在数据库应用开发中，我们经常需要面对各种复杂的SQL计算，多层固定分组就是其中一种。实现该算法的思路是用left join语句将源数据按照固定的依据对齐，但由于该算法往往涉及分组汇总、行间计算、填补缺失数据，而且层次较多，因此相应的SQL语句会非常复杂。

本文将介绍一种相对简单易懂的方法，也就是用SPL实现多层固定分组。下面用一个实例进行说明：

表stocklog存储着每天多种货物的多次出入库记录，表中部分数据如下：

我们期望的结果是列出指定时间段内，每天每种货物的库存状态。其中，库存状态是指每天每种产品的开库时数量（Open）、入库数量（Enter）、最高库存（Total）、出库数量（Issued）、闭库时数量（Close）。如下图所示：

在原表数据中，如果Indicator的值为空，则表示该记录是入库动作，如果为ISSUE，则表示出库。同时需要注意的是，实际记录日期也许有缺失，即某几天完全没有出入库记录，但对于统计的库存状态来说必须包含完整连续的日期。而且满足以下规则：当日的“Open”应该等于前一日的“Close”，“Enter”和“Issued”来自于表stocklog，“Total”等于“Open+Enter”，“Close”等于“Open+Enter-Issued”或者是“Total-Issued”。

SPL代码如下：

A1：查询数据库，根据表stocklog计算出每种产品每天总的入库数量和出库数量。这里只需要对数据进行分组汇总，计算上没有难度，可以交给SQL语句去完成。值得注意的是，A1中有两个参数start和end，分别对应SQL语句中的两个问号，代表外部传入的时间段，可以来自于JAVA或报表工具。假设start和end的值分别为 2014-04-01和2014-04-10，则A1的计算结果如下：

A2=A1.group(INAME)

这句代码将A1按照INAME分组，每组数据是一个产品各天的总出入库数量。值得注意的是，这里无需对分组后的数据进行汇总计算，也就是说是一个纯粹的分组操作。A2的计算结果如下图左侧，右侧是每组数据的明细。

关于分组，集算器SPL有两个函数：groups和group。函数groups类似于SQL中的group by语句，可以在分组的同时进行汇总。而group函数只分组，不做汇总，这种纯粹的分组功能是SQL所缺乏的。

最终需要的计算结果是start到end之间每一天的库存状态，而源数据并非每天都有出入库记录，因此还要把A2按照连续的时间序列对齐。下面先生成这个时间序列。

B2=periods(start,end,1)

函数periods可以生成时间序列，有三个参数：起始时间、终止时间、间隔。缺省将生成日期序列，使用选项还可以生成年、季、月、旬的时间序列。A3的计算结果如下：

A3=for A2，这是循环语句，表示对A2进行循环，每次计算一个产品。

B3-B6是循环体，具体算法是先将该产品的出入库记录与B2中的时间序列对齐，然后计算每个产品每天的库存状态，最后将计算结果追加到B6中。值得注意的是，集算器使用直观的缩进来表示循环体，而不是括号或begin/end等标识符。B3-B6就是循环语句A3的循环体。

B3=A3.align(B2,IDATE)

这句代码将当前产品的出入库记录与B2中的时间序列对齐。注意，A3既是循环语句，也是循环变量，即当前产品的出入库记录。以产品Item3为例，下图左侧是对齐前的数据，右侧是对齐后的数据：

      

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的计算结果如下：

B6=@|B5

这句代码将B5不断地追加到当前格中，@表示当前格B6，最终计算结果如下：

B6就是本案例的最终计算结果。

file("stocklog.xlsx").xlsexport@t(B6)

这句代码将计算结果导出到"stocklog.xlsx"文件，可以通过excel工具来查看：

另外，集算器可被报表工具或java程序调用，调用的方法也和普通数据库相似，使用它提供的JDBC接口即可向java主程序返回ResultSet形式的计算结果，具体方法可参考相关文档。【Java如何调用SPL脚本】