How to calculate moving sums within variable size windows?

Database
Enthusiast

How to calculate moving sums within variable size windows?

Hi!

I am trying to calculate moving sums and row counts in a query, to do further arithmetic on the said values. For a range of 3 days, for example, I would sum the values given in the rows for those 3 days, and then divide them by the amount of rows in that same time period.

In Oracle, the syntax would be:

      sum(score) over(order by responsedate range between interval '2' day preceding and current row)/

      sum(n) over(order by responsedate range between interval '2' day preceding and current row) as rolling_score

to clarify with an example, if data was:

responsedt    score

2013-09-04    1

2013-09-04    1

2013-09-04    1

2013-09-04    -1

2013-09-03    0

2013-09-03    1

2013-09-02    -1

2013-09-02    0

2013-09-02    1

...the sum(score) and sum(n) would result in a dataset of:

responsedt    score    sum(n)    sum(score)

2013-09-04    1        

2013-09-04    1        

2013-09-04    1        

2013-09-04    -1        4        2

2013-09-03    0        

2013-09-03    1        2        1

2013-09-02    -1        

2013-09-02    0        

2013-09-02    1        3        0

...with the result being 3/9 = 0.3333...

Trying the Oracle syntax, which looks pretty ANSI to me, I get "expected the word RESET or ')' after ORDER BY clause."

I have experimented now with range_N, where the intervals were not accepted, partitioning by response date, which compiles but does not give me what I want...

Please help. Googling did not help, also, related questions in TD forums have not resulted in an answer I would have seen as answering the question.

Thx,

5 REPLIES
Enthusiast

Re: How to calculate moving sums within variable size windows?

..and the TD version is 13.10

Enthusiast

Re: How to calculate moving sums within variable size windows?

Ulrich? Dieter? Anyone?

Teradata Employee

Re: How to calculate moving sums within variable size windows?

Hi,

if you have "2 days preceeding" task, then you can actually switch to fixed size windows.    

First do a subquery to get one row per day, grouping by "responsedt" column, and summing up the "score" and "n" columns.    

Then, if you need to fill the missing dates, then add an outer join with a full calendar.    That is, if you don't have data for say 2013-09-01 - then the question is - "2 days preceeding" mean 2013-08-30 and 2013-08-31, OR only 2013-08-31 and NULLs for missing 2013-09-01.

Then, use simple fixed-length windows functions - ROWS BETWEEN 2 PRECEEDING AND CURRENT ROW.

Hope this helps.   Please let me know if you find a better solution.:)

Regards,

Vlad.

Senior Apprentice

Re: How to calculate moving sums within variable size windows?

The RANGE syntax is Standard SQL but unfortunately it's not implemented in Teradata.

So Vlad's solution is complicated, but probably the only one (if you actually need this for each row in your result set you have to do it in a Derived Table and the join back). There just one possible enhancement, in TD13.10 you might get any missing dates using EXPAND ON instead of a join to sys_calendar 

Dieter

Enthusiast

Re: How to calculate moving sums within variable size windows?

Thanks guys, what confused me was that I too thought range should be accepted by TeraData - I am in a middle of a conversion project from Oracle and have run into some hurdles with both ANSI SQL as well as Oracle analytical features not being implemented in TD.

MEDIAN...dense rank...dataset aliases in merges...;) thanks to Dieter on posts on those. Got them up.

Will try these solutions. Lets see what happens.