如何在SQL中遍历产品名称与排名的排列组合并计算占比

针对你需要统计不同产品组合购买顺序占比的需求，不需要手动编写大量CASE语句，通过自连接+CTE分组统计就能实现迭代式的自动计算，以下是两种常见业务场景的实现方案：

场景1：按用户维度统计（用户购买X后是否购买过Y） #

这个场景统计的是购买过X的用户中，后续购买过Y的用户占比，更贴合用户整体购买行为的分析：

WITH user_product AS (
    -- 提取每个用户购买过的所有产品（去重，避免同一用户多次买同一产品干扰统计）
    SELECT DISTINCT CUST_ID, PRODUCT
    FROM purchases
),
user_sequence_pairs AS (
    -- 获取每个用户的有效X→Y购买顺序对（Y在X之后购买）
    SELECT 
        up1.PRODUCT AS prior_product,
        up2.PRODUCT AS next_product,
        up1.CUST_ID
    FROM user_product up1
    JOIN user_product up2 
        ON up1.CUST_ID = up2.CUST_ID
    JOIN purchases p1 
        ON up1.CUST_ID = p1.CUST_ID AND up1.PRODUCT = p1.PRODUCT
    JOIN purchases p2 
        ON up2.CUST_ID = p2.CUST_ID AND up2.PRODUCT = p2.PRODUCT
    WHERE p2.RANK > p1.RANK
    GROUP BY up1.CUST_ID, up1.PRODUCT, up2.PRODUCT -- 同一用户的X→Y只算一次
),
total_prior_users AS (
    -- 统计每个产品X的购买用户总数
    SELECT PRODUCT AS prior_product, COUNT(DISTINCT CUST_ID) AS total_users
    FROM user_product
    GROUP BY PRODUCT
)
SELECT 
    usp.prior_product,
    usp.next_product,
    COUNT(DISTINCT usp.CUST_ID) AS users_with_sequence,
    tpu.total_users,
    ROUND(COUNT(DISTINCT usp.CUST_ID) * 100.0 / tpu.total_users, 2) AS sequence_percentage
FROM user_sequence_pairs usp
JOIN total_prior_users tpu 
    ON usp.prior_product = tpu.prior_product
GROUP BY usp.prior_product, usp.next_product, tpu.total_users
ORDER BY usp.prior_product, usp.next_product;

示例结果（对应你的测试数据）： #

场景2：按订单维度统计（X订单之后是否出现Y订单） #

如果需要统计所有X订单中，后续出现Y订单的订单占比，可以用以下方案：

WITH order_pairs AS (
    -- 获取所有前置订单与后置订单的关联对（Y订单在X订单之后）
    SELECT 
        p1.PRODUCT AS prior_product,
        p2.PRODUCT AS next_product,
        p1.CUST_ID,
        p1.RANK AS prior_rank
    FROM purchases p1
    JOIN purchases p2 
        ON p1.CUST_ID = p2.CUST_ID AND p2.RANK > p1.RANK
),
total_prior_orders AS (
    -- 统计每个产品X的总订单数
    SELECT PRODUCT AS prior_product, COUNT(*) AS total_orders
    FROM purchases
    GROUP BY PRODUCT
)
SELECT 
    op.prior_product,
    op.next_product,
    COUNT(DISTINCT op.CUST_ID, op.prior_rank) AS orders_with_sequence,
    tpo.total_orders,
    ROUND(COUNT(DISTINCT op.CUST_ID, op.prior_rank) * 100.0 / tpo.total_orders, 2) AS sequence_percentage
FROM order_pairs op
JOIN total_prior_orders tpo 
    ON op.prior_product = tpo.prior_product
GROUP BY op.prior_product, op.next_product, tpo.total_orders
ORDER BY op.prior_product, op.next_product;

关键说明 #

1. 迭代式特性：不管你的产品有多少种，SQL会自动通过自连接生成所有可能的X→Y组合，不需要手动添加CASE语句枚举组合。
2. 去重处理：两种方案都做了去重，避免同一用户/订单的重复统计干扰结果。
3. 方言适配：如果使用MySQL，COUNT(DISTINCT CUST_ID, prior_rank)需要替换为COUNT(DISTINCT CONCAT(CUST_ID, '-', prior_rank))，其他主流SQL（PostgreSQL、SQL Server、BigQuery等）支持多列DISTINCT。
4. 排除重复产品：如果不需要统计同一产品的重复购买（如X=Y的情况），只需在WHERE子句中添加prior_product != next_product即可。

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