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

aggregatepostgresqlwindow functions

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?

Best Answer

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-01T00:00:00.000Z | 2       | 3          | 0    | 2        |
| 2017-01-02T00:00:00.000Z | 2       | 3          | 4    | 5        |
| 2017-01-04T00:00:00.000Z | 3       | 4          | 2    | 0        |

View on DB Fiddle

Related Question