非常規聚合問題舉例
【摘要】
聚合運算是指對數據進行計算，返回聚合結果。聚合運算經常伴隨着分組運算，除了常見的求和、最大值、最小值、計數等聚合運算，還有一些邏輯運算等等。如何簡便快捷的處理聚合問題，這裏爲你全程解析，並提供 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》中還有更多相關計算示例。