SQL如何高效实现子组取最大值后跨大组取最小值

需求梳理 #

需要完成两层统计逻辑：

• 子组划分：action取值为1、2的记录归为子组A，action取值为3、4的记录归为子组B，先分别提取每个子组内actiontime（操作时间）最大的完整记录
• 最终筛选：从两个子组的最大时间记录中，选出actiontime最小的记录作为最终结果

测试数据计算示例：

• 子组A最大时间记录：action=1、actionBy=Tom、actiontime=2022-07-15 15:21:00
• 子组B最大时间记录：action=4、actionBy=Mary、actiontime=2022-07-15 14:25:00
• 最终返回actiontime更小的子组B记录

现有代码问题 #

你当前使用CTE+row_number()+UNION的写法存在三个可优化点：

• 两次查询分别过滤子组A、B数据再UNION，会带来两次全表扫描开销，数据量大时性能损耗明显
• 窗口函数分区维度使用actionName，和「1、2合并为A组，3、4合并为B组」的子组规则不匹配，实际会按actionName拆分出远多于2个的分组，不符合需求
• 未实现第二步跨子组取最小时间记录的逻辑，若用自连接实现会进一步增加查询开销

优化后实现 #

仅需单次扫描原表，通过两层窗口函数即可完成全部逻辑，无需UNION、无需自连接，性能最优：

WITH sub_group_latest AS (
    SELECT
        action,
        actionName,
        actiontime,
        actionBy,
        -- 按自定义子组分区，取每个子组内操作时间最新的记录
        ROW_NUMBER() OVER (
            PARTITION BY CASE
                WHEN action IN ('1', '2') THEN 'A'
                WHEN action IN ('3', '4') THEN 'B'
            END
            ORDER BY actiontime DESC
        ) AS sub_rn
    FROM actionDetails
    WHERE action IN ('1', '2', '3', '4')
),
final_result AS (
    SELECT
        action,
        actionName,
        actiontime,
        actionBy,
        -- 从两个子组的最新记录中，取操作时间最早的记录
        ROW_NUMBER() OVER (ORDER BY actiontime ASC) AS final_rn
    FROM sub_group_latest
    WHERE sub_rn = 1
)
SELECT action, actionName, actiontime, actionBy
FROM final_result
WHERE final_rn = 1

注意事项 #

• 如果业务场景下存在同一子组内多条记录actiontime完全相同、并列最大的情况，可以将第一层的ROW_NUMBER()替换为RANK()，避免遗漏符合条件的记录
• 该写法逻辑分层清晰，后续如果需要调整子组划分规则、排序优先级，仅需修改对应位置的判断条件即可，维护成本低

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