SQL计算客户分流率（Cannibalization Rate）结果异常排查

核心问题所在 #

你的SQL结果全为1的原因很明确：

1. JOIN条件过滤了关键数据：你用INNER JOIN且强制t1.CustomerID = t2.CustomerID，最终结果只保留了同时访问主门店和竞争门店的客户，完全丢失了仅访问主门店的客户数据。
2. 分流率计算逻辑错误：分母用的COUNT(DISTINCT t1.CustomerID, t2.CustomerID)，因JOIN条件已保证t1.CustomerID = t2.CustomerID，这个计数和分子OverlapCustomers数值完全一致，比值自然为1。
3. 冗余判断：IF(t1.CustomerID = t2.CustomerID, ...)完全没必要，JOIN条件已经确保两者相等。

另外，你当前的StoreCustomers字段统计的不是主门店总客户数，只是主门店中同时去过竞争门店的客户数，不符合你定义的分流率公式。

正确计算思路 #

按照你的公式，需要先拿到两个核心数据：

• 主门店在指定时间段的总唯一客户数（无论是否去过其他门店）
• 每个竞争门店对应的重叠客户数（既去主门店又去该竞争门店的唯一客户数）

再通过「重叠客户数 / 主门店总客户数 * 100」计算分流率。

修正后的SQL代码 #

WITH store_total AS (
    -- 第一步：单独计算指定时间段内主门店的总唯一客户数
    SELECT 
        COUNT(DISTINCT CustomerID) AS TotalStoreCustomers
    FROM My_Table
    WHERE 
        Year = <year>
        AND Quarter = <quarter>
        AND StoreNum = <store_num>
),
overlap_customers AS (
    -- 第二步：统计每个竞争门店对应的重叠客户及细分数据
    SELECT 
        t2.StoreNum AS CompetitorStore,
        COUNT(DISTINCT t1.CustomerID) AS OverlapCustomers,
        COUNT(DISTINCT CASE WHEN t1.ReturnStatus = 'RETURNED' THEN t1.CustomerID END) AS OverlapReturnedCustomers,
        COUNT(DISTINCT CASE WHEN t1.ActiveStatus = 'ACTIVE' THEN t1.CustomerID END) AS OverlapActiveCustomers,
        COUNT(DISTINCT CASE WHEN t1.ReturnStatus = 'RETURNED' THEN t1.CustomerID END) AS StoreReturnedCustomers
    FROM My_Table t1
    INNER JOIN My_Table t2 
        ON t1.CustomerID = t2.CustomerID
        AND t1.Year = t2.Year
        AND t1.Quarter = t2.Quarter
        AND t1.StoreNum <> t2.StoreNum
    WHERE 
        t1.Year = <year>
        AND t1.Quarter = <quarter>
        AND t1.StoreNum = <store_num>
    GROUP BY t2.StoreNum
)
-- 第三步：关联总客户数与重叠数据，计算最终分流率
SELECT 
    <store_num> AS StoreNum,
    oc.CompetitorStore,
    <year> AS Year,
    <quarter> AS Quarter,
    st.TotalStoreCustomers,
    oc.OverlapCustomers,
    oc.OverlapReturnedCustomers,
    oc.OverlapActiveCustomers,
    oc.StoreReturnedCustomers,
    -- 处理分母为0的边界情况，保留两位小数
    CASE WHEN st.TotalStoreCustomers > 0 
         THEN ROUND((oc.OverlapCustomers / st.TotalStoreCustomers) * 100, 2) 
         ELSE 0 
    END AS CannibalizationRate
FROM overlap_customers oc
CROSS JOIN store_total st

关于“聚合列”的解释 #

你朋友说的「聚合列」，本质是指先通过**聚合函数（比如COUNT(DISTINCT)）**单独计算主门店的总客户数（这就是一个聚合结果），再把这个聚合值和其他数据关联使用。而你之前的写法直接在JOIN后的结果里计算，因JOIN已经过滤掉仅访问主门店的客户，根本拿不到总客户数，所以必须先单独聚合出这个关键值。

额外优化点 #

• 用标准SQL的CASE WHEN替代IF，兼容性更强
• 用ROUND函数控制分流率小数位数，结果更直观
• 用CTE（公共表表达式）拆分逻辑，代码可读性更强

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