How to fill null values in PostgreSQL with grouping and ordering-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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;