Within Table Subquery of Identical Combinations

Question

I would like to select groups that have the exact same attributes from a table. For example, my table is like the following

facs_run_id |  fcj_id
    1       |     17
    1       |     4
    1       |     12
    2       |     17
    2       |     4
    2       |     12
    3       |     17
    3       |     12
    3       |     10

In this table each facs_run_id has different combinations of fcj_id, some are shared between facs_run_id numbers while others are not. For example, above facs_run_id 1 and 2 are identical, while 3 has shared fcj_id but is not identical to 1 and 2. I would like to make query to:

1. gather all fcj_id from a particular facs_run_id
2. find all facs_run_id that have the exact same fcj_id combination.

Herein, I want to find all facs_run_id that are equal in fcj_id combinations to facs_run_id: 1, so it should return 2 (or 1 & 2).

I can get those that are missing certain fcj_id and even find which fcj_id are missing with this:

SELECT facs_run_id 
FROM   facs_panel 
EXCEPT 
SELECT fcj_id 
FROM   facs_panel 
WHERE  facs_run_id = 2;

or this:

SELECT row(fp.*, fcj.fcj_antigen, fcj.fcj_color) 
FROM   facs_panel fp 
       LEFT OUTER JOIN facs_conjugate_lookup fcj ON fcj.fcj_id = fp.fcj_id
WHERE  fp.fcj_id  in ( SELECT fp.fcj_id 
                       FROM   facs_panel fp 
                       WHERE  fp.facs_run_id = 1);

But I am not able to make a query that returns IDENTICAL facs_run_id. I suppose this could be considered a way of looking for aggregated duplicates, but I don't know how to do that. Any suggestions or pointers would be greatly appreciated (or a better way to create the table if this type of query will not work).

Answer 1

It's pretty easy with a couple CTEs:

with f1 as (select
                facs_run_id,
                array_agg(fcj_id) as flist
              from facs_panel
              group by facs_run_id),
     f2 as (select flist, count(*)
              from f1
              group by flist
              having count(*) > 1)
select f2.flist, f1. facs_run_id
  from f2
  join f1 on (f2.flist = f1.flist)
  order by flist, facs_run_id;

The data from the question, run through this query, produces:

   flist   | facs_run_id 
-----------+-------------
 {4,12,17} |           1
 {4,12,17} |           2
(2 rows)