​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama

Using Analytics to do moving averages


We'll use this data to illustrate our examples


SQL> select x from t1

X

--

1

2

3

4

5

6

7

8

9


1. Select a moving SUM of the current row and the previous 2 rows


1 select x,sum(x) over(order by x rows 2 preceding) mov_total

2 from t1

SQL>/


X       MOV_TOTAL

===============

1               1

2               3

3               6

4               9

5              12

6              15

7              18

8              21

9              24


2. Select moving SUM of the current row, previous row and next row


1 select x

2 SUM(x) over(order by x rows between 1 preceding and 1 following) sumx

3 from t1

SQL>/


X              sumx

===============

1                3

2                6

3                9

4               12

5               15

6               18

7               21

8               24

9               17



3 Cumulative SUM


​select x,SUM(x) over(partition by x order by x,rowid range unbounded preceding) cumx

from t1


X            CUMX

=========

1               1

2               3

3               6

4              10

5              15

6              21

7              28

8              36

9              45


Note the order by in the partition has to be unique which is why we also bring out the rowid


4. Using date and time intervals


Note that you can also use time intervals for moving aggregates. Here's how you do one if you had a table with date and time columns in it and you wanted a centered aggregate around the current row and the previous and next days rows.


select s.cust_id, t.time_id,

to_char(SUM(amount_sold),'9,999,999,999') as sales,

to_char(AVG(SUM(amount_sold)) over(partition by s.cust_id order by t.time_id

range between interval '1' day preceding and interval '1' day following),'9,999,999,999') as centered_3_day_avg

from sales s, times t

where s.time_id =- t.time_id

groupo by s.cust_id,t.time_id

order by s.cust_id,t.time_id