Select last non-null value for given partition - Postgres 10

Question

I have a table with the following structure:

date city cntry free occ
2018年08月13日 16:30:00 2 12 5
2018年08月13日 16:30:00 2 12 0 
2018年08月13日 14:30:00 2 12 1 
2018年08月13日 11:00:00 2 12 2 
2018年08月12日 13:00:00 2 12 1 
2018年08月12日 13:00:00 2 12 4
2018年08月12日 08:00:00 2 12 3
2018年08月12日 08:00:00 2 12 2 
2018年08月10日 15:30:00 2 12 4

• date is timestamp without timezone
• the rest are numbers

I want to get the previous non-null value for a given city_id/country_id combo, for both the free and occupied:

date city cntry free occ
2018年08月13日 16:30:00 2 12 0 5
2018年08月13日 14:30:00 2 12 1 4
2018年08月13日 11:00:00 2 12 2 4
2018年08月12日 13:00:00 2 12 1 4
2018年08月12日 08:00:00 2 12 2 3
2018年08月10日 15:30:00 2 12 1 4

• partition is by city_id, country_id, order by date
• for the first date of each partition, if there is a NULL values, it becomes zero
• for an existing partition, it uses the previous value
• I'd like the ability to filter by city_id and country_id AFTERWARDS

Basically I'm merging two timelines and I want to keep the previous values (free/occupied) for each partition of <city_id, country_id>.

I tried playing with window functions to no avail. I can get my data for either free values or occupied values, but not both.

How can I accomplish this?

• Erwin Brandstetter: Can there be multiple consecutive NULL values? Please always disclose your version of Postgres. And ideally a CREATE TABLE script defining data types and constraints. It makes it so much easier for us to test.
• user1822: The easiest way is probably to create your own window function for this. E.g. this example: wiki.postgresql.org/wiki/First/last_%28aggregate%29 or this: stackoverflow.com/a/8354094

Answer 1

You can try to use MAX function with coalesce to make it.

Schema (PostgreSQL v9.6)

CREATE TABLE T(
 date date,
 city_id int,
 country_id int,
 free int,
 occupied int
);
insert into T values ('2017-01-01',2,3,null, 2);
insert into T values ('2017-01-02',2,3,4, null);
insert into T values ('2017-01-02',2,3,null, 5);
insert into T values ('2017-01-04',3,4,2, null);

---

Query #1

SELECT
 date,
 city_id,
 country_id,
 coalesce(MAX(free),0) free ,
 coalesce(MAX(occupied),0) occupied
FROM T
GROUP BY date,city_id,country_id
order by date;
| date | city_id | country_id | free | occupied |
| ------------------------ | ------- | ---------- | ---- | -------- |
| 2017年01月01日T00:00:00.000Z | 2 | 3 | 0 | 2 |
| 2017年01月02日T00:00:00.000Z | 2 | 3 | 4 | 5 |
| 2017年01月04日T00:00:00.000Z | 3 | 4 | 2 | 0 |

---

View on DB Fiddle

• Miguel Ping: Sorry, I had some typos in my example. I updated it. Consequently, MAX won't work because I need to get the previous value which has a different date - it won't fall on the same bucket used by the group by.