如何在时间序列中删除/更新重复值？（ANSI SQL及PostgreSQL方案）

问题背景 #

现有一张edge表，存储父子节点间带时间区间的关联值，需要将同一(parent_id, child_id, value)组合下连续衔接的时间区间合并为单条记录，保留最早的start时间和最晚的end时间（end为NULL表示持续有效）。

表结构（ANSI SQL） #

create table edge (
   parent_id int not null,
   child_id int not null,
   value float not null,
   start date not null,
   end date
);

注：原表定义中end字段允许为NULL，对应持续有效的记录。

原始输入数据 #

1,2,0,2023-01-01,2023-01-10
1,2,0,2023-01-11,2023-01-20
1,2,0,2023-01-21,NULL
1,3,0,2023-01-01,2023-01-10
1,3,0,2023-01-11,2023-01-20
1,3,1,2023-01-21,NULL

期望合并结果 #

1,2,0,2023-01-01,NULL
1,3,0,2023-01-01,2023-01-20
1,3,1,2023-01-21,NULL

ANSI SQL 解决方案 #

方式1：生成合并后结果（不修改原表） #

通过窗口函数识别连续区间的分组，再聚合得到合并记录：

WITH grouped AS (
    SELECT 
        parent_id,
        child_id,
        value,
        start,
        end,
        -- 标记当前行是否为新组起始：若当前start不等于上一行end+1，则开启新组
        SUM(CASE 
            WHEN LAG(end) OVER (PARTITION BY parent_id, child_id, value ORDER BY start) + 1 = start 
            THEN 0 
            ELSE 1 
        END) OVER (PARTITION BY parent_id, child_id, value ORDER BY start) AS group_id
    FROM edge
)
SELECT 
    parent_id,
    child_id,
    value,
    MIN(start) AS start,
    MAX(end) AS end
FROM grouped
GROUP BY parent_id, child_id, value, group_id
ORDER BY parent_id, child_id, start;

方式2：直接修改原表（删除冗余+更新记录） #

通过临时表中转合并结果，再覆盖原表：

-- 1. 创建临时表存储合并结果
CREATE TABLE temp_edge AS
WITH grouped AS (
    SELECT 
        parent_id,
        child_id,
        value,
        start,
        end,
        SUM(CASE 
            WHEN LAG(end) OVER (PARTITION BY parent_id, child_id, value ORDER BY start) + 1 = start 
            THEN 0 
            ELSE 1 
        END) OVER (PARTITION BY parent_id, child_id, value ORDER BY start) AS group_id
    FROM edge
)
SELECT 
    parent_id,
    child_id,
    value,
    MIN(start) AS start,
    MAX(end) AS end
FROM grouped
GROUP BY parent_id, child_id, value, group_id;

-- 2. 清空原表
TRUNCATE TABLE edge;

-- 3. 插入合并结果到原表
INSERT INTO edge (parent_id, child_id, value, start, end)
SELECT * FROM temp_edge;

-- 4. 清理临时表（可选）
DROP TABLE temp_edge;

PostgreSQL 专属解决方案（直接修改原表） #

利用PostgreSQL的UPDATE ... FROM和DELETE ... USING语法，无需临时表即可完成操作：

步骤1：更新保留行的start为组内最早时间 #

WITH grouped AS (
    SELECT 
        parent_id,
        child_id,
        value,
        start,
        end,
        MIN(start) OVER (PARTITION BY group_id) AS group_start,
        SUM(CASE 
            WHEN LAG(end) OVER (PARTITION BY parent_id, child_id, value ORDER BY start) + 1 = start 
            THEN 0 
            ELSE 1 
        END) OVER (PARTITION BY parent_id, child_id, value ORDER BY start) AS group_id,
        -- 标记组内最后一行（需要保留的行）
        ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY start DESC) AS rn
    FROM edge
)
UPDATE edge e
SET start = g.group_start
FROM grouped g
WHERE e.parent_id = g.parent_id
  AND e.child_id = g.child_id
  AND e.value = g.value
  AND e.start = g.start
  AND e.end = g.end
  AND g.rn = 1;

步骤2：删除冗余的连续区间行 #

DELETE FROM edge e
USING edge e_prev
WHERE e.parent_id = e_prev.parent_id
  AND e.child_id = e_prev.child_id
  AND e.value = e_prev.value
  AND e.start = e_prev.end + 1;

执行上述两步后，查询edge表即可得到期望的合并结果。

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