在PostgreSQL中以编程方式在多个列值之间进行插值

问题描述

我的PostgreSQL数据库中有一个表，其中包含1995年至2015年（相隔5年）售出的汽车数量数据：

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

我想通过编程的方式插值缺失年份和所有ID的值这样我就可以获得1995年至2015年的售车记录。有人能建议我怎么做吗？ 显示两个值之间的插值，并且通过 psql显示插值方法。但是，我正在寻找我不想使用存储过程或基于psql的方法的PostgreSQL Select查询解决方案。我在基于Windows 7的计算机上使用PostgreSQL 9.5版。

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.

推荐答案

由于插值很简单（您事先知道插值点在年份维度上的位置），因此查询相当简单：

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;

这篇关于在PostgreSQL中以编程方式在多个列值之间进行插值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！