如何在Snowflake SQL中按多条件筛选单用户单门户记录

需求说明 #

• 每个门户下的每个用户仅保留1条记录，筛选优先级为：Prod（1）> QA（2）> Stage（3）> Max（4）
• 若无Prod则选QA，以此类推，确保每个用户每个门户仅保留1条符合优先级的记录
• 特殊情况：用户ID3543591同时属于AAA和BBB门户，AAA门户选Prod记录，BBB门户因无更高优先级数据，选Max记录

SQL实现方案 #

WITH ranked_records AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY user, portal
            ORDER BY 
                CASE source 
                    WHEN 'Prod' THEN 1
                    WHEN 'QA' THEN 2
                    WHEN 'Stage' THEN 3
                    WHEN 'Max' THEN 4
                    ELSE 5
                END ASC
        ) AS rn
    FROM your_original_table_name
)
SELECT 
    Date, user, portal, country, state, source
FROM ranked_records
WHERE rn = 1
ORDER BY portal, user;

代码说明 #

• 使用ROW_NUMBER()窗口函数，按user和portal分组（PARTITION BY）
• 通过CASE语句定义source的优先级排序规则，优先级高的记录排名靠前
• 筛选出每组中排名为1的记录，即为每个用户每个门户的最优匹配记录

原始表格 #

预期输出 #

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