CODEWITHSUNDEEP
sqlpostgresql
Clinton Lam
Clinton Lam

Reputation: 727

Postgresql averaging neighbouring data into new table with sql

Here is the data structure. date is unique but could be missing (means jumping dates but not NULL) and is out of order. If the number of records is odd, just omit the last record.

date       | value | value2
-----------+-------+--------
2017-10-11 |    10 |    0.2
2017-10-13 |    22 |    0.3
2017-10-12 |     6 |    0.0
2017-10-14 |    20 |    0.6
         ...

I want to have the nearest two records to merge with the new value using the average of the value of record-to-merge. Using SQL only.

Expecting result.

date       | value | value2
-----------+-------+--------
2017-10-11 |     8 |    0.1
2017-10-13 |    21 |   0.45
         ...

2017-10-11 merged 2017-10-11 and 2017-10-12, i.e. value = AVG(10,6) = 8
2017-10-13 merged 2017-10-13 and 2017-10-14, i.e. value = AVG(22,20) = 21

Upvotes: 0

Views: 39

Answers (1)

Mike
Mike

Reputation: 2005

select min(date), avg(value), avg(value2)
  from (
    select *, (row_number() over(order by date)-1)/2 grp
      from Table1
  ) X
group by grp

Demo on sqlfiddle.com

Upvotes: 2

Related Questions