在SQL数据处理场景中，经常需要先对数据按指定字段分组，再在组内按某个字段排序，最后对排序后的结果进行聚合计算，比如统计每个班级前三名的总分、每个用户最近三笔订单的总金额等。这类需求如果仅使用基础的GROUP BY语句配合SUM、MAX等聚合函数，会丢失分组内的排序信息，无法得到符合预期的结果。
常见实现方案
1. 使用窗口函数实现
窗口函数是处理分组排序后聚合最便捷的方式，它可以在保留原表所有行的基础上，对分组内的数据进行排序和聚合计算。常用的窗口函数包括ROW_NUMBER、RANK、DENSE_RANK用于排序，SUM、AVG等聚合函数结合OVER子句实现分组聚合。
假设我们有一张订单表order_info，结构如下：
| 字段名 | 类型 | 说明 |
|---|---|---|
| order_id | int | 订单ID |
| user_id | int | 用户ID |
| order_amount | decimal | 订单金额 |
| create_time | datetime | 下单时间 |
需求是统计每个用户最近3笔订单的总金额，实现代码如下：
-- 先给每个用户的订单按时间倒序排序，标记行号
WITH order_rank AS (
SELECT
user_id,
order_amount,
create_time,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn
FROM order_info
)
-- 筛选行号小于等于3的记录，按用户分组求和
SELECT
user_id,
SUM(order_amount) AS recent_3_order_total
FROM order_rank
WHERE rn <= 3
GROUP BY user_id;
上述代码中，PARTITION BY user_id表示按用户ID分组，ORDER BY create_time DESC表示组内按下单时间倒序排序，ROW_NUMBER()会给每个组内的行分配连续的序号。之后筛选序号小于等于3的记录，再按用户分组求和即可得到结果。
2. 使用子查询实现
如果不支持窗口函数的数据库，可以使用子查询的方式实现。核心思路是先查询出每个分组内排序后的明细，再通过条件筛选需要聚合的记录。
同样以上述订单表为例，统计每个用户最近3笔订单总金额的代码如下：
SELECT
t1.user_id,
SUM(t1.order_amount) AS recent_3_order_total
FROM order_info t1
WHERE (
-- 统计当前订单在所属用户下的时间排名，排名前3的保留
SELECT COUNT(*)
FROM order_info t2
WHERE t2.user_id = t1.user_id
AND t2.create_time >= t1.create_time
) <= 3
GROUP BY t1.user_id;
子查询中，对于t1表的每一行，统计t2表中同用户下下单时间大于等于当前订单时间的记录数，这个数量就是当前订单在组内的倒序排名，排名小于等于3的就会被保留下来，之后再进行分组求和。
3. 自连接方式
自连接的方式适合数据量较小的场景，通过同一张表的不同别名进行连接，实现分组排序和筛选。
示例代码如下：
SELECT
t1.user_id,
SUM(t1.order_amount) AS recent_3_order_total
FROM order_info t1
LEFT JOIN order_info t2
ON t1.user_id = t2.user_id
AND t1.create_time <= t2.create_time
GROUP BY t1.user_id, t1.order_id, t1.order_amount, t1.create_time
HAVING COUNT(t2.order_id) <= 3;
连接条件中，t1.create_time <= t2.create_time保证同用户下，t2的订单时间晚于等于t1的订单时间，分组后统计t2的记录数，就是t1订单的倒序排名，排名小于等于3的保留后求和。
不同方案对比
| 方案 | 适用场景 | 性能 | 兼容性 |
|---|---|---|---|
| 窗口函数 | 大部分现代数据库，数据量中大的场景 | 较高，数据库原生优化 | MySQL8+、PostgreSQL、SQL Server等支持 |
| 子查询 | 不支持窗口函数的数据库 | 数据量大时性能较差 | 几乎所有数据库都支持 |
| 自连接 | 数据量很小的场景 | 数据量大时性能极差 | 所有数据库都支持 |
注意事项
- 排序字段如果有重复值，需要根据业务需求选择排序函数，比如允许并列排名可以用RANK，不允许并列用ROW_NUMBER。
- 使用子查询和自连接时，要注意连接条件和筛选条件的逻辑，避免出现数据重复或者漏算的情况。
- 聚合前一定要先完成分组和排序筛选，否则聚合函数会作用于整个分组的所有数据，得不到预期结果。
实际开发中，优先选择窗口函数实现分组排序后的聚合操作，既能简化代码逻辑，也能获得更好的执行性能。如果遇到不支持窗口函数的旧版本数据库，再根据数据量选择合适的替代方案。