sql - 在多列中向前填充 NULL 值

Question

我有一个包含一些时间序列数据的表。

             time              |  bid   |  ask   
-------------------------------+--------+--------
 2018-12-27 01:04:06.978456+00 | 1.7086 |       
 2018-12-27 01:04:07.006461+00 | 1.7087 |       
 2018-12-27 01:04:07.021961+00 |        | 1.7106
 2018-12-27 01:04:08.882591+00 | 1.7025 | 1.7156
 2018-12-27 01:04:09.374118+00 |        | 1.7106
 2018-12-27 01:04:09.39018+00  | 1.7087 | 1.7156      
 2018-12-27 01:04:15.793528+00 | 1.7045 | 
 2018-12-27 01:04:15.833545+00 | 1.7083 |       
 2018-12-27 01:04:15.893536+00 |        | 1.7096
 2018-12-27 01:04:16.258062+00 | 1.7045 | 1.7095
 2018-12-27 01:04:16.653573+00 | 1.7046 | 1.7148
 2018-12-27 01:04:16.665564+00 |        | 1.7097

我想前向填充 NULL 值，以便我的查询结果如下所示:

             time              |  bid   |  ask   
-------------------------------+--------+--------
 2018-12-27 01:04:06.978456+00 | 1.7086 |       
 2018-12-27 01:04:07.006461+00 | 1.7087 |       
 2018-12-27 01:04:07.021961+00 | 1.7087 | 1.7106
 2018-12-27 01:04:08.882591+00 | 1.7025 | 1.7156
 2018-12-27 01:04:09.374118+00 | 1.7025 | 1.7106
 2018-12-27 01:04:09.39018+00  | 1.7087 | 1.7156      
 2018-12-27 01:04:15.793528+00 | 1.7045 | 1.7156
 2018-12-27 01:04:15.833545+00 | 1.7083 | 1.7156      
 2018-12-27 01:04:15.893536+00 | 1.7083 | 1.7096
 2018-12-27 01:04:16.258062+00 | 1.7045 | 1.7095
 2018-12-27 01:04:16.653573+00 | 1.7046 | 1.7148
 2018-12-27 01:04:16.665564+00 | 1.7046 | 1.7097

我怎样才能做到这一点？

我正在使用带有 timescaledb 扩展的 postgresql 10

Answer 1

您可以使用几个窗口函数来完成此操作。在子查询中，我们将使用 count 来计算行数，不包括空值，直到当前行，按时间排序，这将使我们能够找出单独的组。从那里，我们可以只使用该组的 first_value，如果它还没有值的话。

select t,
       coalesce(bid, first_value(bid) OVER (partition by bid_group ORDER BY t)) as bid_filled,
       coalesce(ask, first_value(ask) OVER (partition by ask_group ORDER BY t)) as ask_filled
FROM (
  select t, ask, bid,
         count(bid) OVER (order by t) as bid_group,
         count(ask) OVER (order by t) as ask_group
  FROM test
) sub;
             t              | bid_filled | ask_filled
----------------------------+------------+------------
 2018-12-27 01:04:06.978456 |     1.7086 |
 2018-12-27 01:04:07.006461 |     1.7087 |
 2018-12-27 01:04:07.021961 |     1.7087 |     1.7106
 2018-12-27 01:04:08.882591 |     1.7025 |     1.7156
 2018-12-27 01:04:09.374118 |     1.7025 |     1.7106
 2018-12-27 01:04:09.39018  |     1.7087 |     1.7156
 2018-12-27 01:04:15.793528 |     1.7045 |     1.7156
 2018-12-27 01:04:15.833545 |     1.7083 |     1.7156
 2018-12-27 01:04:15.893536 |     1.7083 |     1.7096
 2018-12-27 01:04:16.258062 |     1.7045 |     1.7095
 2018-12-27 01:04:16.653573 |     1.7046 |     1.7148
 2018-12-27 01:04:16.665564 |     1.7046 |     1.7097