鍍金池/ 問答/數(shù)據(jù)庫/ mysql：查詢所有產(chǎn)品組合的用戶人數(shù)

mysql：查詢所有產(chǎn)品組合的用戶人數(shù)

有一張用戶購買產(chǎn)品的增量表order_list，字段為id（主鍵自增不用管），product_id（產(chǎn)品ID，屬性值為A、B、C、D），user_id（用戶ID）。
如下表：

clipboard.png

想要查詢有A/B/C/D/AB/AC/AD/BC/BD/CD/ABC... .../ABCD的一共4+6+4+1=15種組合的分別有多少個？
例如：user_id為1的A、B、D都買過，那么在A/B/D/AB/BD/ABD這幾個組合里都+1。
應(yīng)該是個怎么查詢思路？

我只想到按最大的產(chǎn)品組合的用戶數(shù)可以這么查：
clipboard.png

#附創(chuàng)建表和插入數(shù)據(jù)
CREATE TABLE `order_list` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `product_id` char(10) DEFAULT NULL,
  `user_id` int(5) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

INSERT INTO order_list(product_id,user_id) VALUES('A',1),('A',1),('B',1),('C',2),('A',2),('A',3),('B',2),('C',2),('D',1);
回答
編輯回答
別硬撐

CUBE的用法和Postgres數(shù)組了解一下。

with usr as (
    select user_id, array_agg(distinct product_id) as prds
    from (values('A',1),('A',1),('B',1),('C',2),('A',2),('A',3),('B',2),
        ('C',2),('D',1)) as order_list(product_id, user_id)
    group by user_id),
cmbs as ( -- combinations
    select array_remove(array[a,b,c,d], null) as cmb
    from (values('A', 'B', 'C', 'D')) as prd(a,b,c,d)
    group by cube (a,b,c,d))
select
    array_to_string(cmb, ' ') as prod,
    array_agg(user_id) as users,
    count(user_id) as tally
from cmbs inner join usr on cmb <@ prds
where array_length(cmb, 1) > 0
group by cmb
prod users tally
A {1,2,3} 3
A B {1,2} 2
A B C {2} 1
A B D {1} 1
A C {2} 1
A D {1} 1
B {1,2} 2
B C {2} 1
B D {1} 1
C {2} 1
D {1} 1
2018年1月31日 20:14
編輯回答
不二心

首先要生成一個各種組合列表，逗號分隔product(pids varchar(8))
第一種統(tǒng)計每個用戶買過哪些產(chǎn)品，然后產(chǎn)品在pids中出現(xiàn)的次數(shù)累計

select sum(case when find_in_set(b.pid,a.pids) then 1 else 0 end),a.pids 
from product a,
    (select product_id pid from order_list group by user_id,product_id) b 
group by a.pids order by null;

第二種，統(tǒng)計每種商品被幾個人買過，再在組合中加和

select sum(case when find_in_set(b.pid,a.pids) then b.cuid else 0 end),a.pids 
from product a,
    (select count(distinct user_id) cuid,product_id pid from order_list group by product_id) b 
group by a.pids order by null;
2017年12月29日 18:49