如何在两个表的最小和最大时间戳之间生成序列(Postgresql)

Question

我有两个表，其中包含经常更新的天气数据。表A的数据间隔为10分钟，表B的数据间隔为1小时。

表A(已实现天气)

tbody> <<tr>

observationtime	temperature
17/02/21 00:00	9
17/02/21 00:10	9
17/02/21 00:20	9
17/02/21 00:30	9
…	…
17/02/21 03:00	9

在我看来不需要generate_series():

演示:db<在小提琴>

SELECT
COALESCE(r.observationtime, fc.observationtime) as observationtime,     -- 3
r.temperature as realized_temperature,
fc.temperature as forecasted_temperature
FROM (
SELECT DISTINCT ON (date_trunc('hour', observationtime))                -- 1
*
FROM r
ORDER BY date_trunc('hour', observationtime), observationtime
) r
FULL JOIN fc ON r.observationtime = fc.observationtime                      -- 2
ORDER BY 1

1. 首先从realized表中每小时提取第一条记录。这可以使用DISTINCT ON来完成，它返回一个有序组的第一个记录。这里你的组是小时(与date_trunc()你可以转换hh:10-hh:50值到完整的小时加入组)。
2. 使用FULL JOIN:这将连接表，即使没有匹配的时间戳
3. 使用COALESCE()返回列表中第一个非NULL的值。因此，如果有realized温度，将取此值，否则取预测值。