集算器协助SQL实现非等值分组

SQL通常只能按源表字段进行分组，如果分组依据来自另一张表、外部参数、条件列表，用SQL就很繁琐。有时分组依据需要动态生成，这些往往要借助高级语言实现。有时分组依据和源表不完全对应（或区间没有交集），差异部分有时要补齐在分组结果中，有时要排除在外，用SQL处理起来难度很大。

         集算器支持对位分组、枚举分组、有序计算，可轻松实现上述非等值分组。集算器还支持独立使用、控制台执行、报表调用、JAVA代码调用，参考【集算器辅助SQL编写的应用结构】。

下面举例说明SQL开发中常见的非分等值分组问题，以及集算器的解法。

 

基本固定分组

表sales存储着订单记录，其中CLIENT列是客户名，AMOUNT列是订单金额，请将sales按照“潜力客户列表”进行分组，并对各组的AMOUNT列汇总求和，其中潜力客户列表是外部参数，有些项目可能不在sales表的Client列（汇总结果应为0），比如：TAS,BON,CHO,ZTOZ。部分数据如下：

OrderID	Client	SellerId	Amount	OrderDate
26	TAS	1	2142.4	2009/8/5
33	DSGC	1	613.2	2009/8/14
84	GC	1	88.5	2009/10/16
133	HU	1	1419.8	2010/12/12
32	JFS	3	468	2009/8/13
39	NR	3	3016	2010/8/21
43	KT	3	2169	2009/8/27

         分组依据是定值且项目较少时，SQL可用union/decode配合进行分组，但本案例的分组依据是外部参数，经常发生变化，SQL就只能创建临时表，将参数解析后一条条插入临时表，再进行后续的计算。用集算器实现本案例不必建立临时表，代码如下：

 

         函数align可按外部参数列表arg1分组，选项@a表示取出分组中的所有记录，无该选项时只取每组第一条。函数pjoin可按顺序横向拼接记录，结果如下：

动态分段合计

按订单金额对表sales进行分段，统计每一段的订单总额，分段条件是参数列表，每次都可能不同，比如分为4个区间：0-1000、1000-2000、2000-4000。

 

如果条件已知，那就可以将这些条件写死在SQL里，如果条件是动态的外部参数，则经常需要用JAVA等高级语言拼凑SQL，过程非常复杂。集算器支持动态表达式，代码更加简单：

其中byFac是参数，比如 ["?<=1000" ,"?>1000 && ?<=2000","?>2000 && ?<=4000","?>4000"]。Enum函数可按条件对数据分组。结果如下：

 

         上述条件分组中，条件恰好没有发生重叠，但实际上发生重叠的情况很常见，比如将订单金额按照如下规则分组：

         1000至4000：常规订单r14

         2000以下：非重点订单r2

         3000以上：重点订单r3

上述条件中r2和r1发生了条件重叠，在这种情况下有时我们希望结果不重叠（即按r1统计完后，从剩下的记录中统计r2），有时也会要求重叠（每次都按全部数据重新统计）。想实现此类需求，SQL要使用大量with、union、except或minus语句，代码非常繁琐。集算器的函数enum默认不重叠，使用@r选项时允许重叠。

 

动态分段累计

         表performance存储每位员工的绩效分和绩效奖金，现在要从0开始，以10分为一个分数段，将绩效分为多个分数段，然后从低到高累计每个分数段的奖金总数，即当前段的累计值要包含之前所有段的奖金总数。源数据如下：

id	score	bonus
e01	9	800
e02	21	2300
e03	25	2800
e04	33	4100
e05	46	5800
e06	52	6099

 

         绩效分数不定，因此区间数量不定，Oracle/MSSQL可以建立辅助区间表结合窗口函数来实现，但代码相当繁琐，MySQL等不支持窗口函数的数据库将更加困难。此类非等值分组用集算器实现较简单，代码如下：

 

         A2动态生成区间范围，其中m表示按序号取成员，m(-1)表示取倒数第1条，“\”表示除后取整数部分。A3根据A2建立新二维表，通过查询A1对应的记录来累计奖金，结果如下：

 

