驗證 Redshift 記錄中的 id 序列一致性

我有一個將事件記錄到 Redshift 的後端，它為每個事件生成一個唯一的 ID。id 是一個序號。

我有類似（events表）的東西：

+-------------------------+------+
| created_at              |  id  |
+-------------------------+------+
| 2017-06-30 09:20:47 UTC | 100  |
| 2017-06-30 09:18:31 UTC | 101  |
| 2017-06-30 09:16:19 UTC | 102  |
| 2017-06-30 09:12:08 UTC | 103  |
| 2017-06-30 09:11:59 UTC | 104  |
| 2017-06-30 09:11:15 UTC | 105  |
| 2017-06-30 07:03:41 UTC | 106  |
+-------------------------+------+

我的任務比每小時執行一次，將很少的記錄移動到另一個表（deactivated_events）。

我想驗證過去 3 小時內我沒有使用id序列失去任何記錄。首先，我考慮過使用 generate_series，但這在 Redshift 中不存在。其他人建議製作一個只有 id 的表，但是用整數填充數據庫仍然很痛苦（這裡有一個範例生成 100 萬

我想知道最好的方法是否不使用 min max 並像這樣計數：

WITH merged_events AS
 (SELECT *
  FROM
    (SELECT id, created_at
     FROM events
     UNION 
     SELECT id, created_at
     FROM deactivated_events
    )
  WHERE created_at > GETDATE() - INTERVAL '3 hours'
  ORDER BY id)
SELECT COUNT(*), (max(id) - min(id) + 1) AS diff
FROM merged_events;

PS：獎金，如何找到失去或重複的記錄？

假設您有以下設置：

INSERT INTO events
   (created_at, id)
VALUES
   ('2017-06-30 09:20:47 UTC', 100),
   -- ('2017-06-30 09:18:31 UTC', 101), -- Missing row
   ('2017-06-30 09:16:19 UTC', 102),
   ('2017-06-30 09:12:08 UTC', 103),
   ('2017-06-30 09:11:59 UTC', 104),
   ('2017-06-30 09:11:15 UTC', 105),
   ('2017-06-30 07:03:41 UTC', 106) ;

和 …

INSERT INTO deactivated_events
   (created_at, id)
VALUES
   ('2017-06-30 07:03:41 UTC', 97),
   ('2017-06-30 09:11:15 UTC', 98),
   ('2017-06-30 09:11:15 UTC', 99),
   ('2017-06-30 09:18:31 UTC', 100)     -- Repeated row
   ;

假設 Redshift 提供Window Functions，您可以使用查詢的細微變化來做兩件事：

1. id考慮分組時有重複的 sid並發現計數> 1
2. id如果前一行的 不是 1 + 目前行，則考慮缺少一行（或更多行！） 。這是通過LAG函式來完成的。

這可以通過以下查詢來完成

WITH merged_events AS
(
 SELECT 
     id
 FROM
 (
     SELECT 
         id
     FROM 
         events
     UNION ALL  /* Must be UNION ALL, because we want to find repeated values */
     SELECT 
         id
     FROM 
         deactivated_events
 ) AS q0
 WHERE true -- In practice, created_at > GETDATE() - INTERVAL '3 hours'
)
SELECT 
   id, 
   count(id) > 1 AS repeated_event, 
   ((lag(id) OVER(ORDER BY id)) /* previous id */ + 1) <> id AS previous_event_missing
FROM 
   merged_events
GROUP BY
   id ;

這將產生：

編號 | 重複事件 | previous_event_missing
--: | :------------- | :---------------------
97 | f | *空值*                  
98 | f | F 
99 | f | F 
100 | *真的* | F 
102 | f | *真的*                     
103 | f | F 
104 | f | F 
105 | f | F 
106 | f | F 

您可以在此處**的dbfiddle檢查整個設置（使用 PostgreSQL 而不是 RedShift）

---

注意事項id：如果它實際上來自一IDENTITY列（或者SEQUENCERedshift 恰好實現它，我認為不是這種情況），您可能會有差距。因此，您應該首先通過其他方式保證您的 id 最初是連續的……

參考：

• AWS redshift 中的序列號生成函式，它討論了獲取序列的不同方法及其權衡。
• 如何使用顯示不同替代方案的 SQL 查找序列中的缺失值。

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