postgresql - SQL Data to interpolate/extrapolate -

if have table keeps running average of kw usage @ temperature, , wanted kw usage temperature has not been recorded before, how either

(a) 2 data points above or 2 points below temperature extrapolate.

(b) closest data above , below temperature interpolate

the table temperatures looks this

         column          |       type       | modifiers | storage | stats target |  description -------------------------+------------------+-----------+---------+--------------+---------------  temperature_io_id       | integer          | not null  | plain   |              |  temperature_station_id  | integer          | not null  | plain   |              |  temperature_value       | integer          | not null  | plain   |              | in fahrenheit  temperature_current_kw  | double precision | not null  | plain   |              |  temperature_value_added | integer          | default 1 | plain   |              |  temperature_kw_year_1   | double precision | default 0 | plain   |              |  "temperatures_pkey" primary key, btree (temperature_io_id, temperature_station_id, temperature_value) 

(a) proposed solution

this bit easier think. query order rows temperature value > or < temperature im going for, limit results 2? give me 2 closest values above or below temperature. of course order have descending , ascending make sure right side of items.

select * temperatures temperature_value > actualtemp , temperature_io_id = actual_io_id order temperature_value limit 2; 

i think similar above, limit 1 , 2 queries, 1 > , other <. feel done better though?

edit - sample data

 temperature_io_id | temperature_station_id | temperature_value | temperature_current_kw | temperature_value_added | temperature_kw_year_1 -------------------+------------------------+-------------------+------------------------+-------------------------+-----------------------              18751 |                    151 |                35 |                   26.1 |                       2 |                     0              18752 |                    151 |                35 |                   30.5 |                       2 |                     0              18753 |                    151 |                35 |                   15.5 |                       2 |                     0              18754 |                    151 |                35 |                   12.8 |                       2 |                     0              18643 |                    151 |                35 |                   4.25 |                       2 |                     0              18644 |                    151 |                35 |                  22.15 |                       2 |                     0              18645 |                    151 |                35 |                   7.45 |                       2 |                     0              18646 |                    151 |                35 |                    7.5 |                       2 |                     0              18751 |                    151 |                34 |                  25.34 |                       5 |                     0              18752 |                    151 |                34 |                  30.54 |                       5 |                     0              18753 |                    151 |                34 |                  15.48 |                       5 |                     0              18754 |                    151 |                34 |                  13.08 |                       5 |                     0              18643 |                    151 |                34 |                    4.3 |                       5 |                     0              18644 |                    151 |                34 |                  22.44 |                       5 |                     0              18645 |                    151 |                34 |                   7.34 |                       5 |                     0              18646 |                    151 |                34 |                   7.54 |                       5 |                     0 

you can nearest rows using:

select t.* temperatures t order abs(temperature_value - actual_temperature) asc limit 2  

or, better idea in case, union:

(select t.*  temperatures t  temperature_value <= actual_temperature  order temperature_value desc  limit 1 ) union (select t.*  temperatures t  temperature_value >= actual_temperature  order temperature_value asc  limit 1 )  

this version better because returns 1 row if temperature in table. case union , duplicate removal useful.

next use conditional aggregation information needed. uses short-cut, assuming kw increases temperature:

select min(temperature_value) mintv, max(temperature_value) maxtv,        min(temperature_current_kw) minck, max(temperature_current_kw) maxck ((select t.*        temperatures t        temperature_value <= actual_temperature        order temperature_value desc        limit 1       ) union       (select t.*        temperatures t        temperature_value >= actual_temperature        order temperature_value asc        limit 1       )       ) t; 

finally, arithmetic weighted average:

select (case when maxtv = mintv minkw              else minkw + (actual_temperature - mintv) * ((maxkw - minkw) / (maxtv - mintv))         end) (select min(temperature_value) mintv, max(temperature_value) maxtv,              min(temperature_current_kw) minkw, max(temperature_current_kw) maxkw       ((select t.*              temperatures t              temperature_value <= actual_temperature              order temperature_value desc              limit 1             ) union             (select t.*              temperatures t              temperature_value >= actual_temperature              order temperature_value asc              limit 1             )             ) t      ) t;