I have a postgreSQL table where i store in five different columns (say col1, col2, col3, col4, col5) numbers. In each row the numbers stored are different from each other.

I want to make some select that gives me the pairs that exists and how many times they appear (count the times that they are present in rows).

Example:

Results (something more or less like this):

I can make some SQL to execute code in specific columns:

SELECT count (*) as total, col1, col2
FROM numbers
group by col1, col2;

But that won't give me all the combinations and I don't know how to get the rest of the pairs. Also, this may be inefficient in terms of performance.

Any help will be appreciated.

Regards, Miguel.

CodePudding user response：

You can use recursive query for generating the output you are expecting

Demo

-- You can change "select 3" to "select 2" or any number you want

with recursive col_join as (select 3),
               numbers_row as (
                   select *, row_number() over () as row
                   from numbers
               ),
               cte_r as (
                   select col1 as value, row, 1 as col
                   from numbers_row
                   union all
                   select col2 as value, row, 2 as col
                   from numbers_row
                   union all
                   select col3 as value, row, 3 as col
                   from numbers_row
                   union all
                   select col4 as value, row, 4 as col
                   from numbers_row
                   union all
                   select col5 as value, row, 5 as col
                   from numbers_row
               ),
               cte as (
                   select array_agg(value order by col) as value,
                          row,
                          col
                   from cte_r
                   group by row, col
                   union all
                   select c.value || cr1.value as value,
                          cr1.row,
                          cr1.col
                   from cte c,
                        cte_r cr1
                   where c.row = cr1.row
                     and not c.value @> array [cr1.value]
                     and c.col < cr1.col
                     and array_length(c.value || cr1.value, 1) <= (select * from col_join)
               )
select array_to_string(value, ','), count(*)
from cte
where array_length(value, 1) = (select * from col_join)
group by 1
order by 1

**Old scenario for joining two-column"

Demo

with recursive 
  numbers_row as (
    select *, row_number() over () as row from numbers
  ),
  cte_r as (
    select col1 as value, row, 1 as col from numbers_row
    union all
    select col2 as value, row, 2 as col from numbers_row
    union all
    select col3 as value, row, 3 as col from numbers_row
    union all
    select col4 as value, row, 4 as col from numbers_row
    union all
    select col5 as value, row, 5 as col from numbers_row
  ),
  cte as (
    select 
      value as val1, 
      value as val2, 
      row, 
      col
    from cte_r
    union all
    select 
      c.val1 as val1,
      cr.value as val2,
      cr.row,
      cr.col
    from 
      cte c,
      cte_r cr 
    where c.row = cr.row and c.col = cr.col - 1
  )
select val1 || ',' || val2, count(*)
from cte
where val1 <> val2
group by val1, val2, val1 || ',' || val2
order by val1, val2