Snowflake中修改QUALIFY子句实现consumer_id下双维度去重

根据你的需求，要在原查询基础上同时保证consumer_id下business_id不重复，可根据业务场景选择以下两种简便修改方式：

场景1：business_id与store_id一一对应 #

如果每个store_id属于唯一的business_id，且每个business_id仅对应一个store_id，只需修改PARTITION BY的分组字段，将business_id加入（或直接替换store_id），即可同时满足store_id和business_id在consumer_id下的唯一性：

SELECT
    t.consumer_id
    , t.business_id
    , t.store_id
    , t.campaign_id
FROM campaigns_mini AS t
QUALIFY ROW_NUMBER() OVER (PARTITION BY t.consumer_id, t.business_id ORDER BY t.campaign_id) = 1

场景2：business_id与store_id非一一对应 #

如果一个business_id对应多个store_id，且需要同时保证consumer_id下store_id和business_id都唯一，可通过两层QUALIFY实现：先确保store_id唯一，再在结果中确保business_id唯一（保留每个business_id对应的第一条记录）：

SELECT
    consumer_id
    , business_id
    , store_id
    , campaign_id
FROM (
    SELECT
        t.consumer_id
        , t.business_id
        , t.store_id
        , t.campaign_id
    FROM campaigns_mini AS t
    QUALIFY ROW_NUMBER() OVER (PARTITION BY t.consumer_id, t.store_id ORDER BY t.campaign_id) = 1
)
QUALIFY ROW_NUMBER() OVER (PARTITION BY consumer_id, business_id ORDER BY campaign_id) = 1

说明 #

• 两种方案均通过ROW_NUMBER()函数按campaign_id排序取第一条记录，确保保留的是最早（若需最新记录，可将排序规则改为ORDER BY t.campaign_id DESC）的关联记录。
• 若需优先保证business_id唯一再处理store_id，可调换两层QUALIFY的顺序。

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