我在Postgres中有一个包含时间序列数据的表。该表包含同一时间序列的多个版本(发行版)。由于历史修订和预测更改,同一数据点在不同版本中可能具有不同的值。对于那些在新版本中没有差异的数据点,我们只存储原始数据点。
下面是一个简单的例子:
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)
在这种情况下,我设法编写了一个查询,通过将未更改的数据点与增量(新版本中不同的数据点)组合起来,检索任何release_date的特定完整历史。我可以通过以下查询来实现:
SELECT DISTINCT ON (series_id, value_date)
series_id,
(SELECT max(release_date) AS max_release_date
FROM silver.test_timeseries WHERE DATE(release_date) <= '2023-03-01') AS release_date,
value_date,
"value"
FROM silver.test_timeseries
ORDER BY series_id, value_date, release_date DESC;
现在我需要帮助的是,如果我想在一个查询中扩展所有的时间序列,那么对于这个特定的情况,我想返回2023-02-01
和2023-03-01
的完整时间序列。
注:提供的数据只是一个简单的例子。该查询需要对两个以上的版本进行查询,并且可以有多个series_id,并且所有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)
1条答案
按热度按时间sbdsn5lh1#
我注意到,如果对每个不同的release_date重复执行原始查询并使用UNION返回所有结果行,那么您所追求的结果与您可能得到的结果相同。
您可以更简洁地执行此操作,方法是使用一个公共表表达式来标识每个不同的发布日期,并使用一个从该表达式到test_timeseries表的全联接来实现类似的结果:
DB fiddle在这里:https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/0