基于复杂条件筛选并删除Signals表中的重复行

需求说明 #

需要对Signals表执行两类清理操作，用单条SQL完成：

• 删除所有在相同Latitude、Longitude、OperatorID分组下，存在更晚Date的行
• 对相同Latitude、Longitude、OperatorID且Date一致的行，仅保留SignalStrength最高的记录，删除其余行

实现语句 #

以下是兼容多数主流数据库（如PostgreSQL、SQL Server、MySQL 8.0+）的写法：

WITH ranked_signals AS (
    SELECT 
        ID,
        ROW_NUMBER() OVER (
            PARTITION BY Latitude, Longitude, OperatorID
            ORDER BY Date DESC, SignalStrength DESC
        ) AS rn
    FROM Signals
)
DELETE FROM Signals
WHERE ID IN (SELECT ID FROM ranked_signals WHERE rn > 1);

逻辑解释 #

1. CTE部分：通过ROW_NUMBER()窗口函数，以Latitude、Longitude、OperatorID为分组依据，先按Date从新到旧排序，再按SignalStrength从高到低排序，给每行分配一个排名rn
2. 删除操作：删除所有排名大于1的行——这些行要么是同分组下日期更早的记录，要么是同日期下信号强度不是最高的记录

低版本MySQL兼容写法 #

若使用MySQL 5.x版本（不支持CTE），可改用嵌套子查询规避同表查询限制：

DELETE FROM Signals
WHERE ID NOT IN (
    SELECT ID FROM (
        SELECT 
            ID,
            ROW_NUMBER() OVER (
                PARTITION BY Latitude, Longitude, OperatorID
                ORDER BY Date DESC, SignalStrength DESC
            ) AS rn
        FROM Signals
    ) AS ranked
    WHERE rn = 1
);

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