Oracle SQL中使用ROLLUP实现分组Top人员及全局汇总的问题

需求 #

从存储任务、人员、耗时的表中，实现两个查询目标：

• 每个任务下，找出耗时最长的人员及其对应耗时
• 添加一行全局汇总，展示所有任务总耗时最高的人员及其总耗时

示例数据 #

task        person  time
----        ------  ----
Admin       Sue     0.5
Admin       Ted     0.25
Meetings    Ted     1.25
Meetings    Sue     0.75

期望输出 #

task        top_person
----        ----------
Admin       Sue (0.5)
Meetings    Ted (1.25)
Overall     Ted (1.5)

原查询的问题 #

原SQL通过ROLLUP(task)分组，能正确获取各任务的Top人员，但全局汇总行错误地取了单条记录的最大耗时（Ted的1.25），没有按人员的全局总耗时（Ted总耗时1.5）计算排名。

原查询代码：

SELECT
    DECODE(GROUPING(task), 0, task, 'Overall') AS task,
    MAX(person || ' (' || time || ')') KEEP (DENSE_RANK FIRST ORDER BY time DESC) AS top_person
FROM time_table
GROUP BY ROLLUP(task)

修改后的查询方案 #

方案一：分步聚合+合并结果 #

先按任务和人员聚合计算耗时，再分别处理任务级和全局级的Top数据，最后合并结果：

WITH task_person_totals AS (
    -- 计算每个任务下每个人员的总耗时（同一任务同一人员有多条时求和）
    SELECT 
        task,
        person,
        SUM(time) AS total_time
    FROM time_table
    GROUP BY task, person
),
task_top AS (
    -- 提取每个任务的耗时最长人员
    SELECT
        task,
        MAX(person || ' (' || total_time || ')') KEEP (DENSE_RANK FIRST ORDER BY total_time DESC) AS top_person
    FROM task_person_totals
    GROUP BY task
),
overall_top AS (
    -- 计算全局总耗时最长的人员
    SELECT
        'Overall' AS task,
        MAX(person || ' (' || SUM(total_time) || ')') KEEP (DENSE_RANK FIRST ORDER BY SUM(total_time) DESC) AS top_person
    FROM task_person_totals
    GROUP BY GROUPING SETS (())
)
-- 合并结果并排序，让汇总行在最后
SELECT * FROM task_top
UNION ALL
SELECT * FROM overall_top
ORDER BY 
    CASE WHEN task = 'Overall' THEN 1 ELSE 0 END,
    task;

方案二：利用ROLLUP和条件判断简化查询 #

借助Oracle的GROUPING()函数区分任务行和汇总行，在排序时动态切换计算逻辑：

WITH task_person_totals AS (
    SELECT 
        task,
        person,
        SUM(time) AS total_time
    FROM time_table
    GROUP BY task, person
)
SELECT
    CASE WHEN GROUPING(task) = 1 THEN 'Overall' ELSE task END AS task,
    MAX(person || ' (' || 
        CASE WHEN GROUPING(task) = 1 THEN SUM(total_time) OVER (PARTITION BY person) ELSE total_time END 
        || ')') 
    KEEP (DENSE_RANK FIRST ORDER BY 
        CASE WHEN GROUPING(task) = 1 THEN SUM(total_time) OVER (PARTITION BY person) ELSE total_time END DESC) 
    AS top_person
FROM task_person_totals
GROUP BY ROLLUP(task);

逻辑说明 #

1. 先通过task_person_totals确保每个任务下的人员耗时是准确的总和，避免单条记录的干扰。
2. 任务级查询直接按任务分组，取该任务下总耗时最高的人员信息。
3. 全局汇总时，计算每个人员所有任务的总耗时，再选出最大值对应的人员；或者通过GROUPING()函数判断汇总行，动态计算人员的全局总耗时用于排序和展示。

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