PostgreSQL：使用上一个值填充时间序列查询中的NULL值

Question

我有一个包含与时间相关信息的数据库。我想要列举出每分钟的值清单，如下所示：

12:00:00  3
12:01:00  4
12:02:00  5
12:03:00  5
12:04:00  5
12:05:00  3

但是当没有数据几分钟后，我得到了这样的结果：

12:00:00  3
12:01:00  4
12:02:00  5
12:03:00  NULL
12:04:00  NULL
12:05:00  3

我希望用前一个非空值来填充NULL值。

这个查询为每一分钟创建一个时间序列。然后将其与我的数据库中的数据连接起来。

我读到关于窗口函数用前一个非空值来填充NULL值的内容，但是我无法想出如何在这个查询中实现。有人能指导我一下吗？

我尝试了这个解决方案，但是NULL值仍然存在：PostgreSQL use value from previous row if missing

这是我的查询：

SELECT
    date,
    close
FROM generate_series(
  '2017-11-01 09:00'::timestamp,
  '2017-11-01 23:59'::timestamp,
  '1 minute') AS date
LEFT OUTER JOIN
 (SELECT
    date_trunc('minute', market_summary."timestamp") as day,
    LAST(current, timestamp) AS close
    FROM market_summary
  WHERE created_at >= '2017-11-01 09:00'
    AND created_at < '2017-11-01 23:59'
    GROUP BY day
 ) results
ON (date = results.day)
ORDER BY date

- Edwin Pauli

Answer 1

我发现下面这种方法更容易：

创建给定的数据样本：

WITH example (date,close) AS 
(VALUES 
    ('12:00:00',3),
    ('12:00:01',4),
    ('12:00:02',5),
    ('12:00:03',NULL),
    ('12:00:04',NULL), 
    ('12:00:05',3)
) 
SELECT * INTO temporary table market_summary FROM example;

查询以前填充值来填充NULL值

select 
    date, 
    close, 
    first_value(close) over (partition by grp_close) as corrected_close
from (
      select date, close,
             sum(case when close is not null then 1 end) over (order by date) as grp_close
      from   market_summary
) t

返回

date      | close | corrected_close
-----------------------------------
12:00:00  | 3     | 3
12:01:00  | 4     | 4
12:02:00  | 5     | 5
12:03:00  | NULL  | 5
12:04:00  | NULL  | 5
12:05:00  | 3     | 3

• 关闭：现有值
• 纠正后关闭：更正的值

- benjamin berhault

---

我发现你可能需要在你的第一行值（first_value）中按日期排序，否则可能仍然会出现空值。first_value(close) over (partition by grp_close order by date) as corrected_close - Bryan_D

Answer 2

我在这个页面找到了一个解决方案： http://www.postgresql-archive.org/lag-until-you-get-something-OVER-window-function-td5824644.html

CREATE OR REPLACE FUNCTION GapFillInternal( 
    s anyelement, 
    v anyelement) RETURNS anyelement AS 
$$ 
BEGIN 
  RETURN COALESCE(v,s); 
END; 
$$ LANGUAGE PLPGSQL IMMUTABLE; 

CREATE AGGREGATE GapFill(anyelement) ( 
  SFUNC=GapFillInternal, 
  STYPE=anyelement 
); 

postgres=# select id, natural_key, gapfill(somebody) OVER (ORDER BY 
natural_key, id) from lag_test; 
 id │ natural_key │ gapfill 
────┼─────────────┼───────── 
  1 │           1 │ 
  2 │           1 │ Kirk 
  3 │           1 │ Kirk 
  4 │           2 │ Roybal 
  5 │           2 │ Roybal 
  6 │           2 │ Roybal 
(6 rows) 

- Edwin Pauli

Answer 3

这里有一种方法：

select ms.*, ms_prev.close as lag_close
from (select ms.*,
             max(date) filter (where close is not null) over (order by date rows between unbounded preceding and 1 preceding) as dprev
      from market_summary ms
     ) ms left join
     market_summary ms_prev
     on ms_prev.dprev = ms.date
order by ms.date;

然而，如果你只有一两个连续的NULL，那么使用以下语法可能更简单：

select ms.*,
       coalesce(lag(ms.close, 1) over (order by date),
                lag(ms.close, 2) over (order by date),
                lag(ms.close, 3) over (order by date)
               ) as prev_close
from market_summary ms;

- Gordon Linoff

Answer 4

如何使用原始posgres进行此操作，使用一些自定义函数。

架构（PostgreSQL v12）

CREATE TABLE test (ts timestamp, email varchar, title varchar);
insert into test values
('2017-01-01', '[e-mail]', 'Old title'),
('2017-01-02', '[e-mail]', null),
('2017-01-03', '[e-mail]', 'New Title'),
('2017-01-04', '[e-mail]', null),
('2017-01-05', '[e-mail]', null),
('2017-01-06', '[e-mail]', 'Newer Title'),
('2017-01-07', '[e-mail]', null),
('2017-01-08', '[e-mail]', null);

 -- The built in function coalesce is not a aggregate function, nor is variadic.
 -- It might just be a compiler construct.
 -- So we define our own version
 CREATE FUNCTION f_coalesce(a anyelement, b anyelement) RETURNS anyelement AS '
    SELECT COALESCE(a,b);
 ' LANGUAGE SQL PARALLEL SAFE;
 -- Aggregate colasce that keeps first non-null value it sees
CREATE AGGREGATE agg_coalesce (anyelement)
(
    sfunc = f_coalesce,
    stype = anyelement
);

---

查询 #1

SELECT
    ts,
    email,

    array_agg(title) FILTER (WHERE title is not null ) OVER ( 
        order by ts desc ROWS BETWEEN current row and unbounded following 
    ) as title_array,
    (array_agg(title) FILTER (WHERE title is not null ) OVER ( 
        order by ts desc ROWS BETWEEN current row and unbounded following )
    )[1] as title,
    COALESCE(
        agg_coalesce(title) OVER ( 
            order by ts desc ROWS BETWEEN current row and unbounded following 
        ),
        (select title from test 
            where title is not null 
            and ts < '2017-01-02'
            order by ts desc limit 1 )
    )as title_locf 
from test
where ts >= '2017-01-02'
order by ts desc;

要点：

https://gist.github.com/DanielJoyce/cc9f80d4326b7cb40d07af2ffb069b74

- Daniel Joyce