I have per ID monthly cumulative counters, where in some months the counters are missing. I would like to fill these missing values up by the monthly average based on the period of missing months.
My table looks like:
| ID | Month | Counter |
|---|---|---|
| AAA | 2023-09 | 1000 |
| AAA | 2023-10 | – |
| AAA | 2023-11 | – |
| AAA | 2023-12 | 4000 |
| BBB | 2022-11 | 2000 |
| BBB | 2022-02 | – |
| BBB | 2023-01 | – |
| BBB | 2022-02 | – |
| BBB | 2022-03 | 4000 |
What I would like to have:
| ID | Month | Counter |
|---|---|---|
| AAA | 2023-09 | 1000 |
| AAA | 2023-10 | 2000 |
| AAA | 2023-11 | 3000 |
| AAA | 2023-12 | 4000 |
| BBB | 2022-11 | 2000 |
| BBB | 2022-12 | 2500 |
| BBB | 2023-01 | 3000 |
| BBB | 2022-02 | 3500 |
| BBB | 2022-03 | 4000 |
How can this be done in PostgreSQL?