标准 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 概念清晰，语法风格一致，可以写出最简洁易懂的代码。