4

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?

3

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

  • 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. – Miguel Ping Sep 17 '18 at 22:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.