英文:
Count product combinations which are under same customer
问题
我有这样的表格：
| Customer_nr | Product |
|---|---|
| 11111 | Table |
| 22222 | Sofa |
| 333333 | Table |
| 444444 | Closet |
| 11111 | Bed |
我如何找到由同一客户购买的产品组合的数量？
输出应该如下：
| Product A | Product B | 组合计数 |
|---|---|---|
| Table | Bed | 245 |
这意味着有 245 位客户购买了桌子和床。
英文:
I have table like this:
| Customer_nr | Product |
|---|---|
| 11111 | Table |
| 22222 | Sofa |
| 333333 | Table |
| 444444 | Closet |
| 11111 | Bed |
How can I find the number of product combination that a bought by same customer?
Out come should be like that:
| Product A | Product B | Count of combination |
|---|---|---|
| Table | Bed | 245 |
It mean that 245 customers bought table and bed
答案1
得分: 2
执行自连接并按如下方式聚合：
select t.Product Product_A, d.Product Product_B,
count(*) com_cnt
from table_name t join table_name d
on d.Customer_nr = t.Customer_nr and
d.Product > t.Product
group by t.Product, d.Product
英文:
Do a self-join and aggregate as the following:
select t.Product Product_A, d.Product Product_B,
count(*) com_cnt
from table_name t join table_name d
on d.Customer_nr = t.Customer_nr and
d.Product > t.Product
group by t.Product, d.Product
答案2
得分: 0
如果您想统计特定组合，请使用 group by 和 having：
首先，识别购买了这些产品的客户：
select customer_nr
from mytable
where product in ('Table', 'Bed')
group by customer_nr
having count(distinct product) = 2
然后统计有多少客户：
select 'Table' as 'product A', 'Bed' as 'product B', count(distinct customer_nr)
from (
select customer_nr
from mytable
where product in ('Table', 'Bed')
group by customer_nr
having count(distinct product) = 2
) as s
英文:
If you want to count a specific combination, then use group by and having :
First, identify the customers who purchased those products:
select customer_nr
from mytable
where product in ('Table', 'Bed')
group by customer_nr
having count(distinct product) = 2
Then count how many customers there are:
select 'Table' as 'product A', 'Bed' as 'product B', count(distinct customer_nr)
from (
select customer_nr
from mytable
where product in ('Table', 'Bed')
group by customer_nr
having count(distinct product) = 2
) as s
Demo here on mysql
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论