PostgreSQL双时间轴跨数据源高效查询方案求助

核心思路 #

放弃笛卡尔积方案，改用时间区间切片+优先级合并的方式处理，从根源降低时间复杂度：

1. 提取所有关键时间点（两个数据源的observation_date、assertion_range起止时间），生成无重叠的时间切片作为基础区间；
2. 按时间切片匹配两个数据源的有效数据（数据的observation_date落在切片内，且assertion_range覆盖切片）；
3. 依据给定优先级规则合并数据，过滤无数据区间，确保最终结果符合所有规则。

具体SQL实现 #

步骤1：生成无重叠时间切片 #

先收集所有需要切割的时间点，生成连续的时间区间：

WITH time_boundaries AS (
    -- 收集所有关键时间点：observation_date、assertion_range的首尾时间
    SELECT observation_date AS boundary FROM sensor_data WHERE sensor_id IN (0,2)
    UNION
    SELECT lower(assertion_range) AS boundary FROM sensor_data WHERE sensor_id IN (0,2)
    UNION
    SELECT upper(assertion_range) AS boundary FROM sensor_data WHERE sensor_id IN (0,2)
),
time_slices AS (
    -- 生成无重叠的时间切片
    SELECT 
        boundary AS slice_start,
        LEAD(boundary) OVER (ORDER BY boundary) AS slice_end
    FROM time_boundaries
    ORDER BY boundary
)
SELECT * FROM time_slices WHERE slice_end IS NOT NULL;

步骤2：匹配各切片的有效数据源 #

为每个时间切片关联sensor_id=2和0的有效数据：

WITH time_boundaries AS (
    SELECT observation_date AS boundary FROM sensor_data WHERE sensor_id IN (0,2)
    UNION
    SELECT lower(assertion_range) AS boundary FROM sensor_data WHERE sensor_id IN (0,2)
    UNION
    SELECT upper(assertion_range) AS boundary FROM sensor_data WHERE sensor_id IN (0,2)
),
time_slices AS (
    SELECT 
        boundary AS slice_start,
        LEAD(boundary) OVER (ORDER BY boundary) AS slice_end
    FROM time_boundaries
    ORDER BY boundary
),
sensor_2_matches AS (
    -- 匹配sensor_id=2的有效数据到时间切片
    SELECT
        ts.slice_start,
        ts.slice_end,
        sd.species_id,
        sd.count_a,
        sd.count_b,
        sd.assertion_range
    FROM time_slices ts
    JOIN sensor_data sd ON sd.sensor_id = 2
        AND sd.observation_date BETWEEN ts.slice_start AND ts.slice_end
        AND ts.slice_start >= lower(sd.assertion_range)
        AND ts.slice_end <= upper(sd.assertion_range)
),
sensor_0_matches AS (
    -- 匹配sensor_id=0的有效数据到时间切片
    SELECT
        ts.slice_start,
        ts.slice_end,
        sd.species_id,
        sd.count_a,
        sd.assertion_range
    FROM time_slices ts
    JOIN sensor_data sd ON sd.sensor_id = 0
        AND sd.observation_date BETWEEN ts.slice_start AND ts.slice_end
        AND ts.slice_start >= lower(sd.assertion_range)
        AND ts.slice_end <= upper(sd.assertion_range)
)

步骤3：按规则合并数据生成视图 #

合并两个数据源的结果，严格遵循给定规则：

-- 接上一步CTE
SELECT
    COALESCE(s2.species_id, s0.species_id) AS species_id,
    ts.slice_start AS observation_date_start,
    ts.slice_end AS observation_date_end,
    -- 规则2、3、4：优先取sensor2的count_a，无则取sensor0的
    COALESCE(s2.count_a, s0.count_a) AS count_a,
    -- 规则2、3、4：处理count_b逻辑
    CASE
        WHEN s2.count_b IS NOT NULL THEN s2.count_b
        WHEN s2.count_a IS NOT NULL THEN NULL
        WHEN s0.count_a IS NOT NULL THEN 0
        ELSE NULL
    END AS count_b,
    -- 规则6：生成与observation_date区间无重叠的assertion_range
    COALESCE(
        (SELECT upper(ar) FROM sensor_data WHERE sensor_id=2 AND species_id=COALESCE(s2.species_id, s0.species_id) AND ar @> ts.slice_start),
        (SELECT upper(ar) FROM sensor_data WHERE sensor_id=0 AND species_id=COALESCE(s2.species_id, s0.species_id) AND ar @> ts.slice_start)
    ) AS assertion_range_end
FROM time_slices ts
LEFT JOIN sensor_2_matches s2 ON ts.slice_start = s2.slice_start AND ts.slice_end = s2.slice_end
LEFT JOIN sensor_0_matches s0 ON ts.slice_start = s0.slice_start AND ts.slice_end = s0.slice_end
    AND s0.species_id = COALESCE(s2.species_id, s0.species_id)
-- 规则5：过滤无数据的区间
WHERE COALESCE(s2.count_a, s0.count_a, s2.count_b) IS NOT NULL
-- 规则6：确保assertion_range与observation_date区间无重叠
AND assertion_range_end <= ts.slice_start;

性能优化要点 #

• 为sensor_data建立复合索引：CREATE INDEX idx_sensor_time ON sensor_data(sensor_id, species_id, observation_date, assertion_range);，加速时间切片的关联查询；
• 若数据量极大，可按observation_date对sensor_data做分区处理；
• 预先缓存时间边界点（如存入临时表），避免重复计算。

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