获取给定时间段内每天只存在一次的行

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;

如果每个日期至少有一个，但有多个日期，则 count(date) 将 > 3。如果某个日期少于一个，则 count(distinct date) 将 < 3。

我也希望得到计数，在这种情况下为 2

弄清楚 B 和 D 的基数为 2 似乎很容易。这真的需要在查询中完成吗？如果是这样，请使用窗口函数

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;

测试表：

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

测试数据：

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'); 

很容易做到did每天只发生一次：

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

结果：

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

这是它变得更加复杂的地方。这完全取决于你想如何传递日期......

我假设您想从表中的完整数据集中获取日期范围。如果您不这样做，您可以修改 CTE 以提供开始和结束日期。

我将从答案的扩展版本开始，以演示它是如何工作的。

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
;

我们计算日期的差异以获得所涉及的天数，然后检查dids 的计数以查看它们是否每天都出现。

瘦身版：

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)
;

如果您想知道这些东西是什么，请在此处with .... as (阅读公用表表达式。

小提琴链接在这里。

这是关系除法的特例。谓词日期恰好是按顺序排列的，重复的被排除在外。有许多可能的解决方案。最佳匹配取决于完整的用例。

例如，如果did您需要优化性能并且您的表很大并且该列具有高基数并且通常只有少数did符合条件并且在 上有一个索引(dt, did)，那么这个查询应该比使用&的查询快得多：GROUP BYcount()

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);

为什么？因为它可以通过一些相对非常便宜的索引（仅）扫描来回答您的查询，并尽早排除不合格的行，而基于查询的查询GROUP BY始终必须处理整个表。

ctid在我的示例中，可以安全地回退以识别各个列，而我们不知道您的实际设计。

对于长日期范围，这会变得冗长。动态生成查询可能是值得的。或者混合的方法可能是有意义的。或者使用递归 CTE使其简短且仍然非常快：

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

此变体返回一组合格的 ID 和一个计数。

db<>在这里摆弄

相关：（推荐阅读！）

• 用于查找具有特定数量关联的行的 SQL 查询

这一切都取决于实际的完整用例。

还有相关的：

• 根据不定数量的过滤器选择行