SQL按booking分组优先取doc_type_id=2最新记录 高性能查询方案咨询

bookings表 #

docs表 #

按booking_id分组匹配规则：

• 分组内存在doc_type_id=2的记录时，取该分组下doc_type_id=2的最大doc_id（最新）对应的记录
• 分组内不存在doc_type_id=2的记录时，取该分组下doc_type_id=1的最大doc_id（最新）对应的记录

预期输出：

针对超大规模数据场景，优先采用支持谓词下推、可利用覆盖索引的窗口函数方案，避免多次关联扫描：

SELECT booking_id, doc_id
FROM (
    SELECT 
        d.booking_id,
        d.doc_id,
        -- 按优先级排序：doc_type_id=2优先级高于1，同类型下doc_id倒序取最新
        ROW_NUMBER() OVER (
            PARTITION BY d.booking_id 
            ORDER BY d.doc_type_id DESC, d.doc_id DESC
        ) AS rn
    FROM docs d
    -- 若需要过滤无关联文档的booking，保留下面的JOIN，否则可省略
    INNER JOIN bookings b ON d.booking_id = b.booking_id
) t
WHERE rn = 1;

• 只需在docs表建立联合索引 (booking_id, doc_type_id DESC, doc_id DESC)，即可让窗口函数直接走索引扫描，无需额外排序、无需回表，性能远高于子查询关联方案
• 单次扫描即可完成所有分组计算，时间复杂度为O(N)，适合PB级以上大数据量场景
• 兼容绝大多数主流数据库（MySQL 8.0+/PostgreSQL/Oracle/Spark SQL/Flink SQL等）

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