user992627 user992627 - 1 year ago 71
SQL Question

Query to complete empty spaces on table in Postgres

I need to create a view that given the first two tables (A and B), I get the result like in table C.

Basically I need to fill empty spaces on table B, using the first previous value available like shown below.

I've accomplished this using two loops on a procedure, but I'd like to try a solution using just selects statements.















date value

1/1/2013 10

3/1/2013 5

7/1/2013 30

10/1/2013 40

table_c - Desired result

date value

1/1/2013 10

2/1/2013 10

3/1/2013 5

4/1/2013 5

5/1/2013 5

6/1/2013 5

7/1/2013 30

8/1/2013 30

9/1/2013 30

10/1/2013 40

Does someone has any idea on how to accomplish this?

Answer Source

My sql is very rusty so I have this nagging feeling there's a better way, but what I came up with was to join against a sub-select that's a self join of table_b to make a new table b with date ranges. With that, it's easy to match table_a with the proper value.

I left a test on sqlfiddle so you can see the assumptions I made. This is the code below :

select date_format(,'%m/%d/%Y') as date, b.value as value 
from table_a as a join 
    (select as start, IFNULL(min(,'9999-12-31') as end, b1.value as value
            from table_b as b1 left outer join table_b as b2
            on <
            group by as b
on >= b.start and < b.end

The self join trims out the extra b2 entries with a group by and taking the min b2 date that's larger than b1's date. In the case of the very last entry, there is no b2 date larger so it ends up null; that I map to 12/31/9999 to be a really large date.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download