標記重複記錄

我有一個東西清單，其中一些是重複的。

create table things (
 id int primary key,
 title text not null,
 author text not null,
 duplicate_of int null references things(id)
);

我想這樣標記重複項（不能刪除它們）。第一項（由最低 id 定義）永遠不應被標記為重複項。它的副本應該將它們的duplicate_of值設置為它的 id。

這是我到目前為止所擁有的：

update things a set duplicate_of = (
 select 
   id 
 from things b 
 where 
   b.title = a.title 
   and b.author = a.author 
   and b.id < a.id 
 order by 
   id asc 
 limit 1
 );

有沒有更有效的方法來實現這一點？

這可以在FIRST_VALUE分析函式的幫助下解決：

UPDATE
 things AS tgt
SET
 duplicate_of = src.first_id
FROM
 (
   SELECT
     id
   , title
   , author
   , FIRST_VALUE(id) OVER (PARTITION BY title, author ORDER BY id ASC) as first_id
   FROM
     things
 ) AS src
WHERE
 tgt.id = src.id
 AND tgt.id <> src.first_id
;

該函式根據 定義的排序順序FIRST_VALUE返回子句定義的每個子集中的第一個值。PARTITION BY``ORDER BY

假設我們有一個這樣的數據集：

對於上面的範例，src派生表的計算方式如下：

如您所見，對於 Hamlet 的三個實例，first_id計算列顯示 1，即第一個實例的 ID。戰爭與和平的兩個實例類似。

將此集合與原始表連接允許您使用 的值first_id來更新duplicate_of列，這正是查詢所做的。的附加過濾器tgt.id <> src.first_id是為了防止更新每個重複項的第一個實例。

這是查詢後表格的最終結果：

測試設置和解決方案可在 db<>fiddle的現場展示中獲得。

有關分析和其他視窗函式的更多資訊，請參閱線上 PostgreSQL 手冊（目前版本）。

你可以使用所謂的 ctid （這基本上是一個內部的東西，但對於一個糟糕的黑客來說它應該這樣做。這是一個例子：'

test=# CREATE TABLE x (id int);
CREATE TABLE
test=# INSERT INTO x SELECT * FROM generate_series(1, 4);
INSERT 0 4
test=# INSERT INTO x SELECT * FROM generate_series(1, 4);
INSERT 0 4
test=# SELECT ctid, * FROM x;
ctid  | id 
-------+----
(0,1) |  1
(0,2) |  2
(0,3) |  3
(0,4) |  4
(0,5) |  1
(0,6) |  2
(0,7) |  3
(0,8) |  4
(8 rows)

test=# SELECT id, min(ctid) FROM x GROUP BY 1;
id |  min  
----+-------
 4 | (0,4)
 1 | (0,1)
 3 | (0,3)
 2 | (0,2)
(4 rows)

test=# DELETE FROM x WHERE ctid NOT IN (SELECT min(ctid) FROM x GROUP BY id);
DELETE 4
test=# SELECT ctid, * FROM x;
ctid  | id 
-------+----
(0,1) |  1
(0,2) |  2
(0,3) |  3
(0,4) |  4
(4 rows)
`

記住; ctid 是內部的東西，所以要小心。

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