集算器协助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字段),则代码应当写成这样:
结果如下:
下一篇: 文件下载零基础入门教程推荐