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.

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.

ANSI and Teradata Function Equivalents - Part 3 - Cumulative SUM(CSUM) and CSUM with Grouping

Function: Cumulative Sum

Teradata Syntax: SELECT EMPLOYEEID, HIREDATE, SALARY, CSUM(SALARY,HIREDATE) FROM EMPLOYEE; Employeeid Hiredate Salary CSum(Salary,Hiredate) 8 1/1/1992 8000 8000 4 1/1/1996 4000 12000 7 6/1/1996 7000 19000 3 1/1/1997 3000 22000 6 3/1/1997 6000 28000 10 1/1/1998 10000 38000 2 1/1/1998 2000 40000 5 2/1/1998 5000 45000 1 1/1/1999 1000 46000 9 4/1/1999 9000 55000 11 5/1/1999 11000 66000 12 8/1/1999 12000 78000 144 4/20/2013 12345.11 90345.11 124 4/20/2013 12345.11 102690.2 In Teradata Syntax: The 1st column in the brackets indicates the column to be Summed Columns following in the 1st column in brackets indicate the columns used for sorting. BY default its ASCENDING.

ANSI syntax: Following is the way we write ANSI syntax to get same result: SELECT EMPLOYEEID,HIREDATE,SALARY, SUM(SALARY) OVER (ORDER BY HIREDATE ROWS UNBOUNDED PRECEDING) FROM EMPLOYEE;   Employeeid Hiredate Salary Cumulative 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 22000 6 3/1/1997 6000 28000 2 1/1/1998 2000 30000 10 1/1/1998 10000 40000 5 2/1/1998 5000 45000 1 1/1/1999 1000 46000 9 4/1/1999 9000 55000 11 5/1/1999 11000 66000 12 8/1/1999 12000 78000 144 4/20/2013 12345.11 90345.11 124 4/20/2013 12345.11 102690.2 In ANSI syntax: The column do be used for summing up is enclosed in the brackets following keyword SUM. Note that when we used RANK, we did not write any column in the brackets following the keyword RANK. Column used for ordering is specified by the 'ORDER BY' clause. Defaulting sequence is ASCENDING. ROWS UNBOUNDED PRECEEDING indicates that all the rows preceding the current row (including current row) are to be used for summation. This generate Cumulative Summation.

Function : Cumulative SUM with grouping

Teradata Syntax:   Following is the way we perform cumulative sum with grouping (also called cumulative sum with reset) SELECT EMPLOYEEID,HIREDATE,DEPARTMENTNO,SALARY, CSUM(SALARY,HIREDATE) FROM EMPLOYEE GROUP BY DEPARTMENTNO; Employeeid Hiredate DepartmentNo Salary CSum(Salary,Hiredate) 5 2/1/1998 100 5000 5000 1 1/1/1999 100 1000 6000 9 4/1/1999 100 9000 15000 6 3/1/1997 200 6000 6000 10 1/1/1998 200 10000 16000 2 1/1/1998 200 2000 18000 124 4/20/2013 200 12345.11 30345.11 7 6/1/1996 300 7000 7000 3 1/1/1997 300 3000 10000 11 5/1/1999 300 11000 21000 8 1/1/1992 400 8000 8000 4 1/1/1996 400 4000 12000 12 8/1/1999 400 12000 24000 144 4/20/2013 400 12345.11 36345.11 Group by indicates the column to be used for resetting the cumulative sum. When the column changes ( in above example as the department changes ) the accumulated value is discarded. Note that Column indicated by group by also indicates the major sorting sequence. The output is sorted by this column used for grouping.

ANSI syntax: Following is how we use ANSI syntax to perform cumulative sum with reset SELECT EMPLOYEEID,HIREDATE,DEPARTMENTNO,SALARY, SUM(SALARY) OVER (PARTITION BY DEPARTMENTNO ORDER BY HIREDATE ROWS UNBOUNDED PRECEDING) FROM EMPLOYEE; Employeeid Hiredate DepartmentNo Salary Cumulative Sum(Salary) 5 2/1/1998 100 5000 5000 1 1/1/1999 100 1000 6000 9 4/1/1999 100 9000 15000 6 3/1/1997 200 6000 6000 10 1/1/1998 200 10000 16000 2 1/1/1998 200 2000 18000 124 4/20/2013 200 12345.11 30345.11 7 6/1/1996 300 7000 7000 3 1/1/1997 300 3000 10000 11 5/1/1999 300 11000 21000 8 1/1/1992 400 8000 8000 4 1/1/1996 400 4000 12000 12 8/1/1999 400 12000 24000 144 4/20/2013 400 12345.11 36345.11 PARTITION BY indicates the column(s) to be used for grouping. In the above example as the DEPARTMENTNO changes the cumulative sum is reset.