如何删除SQL大表中仅effective_from字段变更的冗余行？

Hey, let's work through this problem—since you're dealing with a huge table, we need solutions that don't drag your system to a halt. Here's how to clean up those redundant rows where only effective_from changed but nothing else did:

First, we need to pinpoint rows where all fields except effective_from match the previous row in the same CODE group. The LAG() window function is perfect for this—it grabs values from the prior row in a sorted partition so we can compare:

WITH redundant_candidates AS (
    SELECT 
        *,
        -- Add every non-effective_from field you need to compare here
        LAG(NAME) OVER (PARTITION BY CODE ORDER BY EFFECTIVE_FROM) AS prev_name,
        LAG(your_field_1) OVER (PARTITION BY CODE ORDER BY EFFECTIVE_FROM) AS prev_field1,
        LAG(your_field_2) OVER (PARTITION BY CODE ORDER BY EFFECTIVE_FROM) AS prev_field2
        -- Keep adding other business fields as needed
    FROM your_large_table
)
SELECT *
FROM redundant_candidates
WHERE 
    -- All non-effective_from fields must match the prior row
    NAME = prev_name
    AND your_field_1 = prev_field1
    AND your_field_2 = prev_field2;

Run this query first to confirm the results are exactly the redundant rows you want to delete—don't skip this step to avoid accidental data loss!

For large tables, a one-time delete can cause long locks or performance crashes. We'll cover options based on your table size:

1. Add a Critical Index First #

The PARTITION BY CODE ORDER BY EFFECTIVE_FROM logic needs fast sorting support. Create this composite index to speed up the window function drastically:

CREATE INDEX idx_code_effective_from ON your_large_table (CODE, EFFECTIVE_FROM);

If you already have an index covering these two fields, you can skip this, but double-check it exists—it makes all the difference.

2. One-Time Delete (For Moderate Redundancy) #

If redundant rows don't make up a huge portion of your table, use a CTE to delete directly. Syntax varies slightly by database:

PostgreSQL/SQL Server: #

WITH redundant_candidates AS (
    SELECT 
        id, -- Assume your table has a primary key like `id` to identify rows
        LAG(NAME) OVER (PARTITION BY CODE ORDER BY EFFECTIVE_FROM) AS prev_name,
        LAG(your_field_1) OVER (PARTITION BY CODE ORDER BY EFFECTIVE_FROM) AS prev_field1,
        LAG(your_field_2) OVER (PARTITION BY CODE ORDER BY EFFECTIVE_FROM) AS prev_field2
    FROM your_large_table
)
DELETE FROM your_large_table
WHERE id IN (
    SELECT id 
    FROM redundant_candidates
    WHERE 
        NAME = prev_name
        AND your_field_1 = prev_field1
        AND your_field_2 = prev_field2
);

MySQL 8.0+: #

WITH redundant_candidates AS (
    SELECT 
        id,
        NAME,
        your_field_1,
        your_field_2,
        LAG(NAME) OVER (PARTITION BY CODE ORDER BY EFFECTIVE_FROM) AS prev_name,
        LAG(your_field_1) OVER (PARTITION BY CODE ORDER BY EFFECTIVE_FROM) AS prev_field1,
        LAG(your_field_2) OVER (PARTITION BY CODE ORDER BY EFFECTIVE_FROM) AS prev_field2
    FROM your_large_table
)
DELETE t
FROM your_large_table t
JOIN redundant_candidates rc ON t.id = rc.id
WHERE 
    rc.NAME = rc.prev_name
    AND rc.your_field_1 = rc.prev_field1
    AND rc.your_field_2 = rc.prev_field2;

3. Batched Delete (For Ultra-Large Tables) #

If your table has millions/billions of rows, a one-time delete will lock the table for too long. Use a loop to delete small batches (e.g., 10,000 rows at a time):

PostgreSQL Example: #

WHILE EXISTS (
    SELECT 1 
    FROM your_large_table t
    JOIN (
        SELECT 
            id,
            LAG(NAME) OVER (PARTITION BY CODE ORDER BY EFFECTIVE_FROM) AS prev_name,
            LAG(your_field_1) OVER (PARTITION BY CODE ORDER BY EFFECTIVE_FROM) AS prev_field1,
            LAG(your_field_2) OVER (PARTITION BY CODE ORDER BY EFFECTIVE_FROM) AS prev_field2
        FROM your_large_table
    ) rc ON t.id = rc.id
    WHERE 
        t.NAME = rc.prev_name
        AND t.your_field_1 = rc.prev_field1
        AND t.your_field_2 = rc.prev_field2
) LOOP
    DELETE FROM your_large_table
    WHERE id IN (
        SELECT id 
        FROM (
            SELECT 
                id,
                LAG(NAME) OVER (PARTITION BY CODE ORDER BY EFFECTIVE_FROM) AS prev_name,
                LAG(your_field_1) OVER (PARTITION BY CODE ORDER BY EFFECTIVE_FROM) AS prev_field1,
                LAG(your_field_2) OVER (PARTITION BY CODE ORDER BY EFFECTIVE_FROM) AS prev_field2
            FROM your_large_table
        ) rc
        WHERE 
            NAME = prev_name
            AND your_field_1 = prev_field1
            AND your_field_2 = prev_field2
        LIMIT 10000
    );
    COMMIT; -- Release locks after each batch
END LOOP;

MySQL Example: #

REPEAT
    DELETE t
    FROM your_large_table t
    JOIN (
        SELECT 
            id,
            LAG(NAME) OVER (PARTITION BY CODE ORDER BY EFFECTIVE_FROM) AS prev_name,
            LAG(your_field_1) OVER (PARTITION BY CODE ORDER BY EFFECTIVE_FROM) AS prev_field1,
            LAG(your_field_2) OVER (PARTITION BY CODE ORDER BY EFFECTIVE_FROM) AS prev_field2
        FROM your_large_table
        LIMIT 10000 -- Limit batch size
    ) rc ON t.id = rc.id
    WHERE 
        rc.NAME = rc.prev_name
        AND rc.your_field_1 = rc.prev_field1
        AND rc.your_field_2 = rc.prev_field2;
UNTIL ROW_COUNT() = 0 END REPEAT;

• No primary key? If your table lacks a unique identifier (like id), add an auto-incrementing primary key first—without it, safely targeting rows will be inefficient or impossible.
• Backup first：Always back up the affected data or test the full workflow in a staging environment before running deletes in production.
• Pick the right time：Run delete operations during low-traffic hours to minimize impact on your users.

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