PostgreSQL中去除重复service_id并保留有效记录的方法

针对你遇到的1300万条数据的去重需求，结合部分表有last_update字段的情况，以下是几种高效可行的方案：

方案1：窗口函数优先保留最新更新记录（推荐） #

利用ROW_NUMBER()窗口函数按service_id分组，通过排序规则优先选择有更新时间的最新记录，无更新时间的则按指定规则选一条，适合需要明确保留逻辑的场景：

WITH ranked_temp AS (
    SELECT 
        cust_id,
        service_id,
        last_update,
        -- 排序优先级：有last_update的排前 → 按更新时间倒序 → 无更新时间按cust_id升序
        ROW_NUMBER() OVER (
            PARTITION BY service_id 
            ORDER BY 
                CASE WHEN last_update IS NOT NULL THEN 0 ELSE 1 END,
                last_update DESC,
                cust_id ASC
        ) AS rn
    FROM temp
)
SELECT cust_id, service_id, last_update
FROM ranked_temp
WHERE rn = 1;

说明： #

• PARTITION BY service_id将数据按服务ID分组
• 排序规则确保有更新时间的记录优先被保留，且取同服务ID下最新的一条；没有更新时间的记录则按cust_id升序取第一条
• 窗口函数在大数据量下的效率远高于自连接，避免笛卡尔积导致的性能问题

方案2：GROUP BY快速去重（无明确保留规则时用） #

如果对保留哪条记录没有要求，仅需每个service_id留一条，可以用分组聚合的方式，不同数据库对应不同函数：

MySQL写法： #

SELECT ANY_VALUE(cust_id) AS cust_id, service_id, ANY_VALUE(last_update) AS last_update
FROM temp
GROUP BY service_id;

PostgreSQL写法： #

SELECT DISTINCT ON (service_id) cust_id, service_id, last_update
FROM temp
ORDER BY service_id, last_update DESC NULLS LAST, cust_id ASC;

SQL Server写法： #

SELECT cust_id, service_id, last_update
FROM (
    SELECT 
        cust_id,
        service_id,
        last_update,
        ROW_NUMBER() OVER (PARTITION BY service_id ORDER BY last_update DESC, cust_id ASC) AS rn
    FROM temp
) t
WHERE rn = 1;

方案3：分阶段处理（精准区分有/无更新时间的记录） #

先处理有last_update的记录，保留最新的一条；再补充处理没有更新时间且未被选中的service_id：

-- 第一步：提取有更新时间的service_id的最新记录
WITH has_update AS (
    SELECT 
        cust_id,
        service_id,
        last_update,
        ROW_NUMBER() OVER (PARTITION BY service_id ORDER BY last_update DESC) AS rn
    FROM temp
    WHERE last_update IS NOT NULL
),
-- 第二步：获取已处理的service_id集合
processed_services AS (
    SELECT service_id FROM has_update WHERE rn = 1
)
-- 第三步：合并结果：有更新的最新记录 + 无更新且未处理的service_id记录
SELECT cust_id, service_id, last_update FROM has_update WHERE rn = 1
UNION ALL
SELECT 
    ANY_VALUE(cust_id) AS cust_id,
    service_id,
    last_update
FROM temp
WHERE last_update IS NULL
AND service_id NOT IN (SELECT service_id FROM processed_services)
GROUP BY service_id;

关于自连接方案的说明 #

你之前尝试的自连接方法（a.cust_id <> b.cust_id AND a.service_id = b.service_id）虽然能识别关联多cust_id的service_id，但会产生大量笛卡尔积，在1300万数据量下性能极差，不建议使用。窗口函数是更高效的替代方案。

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