另类却不罕见的聚合运算
标准 SQL 中提供了五种最常用的聚合运算:SUM/COUNT/AVG/MIN/MAX,都是对集合计算出单值。
比如日志表中找出用户 1001 第一次登录的时间,SQL 很简单:
SELECT MIN(time) FROM login WHERE user=1001
聚合运算还常常用在分组中,比如找每个用户的第一次登录时间:
SELECT user,MIN(time) FROM login GROUP BY user
语法上只是多了分组字段。
Python 也是类似的:
login[login['user']==1001]['time'].min()
login.groupby('user')['time'].min()
看起来这两句代码风格是一致的,但分组聚合的计算结果却不是二维的 DataFrame。
如果想返回 DataFrame,要写成:
login.groupby("user").agg({"time": "min"})
但这和全集聚合的语法风格又有点不一致了。
esProc SPL 当然也支持这种基础形式:
login.select(user==1001).min(time)
login.groups(user;min(time))
和 SQL 一样,对象和语法风格都是一致的。
不过,聚合运算并不总以这么基础的形式出现,情况更复杂时,SPL 就会显出优势了。
1. 非常规聚合函数
有时候我们关心的不是聚合结果数值本身,而是与结果数值相关的信息。比如我们想找用户第一次登录的 ip 地址、浏览器类型等,而不仅仅是登录的时刻,聚合的结果就应该是记录而不是某个数值了。
SQL 的写法:
SELECT * FROM login
WHERE user =1001 and
time = (SELECT MIN(time) FROM login WHERE user=1001);
SQL 没有返回记录的聚合函数,先用子查询计算出最小登录时间,外层查询再找出该时间的其他信息,要把数据集遍历两次。
聚合运算和分组结合时,SQL 就更麻烦了,比如找每个用户第一次登录的记录:
SELECT * FROM login
JOIN (
SELECT user,MIN(time) AS first_login_time
FROM login
GROUP BY user
) sub ON user = sub.user AND time = sub.first_login_time;
子查询先分组聚合出每个用户第一次登录的时间,再和原表连接取其他字段。
SPL 提供了“另类”聚合函数 minp、maxp,可以返回最小值、最大值对应的记录。我们想把求“最小值”改成“最小值对应记录”,只要将聚合函数 min 换成 minp 就可以了:
login.select(user==1001).minp(time)
分组聚合的情况也一样,仅仅换个聚合函数就行:
login.groups(user;minp(time))
Python 也提供了类似的函数:
login[login['user']==1001].nsmallest(1, "time")
nsmallest(1, "time") 找出 time 值最小的那一行,类似的还有 nlargest 函数。
不过,分组聚合时 nsmallest 就不能像 min 那样写到分组 groupby 后的 agg 中了,要借助 apply,概念不太一致,而且书写略显复杂:
login.groupby('user').apply(lambda group: group.nsmallest(1, 'time'))
因为,Python 把 nsmallest、nlargest 看成是对数据的筛选方法,计算过程和 min、max 这些聚合函数不一样。聚合函数常常可以在遍历过程中完成计算,不需要先获取完整的分组子集,这样内存占用会小很多。
SPL 的 minp、maxp 也是这样计算的,但 Python 的 nsmallest、nlargest 却不行,只能针对算出来的分组子集做聚合。
当然 SPL 也支持这种对分组子集再聚合的写法:
login.group(user;~.minp(time))
~ 就是分组子集,代码要比 Python 更简洁。
聚合运算还可能返回集合,比如常见的 topN 问题:找出员工薪酬最高的前三名。
SQL 并没有把 topN 理解为聚合函数,只能写成排序的样子:
SELECT * FROM employee ORDER BY salary DESC LIMIT 3;
这是 mysql 的写法,不同数据库的写法不同,但都包含排序。
把这个计算和分组结合,想找出每个部门薪酬最高的三个员工,用 SQL 就更麻烦了:
SELECT *
FROM(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as row_num
FROM employee
)
WHERE row_num<= 3;
写法和计算全集 topN 时完全不一样,需要用窗口函数生成个序号再过滤出来,思路很绕。
SPL 则把 topN 也视为聚合运算,提供了聚合函数 top:
employee.top(-3;salary)
top 函数在遍历原表的过程中,将数据向一个大小为 N 的小集合聚合。
用到分组中也容易:
employee.groups(department;top(-3;salary))
也可以用分组子集的写法:
employee.group(department;~.top(-3;salary))
Python 的 nlargest 也能返回集合:
employee.nlargest(3, 'salary')
但用在分组中还是要借助 apply。
employee.groupby('department').apply(lambda group: group.nlargest(3, 'salary'))
2. 复杂聚合运算
更复杂的聚合运算就不能用单个函数实现了,需要写多个步骤。
如果是针对全集的运算,那还没什么,分步写就是了,但如果伴随着分组,那么这些步骤就要针对分组子集进行。比如:
某连锁店各门店有不同品类的商品销售量数据,计算各门店平均销量,并对每个门店求超出平均销量的商品的总销售量、以及这些商品中电子品类商品的总销售量。
没有分组子集的 SQL 对付这种问题非常麻烦:
WITH StoreAvgVolume AS (
SELECT store, AVG(volume) AS avg_volume
FROM sales
GROUP BY store
),
AboveAvgVolume AS (
SELECT s.store, s.product, s.category, s.volume, sav.avg_volume
FROM sales s
JOIN StoreAvgVolume sav ON s.store = sav.store
WHERE s.volume > sav.avg_volume
)
SELECT store,avg_volume,
SUM(volume) AS total_volume,
SUM(CASE WHEN category = 'Electronics' THEN volume ELSE 0 END) AS electronic_volume
FROM AboveAvgVolume
GROUP BY store,avg_volume;
第一步分组计算每个网点的平均销售量。第二步用连接筛选出销量超过网点平均值的商品。第三步再次分组,计算这些商品总销售量、其中电子产品的总销售量。
SPL 分组后保持分组子集,可以对分组子集进行连续多步的计算:
sales.group(store;a=~.avg(volume):avg,(above=~.select(volume>a)).sum(volume):total,above.select(category=="Electronics").sum(volume):e_total)
聚合运算有些中间结果需要重复使用,比如代码中的 above 定义成中间变量就可以了。
Python 也有分组子集的概念,但相关的数据对象和函数却很杂乱,需要多出 Series 对象,而且字段名都要写成 tablename['fieldname'] 的啰嗦形式:
import pandas as pd
sales = pd.read_csv('sales.csv')
result = sales.groupby('store').apply(lambda group: pd.Series({
'avg': (avg := group['volume'].mean()),
'above': (above := group[group['volume'] > avg]),
'total': (above['volume'].sum()),
'e_total': (above[above['category'] == 'Electronics']['volume'].sum())
})).drop(columns=['above'])
聚合运算的中间结果要定义成计算列,比如 above,最后还要删掉这一列。虽然代码逻辑上没问题,但书写出来还是比 SPL 要繁琐不少。
小结一下:SQL 只提供了最基础的聚合运算,稍复杂一些的任务都会非常困难;Python 要好很多,有更丰富的概念和运算,能够完成更复杂的计算任务,但是概念体系比较杂乱,语法风格在不同情况下不一致,难学难记,代码也较为繁琐。SPL 概念清晰,语法风格一致,可以写出最简洁易懂的代码。