user992627 user992627 - 1 month ago 8
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.

table_a

date

1/1/2013

2/1/2013

3/1/2013

4/1/2013

5/1/2013

6/1/2013

7/1/2013

8/1/2013

9/1/2013

10/1/2013

....



table_b

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

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