Pairwise intersection counts of multiple columns in SQL

Question

Say I have a SQL db with multiple tables, and each of these tables has a column code. What I want to produce is a table showing the number of codes shared between each pair of code columns. I.e. a pairwise intersection count plot:

       table1 table2 table3 table4
table1     10      2      3      5
table2      2     10      4      1
table3      3      4     10      2
table4      5      1      2     10

I can get each value individually using e.g.

WITH abc AS 
(
SELECT code FROM table1
INTERSECT
SELECT code FROM table2
)

SELECT COUNT(*) AS table1Xtable2 FROM abc

But is there a query that will generate the entire output as desired as a table?

score 3 · Accepted Answer · edited Mar 10 '21 at 10:10

The following gets all combinations among the tables:

select t1, t2, t3, t4, count(*) 
from (select code, max(t1) as t1, max(t2) as t2, max(t3) as t3, max(t4) as t4
      from ((select code, 1 as t1, 0 as t2, 0 as t3, 0 as t4
             from table1
            ) union all
            (select code, 0 as t1, 1 as t2, 0 as t3, 0 as t4
             from table2
            ) union all
            (select code, 0 as t1, 0 as t2, 1 as t3, 0 as t4
             from table3
            ) union all
            (select code, 0 as t1, 0 as t2, 0 as t3, 1 as t4
             from table4
            )
           ) t
      group by code
     ) t
group by t1, t2, t3, t4;

For your particular problem, you can use:

with t as (
      select code, 'table1' as tablename from table1 union all
      select code, 'table2' as tablename from table2 union all
      select code, 'table3' as tablename from table3 union all
      select code, 'table4' as tablename from table4 
     )
select t1.tablename,
       sum(case when t2.tablename = 'table1' then 1 else 0 end) as t1,
       sum(case when t2.tablename = 'table2' then 1 else 0 end) as t2,
       sum(case when t2.tablename = 'table3' then 1 else 0 end) as t3,
       sum(case when t2.tablename = 'table4' then 1 else 0 end) as t4
from t t1 join
     t t2
on t1.code = t2.code
group by t1.tablename

Note that the above assumes that code is unique in the tables. If it is duplicated, you can replace union all with union.

Pairwise intersection counts of multiple columns in SQL

1 Answers1