Average over hard to define partition

Question

I have this table:

create table t (value int, dt date);

 value |     dt     
-------+------------
    10 | 2012-10-30
    15 | 2012-10-29
  null | 2012-10-28
  null | 2012-10-27
     7 | 2012-10-26

And I want this output:

 value |     dt     
-------+------------
    10 | 2012-10-30
     5 | 2012-10-29
     5 | 2012-10-28
     5 | 2012-10-27
     7 | 2012-10-26

I want the null values, as well as the one previous non null value, to be replaced by the average of the previous not null value when the table is ordered by date descending. In this example the value 15 is the previous not null value of the next two nulls. So 15 / 3 = 5.

SQL Fiddle

Answer 1

I found a surprisingly simple solution:

SELECT max(value) OVER (PARTITION BY grp)
      / count(*)  OVER (PARTITION BY grp) AS value
      ,dt
FROM   (
   SELECT *, count(value) OVER (ORDER BY dt DESC) AS grp
   FROM   t
   ) a;

-> sqlfiddle

Since count() ignores NULL values, you can use a running count (default in window function) to group values quickly (-> grp).

Every group has exactly one non-null value, so we can use min / max / sum to get to the same result in another window function on top of that. Divide by the number of members (count(*) this time, to count NULL values!) in the grp and we are done.