如何扩展SQL ROLLUP查询实现Top-Q个人的Top-P事件排序

问题背景 #

现有money_raised表，原始数据示例：

date,   name,   event,      money
1       2       b           1
2       2       b           2
1       1       a           1
3       1       a           1
1       1       c           3
4       2       a           2
3       1       c           1
3       2       a           1
2       1       b           1
6       2       a           1
5       2       c           2

原ROLLUP查询及输出：

SELECT
    COALESCE(name, "Total"),
    COALESCE(event, "Total"),
    SUM(money) AS money
FROM money_raised
GROUP BY name, event WITH ROLLUP

+--------+--------+-----------+
| name   | event  | money     |
+--------+--------+-----------+
| 2      | Total  | 9         |
| 1      | a      | 2         |
| 2      | b      | 3         |
| 1      | c      | 4         |
| Total  | Total  | 16        |
| 1      | Total  | 7         |
| 2      | a      | 4         |
| 1      | b      | 1         |
| 2      | c      | 2         |
+--------+--------+-----------+

需求：获取筹款总额Top Q的个人，并展示每人筹款额Top P的事件，两者均按降序排列。例如Q=2、P=2时，期望输出：

+--------+--------+-----------+
| name   | event  | money     |
+--------+--------+-----------+
| 2      | a      | 4         |
| 2      | b      | 3         |
| 1      | c      | 4         |
| 1      | a      | 2         |
+--------+--------+-----------+

解决方案 #

通过嵌套窗口函数实现，核心是先计算个人总筹款排名，再计算每个个人下事件的筹款排名，最后筛选符合条件的记录：

WITH individual_total AS (
    -- 计算每个个人的总筹款额及排名
    SELECT 
        name,
        SUM(money) AS total_money,
        RANK() OVER(ORDER BY SUM(money) DESC) AS individual_rank
    FROM money_raised
    GROUP BY name
),
event_total AS (
    -- 计算每个个人每个事件的筹款额
    SELECT 
        name,
        event,
        SUM(money) AS event_money
    FROM money_raised
    GROUP BY name, event
),
ranked_events AS (
    -- 关联个人总筹款信息，同时计算每个个人下事件的排名
    SELECT 
        et.name,
        et.event,
        et.event_money AS money,
        it.total_money,
        -- 按个人分组，事件筹款降序排名
        ROW_NUMBER() OVER(PARTITION BY et.name ORDER BY et.event_money DESC) AS event_rank
    FROM event_total et
    JOIN individual_total it ON et.name = it.name
    -- 筛选出Top Q的个人，这里Q=2
    WHERE it.individual_rank <= 2
)
-- 筛选每个个人下Top P的事件，这里P=2
SELECT name, event, money
FROM ranked_events
WHERE event_rank <= 2
ORDER BY total_money DESC, money DESC;

逻辑说明 #

1. individual_total：按个人分组计算总筹款，用RANK()生成个人总筹款的排名，用于筛选Top Q个人。
2. event_total：按个人+事件分组，计算每个事件的筹款总额。
3. ranked_events：关联前两个结果集，用ROW_NUMBER()按个人分组对事件筹款降序排名，同时只保留总筹款排名在前Q的个人记录。
4. 最后筛选事件排名在前P的记录，按个人总筹款、事件筹款降序排列，得到目标结果。

注：MySQL 8.0+、PostgreSQL等支持CTE和窗口函数的数据库均可直接运行该代码；旧版本数据库可将CTE替换为子查询，逻辑保持一致。

内容的提问来源于stack exchange，提问作者DavieRodger