PostgreSQL中传感器多标签同时关联的SCD-2时间范围重叠查询

问题背景 #

我有一张采用SCD-2（缓慢变化维度）设计的PostgreSQL表SensorLabel，用于记录传感器与标签的时间绑定关系。表结构如下：

CREATE TABLE SensorLabel (
    sensor_id INT,
    label_id INT,
    start_time TIMESTAMPTZ,
    end_time TIMESTAMPTZ
);

每行数据代表某一传感器（sensor_id）在[start_time, end_time)左闭右开时间段内与对应标签（label_id）关联。

需求：给定任意N个标签的集合，找出每个传感器同时关联所有这些标签的时间范围。这些交集可能被拆分为多个不连续的时间段，仅返回符合条件的时间段。目前能实现固定2-3个标签的查询，但需要支持任意数量标签的通用SQL。

示例说明 #

输入数据 #

sensor|label|from|to
1|1|2021-01-01|2021-10-01
1|2|2020-12-01|2021-05-01
1|2|2021-07-01|2021-09-01
1|3|2021-03-01|2021-06-01
1|3|2021-08-01|2021-12-01

（注：示例中from对应表的start_time，to对应end_time）

期望输出（查询标签集合{1,2,3}的结果） #

sensor|from|to
1|2021-03-01|2021-05-01
1|2021-08-01|2021-09-01

通用SQL解决方案 #

核心思路是将目标标签的所有时间点（开始/结束）作为事件节点，按传感器分组后统计每个时间区间内的活跃标签数，当数量等于目标标签总数时，该区间即为所有标签的关联交集。

完整SQL代码 #

WITH target_labels AS (
    -- 替换为你的目标标签集合，支持任意数量
    SELECT unnest(ARRAY[1,2,3]) AS label_id
),
label_events AS (
    SELECT
        sl.sensor_id,
        sl.start_time AS event_time,
        +1 AS delta
    FROM SensorLabel sl
    JOIN target_labels tl ON sl.label_id = tl.label_id
    UNION ALL
    SELECT
        sl.sensor_id,
        sl.end_time AS event_time,
        -1 AS delta
    FROM SensorLabel sl
    JOIN target_labels tl ON sl.label_id = tl.label_id
),
sorted_events AS (
    SELECT
        sensor_id,
        event_time,
        delta,
        SUM(delta) OVER (PARTITION BY sensor_id ORDER BY event_time) AS active_labels
    FROM label_events
),
interval_candidates AS (
    SELECT
        sensor_id,
        event_time AS start_interval,
        LEAD(event_time) OVER (PARTITION BY sensor_id ORDER BY event_time) AS end_interval,
        active_labels
    FROM sorted_events
)
SELECT
    sensor_id,
    start_interval AS "from",
    end_interval AS "to"
FROM interval_candidates
WHERE
    active_labels = (SELECT COUNT(*) FROM target_labels)
    AND end_interval IS NOT NULL
    AND start_interval < end_interval; -- 排除空区间

代码说明 #

• target_labels：定义待查询的标签集合，通过ARRAY[...]可快速扩展到任意数量标签。
• label_events：将每个标签的时间区间拆分为"开始+1"和"结束-1"的事件，用于后续统计活跃标签数。
• sorted_events：按传感器和事件时间排序，用窗口函数累计计算每个时间点的活跃标签数量。
• interval_candidates：通过LEAD函数获取下一个事件时间，形成连续的时间区间。
• 最终筛选：仅保留活跃标签数等于目标标签总数的有效区间，排除空区间和无效时段。

内容的提问来源于stack exchange，提问作者Geert-Jan