獲取給定時間段內每天只存在一次的行

我有一個案例，我想提取DID在特定時間段內每天存在一次且僅存在一次的設備 id ( )。我嘗試了不同的方法和分區，但我似乎每天只能單獨獲取這些數據（where date = X），但我需要使用where date between X and 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因為從 01 到 03 每天存在一次。

我還希望得到count，在這種情況下為2

這是關係除法的特例。謂詞日期恰好是按順序排列的，重複的被排除在外。有許多可能的解決方案。最佳匹配取決於完整的案例。

例如，如果did您需要優化性能並且您的表很大並且該列具有高基數並且通常只有少數did符合條件並且在 上有一個索引(dt, did)，那麼這個查詢應該比使用&的查詢快得多：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);

***為什麼？***因為它可以通過一些相對非常便宜的索引（僅）掃描來回答您的查詢，並儘早排除不合格的行，而基於查詢的查詢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 查詢

這一切都取決於實際的完整案例。

還有相關的：

• 根據不定數量的過濾器選擇行

引用自：https://dba.stackexchange.com/questions/246714