Blog Archive

Monday 22 April 2013

ANSI and Teradata Function Equivalents - Part 5 - Moving AVG(MAVG)



Function :Moving  Average
Teradata Syntax:

Following is the Teradata way of  performing moving average.
In the below example we have moving average window as 7


SELECT EMPLOYEEID,DEPARTMENTNO,HIREDATE,SALARY,
MAVG(SALARY,7,HIREDATE)
FROM EMPLOYEE;

Employeeid
DepartmentNo
Hiredate
Salary
MAvg(Salary,7,Hiredate)
8
400
1/1/1992
8000
8000
4
400
1/1/1996
4000
6000
7
300
6/1/1996
7000
6333.33
3
300
1/1/1997
3000
5500
6
200
3/1/1997
6000
5600
10
200
1/1/1998
10000
6333.33
2
200
1/1/1998
2000
5714.29
5
100
2/1/1998
5000
5285.71
1
100
1/1/1999
1000
4857.14
9
100
4/1/1999
9000
5142.86
11
300
5/1/1999
11000
6285.71
12
400
8/1/1999
12000
7142.86
144
400
4/20/2013
12345.11
7477.87
124
200
4/20/2013
12345.11
8955.75

In the above result we can see that the value of moving avg is equal to the avg of the salary in the current row and 6 rows above it.
When there are less than 6 rows above the current rows, average is calculated based upon the existing rows.

In the above

  • 1st column in the brackets of the MAVG indicate the column to be summed.

  • 2nd argument indicates the window for averaging rows. In above query the window width is 7

  • Column names following the window width are the columns that are used for ordering . Default sequence is ascending. In above HIREDATE is used for ordering.

ANSI syntax:

Following is the ANSI syntax of getting similar result as above

 
SELECT EMPLOYEEID,DEPARTMENTNO,HIREDATE,SALARY,
AVG(SALARY) OVER (ORDER BY HIREDATE  ROWS 6 PRECEDING)
FROM EMPLOYEE;

Employeeid
DepartmentNo
Hiredate
Salary
Moving Avg(Salary)
8
400
1/1/1992
8000
8000
4
400
1/1/1996
4000
6000
7
300
6/1/1996
7000
6333.33
3
300
1/1/1997
3000
5500
6
200
3/1/1997
6000
5600
10
200
1/1/1998
10000
6333.33
2
200
1/1/1998
2000
5714.29
5
100
2/1/1998
5000
5285.71
1
100
1/1/1999
1000
4857.14
9
100
4/1/1999
9000
5142.86
11
300
5/1/1999
11000
6285.71
12
400
8/1/1999
12000
7142.86
124
200
4/20/2013
12345.11
7477.87
144
400
4/20/2013
12345.11
8955.75

In the above syntax:

  • The Column specified in the brackets following keyword AVG is the column used for Averaging.

  • Column specified by the 'ORDER BY' indicates the column used for ordering. Default is ascending.

  • COLUMN specified by the 'ROWS N PRECEDING' indicates the number of rows above the current rows to be used for averaging .

Note that this number indicates the number of rows above the current row.
Thus in the above example to have a window of 7 , we need to use 'ROWS 6 PRECEDING'.

However when working with MAVG we need to write the 7 to achieve a window width of 7.

No comments:

Post a Comment