0

I have the following table in Teradata:

 ranked | data_val 
-------- ----------
 1      | 100
 2      |  30
 2      |  20
 2      |  70

I want the following table, where avg_val is the rolling average of data_val values ordered by increasingly ascending ranked values:

 ranked | avg_val 
------ ---------
 1      | 100
 2      |  55

I try using:

SELECT 
    ranked
  , AVERAGE(data)val) OVER (
      PARTITION BY NULL 
      ORDER BY ranked ASC
      ROWS UNBOUNDED PRECEDING
      ) AS avg_val
  FROM tbl
;

but I get this:

 ranked | avg_val 
------ ---------
 1      | 100
 2      |  65
 2      |  50
 2      |  55

which is not what I want.

How do I return the desired output?

Sam Y
  • 30
  • 3

1 Answers1

1

How about aggregation with window functions?

select ranked,
       (sum(sum(data_val)) over (order by ranked rows between unbounded preceding and current row) /
        sum(count(*)) over (order by ranked rows between unbounded preceding and current row)
       )
from t
group by ranked;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Needs a ROWS UNBOUNDED PRECEDING in there a couple times, but it works. ```select ranked, sum(sum(data_val)) over (order by ranked ROWS UNBOUNDED PRECEDING) / sum(count(*)) over (order by ranked ROWS UNBOUNDED PRECEDING) from tbl group by ranked; ``` – Sam Y May 15 '19 at 19:02
  • @Sam . . . Arrgh. Hasn't Teradata fixed that yet. I added the windowing clause. – Gordon Linoff May 15 '19 at 19:22
  • No worries. I don't have a have enough stack overflow reputation to show the upvote yet, but rest assured, you have been upvoted. – Sam Y May 15 '19 at 20:04
  • 1
    Not defaulting to `RANGE unbounded preceding` has historical reasons (and `range` is not supported in Teradata), but explixit `ROWS unbounded preceding` is recommended anyway, because of the possible better performance (and a potentially unexpected result for a non-unique order by) – dnoeth May 15 '19 at 20:28
  • 1
    @dnoeth . . . Oh. The default should be `range` but Teradata doesn't support that. So, it requires the windowing clause to express the `rows` construct. That actually makes some sense. – Gordon Linoff May 15 '19 at 20:50