Can’t figure this out… 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:
Tables 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…
(The table will later be pivoted into a proper (if though patchy) time series table, but before that happens I’d like the pre-table “cleaned”… Time-series is therefore not really within the scope of this current issue)
(The bot is also not particularly happy with the title – Suggestions for a better one would also be welcomed 😉