简化SQL式计算之多层固定分组

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

集算器可以实现多层固定分组，代码简单易懂，下面用一个例子来说明。

   表stocklog存储着每天多种货物的多次出入库记录，要计算出指定时间段内每天每种货物的库存状态。表stocklog的部分数据如下：

 

其中Indicator的值如果为空，则表示该记录是入库动作，如果为ISSUE，则表示出库。需要注意的是，这里的日期也许有缺失，即某几天完全没有出入库记录，但库存状态必须包含完整的连续日期。

库存状态是每天每种产品的开库时数量（Open）、入库数量（Enter）、最高库存（Total）、出库数量（Issued）、闭库时数量（Close）。其中：当日的“Open”等于前一日的“Close”，“Enter”和“Issued”来自于表stocklog，“Total”等于“Open+Enter”，“Close”等于“Open+Enter-Issued”或者是“Total-Issued”。

   集算器代码如下：

 

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

 

A2=A1.group(Lname)

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

 

关于分组，集算器有两个函数：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(A3,Date)

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

 

B4>c=0

这句代码用来给变量c赋初值0。c代表当前产品每条库存状态的Open字段，初始日期的Open字段为0，c会在B5中不断被修改。

 

B5=B3.new(A3.Lname:Lname,B2(#):LDate, c:Opening, Enter,(b=c+Enter):Total,Issue,(c=b-Issue):Close)

这句代码用来计算库存状态。B3.new(…)表示以B3为基础新建一个序表，即当前产品的库存状态。新序表中有7个字段，如下：

A3.Lname:Lname----从当前产品的出入库记录A3取出Lname字段，新字段名为Lname。

B2 (#):LDate ----将时间序列B2按顺序插入新序表，作为新字段LDate。注意，#表示A3的记录序号，B2(N)表示B2的第N条记录，因此B2(#)表示按A3的序号将B2插入新序表。

c:Open----将变量c作为Open的字段值，第一条记录时，这个值为0。

Enter----将B3中的字段Enter直接当做新字段。由于新序表是基于B3的，因此无需像Lname字段那样重命名。

(b=c+Enter):Total----按公式Open+Enter计算出字段Total，为了清晰起见，这里用括号把表达式括起来。

Issue---将B3中的字段Issue直接当做新字段。

(c=b-Issue):Close---按公式Total-Issued计算Close字段。注意，这里的c已经被修改了，在计算下一条记录时，c会作为Open字段的值，从而满足业务规则：当日的“Open”等于前一日的“Close”。

以item3为例，B5的计算结果如下：

 

B6=@|B5

 

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

 

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

 

 

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