How to fill null values in PostgreSQL with grouping and ordering

Question

Lets say I have the following data:

+---------------------+---------+---------------------+
| date                | tokenId | last_price          |
|---------------------+---------+---------------------|
| 2021-07-01 00:00:00 | 1       | <null>              |
| 2021-08-01 00:00:00 | 1       | 5.0                 |
| 2021-09-01 00:00:00 | 1       | <null>              |
| 2021-10-01 00:00:00 | 1       | <null>              |
| 2021-11-01 00:00:00 | 1       | 4.6000000000000005  |
| 2021-12-01 00:00:00 | 1       | <null>              |
| 2022-01-01 00:00:00 | 1       | <null>              |
| 2022-02-01 00:00:00 | 1       | <null>              |
| 2022-03-01 00:00:00 | 1       | <null>              |
| 2022-04-01 00:00:00 | 1       | <null>              |
| 2022-05-01 00:00:00 | 1       | <null>              |
| 2021-07-01 00:00:00 | 18      | 0.09200000000000001 |
| 2021-08-01 00:00:00 | 18      | <null>              |
| 2021-09-01 00:00:00 | 18      | <null>              |
| 2021-10-01 00:00:00 | 18      | <null>              |
| 2021-11-01 00:00:00 | 18      | 7.6000000000000005  |
| 2021-12-01 00:00:00 | 18      | <null>              |
| 2022-01-01 00:00:00 | 18      | 15.200000000000001  |
| 2022-02-01 00:00:00 | 18      | <null>              |
| 2022-03-01 00:00:00 | 18      | <null>              |
| 2022-04-01 00:00:00 | 18      | <null>              |
| 2022-05-01 00:00:00 | 18      | <null>              |
+---------------------+---------+---------------------+

How could I fill the null rows with the last price of each token from the previous rows like that:

+---------------------+---------+---------------------+
| date                | tokenId | last_price          |
|---------------------+---------+---------------------|
| 2021-07-01 00:00:00 | 1       | <null>              |
| 2021-08-01 00:00:00 | 1       | 5.0                 |
| 2021-09-01 00:00:00 | 1       | 5.0                 |
| 2021-10-01 00:00:00 | 1       | 5.0                 |
| 2021-11-01 00:00:00 | 1       | 4.6000000000000005  |
| 2021-12-01 00:00:00 | 1       | 4.6000000000000005  |
| 2022-01-01 00:00:00 | 1       | 4.6000000000000005  |
| 2022-02-01 00:00:00 | 1       | 4.6000000000000005  |
| 2022-03-01 00:00:00 | 1       | 4.6000000000000005  |
| 2022-04-01 00:00:00 | 1       | 4.6000000000000005  |
| 2022-05-01 00:00:00 | 1       | 4.6000000000000005  |
| 2021-07-01 00:00:00 | 18      | 0.09200000000000001 |
| 2021-08-01 00:00:00 | 18      | 0.09200000000000001 |
| 2021-09-01 00:00:00 | 18      | 0.09200000000000001 |
| 2021-10-01 00:00:00 | 18      | 0.09200000000000001 |
| 2021-11-01 00:00:00 | 18      | 7.6000000000000005  |
| 2021-12-01 00:00:00 | 18      | 7.6000000000000005  |
| 2022-01-01 00:00:00 | 18      | 15.200000000000001  |
| 2022-02-01 00:00:00 | 18      | 15.200000000000001  |
| 2022-03-01 00:00:00 | 18      | 15.200000000000001  |
| 2022-04-01 00:00:00 | 18      | 15.200000000000001  |
| 2022-05-01 00:00:00 | 18      | 15.200000000000001  |
+---------------------+---------+---------------------+

I have found some good answer about the similar question here, but I can't understand how I can use it with additional grouping by tokenId field.

Cetin Basoz · Accepted Answer · 2022-05-11 17:56:45Z

0

You could use lateral. ie:

select t1.date, t1.tokenid, coalesce(t1.last_price, p.last_price) last_price 
from myTable t1
left join lateral(select last_price 
                        from myTable t2
                        where t1.tokenId = t2.tokenId and t1.date > t2.date
                           and t2.last_price is not null
                        order by t2.date desc
                        limit 1) p on true
order by t1.tokenId, t1.date;

answered May 11, 2022 at 17:56

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

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

Comments

SQLpro · Accepted Answer · 2022-05-11 17:25:34Z

0

One possible solution is :

SELECT a_date, tokenId, last_price,
       (SELECT last_price
        FROM   A_TABLE 
        WHERE  a_date = (SELECT MAX(a_date) 
                         FROM   A_TABLE AS A 
                         WHERE  A.a_date <= T.a_date AND last_price IS NOT NULL))
FROM   A_TABLE AS T

Some other uses windowing functions

answered May 11, 2022 at 17:25

SQLpro

5,3121 gold badge9 silver badges19 bronze badges

Collectives™ on Stack Overflow

How to fill null values in PostgreSQL with grouping and ordering

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related