【摘要】
聚合运算是指对数据进行计算,返回聚合结果。聚合运算经常伴随着分组运算,除了常见的求和、最大值、最小值、计数等聚合运算,还有一些逻辑运算等等。如何简便快捷的处理聚合问题,这里为你全程解析,并提供 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 | =A2.enum@r(A3,City) | /按城市枚举分组 |
5 | =A4.new(A3(#):Area,~.sum(GDP)/~.sum(Population)*10000:CapitaGDP) | /统计每组的 |