CODEWITHSUNDEEP
sqlpostgresql
khajlk
khajlk

Reputation: 861

How do I interpolate values of missing columns in PostgreSQL?

I have two tables i.e., tbl1 and tbl2 in my PostgreSQL 9.5 database. tbl1 contains data (timestamp without time zone) like:

Start_time              
1996-06-07 00:00:00     
1997-02-03 00:00:00     
2000-07-09 00:00:00

and tbl2 contains some values like this:

ID.    veh_1995    veh_2000    
1      200         425         
2      400         478         
3      150         300         
4      700         800         
5      900         1500

The following query interpolates the values of missing years (gap between years columns) for `tbl2:

 SELECT 
        veh_1995,
       (veh_1995 + (veh_2000 - veh_1995) * 0.2)::int AS veh_1996,
       (veh_1995 + (veh_2000 - veh_1995) * 0.4)::int AS veh_1997,
       (veh_1995 + (veh_2000 - veh_1995) * 0.6)::int AS veh_1998,
       (veh_1995 + (veh_2000 - veh_1995) * 0.8)::int AS veh_1999,
       veh_2000
 from tbl2

I need to modify above query such that:

Expected output:

Year          value
1996          ...
1997          ...
2000          ...

I would be thankful if someone could help me to modify above query to get my desired output?

Upvotes: 0

Views: 810

Answers (1)

rd_nielsen
rd_nielsen

Reputation: 2459

Is this more or less what you're looking for?

with yrs as (
    select
        t2.*,
        start_time,
        generate_series(cast(extract(year from start_time) as integer), 2000) as interp_yr
    from
        tbl2 as t2,
        tbl1 as t1
    where
        extract(year from start_time) between 1995 and 2000
    )
select
    iy.*,
    veh_1995 + (veh_2000 - veh_1995) * (interp_yr - 1995)/5 as interp_val
from
    yrs as iy;

Upvotes: 1

Related Questions