SQL实现：按ID优先选择TYPE A END_非空的最新记录

现有MY_TABLE表结构及数据 #

获取每个ID、TYPE最新记录的SQL #

已通过以下SQL获取每个ID、TYPE的最新记录：

SELECT *
FROM MY_TABLE
QUALIFY ROW_NUMBER() OVER (PARTITION BY ID, TYPE ORDER BY SOURCE_DATE DESC) = 1;

上述SQL执行结果 #

执行后得到如下结果：

需求说明 #

需要进一步处理得到最终结果，规则如下：

• 按ID分组
• 若该ID下存在TYPE A且其END_不为NULL，则保留该TYPE A记录
• 否则保留TYPE B记录
• 若ID下仅一条记录则直接保留
• 最终结果不需要LOADED列

期望的最终结果：

尝试过的SQL（存在问题） #

曾尝试以下SQL，但逻辑复杂，且无法处理ID 5这类TYPE A和B的END_均非空时优先选择TYPE A的情况：

WITH GET_LATEST_CHANGES AS (SELECT *
FROM MY_TABLE
QUALIFY ROW_NUMBER() OVER (PARTITION BY ID, TYPE ORDER BY SOURCE_DATE DESC) = 1
)
SELECT ID, TYPE, START_, END_, SOURCE_DATE,
     CASE 
        WHEN TYPE <> LAG(TYPE, 1) OVER (PARTITION BY ID ORDER BY SOURCE_DATE) AND TYPE = 'A' AND END_ IS NULL THEN COALESCE(LAG(END_, 1) OVER (PARTITION BY ID ORDER BY SOURCE_DATE), END_)
        WHEN TYPE <> LAG(TYPE, 1) OVER (PARTITION BY ID ORDER BY SOURCE_DATE) AND TYPE = 'A' AND END_ IS NOT NULL THEN COALESCE(END_, LAG(END_, 1) OVER (PARTITION BY ID ORDER BY SOURCE_DATE))
        ELSE END_
     END AS END_2
FROM GET_LATEST_CHANGES;

简洁解决方案 #

基于已有的获取最新记录的CTE，通过给每个ID下的记录设置优先级排序，即可轻松实现需求：

WITH GET_LATEST_CHANGES AS (
    SELECT *
    FROM MY_TABLE
    QUALIFY ROW_NUMBER() OVER (PARTITION BY ID, TYPE ORDER BY SOURCE_DATE DESC) = 1
)
SELECT ID, TYPE, START_, END_, SOURCE_DATE
FROM (
    SELECT *,
           -- 按需求设置优先级：TYPE A且END_非空排第一，TYPE B排第二，其余排第三
           ROW_NUMBER() OVER (PARTITION BY ID 
                              ORDER BY CASE WHEN TYPE = 'A' AND END_ IS NOT NULL THEN 1
                                           WHEN TYPE = 'B' THEN 2
                                           ELSE 3 END) AS rn
    FROM GET_LATEST_CHANGES
) t
WHERE rn = 1;

逻辑说明 #

1. 先通过GET_LATEST_CHANGES CTE获取每个ID+TYPE的最新记录
2. 对每个ID下的记录，根据需求设置排序优先级：
   • 优先级1：TYPE为A且END_不为空
   • 优先级2：TYPE为B
   • 优先级3：其他情况
3. 给每个ID下的记录按上述规则分配行号rn，取rn=1的记录即为所需结果
4. 最终查询时排除LOADED列，直接返回目标字段

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