SQLite传感器重复数据高效查找与去重的SQL查询优化咨询

需求描述 #

存储传感器定时采集数据的表中，部分数据变更频率极低，存在连续行仅ID不同的重复数据。需要找出这些重复行，仅保留每组相同数据的最早和最晚行。

原方案问题 #

原查询通过关联子查询获取每行的上一行（p）和下一行（n），判断当前行是否为中间重复行：

SELECT
    m.name
  , c.id
FROM statistics_meta AS m
INNER JOIN statistics AS c ON c.metadata_id = m.id
INNER JOIN statistics AS p ON p.id = (SELECT MAX(t.id) FROM statistics AS t WHERE t.metadata_id = m.id AND t.id < c.id)
INNER JOIN statistics AS n ON n.id = (SELECT MIN(t.id) FROM statistics AS t WHERE t.metadata_id = m.id AND t.id > c.id)
WHERE IFNULL(c.state, 0) = IFNULL(p.state, 0)
  AND IFNULL(c.state, 0) = IFNULL(n.state, 0)

注：c - 当前行，p - 上一行，n - 下一行

该方案的核心问题是相关子查询的重复执行：每行数据都要触发两次子查询扫描表获取上/下一行ID，随着数据量增长，查询时间会呈指数级上升，性能极差。

优化方案：使用窗口函数 #

利用SQL窗口函数LAG()和LEAD()，只需一次表扫描即可获取相邻行数据，大幅降低查询开销。以下提供两种实现思路：

思路1：分组取首尾行 #

先将连续相同state的行标记为同一分组，再直接提取每组的最早和最晚ID：

WITH ranked_data AS (
    SELECT
        s.id,
        s.metadata_id,
        s.state,
        -- 标记连续相同state的分组：state变化时分组编号+1
        SUM(CASE WHEN LAG(s.state) OVER (PARTITION BY s.metadata_id ORDER BY s.id) != s.state THEN 1 ELSE 0 END) 
            OVER (PARTITION BY s.metadata_id ORDER BY s.id) AS group_id
    FROM statistics s
),
group_boundaries AS (
    SELECT
        metadata_id,
        group_id,
        MIN(id) AS first_id,
        MAX(id) AS last_id
    FROM ranked_data
    GROUP BY metadata_id, group_id
)
SELECT
    m.name,
    gb.id
FROM group_boundaries gb
-- 展开每组的首尾ID
CROSS JOIN UNNEST(ARRAY[gb.first_id, gb.last_id]) AS id
JOIN statistics_meta m ON m.id = gb.metadata_id
ORDER BY m.name, gb.id;

思路2：直接筛选需保留的行 #

通过判断当前行是否为分组边界（首行、末行、state变化行），直接筛选出需要保留的数据：

SELECT
    m.name,
    s.id
FROM (
    SELECT
        id,
        metadata_id,
        state,
        -- 获取上一行的state
        LAG(state) OVER (PARTITION BY metadata_id ORDER BY id) AS prev_state,
        -- 获取下一行的state
        LEAD(state) OVER (PARTITION BY metadata_id ORDER BY id) AS next_state
    FROM statistics
) s
JOIN statistics_meta m ON m.id = s.metadata_id
WHERE 
    -- 保留分组首行（无上一行）
    prev_state IS NULL
    -- 保留分组末行（无下一行）
    OR next_state IS NULL
    -- 保留state变化的行（与上一行或下一行不同）
    OR state != prev_state
    OR state != next_state
ORDER BY m.name, s.id;

性能提升说明 #

• 窗口函数仅需对statistics表执行一次全表扫描，时间复杂度从原方案的O(n²)降至O(n)，数据量越大性能提升越显著。
• 建议为statistics表创建复合索引：CREATE INDEX idx_statistics_metadata_id_id_state ON statistics(metadata_id, id, state);，窗口函数可直接利用索引完成排序和数据获取，避免额外排序开销。

示例数据验证 #

使用提供的示例数据：

CREATE TABLE statistics_meta (
  id integer primary key,
  name varchar(10)
);
CREATE TABLE statistics (
  id integer primary key,
  metadata_id integer,
  state integer
);

INSERT INTO statistics_meta (name) values ('temp1'), ('temp2');
INSERT INTO statistics (metadata_id, state) values
  (1, 22),
  (2, 23),
  (1, 23),
  (2, 21),
  (1, 23),
  (2, 22),
  (1, 23),
  (2, 21),
  (1, 23),
  (1, 22),
  (2, 21),
  (2, 21);

执行优化后的查询，将得到以下结果：

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