如何通过OR条件识别重复数据并排除最早创建日期的记录？

需要编写查询清理my_users表中的重复数据，重复项识别规则如下：

(user_id不为空且user_id与born_on匹配) OR (first_name、last_name与born_on匹配)

要求保留每组中assigned_to日期最早的记录，查询仅返回重复数据（排除每组中最早日期的那条）。

尝试了以下写法，但无法按预期条件进行PARTITION：

WITH RankedDuplicates AS (
  SELECT
  first_name,
  last_name,
  born_on,
  user_id,
  assigned_to,
  ROW_NUMBER() OVER (
    PARTITION BY
    (user_id IS NOT NULL AND user_id, born_on) OR
    (first_name, last_name, born_on)
    ORDER BY assigned_to
  ) AS row_num
  FROM my_users
)

SELECT
first_name,
last_name,
user_id,
ssn,
assigned_to
FROM RankedDuplicates
WHERE row_num > 1;

表结构及测试数据 #

CREATE TABLE my_users (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name text,
    last_name text,
    born_on date,
    user_id integer,
    assigned_to date
);
CREATE UNIQUE INDEX my_users_pkey ON my_users(id int4_ops);

INSERT INTO public.my_users (first_name, last_name, born_on, user_id, assigned_to) VALUES
(NULL, NULL, '2011-01-01'::date, 23433, '2023-06-01'::date),--这条不返回，是Mike Smith组的最早记录
('Michael', 'Smith', '2011-01-01'::date, 23433, '2023-08-01'::date),
('Mike', 'Smith', '2011-01-01'::date, NULL, '2023-07-01'::date),
('Foo', 'bar', '1988-07-03'::date, 13434, '2023-03-01'::date),
('Foo', 'Bar', '1988-07-03'::date, NULL, '2023-08-01'::date), --这条不返回，是Foo Bar组的最早记录
(NULL, NULL, '1988-07-03'::date, 13434, '2023-05-01'::date),--无匹配记录，不返回
('John', 'Doe', '2000-03-08'::date, 23433, '2023-09-01'::date),--无匹配记录，不返回
(NULL, NULL, '1988-07-03'::date, NULL, '2023-05-01'::date);--无匹配记录，不返回

请问如何实现该查询？

你的问题出在PARTITION BY的逻辑写法上——SQL里不能直接用OR来组合多个分区键集合。要实现你的重复分组规则，需要生成一个统一的分组标识，把符合重复条件的记录归到同一组里。

核心思路是：

1. 对于有user_id的记录，优先用user_id + born_on作为分组依据；
2. 对于没有user_id的记录，用first_name + last_name + born_on作为分组依据；
3. 同时，要把共享同一个user_id的记录，和对应的first_name/last_name/born_on匹配的记录归为同一组。

以下是可行的SQL：

WITH UserGroups AS (
  SELECT
    *,
    -- 生成分组键：优先取同user_id+born_on的组，否则取first_name+last_name+born_on的组
    COALESCE(
      -- 找到当前记录所属的user_id分组的最小id（作为组标识）
      (SELECT MIN(id) FROM my_users u2 
       WHERE u2.user_id = u1.user_id AND u2.born_on = u1.born_on AND u2.user_id IS NOT NULL),
      -- 如果没有user_id，找到同first_name+last_name+born_on的最小id
      (SELECT MIN(id) FROM my_users u2 
       WHERE u2.first_name = u1.first_name AND u2.last_name = u1.last_name AND u2.born_on = u1.born_on)
    ) AS group_id
  FROM my_users u1
),
RankedDuplicates AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY assigned_to) AS row_num
  FROM UserGroups
)
SELECT
  first_name,
  last_name,
  user_id,
  assigned_to
FROM RankedDuplicates
WHERE row_num > 1;

逻辑说明 #

• UserGroups CTE：为每条记录生成唯一的group_id，确保符合重复规则的记录共享同一个group_id。
  • 当记录有user_id时，找到所有同user_id+born_on的记录中最小的id作为组标识；
  • 当记录无user_id时，找到所有同first_name+last_name+born_on的记录中最小的id作为组标识；
• RankedDuplicates CTE：按group_id分区，对每组内的记录按assigned_to升序排名；
• 最后筛选出排名大于1的记录，就是需要清理的重复数据。

测试这个查询会返回两条记录：

• ('Michael', 'Smith', 23433, '2023-08-01')
• ('Mike', 'Smith', NULL, '2023-07-01')

这完全符合预期——保留了2023-06-01的最早记录，排除了其他重复项。

内容的提问来源于stack exchange，提问作者albertski