如何用SQL基于Next Best Offer模型为客户排名适配的合格产品（限2个）

核心思路 #

先把宽格式的适配优先级表转换为窄格式（客户-产品-优先级数值），再与合格产品表关联，仅保留客户的合格产品，最后通过窗口函数按优先级排序并筛选每个客户的前2个产品。

具体SQL实现 #

假设两张表的名称分别为：

• 合格产品表：customer_eligible_products（列：customer, product）
• 适配优先级表：customer_product_preferences（列：customer, top_product, second_product, third_product）

WITH preference_ranks AS (
    -- 把宽表转成窄表，给每个适配产品分配优先级数值
    SELECT customer, top_product AS product, 1 AS priority
    FROM customer_product_preferences
    UNION ALL
    SELECT customer, second_product AS product, 2 AS priority
    FROM customer_product_preferences
    UNION ALL
    SELECT customer, third_product AS product, 3 AS priority
    FROM customer_product_preferences
),
ranked_eligible_products AS (
    -- 关联合格产品表，按优先级排序并生成排名
    SELECT
        cep.customer,
        cep.product,
        ROW_NUMBER() OVER (
            PARTITION BY cep.customer
            ORDER BY pr.priority ASC
        ) AS `排名`
    FROM customer_eligible_products cep
    JOIN preference_ranks pr
        ON cep.customer = pr.customer
        AND cep.product = pr.product
)
-- 筛选每个客户的前2个产品
SELECT customer, product, `排名`
FROM ranked_eligible_products
WHERE `排名` <= 2
ORDER BY customer, `排名`;

代码说明 #

1. preference_ranks CTE：将原适配表的三列优先级产品拆分为行，同时给每个产品分配对应的优先级数值（1=最高优先级），方便后续排序。
2. ranked_eligible_products CTE：将合格产品表与转换后的优先级表关联，仅保留客户同时拥有合格资格和适配优先级的产品；使用ROW_NUMBER()窗口函数，按客户分组，按优先级升序生成排名。
3. 最后筛选排名≤2的记录，得到每个客户需要推送的2个产品。

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