获取在某个日期范围内每天仅存在一次的唯一记录

Question

我有一个案例，我想提取仅在特定时间段内每天存在的设备ID(DID(。我尝试了不同的方法和分区，但我似乎每天只能单独获取这些数据(其中日期= X，但我需要一个查询，我可以将日期放在X和Y之间(

示例，这是数据：

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

查询应该只返回 B&D(因为 B&D 从 01 到 03 每天存在一次( 我也希望得到计数，在这种情况下将是 2

谢谢！

Answer 1

您希望设备在时间段的每一天仅存在一次，因此，如果您group by did，则需要返回count(date)和count(distinct date)等于该时间段天数的did：

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

请参阅演示。
或：

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

请参阅演示。
结果：

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

Answer 2

一种选择是按DID聚合并断言总数等于不同日期的计数。 如果此断言通过，则意味着给定DID仅存在不同的日期。

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

演示

如果要获取匹配DID的总数，则可以对上述内容进行子查询并获取COUNT(*)。 或者，如果要使用相同的查询，可以尝试：

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