Blog Archive

Sunday 21 April 2013

ANSI and Teradata Function Equivalents - Part 4 - Moving SUM(MSUM) and MSUM with Grouping





Function : MOVING SUM
Teradata Syntax:

Following is the Teradata  syntax for getting Moving Sum.
In the following syntax the we get moving sum with a window of 3

SELECT EMPLOYEEID,HIREDATE,SALARY,
MSUM(SALARY,3,HIREDATE)
FROM EMPLOYEE;

Employeeid
Hiredate
Salary
MSum(Salary,3,Hiredate)
8
1/1/1992
8000
8000
4
1/1/1996
4000
12000
7
6/1/1996
7000
19000
3
1/1/1997
3000
14000
6
3/1/1997
6000
16000
10
1/1/1998
10000
19000
2
1/1/1998
2000
18000
5
2/1/1998
5000
17000
1
1/1/1999
1000
8000
9
4/1/1999
9000
15000
11
5/1/1999
11000
21000
12
8/1/1999
12000
32000
144
4/20/2013
12345.11
35345.11
124
4/20/2013
12345.11
36690.22

In the above result we can see that the value of moving sum is equal to the sum of the salary in the current row and 2 rows above it.

In the above

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

  • 2nd argument indicates the window for summing rows. In above query the window width is 3

  • 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.



We can perform resets by making use of 'GROUP BY' as shown below

SELECT EMPLOYEEID,HIREDATE,SALARY,
MSUM(SALARY,5,HIREDATE)
FROM EMPLOYEE
GROUP BY DEPARTMENTNO;

The column used in the 'GROUP BY' also acts as the major sort sequence and the moving sum is reset when the value of department changes.


ANSI syntax:

Following is the way we get the same result as above using the ANSI syntax

 
SELECT EMPLOYEEID,HIREDATE,SALARY,
SUM(SALARY) OVER (ORDER BY HIREDATE ROWS 2 PRECEDING)
FROM EMPLOYEE;


Employeeid
Hiredate
Salary
Moving Sum(Salary)
8
1/1/1992
8000
8000
4
1/1/1996
4000
12000
7
6/1/1996
7000
19000
3
1/1/1997
3000
14000
6
3/1/1997
6000
16000
2
1/1/1998
2000
11000
10
1/1/1998
10000
18000
5
2/1/1998
5000
17000
1
1/1/1999
1000
16000
9
4/1/1999
9000
15000
11
5/1/1999
11000
21000
12
8/1/1999
12000
32000
144
4/20/2013
12345.11
35345.11
124
4/20/2013
12345.11
36690.22

In the above syntax:

  • The Column specified in the brackets following keyword SUM is the column used for summing.

  • 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 summing .

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

However when working with MSUM we need to write the 3 to achieve a window width of 3.


We can perform resets by making use of 'PARTITION BY' as shown below

SELECT EMPLOYEEID,HIREDATE,SALARY,
SUM(SALARY) OVER (PARTITION BY DEPARTMENTNO ORDER BY HIREDATE ROWS 2 PRECEDING)
FROM EMPLOYEE;

The column used in the 'PARTITION BY ' also acts as the major sort sequence and the moving sum is reset when the value of department changes.

No comments:

Post a Comment