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

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

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

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

IDate

Iname

Iquantity

Indicator

2014-04-01

Item1

15


2014-04-01

Item1

4


2014-04-02

Item1

3


2014-04-02

Item1

10

ISSUE

2014-04-03

Item1

3


2014-04-04

Item1

5

ISSUE

2014-04-07

Item1

4


2014-04-10

Item1

2

ISSUE

2014-04-01

Item2

20


2014-04-02

Item3

30


2014-04-03

Item3

14

ISSUE

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

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

SPL代码如下:


A

B

1

=db.query("select IDATE, INAME,   sum(CASE INDICATOR WHEN'ISSUE'THEN 0 ELSE IQUANTITY END) ENTER, sum(CASE   INDICATOR WHEN'ISSUE'THEN IQUANTITY ELSE 0 END) ISSUE from stocklog where   IDATE >= ? and IDATE <= ? group by IDATE, INAME",start,end)

2

=A1.group(INAME)

=periods(start,end,1)

3

for A2

=A3.align(B2,IDATE)

4


>c=0

5


=B3.new(A3.INAME,B2(#):IDATE, c:OPENING,   ENTER,(b=c+ENTER):TOTAL,ISSUE,(c=b-ISSUE):CLOSE)

6


=@|B5

7

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


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

A2=A1.group(INAME)

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

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

最终需要的计算结果是startend之间每一天的库存状态,而源数据并非每天都有出入库记录,因此还要把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赋初值0c代表当前产品每条库存状态的OPENING字段,初始日期的OPENING字段为0c会在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脚本】