嘿，这个场景太常见了——传感器狂刷相同状态的冗余数据，现在要把连续重复的状态只留第一条对吧？我之前帮客户处理过百万级别的传感器数据表，用窗口函数就能高效解决这个问题，不用写复杂的存储过程。

核心思路 #

我们需要识别出每一次状态发生变化的记录，以及每个状态段的第一条记录（也就是切换后的第一个值）。具体来说：

• 用窗口函数LAG()获取当前记录的上一条状态值
• 对比当前状态和上一条状态，如果不一样，就保留这条记录；如果一样，就标记为冗余可以删除
• 注意：每个传感器的状态是独立的，所以要按sensor_id分组处理

具体SQL实现（以主流数据库为例） #

假设你的表结构是这样的（可以根据实际字段调整）：

CREATE TABLE sensor_data (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sensor_id INT,
    status TINYINT(1), -- 0或1
    record_time DATETIME
);

第一步：先查询出需要保留的记录 #

先运行这个查询确认结果是否符合预期，避免误删：

SELECT *
FROM (
    SELECT 
        *,
        -- 获取同传感器的上一条状态
        LAG(status) OVER (PARTITION BY sensor_id ORDER BY record_time) AS prev_status
    FROM sensor_data
) AS sub
-- 保留两种情况：1. 第一条记录（prev_status为NULL）；2. 当前状态和上一条不同
WHERE prev_status IS NULL OR status != prev_status;

第二步：删除冗余记录（两种安全方式） #

方式一：创建新表保存有效数据（推荐大表使用，避免锁表）

如果你的表是百万级别的，直接删除可能导致长时间锁表，更安全的做法是把有效数据导出到新表，然后替换原表：

-- 创建新表并插入有效数据
CREATE TABLE sensor_data_cleaned LIKE sensor_data;
INSERT INTO sensor_data_cleaned
SELECT *
FROM (
    SELECT 
        *,
        LAG(status) OVER (PARTITION BY sensor_id ORDER BY record_time) AS prev_status
    FROM sensor_data
) AS sub
WHERE prev_status IS NULL OR status != prev_status;

-- 验证数据无误后，替换原表（记得备份原表！）
RENAME TABLE sensor_data TO sensor_data_old, sensor_data_cleaned TO sensor_data;

方式二：直接删除冗余记录（小表或离线环境使用）

如果表数据量不大，或者可以停服处理，直接删除：

DELETE FROM sensor_data
WHERE id NOT IN (
    SELECT id
    FROM (
        SELECT 
            id,
            LAG(status) OVER (PARTITION BY sensor_id ORDER BY record_time) AS prev_status
        FROM sensor_data
    ) AS sub
    WHERE prev_status IS NULL OR status != prev_status
);

注意事项 #

• 备份优先：操作前一定要备份原表，比如用CREATE TABLE sensor_data_backup AS SELECT * FROM sensor_data;
• 性能优化：如果表特别大（千万级以上），可以按sensor_id分批次处理，避免一次性操作占用过多资源
• 数据库兼容性：LAG()函数在MySQL 8.0+、PostgreSQL、SQL Server、Oracle 12c+都支持，如果是MySQL 5.x版本，需要用变量来模拟（可以留言我给你写兼容版本）
• 时间排序：一定要确保record_time是准确的排序依据，因为传感器的记录时间可能有延迟，排序错了会导致结果错误

内容的提问来源于stack exchange，提问作者Bitcoin Murderous Maniac