使用分析函数查询类似产品

Question

我正在尝试查找查询，以便匹配类似的客户。

为了简化情况，请考虑以下场景：

我有一张表格，里面有客户的名字和购买的产品。客户名称可以多次购买相同和/或不同的产品。

假设原始数据是：

CustomerName | ProductName
A            | p1
A            | p2
A            | p3
A            | p1
B            | p1
B            | p2
B            | p4
B            | p5
C            | p2

在我要查找的查询中，我想查看至少有一种产品共同购买的所有成对客户，然后让所有产品客户2购买，同时显示客户1和2之间有多少类似产品(不同产品(。根据演示的原始数据，应该是：

客户A和B都购买了p1、p2，因此在显示的每一条记录中，他们的CountSimilarity都有2。客户B还购买了p4、p5，所以它们应该是重要的输出。在对B-A之间，他们有相同的相似性2，但客户A也购买了p3，而B没有，所以这是显著的输出。对C-A和C-B 也会发生同样的情况

CustomerName1 | CustomerName2 | ProductName2 | CountSimilarity
A             | B             | p4           | 2
A             | B             | p5           | 2
B             | A             | p3           | 2
C             | A             | p1           | 1
C             | A             | p3           | 1
C             | B             | p1           | 1
C             | B             | p4           | 1
C             | B             | p5           | 1

在Tim的帮助下，在我之前的问题中，我提出了以下问题，这几乎说明了我需要什么。不幸的是，计数太高了——我需要计算不同的产品名称，这对over partition by不起作用。

select distinct t1.cname, t1.pname, t2.cname, t3.pname pnamet3,
count(*) over (partition by t1.cname, t2.cname) cnt
from MyTable t1 
inner join MyTable t2 on t1.pname = t2.pname and t1.cname != t2.cname
inner join MyTable t3 on t2.cname = t3.cname and t2.pname != t3.pname

如何处理此查询有什么建议吗？

环境是SQL Server。

感谢

Answer 1

需要使用CROSS JOINimo从DISTINCT值构建查询

drop table if exists #dts;
go
create table #dts(
CustomerName        char(1) not null,
ProductName        char(2) not null);
insert #dts(CustomerName, ProductName) values 
('A','p1'),
('A','p2'),
('A','p3'),
('A','p1'),
('B','p1'),
('B','p2'),
('B','p4'),
('B','p5'),
('C','p2');
with
unq_cust_cte as (
select distinct CustomerName 
from #dts),
unq_prod_cte as (
select distinct CustomerName, ProductName 
from #dts),
xjoin_cte as (
select c1.CustomerName cn1, c2.CustomerName cn2
from unq_cust_cte c1
cross join unq_cust_cte c2
where c1.CustomerName<>c2.CustomerName),
similar_count_cte as (
select x.cn1, x.cn2, count(*) similar_count
from xjoin_cte x
join unq_prod_cte p1 on x.cn1=p1.CustomerName
join unq_prod_cte p2 on x.cn2=p2.CustomerName
where p1.ProductName=p2.ProductName
group by x.cn1, x.cn2)
select sc.*, excpt.ProductName2
from similar_count_cte sc
cross apply (select up.ProductName
from unq_prod_cte up
where sc.cn2=up.CustomerName
except
select up.ProductName
from unq_prod_cte up
where sc.cn1=up.CustomerName) excpt(ProductName2)
order by sc.cn1, sc.cn2, excpt.ProductName2;

cn1 cn2 similar_count   ProductName2
A   B   2               p4
A   B   2               p5
B   A   2               p3
C   A   1               p1
C   A   1               p3
C   B   1               p1
C   B   1               p4
C   B   1               p5