我可以改進我的重複檢測查詢嗎？

我有一個包含美國國內航班數據的數據庫表，我正在對其進行排序-有點重複。架構出現在這裡，但它可能不是很重要。沒有完全重複的記錄；相反，我有幾個幾乎形成唯一鍵的欄位：year_、month_、dayofmonth、uniquecarrier、flightnum、origin；不到 0.1% 的記錄在這些欄位上與其他記錄共享相同的值。

我想要做的是隔離這些“偽重複”或鍵違規者，即在這些列上具有相同值的記錄對。

這是我目前正在做的事情：

CREATE TEMPORARY TABLE dupe_keys AS (
   SELECT year_, month_, dayofmonth, uniquecarrier, flightnum, origin 
   FROM (
       SELECT count(*) AS cnt, year_, month_, dayofmonth, uniquecarrier, flightnum, origin 
       FROM ontime
       GROUP BY year_, month_, dayofmonth, uniquecarrier, flightnum, origin
   ) AS t WHERE cnt > 1
) ON COMMIT PRESERVE ROWS;

此查詢需要相當多的時間（相對於基於對鍵列進行排序的 DBMS 之外的理想可能實現）。然後我執行：

CREATE TABLE dupes AS (
   SELECT  o.year_,o.quarter,o.month_,o.dayofmonth,o.dayofweek,o.flightdate,o.uniquecarrier,o.airlineid,o.carrier,o.tailnum,bunch_of,other_fields,go_here 
   FROM ontime AS o, dupe_keys AS dk
   WHERE 
           o.year_         = dk.year_ 
       AND o.month_        = dk.month_ 
       AND o.dayofmonth    = dk.dayofmonth 
       AND o.uniquecarrier = dk.uniquecarrier 
       AND o.flightnum     = dk.flightnum 
       AND o.origin        = dk.origin 
   ORDER BY o.year_ ASC, o.month_ ASC, o.dayofmonth ASC, o.uniquecarrier ASC, o.flightnum ASC, o.origin ASC
);

這顯然需要更少的時間。

我想用更好的查詢來實現同樣的、更快的。或者，我不介意像一對相同大小的桌子，每張桌子都有兩個騙子中的一個。

筆記：

• 您可以假設鍵列值的組合不會出現超過兩次。這是一次或兩次。
• 我正在使用 MonetDB，一種柱狀記憶體 DBMS。因此語法細節。
• 我希望我的問題不是特定於 DBMS，但如果是 - 基於其他 DBMS 行為的答案仍然相關，請說明您假設的 DBMS。
• 此查詢在載入數據集時執行，而不是重複執行，因此需要更多時間的預計算是不相關的。

在創建臨時表的查詢中，可以在沒有子查詢的標準 SQL 中使用HAVING子句來執行此操作，例如：

CREATE TEMPORARY TABLE dupe_keys AS (
   SELECT count(*) AS cnt, year_, month_, dayofmonth, uniquecarrier, flightnum, origin 
   FROM ontime
   GROUP BY year_, month_, dayofmonth, uniquecarrier, flightnum, origin
   HAVING cnt > 1
);

我不知道這是否會加快臨時表的創建速度，但可能值得一試。

您可以使用視窗函式非常巧妙地做到這一點：

SELECT *
FROM (
   SELECT *,
       COUNT() OVER (PARTITION BY year_, month_, dayofmonth, uniquecarrier, flightnum, origin) cnt
   FROM ontime
) AS t
WHERE cnt > 1;

這將返回所有重複的行。如果您只想返回無關的行，而不是每個組的第一行，請使用行編號：

SELECT *
FROM (
   SELECT *,
       ROW_NUMBER() OVER (PARTITION BY year_, month_, dayofmonth, uniquecarrier, flightnum, origin ORDER BY some_Other_Column) rn
   FROM ontime
) AS t
WHERE rn > 1;

在子句中插入一列ORDER BY來決定哪個行的順序在前

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