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 bydate
- 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?
CREATE TABLE
script defining data types and constraints. It makes it so much easier for us to test.