CODEWITHSUNDEEP
databasepostgresql
roy naufal
roy naufal

Reputation: 389

get unique records existing only once per day in a date range

I have a case where I want to extract the device ids (DIDs) that exist only 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 where I can put where date between X & Y)

Example, this is the 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 B & D exists once in each day from 01 to 03) I also wish to get the count, which would be 2 in this case

thanks!

Upvotes: 0

Views: 254

Answers (2)

forpas
forpas

Reputation: 164099

You want the devices to exist only once on each day of the period, so if you group by did you need to return the dids that have count(date) and count(distinct date) equal to the number of days of that period:

select did
from tablename
where date between cast('2019-01-01' as date) and cast('2019-01-03' as date)
group by did
having 
  count(distinct date) = cast('2019-01-03' as date) - cast('2019-01-01' as date) + 1
  and
  count(date) = cast('2019-01-03' as date) - cast('2019-01-01' as date) + 1

See the demo.
Or:

select t.did
from (
  select did, date
  from tablename
  where date between cast('2019-01-01' as date) and cast('2019-01-03' as date)
  group by did, date
  having count(*) = 1
)t  
group by t.did
having count(*) = cast('2019-01-03' as date) - cast('2019-01-01' as date) + 1

See the demo.
Result:

| did |
| --- |
| B   |
| D   |

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521513

One option would be to aggregate by DID and assert that the total count is equal to the count of distinct dates. If this assertion passes, it means that a given DID has only distinct dates present.

SELECT DID
FROM yourTable
GROUP BY DID
HAVING COUNT(date) = COUNT(DISTINCT date);

Demo

If you want to get the total count of matching DID, then you could subquery the above and take COUNT(*). Or, if you wanted to use the same query you might try:

SELECT DID, COUNT(*) OVER () AS total_cnt
FROM yourTable
GROUP BY DID
HAVING COUNT(date) = COUNT(DISTINCT date);

Upvotes: 1

Related Questions