SQL - 计算客户一起购买的产品的重叠

Question

我正在使用 SQL。我的数据库如下所示:

<表类="s-表"><头>Customer_IDProduct_ID已购买 Product_ID 的数量<正文>1x11是62x152z13是13z13x54x24是1

我想了解客户购买同一产品组合的频率。计算 Times_Bought_Together_%:第一列中 Product_ID_1 的计数除以两者的客户总数。这意味着必须忽略购买数量 Product_ID，因为计算是在#customer_ID 级别而不是#product_ID 级别进行的

在此示例中，组合 xy 已被 4 人中的 3 人购买(Customer_ID = 1,2,3,4)，组合 zx 被 2 人中的 1 人购买(Customer_ID = 2,3)，组合 yz 3 人中有 1 人购买(Customer_ID = 1,3,4)

结果应该是这样的:

<表类="s-表"><头>Product_ID_1Bought_With_Product_ID_2Times_Bought_Together_in_%<正文>x是75%zx50%是z33%

我使用自连接和窗口函数来计算:

   select a.product_id, b.product_id,
          count(*) as times_bought_together,
          count(*) * 1.0 / cnt as ratio
    
    from (select t.*, count(*) 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;

然而，count( * ) 函数不是计算每一行，而是计算我不感兴趣的每一行的购买产品 ID 的总数。我如何更改我的比率函数中的 count( * ) 来计算我的我在找什么？

Answer 1

如果您不想要 select 中的内容列表，然后不包括它:

select a.product_id, b.product_id,
       CAST(count(*) * 1.0 / cnt AS DECIMAL(10,2)) as ratio
from (select t.*, count(*) 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 and 
        a.product_id < b.product_id
group by a.product_id, b.product_id, a.cnt;

注意:您的结果表明您实际上想要 a.product_id < b.product_id .