mysql：查询所有产品组合的用户人数

Question

有一张用户购买产品的增量表order_list，字段为id（主键自增不用管），product_id（产品ID，属性值为A、B、C、D），user_id（用户ID）。
如下表：

想要查询有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。
应该是个怎么查询思路？

我只想到按最大的产品组合的用户数可以这么查：

#附创建表和插入数据
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);

Answer 1

CUBE的用法和Postgres数组了解一下。

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

Answer 2

首先要生成一个各种组合列表，逗号分隔product(pids varchar(8))
第一种统计每个用户买过哪些产品，然后产品在pids中出现的次数累计

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;

第二种，统计每种商品被几个人买过，再在组合中加和

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;