如何高效实现分组后对子分组的MAX()嵌套聚合查询？

原始数据表 #

需求说明 #

针对每类运动，筛选出该运动的最新日期，并在该日期下选取最大的每日尝试次数，最终输出如下：

常规实现方式 #

SELECT s.sport, s.date, MAX(s.daily_attempt)
FROM table s
INNER JOIN
(
SELECT t.sport, MAX(t.date) max_date
FROM table t
GROUP BY t.sport
) u ON s.sport = u.sport AND s.date = u.max_date
GROUP BY s.sport, s.date

由于数据量极大，且部分场景需要处理多层分组嵌套聚合，希望找到更简洁或高效的实现方式。之前尝试过以下写法，但逻辑并不等价：

SELECT t.sport, t.date, t.daily_attempt
FROM table t
WHERE EXISTS
(
   SELECT 1 FROM table s WHERE s.sport = t.sport
   GROUP BY s.sport 
   HAVING t.daily_attempt=MAX(s.daily_attempt) AND t.date = MAX(s.date)
)

替代实现方案 #

方案1：窗口函数（ROW_NUMBER/RANK） #

这是处理这类分组取最值场景的最优方案之一，无需多表关联，一次扫描即可完成计算，写法简洁且扩展性强：

WITH ranked_data AS (
    SELECT 
        sport,
        date,
        daily_attempt,
        ROW_NUMBER() OVER (
            PARTITION BY sport 
            ORDER BY date DESC, daily_attempt DESC
        ) AS rn
    FROM table
)
SELECT sport, date, daily_attempt
FROM ranked_data
WHERE rn = 1;

• 逻辑：按sport分组，先按date降序（最新日期优先），再按daily_attempt降序（同日期下最大尝试次数优先），取每组的第一条数据。
• 优势：对于大数据量，窗口函数的执行效率通常优于子查询关联，且易于扩展到多层分组（比如再按其他维度分组）的场景。

方案2：LATERAL JOIN（适用于PostgreSQL、SQL Server等） #

通过LATERAL JOIN可以为每个运动单独计算最新日期的最大尝试次数，避免不必要的全局聚合：

SELECT
    t.sport,
    latest.date,
    latest.max_attempt
FROM (
    SELECT sport, MAX(date) AS max_date
    FROM table
    GROUP BY sport
) t
JOIN LATERAL (
    SELECT date, MAX(daily_attempt) AS max_attempt
    FROM table
    WHERE sport = t.sport AND date = t.max_date
    GROUP BY date
) latest ON true;

• 逻辑：先获取每个运动的最新日期，再通过LATERAL JOIN关联该日期下的最大尝试次数。
• 优势：在数据库支持的情况下，这种写法的执行计划更高效，尤其是当表有(sport, date)复合索引时。

方案3：简化版子查询写法 #

如果不想用窗口函数或LATERAL JOIN，可以简化原有的关联逻辑：

SELECT
    sport,
    max_date,
    (
        SELECT MAX(daily_attempt)
        FROM table
        WHERE sport = t.sport AND date = t.max_date
    ) AS daily_attempt
FROM (
    SELECT sport, MAX(date) AS max_date
    FROM table
    GROUP BY sport
) t;

• 逻辑：先提取每个运动的最新日期，再通过关联子查询获取对应日期的最大尝试次数。
• 注意：确保表上有(sport, date)索引，否则子查询可能会重复扫描全表，影响性能。

关于GROUP BY CUBE/ROLLUP #

CUBE和ROLLUP的作用是生成多级聚合的汇总数据（比如同时按sport、date分组的各种组合），并不适合当前需求——它们会生成多余的聚合结果，无法直接得到“每个运动最新日期的最大尝试次数”，因此不推荐使用。

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