如何编写SQL查询计算销售漏斗各阶段用户转化率？

你的SQL核心问题是按purchase_status分组后，仅能统计当前状态的单条记录，无法关联用户的完整行为路径，同时统计维度混淆了「记录数」和「用户数」，导致结果不符合预期。

错误原因拆解 #

1. 分组逻辑偏差：同一用户可能存在多条不同状态的记录（比如user1有viewed和completed两条记录），按purchase_status分组会割裂用户的行为链，无法统计用户跨状态的转化情况。
2. 统计维度错误：SUM(CASE...)仅统计当前分组内的记录是否匹配状态，而非统计用户是否具备该状态；COUNT(*)统计的是当前状态的记录条数，而非用户数量或总用户数。

修正后的SQL #

我们先按用户维度预处理完整行为数据，再针对每个漏斗阶段做精准统计：

WITH user_behavior AS (
    -- 统计每个用户的全量行为状态，确保每个用户只被计算一次
    SELECT
        user_id,
        MAX(CASE WHEN purchase_status = 'viewed' THEN 1 ELSE 0 END) AS has_viewed,
        MAX(CASE WHEN purchase_status = 'added_to_cart' THEN 1 ELSE 0 END) AS has_added,
        MAX(CASE WHEN purchase_status = 'completed' THEN 1 ELSE 0 END) AS has_completed
    FROM Sales
    GROUP BY user_id
),
stage_stats AS (
    -- 统计浏览阶段数据：所有有浏览行为的用户
    SELECT
        'viewed' AS purchase_status,
        COUNT(*) AS viewed_users,
        0 AS added_to_cart_users,
        0 AS completed_users,
        COUNT(*) AS total_users
    FROM user_behavior
    WHERE has_viewed = 1

    UNION ALL

    -- 统计加入购物车阶段数据：所有有加购行为的用户
    SELECT
        'added_to_cart' AS purchase_status,
        0 AS viewed_users,
        COUNT(*) AS added_to_cart_users,
        0 AS completed_users,
        COUNT(*) AS total_users
    FROM user_behavior
    WHERE has_added = 1

    UNION ALL

    -- 统计完成购买阶段数据：所有完成购买的用户，及其中有浏览行为的用户数
    SELECT
        'completed' AS purchase_status,
        SUM(has_viewed) AS viewed_users,
        0 AS added_to_cart_users,
        COUNT(*) AS completed_users,
        (SELECT COUNT(*) FROM user_behavior) AS total_users
    FROM user_behavior
    WHERE has_completed = 1
)
SELECT *
FROM stage_stats
ORDER BY
    CASE purchase_status
        WHEN 'viewed' THEN 1
        WHEN 'added_to_cart' THEN 2
        WHEN 'completed' THEN 3
    END;

结果匹配说明 #

执行上述SQL后，将完全匹配你期望的结果：

核心优化点 #

1. 用户维度聚合：通过GROUP BY user_id将同一用户的多条记录合并为一条，完整保留用户的行为路径信息。
2. 阶段精准统计：针对每个漏斗阶段单独统计，完成阶段额外计算「从浏览转化到完成」的用户数，贴合漏斗转化分析需求。
3. 顺序控制：通过CASE语句强制按漏斗阶段顺序排序，保证结果的可读性。

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