PostgreSQL：基于两气象表时间范围生成连续序列的关联实现

核心实现逻辑 #

完全匹配预设的时间轴生成思路，补齐10分钟粒度实测表的对齐处理环节，整体分为三个可落地的执行步骤：

• 确定时间轴边界
  • 起点：提取10分钟粒度实测表（表A）的最小观测时间，向下截断对齐到最近整小时（例如原始最小时间为2024-06-10 13:42:17，对齐后起点为2024-06-10 13:00:00）
  • 终点：提取1小时粒度预报表（表B）的最大观测时间作为序列终点，因表B本身为整小时粒度，无需额外截断
• 按1小时间隔生成连续无断档的时间轴
  基于确定的起止边界，通过数据库递归CTE生成逐小时累加的连续时间序列，避免因某张表数据缺失出现时间断点
• 预处理双表数据后关联匹配对应温度值
  因表A为10分钟粒度，无法直接和整小时时间轴匹配，需要先将表A的观测时间截断对齐到整小时粒度，按业务规则提取对应温度值；表B粒度与时间轴完全匹配，可直接关联取值

可直接复用的SQL实现示例 #

（以PostgreSQL语法为例，MySQL等其他数据库可替换对应的时间截断、递归语法）

WITH RECURSIVE time_bound AS (
  -- 计算时间轴的起止边界
  SELECT
    -- 表A最小时间向下对齐到整小时作为起点
    date_trunc('hour', MIN(ta.observationtime)) AS series_start,
    -- 表B最大时间作为终点
    MAX(tb.observationtime) AS series_end
  FROM (SELECT MIN(observationtime) AS observationtime FROM table_a) ta
  CROSS JOIN (SELECT MAX(observationtime) AS observationtime FROM table_b) tb
),
hourly_time_series AS (
  -- 递归生成逐小时连续时间序列
  SELECT series_start AS observationtime FROM time_bound
  UNION ALL
  SELECT observationtime + INTERVAL '1 hour'
  FROM hourly_time_series, time_bound
  WHERE observationtime < series_end
),
-- 预处理表A：对齐到整小时，取每个整小时点对应的最早实测温度
realized_data AS (
  SELECT DISTINCT ON (hour_aligned_time)
    hour_aligned_time AS observationtime,
    temperature AS realized_temperature
  FROM (
    SELECT
      date_trunc('hour', observationtime) AS hour_aligned_time,
      temperature,
      observationtime
    FROM table_a
  ) raw_a
  -- 每个整小时分组内，取最靠近整小时点的实测值
  ORDER BY hour_aligned_time, observationtime ASC
),
-- 预处理表B：本身为整小时粒度，直接映射字段名
forecast_data AS (
  SELECT
    observationtime,
    temperature AS forecasted_temperature
  FROM table_b
)
-- 以连续时间轴为主表关联两表，缺失值自动填充为NULL
SELECT
  hts.observationtime,
  rd.realized_temperature,
  fd.forecasted_temperature
FROM hourly_time_series hts
LEFT JOIN realized_data rd ON hts.observationtime = rd.observationtime
LEFT JOIN forecast_data fd ON hts.observationtime = fd.observationtime
ORDER BY hts.observationtime;

落地注意事项 #

• 避免直接关联非等粒度表：若直接将10分钟粒度的表A与整小时时间轴关联，会出现单小时匹配多条数据的重复问题，必须先完成粒度对齐聚合
• 持续更新场景的性能优化：因两张表为增量更新模式，无需每次全量生成时间轴，可通过记录上次生成的最大时间点，每次仅增量追加最新的时间分段及匹配数据，大幅降低查询耗时
• 匹配规则可灵活调整：表A的温度取值逻辑可根据业务需求替换为小时内温度均值、小时末最新值等规则，仅需调整realized_data CTE内的聚合逻辑即可
• 边界可根据业务调整：若需要覆盖所有已存在数据的时段，时间轴终点可修改为取表A、表B最大观测时间的较大值，避免遗漏最新的实测数据段

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