Get rows that exist exactly once per day for a given period

Question

I have a case where I want to extract the device ids (DID) that exist once and only once for each day in a certain period. I have tried different methods and partitions but I seem to only be able to get that data individually per day (where date = X), but I need a query with where date between X and Y.

Example data:

DID date     
A   2019-01-01
A   2019-01-01
A   2019-01-02
A   2019-01-03
B   2019-01-01
B   2019-01-02
B   2019-01-03
C   2019-01-01
C   2019-01-02
C   2019-01-02
C   2019-01-03
D   2019-01-01
D   2019-01-02
D   2019-01-03

The query should return only B & D because those exists once for each day from 01 to 03.
I also wish to get the count, which would be 2 in this case

score 4 · Answer 1 · answered Sep 01 '19 at 14:57

select did from foobar2 where 
    date between '2019-01-01' and '2019-01-03' 
    group by did 
    having count(distinct date)=3 and count(date)=3;

If it has at least one of each date, but more than one of some, then count(date) will be > 3. If it has less than one for some date, then count(distinct date) will be < 3.

I also wish to get the count, which would be 2 in this case

Figuring out that B and D have a cardinality of 2 seems pretty easy. Does that really need to be done inside the query? If so, use a window function

select did, count(*) over () from foobar2 where 
    date between '2019-01-01' and '2019-01-03' 
    group by did 
    having count(distinct date)=3 and count(date)=3;

Philᵀᴹ · Answer 2 · 2019-09-01T16:34:31.697

Test table:

create table se246714 (
did char(1), 
dt  date 
);

Test data:

insert into se246714 values
('A',   '2019-01-01'),
('A',   '2019-01-01'),
('A',   '2019-01-02'),
('A',   '2019-01-03'),
('B',   '2019-01-01'),
('B',   '2019-01-02'),
('B',   '2019-01-03'),
('C',   '2019-01-01'),
('C',   '2019-01-02'),
('C',   '2019-01-02'),
('C',   '2019-01-03'),
('D',   '2019-01-01'),
('D',   '2019-01-02'),
('D',   '2019-01-03');

Easy enough to get did that only occur once a day:

  select did, dt, count(*) 
  from se246714
  group by did,dt
  having count(*)=1;

Result:

did dt          count
A   2019-01-02  1
A   2019-01-03  1
B   2019-01-01  1
B   2019-01-02  1
B   2019-01-03  1
C   2019-01-01  1
C   2019-01-03  1
D   2019-01-01  1
D   2019-01-02  1
D   2019-01-03  1

This is where it gets a bit more convoluted. It all depends on how you want to pass the dates in...

I'll assume you want to get the date range from the full set of data in the table. If you don’t, you can modify the CTE to give the start & end dates.

I'll start with an expanded version of the answer to demonstrate how it works.

with rowsperday as (
  select did, dt, count(*) 
  from se246714
  group by did,dt
  having count(*)=1 
),
startdate as ( select min(dt) as startdate from se246714 ),
enddate as ( select max(dt) as enddate from se246714 ),
numdays as ( select enddate - startdate + 1 as numdays from startdate,enddate )
select did, count(*)
from rowsperday, numdays
group by did, numdays
having count(*) = numdays
;

We calculate the difference in dates to get the number of days involved, then check the count of dids to see if they appeared every day.

Slimmed down version:

with rowsperday as (
  select did, dt, count(*) 
  from se246714
  group by did,dt
  having count(*)=1 
)
select did, count(*)
from rowsperday
group by did
having count(*) = (select max(dt) - min(dt) + 1 from rowsperday)
;

If you're wondering what the with .... as ( stuff is, read about Common table Expressions here.

Fiddle link here.

Erwin Brandstetter · Accepted Answer · 2019-09-01T23:27:57.813

This is special case of relational-division. Predicate dates just happen to be in sequence and duplicates are excluded. There are many possible solutions. The best fit depends on the complete use case.

If, for example, you need to optimize performance and your table is big and the column did has high cardinality and typically only few did qualify and there is an index on (dt, did), then this query should be substantially faster than queries with GROUP BY & count():

SELECT did
FROM   tbl t1
JOIN   tbl t2  USING (did)
JOIN   tbl t3  USING (did)
WHERE  t1.dt = '2019-01-01'
AND    t2.dt = '2019-01-02'
AND    t3.dt = '2019-01-03'
AND    NOT EXISTS (SELECT FROM tbl t1x WHERE t1x.dt = '2019-01-01' AND t1x.did = t1.did AND t1x.ctid <> t1.ctid)
AND    NOT EXISTS (SELECT FROM tbl t2x WHERE t2x.dt = '2019-01-02' AND t2x.did = t2.did AND t2x.ctid <> t2.ctid)
AND    NOT EXISTS (SELECT FROM tbl t3x WHERE t3x.dt = '2019-01-03' AND t3x.did = t3.did AND t3x.ctid <> t3.ctid);

Why? Because it can answer your query with a few comparatively very cheap index (only) scans, excluding non-qualifying rows early, while queries based on GROUP BY always have to process the whole table.

ctid in my example is safe fallback to identify individual columns, while we don't know your actual design.

This gets verbose for long date ranges. It may pay to generate the query dynamically. Or a mixed approach might make sense. Or use a recursive CTE to make it short and still very fast:

WITH RECURSIVE rcte AS (
   SELECT did, dt
   FROM   tbl t
   WHERE  dt = '2019-01-01'    -- lower bound
   AND    NOT EXISTS (SELECT FROM tbl x WHERE x.dt = t.dt AND x.did = t.did AND x.ctid <> t.ctid)

   UNION ALL
   SELECT t.did, t.dt
   FROM   rcte r
   JOIN   tbl  t USING (did)
   WHERE  r.dt < '2019-01-03'  -- upper bound
   AND    t.dt = r.dt + 1
   AND    NOT EXISTS (SELECT FROM tbl x WHERE x.dt = t.dt AND x.did = t.did AND x.ctid <> t.ctid)
   )
SELECT array_agg(did) AS dids, count(*)
FROM   rcte
WHERE  dt = '2019-01-03'       -- found 1 row from lower to upper bound

This variant returns an array of qualifying IDs and a count.

db<>fiddle here

Related: (recommended reading!)

SQL query to find a row with a specific number of associations

It all depends on the actual, complete use case.

Also related:

SELECT rows based on indefinite number of filters

Get rows that exist exactly once per day for a given period

3 Answers3

Linked