sql - Postgres通过排序删除所有重复记录但一个

Question

我想知道，在 Postgres 中，如何通过按列排序删除所有重复记录，但只删除一个记录。

假设我有下表foo:

 id                 | name |  region   |          created_at
--------------------+------+-----------+-------------------------------
                  1 | foo  | sydney    | 2018-05-24 15:40:32.593745+10
                  2 | foo  | melbourne | 2018-05-24 17:28:59.452225+10
                  3 | foo  | sydney    | 2018-05-29 22:17:02.927263+10
                  4 | foo  | sydney    | 2018-06-13 16:44:32.703174+10
                  5 | foo  | sydney    | 2018-06-13 16:45:01.324273+10
                  6 | foo  | sydney    | 2018-06-13 17:04:49.487767+10
                  7 | foo  | sydney    | 2018-06-13 17:05:13.592844+10

我想通过检查 (name, region) 元组来删除所有重复项，但保留具有最大 created_at 列的那个。结果将是:

 id                 | name |  region   |          created_at
--------------------+------+-----------+-------------------------------
                  2 | foo  | melbourne | 2018-05-24 17:28:59.452225+10
                  7 | foo  | sydney    | 2018-06-13 17:05:13.592844+10

但我不知道从哪里开始。有什么想法吗？

Answer 1

使用带有 ROW_NUMBER 和 PARTITION BY 的子查询来过滤掉具有重复区域的行，同时保留每个区域中的最新行。确保您的子查询使用 AS 关键字来防止 Postgre 语法错误:

SELECT * 
FROM foo 
WHERE id IN (
  SELECT a.id 
  FROM (
    SELECT id, ROW_NUMBER() OVER (
        PARTITION BY region 
        ORDER BY created_at DESC
    ) row_no 
    FROM foo
  ) AS a 
  WHERE row_no > 1
);

... 返回要删除的行。当您对删除行的结果感到满意时，将 SELECT * 替换为 DELETE。

SQLFiddle demo