How to fill forward time series data in Postgres

Question

I am looking to join three tables together and fill forward null values on the resulting table. Three tables:

Table 1 (raw.fb_historical_data) - this is the main table on which I would like to join the other two on to. Each row of this table is related to one or more rows in the other two tables through a combination of columns id, clk and timestamp (mkt_id and row_id in the other tables).

+---------------------+-----+-----+--------------+
|      timestamp      | clk | id  | some_columns |
+---------------------+-----+-----+--------------+
| 2016-06-19 06:11:13 | 123 | 126 | a            |
| 2016-06-19 06:16:13 | 124 | 127 | b            |
| 2016-06-19 06:21:13 | 234 | 126 | c            |
| 2016-06-19 06:41:13 | 456 | 127 | d            |
| ...                 | ... | ... | ...          |
+---------------------+-----+-----+--------------+

Table 2 (raw.fb_runner_changes) - this table essentially gives price changes for a wide range of different markets

+---------------------+--------+--------+-------+
|      timestamp      | row_id | mkt_id | price |
+---------------------+--------+--------+-------+
| 2016-06-19 06:11:13 | 123    | 126    | 1     |
| 2016-06-19 06:21:13 | 123    | 126    | 2     |
| 2016-06-19 06:41:13 | 123    | 126    | 3     |
| 2016-06-06 18:54:06 | 124    | 127    | 1     |
| 2016-06-06 18:56:06 | 124    | 127    | 2     |
| 2016-06-06 18:57:06 | 124    | 127    | 3     |
| ...                 | ...    | ...    | ...   |
+---------------------+--------+--------+-------+

Table 3 (raw.fb_runners) - a table with extra information about market changes that I would like to join

+---------------------+--------+--------+---------------+
|      timestamp      | row_id | mkt_id | other_columns |
+---------------------+--------+--------+---------------+
| 2016-06-19 06:15:13 | 234    | 126    | ab            |
| 2016-06-19 06:31:13 | 234    | 126    | cd            |
| 2016-06-19 06:56:13 | 234    | 126    | ef            |
| 2016-06-06 18:54:06 | 456    | 127    | gh            |
| 2016-06-06 18:56:06 | 456    | 127    | jk            |
| 2016-06-06 18:57:06 | 456    | 127    | lm            |
| ...                 | ...    | ...    | ...           |
+---------------------+--------+--------+---------------+

Essentially what I want to do is fill NULL information forward (ordered by timestamp) while grouping by market id.

So far, I have tried to join the tables together using

SELECT *
FROM raw.fb_historical_data AS h
LEFT JOIN raw.fb_runner_changes AS rc
    ON rc.row_id = h.clk 
    AND rc.timestamp = h.timestamp
    AND rc.mkt_id = h.id
LEFT JOIN raw.fb_runners AS r
    ON r.row_id = h.clk
    AND r.timestamp = h.timestamp
    AND r.mkt_id = h.id

Which has worked as intended, though now there are nulls in the resulting dataset which i'd like to fill in with the last available value for that market.

David דודו Markovitz · Accepted Answer · 2020-03-10 22:05:18Z

4

With some of the other SQL dialects, fill forward could be done using the window function last_value in combination with the instruction ignore nulls.
Since this is not supported in PostgreSQL (check the note at the bottom of this page), we are using a 2 steps work-around.

select  ts, val, val_seq, min(val) over (partition by val_seq) val_fill_fw
from   (select  ts, val, count(val) over(order by ts) as val_seq
        from    t
        )  t

-

+----+----------+---------+-------------+
| ts |   val    | val_seq | val_fill_fw |
+----+----------+---------+-------------+
|  1 | (null)   |       0 | (null)      |
|  2 | (null)   |       0 | (null)      |
|  3 | hello    |       1 | hello       |
|  4 | (null)   |       1 | hello       |
|  5 | (null)   |       1 | hello       |
|  6 | darkness |       2 | darkness    |
|  7 | my       |       3 | my          |
|  8 | (null)   |       3 | my          |
|  9 | old      |       4 | old         |
| 10 | (null)   |       4 | old         |
| 11 | (null)   |       4 | old         |
| 12 | (null)   |       4 | old         |
| 13 | friend   |       5 | friend      |
| 14 | (null)   |       5 | friend      |
+----+----------+---------+-------------+

SQL Fiddle

answered Mar 10, 2020 at 22:05

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Vladimir Emelianov Over a year ago

Going to give this a try now. Just out of any interest, is there any reason why this wasn't implemented in postgres? Seems like a very useful function.

Vladimir Emelianov Over a year ago

Just tried this, it doesn't seem to work as it doesn't take into account the grouping - I wanted to fill forward the time series for each group, so imagine I have several groups of your above table and each grouping has different values in the 'val' column.

David דודו Markovitz Over a year ago

If you need it by groups just add the relevant column(s) to the partition by clause of each window function (add it if not already exist)

Vladimir Emelianov Over a year ago

Thank you David! Looks like I got it by adding the required grouping column into the 'order by' and 'partition' expressions

MikeB2019x · Accepted Answer · 2022-02-03 18:33:57Z

0

This seems to correctly do 'forward fill' in postgres. However I am a postgres newbie so I would appreciate feedback if it's wrong.

DROP TABLE IF EXISTS example;
create temporary table example(id int, str text, val integer);
insert into example values
(1, 'a', null),
(1, null, 1),
(2, 'b', 2),
(2,null ,null );

select * from example

select id, (case
            when str is null
            then lag(str,1) over (order by id)
            else str
            end) as str,
            (case
            when val is null
            then lag(val,1) over (order by id)
            else val
            end) as val
from example

answered Feb 3, 2022 at 18:33

MikeB2019x

1,3073 gold badges14 silver badges37 bronze badges

Collectives™ on Stack Overflow

How to fill forward time series data in Postgres

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related