Snowflake中按优先级筛选分组后Pipeline状态的SQL查询

需求说明 #

现有数据表如下：

要求按pipeline分组后，按照NoDelta（最高）> NoDataFound > NoFiles > Failed的优先级选择对应状态：若分组内存在高优先级状态，优先选取该状态，否则依次判断下一级。预期结果：

实现方案 #

可以通过给状态分配优先级权重，再按分组筛选出权重最高（或最低，取决于权重分配逻辑）的状态，以下提供两种可行写法：

方法一：窗口函数直接筛选 #

利用FIRST_VALUE结合窗口排序，直接提取分组内优先级最高的状态：

SELECT 
    pipeline,
    status
FROM (
    SELECT 
        pipeline,
        status,
        ROW_NUMBER() OVER (
            PARTITION BY pipeline 
            ORDER BY 
                CASE status 
                    WHEN 'NoDelta' THEN 1 
                    WHEN 'NoDataFound' THEN 2 
                    WHEN 'NoFiles' THEN 3 
                    WHEN 'Failed' THEN 4 
                END ASC
        ) AS rn
    FROM your_table_name
)
WHERE rn = 1;

方法二：CTE映射权重后聚合 #

先通过CTE给每个状态分配优先级权重，再按分组取最小权重对应的状态：

WITH status_ranked AS (
    SELECT 
        pipeline,
        status,
        CASE status 
            WHEN 'NoDelta' THEN 1 
            WHEN 'NoDataFound' THEN 2 
            WHEN 'NoFiles' THEN 3 
            WHEN 'Failed' THEN 4 
        END AS rank_num
    FROM your_table_name
)
SELECT 
    pipeline,
    CASE MIN(rank_num)
        WHEN 1 THEN 'NoDelta'
        WHEN 2 THEN 'NoDataFound'
        WHEN 3 THEN 'NoFiles'
        WHEN 4 THEN 'Failed'
    END AS status
FROM status_ranked
GROUP BY pipeline;

两种写法均能满足需求，可根据个人习惯选择。注意将代码中的your_table_name替换为实际表名。

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