postgresql数据按照指定条件删除重复数据

一.按照指定条件删除重复数据方法

-- 假设我们要去重的表名为 YourTable，重复判断依据为 columnA 和 columnB
WITH RankedRecords AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY columnA, columnB ORDER BY (SELECT NULL)) AS RowNum
    FROM YourTable
)
DELETE FROM RankedRecords WHERE RowNum > 1;

1.实践方法

示例1.降雨数据去重

-- 假设我们要去重的表名为 YourTable，重复判断依据为 columnA 和 columnB
WITH RankedRecords AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY STCD, TM, INTV ORDER BY (SELECT NULL)) AS RowNum
    FROM ST_PPTN_R
)
DELETE FROM RankedRecords WHERE RowNum > 1;

示例2.站点数据去重

--数据去重
DELETE
FROM
        model_engineering_rel_stbprp_b 
WHERE
        stbprp_id in (
        SELECT DISTINCT
                stbprp_id 
        FROM
                (
                SELECT
                        *,
                        ROW_NUMBER ( ) OVER ( PARTITION BY STCD, engr_id, sttp ORDER BY frgrd DESC ) AS RowNum 
                FROM
                        model_engineering_rel_stbprp_b 
                WHERE
                        engr_id = 22 
                ) T 
        WHERE
        RowNum > 1 
        )