如何用单查询展开PostgreSQL多版本时间序列完整历史

在PostgreSQL中有一张存储时间序列数据的表，包含同一时间序列的多个发布版本。受历史修订和预测变更影响，同一数据点在不同版本中可能有不同值，无变更的数据点仅存储原始记录。

表结构 #

CREATE TABLE IF NOT EXISTS test_timeseries
(
    series_id integer NOT NULL,
    release_date timestamp with time zone NOT NULL,
    value_date timestamp with time zone NOT NULL,
    "value" numeric,
    CONSTRAINT test_timeseries_pkey PRIMARY KEY (series_id, release_date, value_date)
)

测试数据 #

INSERT INTO test_timeseries VALUES (1, '2023-02-01', '2023-01-01', 1);
INSERT INTO test_timeseries VALUES (1, '2023-02-01', '2023-02-01', 2);
INSERT INTO test_timeseries VALUES (1, '2023-02-01', '2023-03-01', 3);
INSERT INTO test_timeseries VALUES (1, '2023-02-01', '2023-04-01', 4);
INSERT INTO test_timeseries VALUES (1, '2023-03-01', '2023-04-01', 5);

现有单版本查询 #

已实现单版本完整历史查询，可合并未变更数据点与增量数据：

SELECT DISTINCT ON (series_id, value_date) 
    series_id,
    (SELECT max(release_date) AS max_release_date
           FROM test_timeseries WHERE DATE(release_date) <= '2023-03-01') AS release_date,
    value_date,
    "value"
FROM test_timeseries
ORDER BY series_id, value_date, release_date DESC;

需求 #

需要实现单查询展开所有版本的完整时间序列，支持多发布版、多series_id场景。

期望输出 #

(1, '2023-02-01', '2023-01-01', 1)
(1, '2023-02-01', '2023-02-01', 2)
(1, '2023-02-01', '2023-03-01', 3)
(1, '2023-02-01', '2023-04-01', 4)
(1, '2023-03-01', '2023-01-01', 1)
(1, '2023-03-01', '2023-02-01', 2)
(1, '2023-03-01', '2023-03-01', 3)
(1, '2023-03-01', '2023-04-01', 5)

解决方案 #

使用CROSS JOIN生成所有版本与数据点的组合，配合LATERAL JOIN获取每个组合对应的最新有效数据：

SELECT
    s.series_id,
    s.release_date,
    v.value_date,
    t."value"
FROM (
    -- 获取每个时间序列的所有唯一发布版本
    SELECT DISTINCT series_id, release_date
    FROM test_timeseries
) s
CROSS JOIN (
    -- 获取每个时间序列的所有唯一数据点日期
    SELECT DISTINCT series_id, value_date
    FROM test_timeseries
) v
WHERE s.series_id = v.series_id
LEFT JOIN LATERAL (
    -- 匹配当前数据点在当前版本及之前的最新记录
    SELECT "value"
    FROM test_timeseries t
    WHERE t.series_id = s.series_id
      AND t.value_date = v.value_date
      AND t.release_date <= s.release_date
    ORDER BY t.release_date DESC
    LIMIT 1
) t ON true
ORDER BY s.series_id, s.release_date, v.value_date;

逻辑说明 #

1. 子查询s提取每个series_id的所有发布版本，确保覆盖所有需要展开的版本。
2. 子查询v提取每个series_id的所有数据点日期，确保每个版本都包含完整的时间轴。
3. CROSS JOIN生成每个时间序列下，发布版本与数据点日期的所有组合。
4. LATERAL JOIN为每个组合找到对应的最新有效数据（发布时间不晚于当前版本的最新记录）。
5. 最终按series_id、release_date、value_date排序，得到符合要求的完整版本序列。

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