非常规聚合问题举例
【摘要】
聚合运算是指对数据进行计算，返回聚合结果。聚合运算经常伴随着分组运算，除了常见的求和、最大值、最小值、计数等聚合运算，还有一些逻辑运算等等。如何简便快捷的处理聚合问题，这里为你全程解析，并提供 esProc 示例代码。非常规聚合问题举例
1. 枚举分组后聚合求和
【例 1】 从城市 GDP 表中，分别统计直辖市、一线城市和二线城市的人均 GDP。城市 GDP 表部分数据如下：
| ID | City | GDP | Population |
| 1 | Shanghai | 32679 | 2418 |
| 2 | Beijing | 30320 | 2171 |
| 3 | Shenzhen | 24691 | 1253 |
| 4 | Guangzhou | 23000 | 1450 |
| 5 | Chongqing | 20363 | 3372 |
| … | … | … | … |
【SPL 脚本】
| A | B | |
| 1 | =connect("db") | /连接数据库 |
| 2 | =A1.query("select * from GDP") | /查询城市 GDP 表 |
| 3 | [["Beijing","Shanghai","Tianjing","Chongqing"].pos(?)>0,["Beijing","Shanghai","Guangzhou","Shenzhen"].pos(?)>0,["Chengdu","Hangzhou","Chongqing","Wuhan","Xian","Suzhou","Tianjing","Nanjing","Changsha","Zhengzhou","Dongguan","Qingdao","Shenyang","Ningbo","Kunming"].pos(?)>0] | /枚举直辖市、一线城市和二线城市 |
| 4 | [email protected](A3,City) | /按城市枚举分组 |
| 5 | =A4.new(A3(#):Area,~.sum(GDP)/~.sum(Population)*10000:CapitaGDP) | /统计每组的人均 GDP。其中用到了函数 sum() 求和 |
A5的执行结果如下：
| Area | CapitaGDP |
| ["Beijing","Shanghai","Tianjing","Chongqing"].pos(?)>0 | 107345.03 |
| ["Beijing","Shanghai","Guangzhou","Shenzhen"].pos(?)>0 | 151796.49 |
| ["Chengdu","Hangzhou","Chongqing","Wuhan","Xian","Suzhou","Tianjing","Nanjing","Changsha","Zhengzhou","Dongguan","Qingdao","Shenyang","Ningbo","Kunming"].pos(?)>0 | 106040.57 |
2. 合并重叠的时间区间
【例 2】 将客户 ANATR 有重复时间段的订单记录合并。客户表部分数据如下：
| OrderID | Customer | SellerId | OrderDate | FinishDate |
| 10308 | ANATR | 7 | 2012/09/18 | 2012/10/16 |
| 10309 | ANATR | 3 | 2012/09/19 | 2012/10/17 |
| 10625 | ANATR | 3 | 2013/08/08 | 2013/09/05 |
| 10702 | ANATR | 1 | 2013/10/13 | 2013/11/24 |
| 10759 | ANATR | 3 | 2013/11/28 | 2013/12/26 |
| … | … | … | … | … |
【SPL脚本】
| A | B | |
| 1 | =connect("db") | /连接数据源 |
| 2 | =A1.query("select * from Orders where Customer='ANATR'order by OrderDate") | /选出客户 ANATR 的订单信息，按订单日期排序 |
| 3 | [email protected](OrderDate>max(FinishDate[,-1])) | /当订单日期大于前面所有订单的完成日期时分到新组 |
| 4 | =A3.new(Customer,~.min(OrderDate):OrderDate,~.max(FinishDate):FinishDate) | /使用函数 min() 计算每组最早的订单日期作为订单日期，使用函数 max 计算最晚的订单日期作为完成日期 |
A4的执行结果如下：
| Customer | OrderDate | FinishDate |
| ANATR | 2012/09/18 | 2012/10/17 |
| ANATR | 2013/08/08 | 2013/09/05 |
| ANATR | 2013/10/13 | 2013/11/24 |
| ANATR | 2013/11/28 | 2013/12/29 |
| … | … | … |
3. 在分组聚合中统计满足条件的数量
【例 4】 求一班各科不及格人数。成绩表部分数据如下：
| CLASS | STUDENTID | SUBJECT | SCORE |
| Class one | 1 | English | 84 |
| Class one | 1 | Math | 77 |
| Class one | 1 | PE | 69 |
| Class one | 2 | English | 81 |
| Class one | 2 | Math | 80 |
| … | … | … | … |
【SPL 脚本】
| A | B | |
| 1 | =connect("db") | /连接数据库 |
| 2 | =A1.query("select * from Scores where CLASS='Class one'") | /查询一班学生成绩 |
| 3 | =A2.groups(SUBJECT; count(SCORE<60):FailCount) | /分组汇总，其中用到了函数 count() 统计不及格人数 |
A3的执行结果如下：
| SUBJECT | FailCount |
| English | 2 |
| Math | 0 |
| PE | 2 |
4. 在布尔值构成的集合中，聚合时执行逻辑与运算
【例 5】 根据一系列某小学在线教学终端调查表，查看是否所有学生都能够使用手机。各班问卷及汇总目录如下：
| ID | STUDENT_NAME | TERMINAL |
| 1 | Rebecca Moore | Phone |
| 2 | Ashley Wilson | Phone,PC,Pad |
| 3 | Rachel Johnson | Phone,PC,Pad |
| 4 | Emily Smith | Phone,Pad |
| 5 | Ashley Smith | Phone,PC |
| 6 | Matthew Johnson | Phone |
| 7 | Alexis Smith | Phone,PC |
| 8 | Megan Wilson | Phone,PC,Pad |
| … | … | … |
【SPL 脚本】
| A | B | C | |
| 1 | [email protected]("D:/Primary School") | /递归遍历目录，列出所有文件 | |
| 2 | for A1 | =file(A2)[email protected]() | /循环导入各班级问卷 excel 文件 |
| 3 | =B2.([TERMINAL,"Phone"].ifn()[email protected]().pos("Phone") > 0)|@ | /当问卷中终端未填写时不认为不支持手机终端，使用函数 ifn() 保证此项为 true。 | |
| 4 | =B3.cand() | /使用函数 A.cand() 计算 B3 的成员是否都是 true |
A4的执行结果如下：
| Value |
| false |
5. 在布尔值构成的集合中，聚合时执行逻辑或运算
【例 6】 查询客户 RATTC，在 2014 年是否排进过单月销售额的前三名。销售表部分数据如下：
| OrderID | Customer | SellerId | OrderDate | Amount |
| 10400 | EASTC | 1 | 2014/01/01 | 3063.0 |
| 10401 | HANAR | 1 | 2014/01/01 | 3868.6 |
| 10402 | ERNSH | 8 | 2014/01/02 | 2713.5 |
| 10403 | ERNSH | 4 | 2014/01/03 | 1005.9 |
| 10404 | MAGAA | 2 | 2014/01/03 | 1675.0 |
| … | … | … | … | … |
【SPL脚本】
| A | B | |
| 1 | =connect("db").query("select * from sales") | /连接数据源，读取销售表 |
| 2 | =A1.select(year(OrderDate)==2014) | /选出 2014 年数据 |
| 3 | =A2.group(month(OrderDate)) | /将 2014 年的数据按照月份分组 |
| 4 | =A3.(~.groups(Customer; sum(Amount):Amount)) | /分组后的成员按照客户分组汇总销售额 |
| 5 | =A4.new(~.top(-3; Amount):Top3) | /循环每个月的数据，计算每月销售额前 3 的客户 |
| 6 | =A5.(Top3.(Customer).pos("RATTC")>0) | /判断每个月前三名是否包含客户 RATTC |
| 7 | =A6.cor() | /使用函数 A.cor() 计算 A6 的成员是否存在 true |
A7的执行结果如下：
| Value |
| false |
《SPL CookBook》中还有更多相关计算示例。