如何基于多列层级规则为SQL表中每个buyer_id分配Primary_Store

要实现你需要的Primary_Store分配规则，仅用CASE WHEN无法完成跨行的优先级排序，建议通过窗口函数+分组聚合的组合逻辑来实现，具体步骤如下：

假设你的原始数据表结构为： #

buyer_visits（包含字段：buyer_id, store_name, store_type, visit_date）

完整SQL代码 #

-- 第一步：按买家+门店聚合，计算访问次数和最早访问日期
WITH store_visit_stats AS (
    SELECT
        buyer_id,
        store_name,
        store_type,
        COUNT(*) AS visit_count,
        MIN(visit_date) AS earliest_visit
    FROM buyer_visits
    GROUP BY buyer_id, store_name, store_type
),
-- 第二步：按规则对每个买家的门店进行优先级排名
ranked_stores AS (
    SELECT
        buyer_id,
        store_name,
        ROW_NUMBER() OVER (
            PARTITION BY buyer_id
            ORDER BY
                visit_count DESC, -- 规则1：访问次数从高到低
                -- 规则2：门店类型优先级（Retail > Online > Event）
                CASE store_type
                    WHEN 'Retail' THEN 1
                    WHEN 'Online' THEN 2
                    WHEN 'Event' THEN 3
                END ASC,
                earliest_visit ASC -- 规则3：最早访问日期优先
        ) AS store_rank
    FROM store_visit_stats
)
-- 第三步：筛选每个买家的Top1门店作为Primary_Store
SELECT
    buyer_id AS "Buyer ID",
    store_name AS "Primary_Store"
FROM ranked_stores
WHERE store_rank = 1
ORDER BY buyer_id;

逻辑说明 #

1. 聚合统计：先按buyer_id和store_name分组，计算每个门店的访问次数，同时记录该买家对该门店的最早访问日期。
2. 优先级排名：使用ROW_NUMBER()窗口函数，针对每个买家的门店集合，按照你指定的三个规则依次排序，生成排名。
3. 筛选结果：取每个买家排名为1的门店，即为对应的Primary_Store。

如果你的原始数据已经是聚合后的格式（包含访问次数字段），可以直接跳过第一个CTE，直接使用现有字段进行排名即可。

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