PostgreSQL: fill NULL values in timeserie query with previous value

by

I found the following method easier:

Create the given data sample:

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;

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

Return

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
  • close: existing value
  • corrected_close: corrected value

More Related Contents:

  1. Select rows which are not present in other table
  2. Optimize GROUP BY query to retrieve latest row per user
  3. How to implement a many-to-many relationship in PostgreSQL?
  4. Aggregate columns with additional (distinct) filters
  5. Check if value exists in Postgres array
  6. How to use ANY instead of IN in a WHERE clause?
  7. Is there a shortcut for SELECT * FROM?
  8. sql statement error: “column .. does not exist”
  9. What is the difference between single quotes and double quotes in PostgreSQL?
  10. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  11. sql ORDER BY multiple values in specific order?
  12. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  13. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  14. Best way to count rows by arbitrary time intervals
  15. PostgreSQL sequence based on another column
  16. Sorting null values after all others, except special
  17. Reference alias in WHERE clause
  18. NOT NULL constraint over a set of columns
  19. How to compare the current row with next and previous row in PostgreSQL?
  20. ERROR: functions in index expression must be marked IMMUTABLE in Postgres
  21. Why postgres returns unordered data in select query, after updation of row?
  22. postgresql – sql – count of `true` values
  23. Continuing a transaction after primary key violation error
  24. What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
  25. PostgreSQL where all in array
  26. PostgreSQL – fetch the rows which have the Max value for a column in each GROUP BY group
  27. Conditional SQL count
  28. Parallel unnest() and sort order in PostgreSQL
  29. Temporal database design, with a twist (live vs draft rows)
  30. How to bulk insert only new rows in PostreSQL

Leave a Comment