Mysql: queries the number of users in all product portfolios
there is an increment table of products purchased by users whose order_list, field is id (primary key self-increment does not care) and product_id (product ID, attribute values are A, B, C, D), user_id (user ID).
the following table:
A/B/C/D/AB/AC/AD/BC/BD/CD/ABC... .../ABCD4+6+4+1=15
:user_id1ABDA/B/D/AB/BD/ABD+1
:
-sharp
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);
Learn about the usage of
CUBE and the Postgres array.
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
< table >
< thead > < tr >
< th > prod < / th >
< th > users < / th >
< th > tally < / th >
< / tr > < / thead >
< tbody >
< tr >
< td > A < / td >
< td > {1pm 2pm 3} < / td >
< td > 3 < / td >
< / tr >
< tr >
< td > A B < / td >
< td > {1 td 2} < / td >
< td > 2 < / td >
< / tr >
< tr >
< td > A B C < / td >
< td > {2} < / td >
< td > 1 < / td >
< / tr >
< tr >
< td > A B D < / td >
< td > {1} < / td >
< td > 1 < / td >
< / tr >
< tr >
< td > A C < / td >
< td > {2} < / td >
< td > 1 < / td >
< / tr >
< tr >
< td > A D < / td >
< td > {1} < / td >
< td > 1 < / td >
< / tr >
< tr >
< td > B < / td >
< td > {1 td 2} < / td >
< td > 2 < / td >
< / tr >
< tr >
< td > B C < / td >
< td > {2} < / td >
< td > 1 < / td >
< / tr >
< tr >
< td > B D < / td >
< td > {1} < / td >
< td > 1 < / td >
< / tr >
< tr >
< td > C < / td >
< td > {2} < / td >
< td > 1 < / td >
< / tr >
< tr >
< td > D < / td >
< td > {1} < / td >
< td > 1 < / td >
< / tr >
< / tbody >
< / table >
the first step is to generate a list of various combinations, separated by commas (product (pids varchar (8))
first counts which products each user has bought, and then accumulates the number of times the products appear in 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;
second, count how many people have bought each item, and then add
to the combination.
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;
