CODEWITHSUNDEEP
postgresqlinterpolation
khajlk
khajlk

Reputation: 861

Programmatically interpolating among multiple column values in PostgreSQL

I have a table in my PostgreSQL db containing data of number of vehicles sold from 1995 to 2015 (with a gap of 5 years) like this:

ID.    veh_1995    veh_2000    veh_2005    veh_2010    veh_2015
1      200         425         526         725         623
2      400         478         1000        1500        2000
3      150         300         400         700         1100
4      700         800         1000        2000        5000
5      900         1500        1600        2000        2500

I would like to progammatically interpolate the values for the missing years and for all IDs so that I have number of sold vehicles' records from 1995 to 2015. Could anyone suggest me how to do that? This question shows interpolation between two values and this link show interpolation through psql approach. However, I am looking for the PostgreSQL Select query solution I don't want to go for the stored procedure or psql based approach. I am using PostgreSQL version 9.5 on Windows 7 based machine.

Upvotes: 0

Views: 623

Answers (1)

Patrick
Patrick

Reputation: 32316

Since your interpolation is simple (you know beforehand where the interpolation points are located on the year dimension), the query is rather straightforward:

SELECT id,
       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,
       ... -- add similar lines to interpolate other years
       veh_2015
FROM my_table;

Upvotes: 2

Related Questions