PostgreSQL：使用先前的值填充时间查询中的NULL值 [英] PostgreSQL: fill NULL values in timeserie query with previous value

问题描述

我有一个数据库，其中包含与时间相关的信息。我想要一个包含每分钟值的列表。像这样：

I have a database with time related information. I want a list with values for every minute. Like this:

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

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

But when there is no data for some minutes I got a result like this:

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

我想用先前的NOT NULL值填充NULL值

I want to fill the NULL value with the previous NOT NULL value.

此查询每分钟创建一个时钟。然后将其连接到我数据库中的数据。

This query creates a timeserie for every minute. Then it joins this to the data in my database.

我阅读了一些有关窗口函数的知识，以使用先前的NOT NULL值填充NULL值，但是我不知道如何在此查询中实现该功能。有人可以将我推向正确的方向吗？

I read something about window functions to fill the NULL value with the previous NOT NULL value, but I can't figure out how to implement this in this query. Can someone push me in the good direction?

我尝试了此解决方案，但NULL值仍然存在：
PostgreSQL如果丢失则使用前一行的值

I tried this solution, but the NULL values are still there: 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

推荐答案

我发现以下方法更容易：

创建给定的数据样本：

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值

Query to fill NULL values with the previous filled value

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

• 关闭：现有价值


• corrected_close：校正后的值



这篇关于PostgreSQL：使用先前的值填充时间查询中的NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！