SQL查询产品的频率分布矩阵 [英] SQL query Frequency Distribution matrix for product

问题描述

我想创建一个频率分布矩阵

i want to create a frequency distribution matrix

1.Create a matrix.**Is it possible to get this in separate columns**

  customer1       p1         p2      p3
  customer 2      p2         p3
  customer 3      p2         p3      p1
  customer 4      p2         p1

2. Then I have to count the number of products that come together the most

   For eg  
    p2 and p3 comes together 3 times
    p1 p3   comes 2 times
    p1 p2  comes  2 times

I want to recommend products to customers ,frequency of products that comes together

 select customerId,product,count(*) from sales group by customerId,product

任何人都可以帮我解决这个问题

Can anyone please help me for a solution to this

推荐答案

如果要购买客户购买的成对产品，则可以使用自动联接:

If you want pairs of products that customers purchase, then you can use a self join:

select s1.product, s2.product, count(*) as cnt
from sales s1 join
     sales s2
     on s1.customerId = s2.customerId
where s1.product < s2.product
group by s1.product, s2.product
order by cnt desc;

您可以通过使用更多的联接将其扩展到两个以上的产品.

You can extend this to more than two products by using more joins.

这篇关于SQL查询产品的频率分布矩阵的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！