优化动态表查询最新记录时的分区扫描量

问题背景 #

我们需要从动态表（查询中名为STRUCTURED，示例用SHIPMENTS表）里查询最新记录，规则是：每个SHIPMENT_ID下，先取BATCH_ID最大的批次，再在该批次内取SEQUENCE_ID最大的记录。

示例数据：

按规则要拿到第三条记录（Shipment ID=1、Batch ID=2、Sequence ID=5）。

当前使用的查询定义如下：

WITH MAX_BATCH AS (
    SELECT SHIPMENT_ID,
        MAX(BATCH_ID) AS BATCH_ID
    FROM SHIPMENTS
    GROUP BY SHIPMENT_ID

), MAX_SEQUENCE AS (
    SELECT S.SHIPMENT_ID,
        S.BATCH_ID,
        MAX(C.SEQUENCE_ID) AS SEQUENCE_ID
    FROM SHIPMENTS C
    INNER JOIN MAX_BATCH S
        ON C.BATCH_ID = S.BATCH_ID
            AND C.SHIPMENT_ID= S.SHIPMENT_ID
    GROUP BY S.SHIPMENT_ID , S.BATCH_ID

)
SELECT S.*
FROM SHIPMENTS S
INNER JOIN MAX_SEQUENCE C
    ON C.BATCH_ID = S.BATCH_ID
        AND C.SEQUENCE_ID= S.SEQUENCE_ID
        AND C.SHIPMENT_ID= S.SHIPMENT_ID

这个查询支持增量刷新，但刷新时扫描的分区占比过高，现在考虑用QUALIFY语法优化，想确认是否能提升效率、减少分区扫描量。

方案效果分析 #

使用QUALIFY语法确实能提升查询效率、减少分区扫描量，核心原因如下：

• 原查询需要三次扫描源表（两次聚合操作+一次关联查询），而QUALIFY借助窗口函数，单次扫描就能完成排序和筛选逻辑，直接减少对源表的访问次数。
• 这种紧凑的逻辑更容易让数据库优化器生成高效执行计划，如果源表按SHIPMENT_ID或BATCH_ID设置了分区，能精准定位需要扫描的分区，避免扫描大量无关分区。
• 针对增量刷新场景，QUALIFY配合排序逻辑，能快速识别新增分区内的最新记录，无需重复扫描历史分区的全量数据。

优化后的查询示例 #

用QUALIFY实现的等效查询：

SELECT *
FROM SHIPMENTS
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY SHIPMENT_ID
    ORDER BY BATCH_ID DESC, SEQUENCE_ID DESC
) = 1

该查询通过ROW_NUMBER()窗口函数按SHIPMENT_ID分区，先按BATCH_ID降序、再按SEQUENCE_ID降序排序，筛选出每个分区内的第一条记录，就是符合要求的最新记录。

注意事项 #

• 效率提升的前提是源表有合理的分区键（比如SHIPMENT_ID或BATCH_ID），且数据库支持对窗口函数的分区剪枝优化。
• 不同数据仓库或大数据平台对QUALIFY的优化能力有差异，建议结合平台特性测试执行计划，确认分区扫描量的变化。

内容的提问来源于stack exchange，提问作者Bryan Crystal-Thurston