SPL 简化 SQL 案例详解：组内运算

在开发数据库应用时，经常在分组后需要对组内数据进行计算，例如：列出近3年每年都发表过论文的学生名单（按论文发表年分组后列出每年都出现的学生清单），统计全部参加了历次培训的员工（按培训分组后统计每次都有的员工人数），选出每位客户的高尔夫成绩最高的三天（按客户分组后按天计算成绩的Top3）等等。

对于SQL来说，完成这类运算一般较为复杂，需要嵌套多层，往往导致代码难以理解和维护困难。而SPL非常擅长这类组内计算，并且很容易和JAVA、报表工具集成。下面用一个例子来进行说明。

表sales存储着多年的订单数据，部分数据如下：

OrderID	Client	SellerId	OrderDate	Amount
10808	OLDWO	2	2015/1/1	1660
10809	WELLI	7	2015/1/1	140
10810	LAUGB	2	2015/1/1	187
10811	LINOD	8	2015/1/2	852
10812	REGGC	5	2015/1/2	1852
10813	RICAR	1	2015/1/5	648
10814	VICTE	3	2015/1/5	2070
10815	SAVEA	2	2015/1/5	40
10816	GREAL	4	2015/1/6	8891

要求是请根据该表统计出指定年份（例如2015）中，每个月销售金额均排在前20名的客户名称。

解决这个问题的思路是，首先选出2015年销售数据，按月分组统计，然后循环选出每月销售前20名的客户，最后求各组的交集。

这样的计算在SQL很难直接表达，而SPL则可以很自然地逐步计算，将复杂问题拆分后得出最终结果。

SPL代码：

	A
1	=db.query("select   * from sales")
2	=A1.select(year(ORDERDATE)==YEAR)
3	=A2.group(month(ORDERDATE))
4	=A3.(~.group(CLIENT))
5	=A4.(~.top(-20;sum(AMOUNT)))
6	=A5.(~.new(CLIENT,sum(AMOUNT):MONTH_AMOUNT))
7	=A6.(~.(CLIENT))
8	=A7.isect()

A1：从数据库中读出销售表；

A2：=A1.select(year(ORDERDATE)==YEAR)，从销售数据中取出指定年份的数据。通过网格参数YEAR灵活适应不同的查询需求，此例中YEAR=2015。当然，这里的过滤也可以直接通过A1中的SQL来完成。

A3：=A2.group(month(ORDERDATE))，使用group函数，将2015年的数据按照月份分组。这里需要特别说明的是，SPL的数据分组，是真实的分组，会根据需要将数据实际分为多个组。这和SQL中的情况不同，SQL中的group by命令是直接计算分组的汇总值，并不能保留中间的分组结果。因此也就无法对分组的数据进一步计算了。分组后，A3中的数据如下：

我们还可以继续通过双击来查看详细数据，例如下面是三月份的数据：

为了统计每个月中，每个客户的月销售总额，需要再按客户分组。在SPL中，只需要对每个月的数据进行循环，分别按客户分组就可以了。循环组内成员时，还可以使用简洁的A.(x)来执行，而不必再去编写循环代码。

A4：=A3.(~.group(CLIENT))

再次分组后，A4中，每个月的数据就都是分组的分组了：

其中3月按客户分组的数据如下：

可以看到，3月数据中的每个分组，都是某个客户的交易数据。

注意，上述代码中的“~”表示分组中的每个成员，针对“~”书写的代码就是组内运算代码，比如上面的~.group(CLIENT)。

接下里，继续通过组内运算求出每月排名前20的大客户：

A5：=A4.(~.top(-20;sum(AMOUNT)))

A6：=A5.(~.new(CLIENT,sum(AMOUNT):MONTH_AMOUNT))

在A5中，循环每个月的数据，使用top函数计算出了每月销售额最大的前20个客户。在A6中列出了这些客户的名称及月销售额。A6中计算的结果和3月份的统计数据如下：

最后列出分组内的Client字段，并对各分组求交集：

A7：==A6.(~.(CLIENT))

A8：=A7.isect()

在A7中列出每月销售额最大的20个客户名称。最后在A8中求12个月的客户名称交集，得到我们需要的结果如下：

       

从这个问题中可以看到，SPL可以轻松实现结构化数据的组内计算，解决问题时的思路非常直观。组内计算时也能够轻松地完成再分组、排序等计算，每一步的数据处理更加清晰自然。此外，SPL提供的组成员循环、求交集等运算也使得计算变得更为简易，大大减少了代码量。

对于计算结果，除了导出数据，SPL还可以直接以被调用的方式向报表工具或java程序提供数据，调用方法和普通数据库相似，使用它提供的JDBC接口即可向java主程序返回ResultSet形式的计算结果