PostgreSQL如何基于当前表与历史表构建temporal table

现有两张表：

• 当前表：存储实体最新的状态、价格信息

location   status     price
A          sold       3            

• 历史表：采用EAV结构存储每个字段的变更日志，字符串类型字段（如status）的值存在oldval_str/newval_str，数值类型字段（如price）的值存在oldvar_num/newval_num，每条记录带变更时间created_at

location   field     oldval_str   newval_str   oldvar_num   newval_num   created_at
A          status    closed       sold         null         null         2022-06-01
A          status    listed       closed       null         null         2022-05-01
A          status    null         listed       null         null         2022-04-01
A          price     null         null         null         1            2022-04-01
A          price     null         null         1            2            2022-05-01
A          price     null         null         2            3            2022-06-01

需要用PostgreSQL纯SQL生成按变更时间切片的时态表，输出格式如下：

location   status     price   created_at
A          listed     1       2022-04-01
A          closed     2       2022-05-01
A          sold       3       2022-06-01

核心思路是先把EAV结构的变更日志按字段拆分，再按时间顺序累计填充每个时间点的字段最新值，最后对同时间点的多条变更记录去重即可，不需要动态SQL。

PostgreSQL 11+ 简洁写法（支持IGNORE NULLS窗口语法） #

WITH change_events AS (
    SELECT
        location,
        created_at,
        -- 按field匹配，提取对应字段的变更值，非当前字段的变更置为NULL
        CASE WHEN field = 'status' THEN newval_str END AS status_change,
        CASE WHEN field = 'price' THEN newval_num END AS price_change
    FROM history_table
),
filled_snapshot AS (
    SELECT
        location,
        created_at,
        -- 按时间排序，取当前行及之前最后一个非空的变更值，即为该时间点的字段有效值
        last_value(status_change) IGNORE NULLS OVER (
            PARTITION BY location ORDER BY created_at
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS status,
        last_value(price_change) IGNORE NULLS OVER (
            PARTITION BY location ORDER BY created_at
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS price
    FROM change_events
)
-- 同一时间点可能有多条字段变更记录，去重后得到最终快照
SELECT DISTINCT location, status, price, created_at
FROM filled_snapshot
ORDER BY location, created_at;

低版本PostgreSQL兼容写法（不依赖IGNORE NULLS） #

用LATERAL关联逐个查找每个时间点之前最近一次的字段变更值，逻辑更直观，兼容所有PG版本：

WITH all_snapshot_time AS (
    -- 提取所有发生过变更的时间点，作为时态表的行维度
    SELECT DISTINCT location, created_at FROM history_table
)
SELECT
    t.location,
    s_status.newval_str AS status,
    s_price.newval_num AS price,
    t.created_at
FROM all_snapshot_time t
-- 查找当前时间点之前最近一次status变更值
LEFT JOIN LATERAL (
    SELECT newval_str
    FROM history_table
    WHERE location = t.location
      AND field = 'status'
      AND created_at <= t.created_at
    ORDER BY created_at DESC
    LIMIT 1
) s_status ON TRUE
-- 查找当前时间点之前最近一次price变更值
LEFT JOIN LATERAL (
    SELECT newval_num
    FROM history_table
    WHERE location = t.location
      AND field = 'price'
      AND created_at <= t.created_at
    ORDER BY created_at DESC
    LIMIT 1
) s_price ON TRUE
ORDER BY t.location, t.created_at;

• 上述两种写法都支持不同字段变更时间不一致的场景，不需要强制要求同一时间点同时更新所有字段，能正确还原任意时间点的全字段快照。
• 你遇到的「根据field动态匹配对应列」的问题，本质是EAV模型转固定宽表的行转列场景，由于最终输出的时态表列是固定的，不需要使用动态SQL，直接按字段名写匹配逻辑即可。
• 如果需要把当前表的最新状态也纳入时态表（比如覆盖最后一次变更到当前时间的区间），只需要把当前表的数据补充到change_events或all_snapshot_time的CTE中，给created_at赋值为最新变更时间或当前时间即可。

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