SQL计算客户共同购买产品组合重叠占比的问题求解

核心改动点 #

原有代码存在两处统计维度错误：

• 共同购买次数统计的是订单记录数，没有按客户ID去重
• 分母的产品总购买用户数统计的是订单记录数，不是去重客户数

修改后可直接运行的代码 #

select 
  a.product_id as Product_ID_1,
  b.product_id as Bought_With_Product_ID_2,
  round(count(distinct a.customer_id) * 100.0 / cnt, 2) as `Times_Bought_Together_in_%`
from (
  select 
    t.*, 
    count(distinct customer_id) over (partition by product_id) as cnt
  from t
) a 
join t b
  on b.customer_id = a.customer_id 
  and b.product_id != a.product_id 
group by a.product_id, b.product_id, a.cnt;

改动说明 #

1. 分子部分将count(*)替换为count(distinct a.customer_id)：同一个客户多次购买同一组产品时仅统计1次，准确得到同时购买两个产品的去重客户数
2. 子查询中count(*) over (partition by product_id)替换为count(distinct customer_id) over (partition by product_id)：得到购买对应Product_ID_1的去重总客户数，作为占比计算的分母
3. 调整了字段别名匹配输出要求，新增round函数可按需保留百分比小数位，不需要可直接删除

兼容低版本数据库的替代方案 #

如果你的数据库不支持窗口函数中使用distinct（比如老版本MySQL），可以用预聚合方式计算每个产品的总购买客户数：

with product_cust_total as (
  select product_id, count(distinct customer_id) as total_cust
  from t
  group by product_id
)
select 
  a.product_id as Product_ID_1,
  b.product_id as Bought_With_Product_ID_2,
  round(count(distinct a.customer_id) * 100.0 / p.total_cust, 2) as `Times_Bought_Together_in_%`
from t a
join t b
  on b.customer_id = a.customer_id 
  and b.product_id != a.product_id
join product_cust_total p
  on a.product_id = p.product_id
group by a.product_id, b.product_id, p.total_cust;

内容的提问来源于stack exchange，提问作者Es-Dot