How can i get a count of overlapped values and sort by percent of most overlapped

user_6460122 0
Viewed 41

Example:

--------------------------
| product      | store   |
--------------------------
| abc1         | 2       |
| abc2         | 2       |
| abc1         | 3       |
| abc1         | 5       |
| abc1         | 4       |
| abc2         | 3       |
| abc3         | 3       |
| abc1         | 1       |
| abc4         | 3       |
| abc5         | 3       |
--------------------------

I am trying to get the percent of shared products between different stores and sort them by stores with the biggest overlap.

I am not really sure how to approach this problem.

Fiddle: https://www.db-fiddle.com/f/bm5c7k7q5qbQY3Qu8t5Gvx/1

postgresql sql
2 Answers

You can use a self join. The following assumes that you want the proportion based on the products in the first store:

select t1.store_id, t2.store_id, t1.num_products,
       count(*) * 1.0 / t1.num_products as ratio
from (select p.*, count(*) over (partition by store_id) as num_products
      from products p
     ) t1 join
     products t2
     on t1.sku = t2.sku
group by t1.store_id, t2.store_id, t1.num_products
order by ratio desc;

This is not symmetric.

Here is a db<>fiddle.

user_11440350

I think you are looking for a cartesian join between stores and then checking to see the matching count of sku.

Here is my query 

--This gets the total count of sku by store
    with data
      as (select store_id,count(sku) as tot_sku
           from products
           group by store_id
          )
    select a.store_id as store_id
          ,b.store_id as other_store_id
          --when the skus in one store match with another then count those
          ,count(case when a.sku=b.sku then 1 end) as cnt_overlap 
          ,max(c.tot_sku) as tot_sku
          ,count(case when a.sku=b.sku then 1 end)*100.00/max(c.tot_sku) as pct_overlap
     from products a
     join products b
       on a.store_id <> b.store_id
     join data c
       on a.store_id=c.store_id
    group by a.store_id,b.store_id
    order by 1

+----------+----------------+-------------+---------+----------------------+
| store_id | other_store_id | cnt_overlap | tot_sku |     pct_overlap      |
+----------+----------------+-------------+---------+----------------------+
|        1 |              2 |           1 |       1 | 100.0000000000000000 |
|        1 |              3 |           1 |       1 | 100.0000000000000000 |
|        1 |              4 |           1 |       1 | 100.0000000000000000 |
|        1 |              5 |           1 |       1 | 100.0000000000000000 |
|        2 |              1 |           1 |       2 |  50.0000000000000000 |
|        2 |              3 |           2 |       2 | 100.0000000000000000 |
|        2 |              4 |           1 |       2 |  50.0000000000000000 |
|        2 |              5 |           1 |       2 |  50.0000000000000000 |
|        3 |              1 |           1 |       5 |  20.0000000000000000 |
|        3 |              2 |           2 |       5 |  40.0000000000000000 |
|        3 |              4 |           1 |       5 |  20.0000000000000000 |
|        3 |              5 |           1 |       5 |  20.0000000000000000 |
|        4 |              1 |           1 |       1 | 100.0000000000000000 |
|        4 |              2 |           1 |       1 | 100.0000000000000000 |
|        4 |              3 |           1 |       1 | 100.0000000000000000 |
|        4 |              5 |           1 |       1 | 100.0000000000000000 |
|        5 |              1 |           1 |       1 | 100.0000000000000000 |
|        5 |              2 |           1 |       1 | 100.0000000000000000 |
|        5 |              3 |           1 |       1 | 100.0000000000000000 |
|        5 |              4 |           1 |       1 | 100.0000000000000000 |
+----------+----------------+-------------+---------+----------------------+

here is my db fiddle link

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=151fa1f18ac25bb0362ebc47de02dd09

user_71370900
Related