How to only show partitions of a table that contain a complete set of values?

Question

I want to query a table for where the number of identical values in one column is equal to the number distinct values in another column. I'm not sure how to phrase that better, so I think it best to illustrate:

Left one represent the table I have, and the right represents what I want it reduced to. 'Time' column is timestamps, and 'parts' are the name of the devices that are providing the values on each timestamp. In this example there are only 4 distinct parts: A, B, C and D (but there could be more, there could be less).

Every part is supposed to always report a value on a timestamp, but sometimes they don't all do it. I only want to keep the rows where all have reported. When that occur, the number of identical timestamps equals number of unique parts. In this example, that occurs only at t2, t4, t8 and t10.

I know how to get the query to recognize that there are 4 distinct parts, but I don't know how to recognize that there are four t2-s, four t4-s and so on. And then compare that info and make a condition to only give me those. Would it be possible?

So far:

• I've only created the table at fiddle, but as for an actual query I'm stumped.

• I'm currently trying to store the number of distinct parts as a variable (to be used for setting up a condition later), but struggling with it. I've seen some PL/pgSQL methods for this, but I can't seem to get them to work. I'm unsure how to use/"place them" alongside a standard Postgres query. I might try using a CTE as "variable" instead.

• As for the problem of comparing the number of distinct to multiple groups of duplicates within another column, I have no ideas - not even what such a "maneuver" would be called.

Answer 1

No PL/pgSQL and no CTEs are needed for this. To count the number of distinct parts per timestamp, just use a GROUP BY clause:

SELECT *
FROM testTable
WHERE test_time IN (
  SELECT test_time
  FROM testTable
  GROUP BY test_time
  HAVING COUNT(DISTINCT test_part) = (SELECT COUNT(DISTINCT test_part) FROM testTable)
);

^{(online demo)}

Answer 2

Steps

1. Numerate (distinct = dense_rank()) parts for each test_time tn.
2. Take max number of part for each test_time. This is max tn over test_time maxN.
3. Take max number of part for each test_time for overall table.
4. Filter rows (test_time) where maxN=maxSensorCnt.

See example

select *
from ( -- count max number of different parts for each time
       -- and count number of different parts overall
  select *
    ,max(tn)over(partition by test_time) maxN
    ,max(tn)over() maxSensorCnt
  from(  -- numerate different parts (sensors) for each test_time
  select *
    ,dense_rank()over(partition by test_time order by test_part) tn
  from testTable
 )tr
)tm
where maxn=maxsensorcnt

Demo

Other example:

1. We have sensors list in testTable as

(select string_agg(distinct test_part, ',' order by test_part) SensorList
 from testTable)

2. We have sensor list for each test_time when grouping by test_time

string_agg(test_part,',' order by test_part) 

So, filter rows

select * 
from testTable
where test_time in 
  ( select test_time from testTable group by test_time
    having string_agg(test_part,',' order by test_part)
      =(select string_agg(distinct test_part, ',' order by test_part) SensorList
        from testTable)
  )

Example

Answer 3

If we can assume that ...

• no sensor has more than one entry per timestamp.
• only the four given sensors in the example can have entries.
• test_time and test_part are defined NOT NULL

Then the query can be even simpler & cheaper, we only need the row count per test_time:

SELECT (t).*
FROM  (
   SELECT t, count(*) OVER (PARTITION BY test_time) AS ct
   FROM   tbl t
-- ORDER  BY test_time, test_part  -- optional
   ) sub
WHERE  ct = 4;

fiddle

A PK and a CHECK constraint enforce above assumptions:

ALTER TABLE tbl
  ADD PRIMARY KEY (test_time, test_part)
, ADD CONSTRAINT test_part_valid CHECK (test_part IN ('sensor_A', 'sensor_B', 'sensor_C', 'sensor_D'))
;

I select the whole row via table alias t in the subquery and unnest it in the outer SELECT. Saves me the hassle to spell out all columns while excluding the (noise) count from the result.