PostgreSQL中如何将多列时间步数据展开为逐时间步的状态行？

固定时间步列实现（示例中4个时间步的场景可直接用） #

假设你的原表名为time_series_table，直接执行以下查询即可得到预期结果：

SELECT
  t.id,
  t.timestep1,
  CASE WHEN s.step >= 2 THEN t.timestep2 ELSE NULL END AS timestep2,
  CASE WHEN s.step >= 3 THEN t.timestep3 ELSE NULL END AS timestep3,
  CASE WHEN s.step >= 4 THEN t.timestep4 ELSE NULL END AS timestep4
FROM
  time_series_table t
  CROSS JOIN generate_series(1, 4) AS s(step)
WHERE
  CASE s.step
    WHEN 1 THEN t.timestep1 IS NOT NULL
    WHEN 2 THEN t.timestep2 IS NOT NULL
    WHEN 3 THEN t.timestep3 IS NOT NULL
    WHEN 4 THEN t.timestep4 IS NOT NULL
  END
ORDER BY t.id, s.step;

逻辑说明 #

• generate_series(1,4)会给原表的每一行生成4条带步长标记的临时记录，对应4个时间阶段
• CASE语句根据当前步长判断是否返回对应时间步的原值，步长不足时返回NULL，实现累计效果
• WHERE条件会自动过滤原表中对应时间步为NULL的无效行，比如示例中id为b的记录timestep4为NULL，就不会生成步长为4的行

动态时间步列实现（适用于时间步列不固定、会动态新增的场景） #

如果后续会新增timestep5、timestep6等列，不想每次修改查询语句，可以用动态SQL生成视图：

DO $$
DECLARE
  case_list text;
  where_case text;
  max_step int;
BEGIN
  -- 自动读取当前表的最大时间步序号
  SELECT max(regexp_replace(column_name, 'timestep', '')::int) INTO max_step
  FROM information_schema.columns 
  WHERE table_name = 'time_series_table' 
    AND column_name LIKE 'timestep%';

  -- 拼接查询字段的条件逻辑
  SELECT string_agg(
    format('CASE WHEN s.step >= %s THEN t.%I ELSE NULL END AS %I', idx, col, col),
    ', ' ORDER BY idx
  ) INTO case_list
  FROM (
    SELECT 
      column_name AS col,
      regexp_replace(column_name, 'timestep', '')::int AS idx
    FROM information_schema.columns 
    WHERE table_name = 'time_series_table' 
      AND column_name LIKE 'timestep%'
    ORDER BY idx
  ) t;

  -- 拼接过滤条件
  SELECT string_agg(
    format('WHEN %s THEN t.%I IS NOT NULL', idx, col),
    ' ' ORDER BY idx
  ) INTO where_case
  FROM (
    SELECT 
      column_name AS col,
      regexp_replace(column_name, 'timestep', '')::int AS idx
    FROM information_schema.columns 
    WHERE table_name = 'time_series_table' 
      AND column_name LIKE 'timestep%'
    ORDER BY idx
  ) t;

  -- 生成累计状态视图，后续直接查询time_series_cumulative即可
  EXECUTE format('
    CREATE OR REPLACE VIEW time_series_cumulative AS
    SELECT t.id, %s
    FROM time_series_table t
    CROSS JOIN generate_series(1, $1) AS s(step)
    WHERE CASE s.step %s END
    ORDER BY t.id, s.step
  ', case_list, where_case) USING max_step;
END $$;

执行完后直接查询SELECT * FROM time_series_cumulative就能得到结果，新增时间步列后重新执行一遍上面的动态SQL即可更新视图。

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