如何使用ROW_NUMBER() OVER PARTITION BY优先保留非空值的最新分组记录

可以通过在ROW_NUMBER()窗口函数的ORDER BY子句中补充优先级排序规则来实现需求。核心思路是在保证最新日期优先的基础上，让START_VAL和END_VAL均不为NULL的记录在同日期内排在最前面。

修改后的查询语句 #

SELECT *
FROM MY_TABLE
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY ID 
    ORDER BY 
        LOAD_DATE DESC,
        -- 双值非空记录优先的排序规则
        CASE WHEN START_VAL IS NOT NULL AND END_VAL IS NOT NULL THEN 1 ELSE 0 END DESC
) = 1;

逻辑说明 #

• PARTITION BY ID：按ID分组，单独处理每个ID的记录集
• LOAD_DATE DESC：确保每个ID下最新日期的记录排在最靠前的位置
• CASE WHEN ... THEN 1 ELSE 0 END DESC：在同一日期的记录中，给双值非空的标记为1，其余为0，降序排序后让双值非空的记录排在同组最前面，这样ROW_NUMBER()会给它分配序号1，最终被筛选出来

执行结果验证 #

运行该查询后：

• ID=1会选中2023-06-03日那条START_VAL=5、END_VAL=7的记录
• ID=2会选中2023-06-10日的唯一记录（符合最新日期要求）

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