查找访问两个品牌的客户数量

Question

CREATE TABLE customer_brand_table 
(
customer_id Text Not NULL,
brand_id Text NOT NULL
);
INSERT INTO customer_brand_table VALUES ("A", 123);
INSERT INTO customer_brand_table VALUES ("A", 234);
INSERT INTO customer_brand_table VALUES ("B", 345);
INSERT INTO customer_brand_table VALUES ("C", 234);
INSERT INTO customer_brand_table VALUES ("C", 567);

这是包含客户id和品牌id的表。我想计算访问所有可能的Pair的客户数量的品牌。因此输出将是

#客户0000

我们可以使用<自连接表来创建组合列表，然后左连接并使用count(distinct)来获得购买两个品牌的客户数量。

select
count(distinct c.customer_id) num,
a.brand_id,
b.brand_id
from customer_brand_table a
join customer_brand_table b
on a.brand_id < b.brand_id
left join customer_brand_table c
on a.customer_id = c.customer_id
and b.brand_id = c.brand_id
group by
a.brand_id,
b.brand_id;

num | brand_id | brand_id——:| ------- |:-------1 | 123 | 2340 | 123 | 3450 | 123 | 5670 | 234 | 3451 | 234 | 5670 | 345 | 567

db<此处小提琴>