补齐固定分组

         表building存储产品完工记录，其中year为完工时间，字符型，格式为“年份 上半年\下半年”。现在要指定起止年份，统计出每类产品每半年的完工数量。源数据如下：

id	type	year
1	33	2014 last half
2	33	2014 last half
3	33	2013 first half
4	34	2013 first half

         源表中的年份不连续，SQL要先拼凑连续的年份表再left join，难度较大。集算器代码相对简单：

 

         A2创建新二维表，A3按照起止时间参数（argb,arge）创建年份表，A4将building表按type分组，并循环处理每组数据。每次循环（B4）向A2插入与年份表数量相等的记录。A2的最终结果如下：

 

补齐固定分组并转置

         表onBusiness存储员工的出差记录，主要字段是Date,id_user。表user存储用户信息，主要字段是id,name，现在要指定时间段，按顺序列出每周每个员工是否出差，特殊之处在于每个员工要占一列。onBusiness部分数据如下：

 

Date	id_user
2015-06-22	2
2015-06-01	1
2015-06-03	1
2015-06-19	1
2015-06-02	2

如果当起止日期是2015-05-31、2015-06-28，则期望结果应当如下：

week	user1	user2
1	yes	yes
2	No	No
3	yes	No
4	No	yes

 

集算器代码如下：

 

先用简单关联语句查询数据，再按区间造二维表A3，每周占1行（间断时自动补齐数据），每个员工占一列，初值为“No”。然后循环A2，将A3对应的位置修改为“Yes”。

按月份查询分组

       表work存储着某岗位的任职信息，People字段是用户名，Date表示到岗日期，Deleted表示离岗日期，现在要统计出3月份到7月份每个月有多少人在岗。源数据如下：

People	Date	Deleted
Amanda	2015-03-01	Null
Ray	2015-03-01	Null
Moe	2015-04-01	Null
Yan	2015-05-01	Null
Bee	2015-05-05	2015-06-12
Lee	2015-06-06	Null
jason	2015-05-01	2015-07-03

       本案例难点在于源表中月份不一定连续，但计算结果要求连续，因此要先生成连续月份，再将源数据按连续月份对齐分组。此外，如果某人离岗，则当月的在岗人员中包含此人，下月不包含，因此要进行跨列计算和组间累计才能算出正确的在岗人数。

  集算器代码：

 

函数to可生成连续序列，函数new可根据序列（或二维表）生成新二维表，~表示源序列中当前成员，最终结果如下：

 

动态区间定位

         表Transaction记录每个客户的交易时间，表Discount存储某个时间点之后的折扣，折扣信息有多条，形成了一系列动态时间段。现在要计算每位客户每项交易的折扣。

    表Transaction部分数据

TransID	Tuser	Date
t1	Andrew	2015-06-16 13:13:00
t2	Andrew	2015-06-16 13:15:00
t3	Andrew	2015-06-16 13:17:00
t4	Andrew	2015-06-16 14:15:00
t5	Andrew	2015-06-16 14:18:00
t6	Andrew	2015-06-16 14:25:00
t7	Andrew	2015-06-16 14:35:00
t8	Andrew	2015-06-16 14:55:00
t9	tylor	2015-06-16 13:13:00
t10	tylor	2015-06-16 14:15:00
t11	tylor	2015-06-16 14:55:00

    表Discount部分数据：

DiscountID	Date	Discount
d1	2015-06-16 13:00:00	30
d2	2015-06-16 14:00:00	25
d3	2015-06-16 14:30:00	20

 SQL要先跨行算出时间段，再进行join运算，代码很难写。集算器可将Transaction按Disccount等值分组，代码相对简单：

 

         函数pseq可算出某条数据所属的区间号，A2(…)可按序号取记录。计算结果如下：

 

         上述例子中所有的客户共享折扣信息，如果每个客户的折扣都不同(discount表增加DUser字段)，则代码应当写成这样：

 

         结果如下：