第3章：SQL聚合函数与窗口函数——从SUM到ROW_NUMBER

做特征工程这么多年，我有个很深的体会：用好SQL的聚合函数和窗口函数，就等于拿到了变量衍生的半壁江山。很多同学跑来问我“表哥，这个时序特征怎么造？”——其实90%的答案都在窗口函数里。

这一章，我们就把这两类函数彻底讲透。别急，先从最基础的讲起。

3.1 聚合函数：把多行缩成一行

聚合函数大家都不陌生：SUM()、AVG()、COUNT()、MAX()、MIN()。它们的核心特点就是：多行输入，一行输出。配合GROUP BY，可以对不同分组分别计算。

举个例子，我在做一个用户交易行为分析时，需要每个用户的累计消费金额：

SELECT
    user_id,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount,
    COUNT(*) AS order_count,
    MAX(order_time) AS last_order_time
FROM orders
GROUP BY user_id;

这个查询把每个用户的所有订单“揉”成一条记录。注意那个last_order_time，它就是MAX(order_time)的结果——我经常用它来判断用户最近一次下单时间，然后算“距离今天的天数”，这个特征在流失预测里特别管用。

3.2 窗口函数：不缩行，只加列

窗口函数和聚合函数长得像，但行为完全不同。它不会减少行数，而是在每一行旁边新增一列计算结果。

窗口函数语法长这样：

函数名() OVER ( [PARTITION BY 分组列] [ORDER BY 排序列] [ROWS/ROWS BETWEEN ...] )

我刚开始学的时候也觉得括号里那串东西烦人。但你只要拆开看：

• PARTITION BY：相当于GROUP BY，按什么分组
• ORDER BY：决定了窗口内的排序方向
• ROWS/ROWS BETWEEN：定义窗口的大小（从哪行到哪行）

这么说吧，OVER()里什么都不写，那就是把整个表当成一个窗口，对全局做聚合。比如：

SELECT
    user_id, amount,
    SUM(amount) OVER() AS global_total
FROM orders;

结果里每一行都带着一个相同的全局总和，这玩意用聚合函数是做不到的。

3.3 常用窗口函数：SUM、AVG、ROW_NUMBER

咱们一个一个来过。

3.3.1 SUM() OVER：累计求和

用途：计算分组内的累计值。比如按时间顺序累计交易金额：

SELECT
    user_id, order_time, amount,
    SUM(amount) OVER(PARTITION BY user_id ORDER BY order_time) AS cum_amount
FROM orders;

这个cum_amount就是用户从第一单到当前单的累计金额。我曾经用它来构造“消费速度”特征：看用户从0到10000元用了多少天。

3.3.2 AVG() OVER：滑动平均

适合做平滑，比如过去3笔订单的平均金额：

SELECT
    user_id, order_time, amount,
    AVG(amount) OVER(PARTITION BY user_id ORDER BY order_time
                     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS mov_avg
FROM orders;

注意这里用了ROWS BETWEEN。什么叫“2 PRECEDING”？意思就是往前看2行，再加上当前行，一共3行算平均。

3.3.3 ROW_NUMBER()：行号，但不止于编号

ROW_NUMBER() 给组内每一行一个自增序号，从1开始。用法：

SELECT
    user_id, order_time, amount,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_time DESC) AS rn
FROM orders;

这个结果里，每个用户最新的订单rn=1。我经常用它来取“每个用户的最新一条记录”：外面包一层WHERE rn=1就行。

还有另外两个相似的函数——RANK()和DENSE_RANK()——它们处理并列名次的方式不同。看个对比：

函数	相同值处理	示例排名（值[100,100,90]）
ROW_NUMBER()	相同值随机排序，序号不重复	1,2,3
RANK()	相同值并列，跳过中间序号	1,1,3
DENSE_RANK()	相同值并列，不跳过序号	1,1,2

举个实际场景：如果你想给每个用户的消费金额排名，并列的给相同名次，并且后续名次不跳号（比如1,1,2），那就用DENSE_RANK()。我曾经踩过坑——用RANK()出来名次有空洞，下游逻辑直接报错。

3.4 窗口函数的三种“窗口”范围

搞懂了函数，还得搞懂范围。不然你怎么知道你的计算覆盖了哪些行？

• 默认窗口：如果只写ORDER BY没写ROWS，默认是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（从分组第一行到当前行）。
• ROWS vs RANGE：ROWS按物理行号算；RANGE按排序值的逻辑范围算（相同值归为一行）。
• 自定义窗口：用ROWS BETWEEN精确控制，比如只看前3行、后2行。

3.5 聚合 vs 窗口：一张图看懂

看上图就清楚了吧？左边聚合函数把多行“压缩”成一行，你失去了原始行的细节；右边窗口函数保持行数不变，只是在旁边加了一列计算结果。哪个场景用哪个，一目了然。

3.6 实战建议：什么时候用聚合，什么时候用窗口

我根据自己经验列个对照表：

业务需求	推荐函数	理由
计算每个用户的均值、总和（只保留一个值）	聚合函数 + GROUP BY	行数压缩，结果干净
给每条订单追加用户历史累计金额	SUM() OVER(PARTITION BY user_id ORDER BY order_time)	保留原始订单，新增累积特征
取每个用户最近一笔订单	ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_time DESC)	筛选 rn=1 即可
计算连续3天平均活跃度	AVG() OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)	滑动窗口，适合时间序列平滑