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;
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
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
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;
In
the above syntax:
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.
|
Showing posts with label Teradata tutorial. Show all posts
Showing posts with label Teradata tutorial. Show all posts
Monday, 22 April 2013
ANSI and Teradata Function Equivalents - Part 5 - Moving AVG(MAVG)
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;
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
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;
In
the above syntax:
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;
In
Teradata Syntax:
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
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;
In
ANSI syntax:
Note that when we used RANK, we did not write any column in
the brackets following the keyword RANK.
|
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;
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;
PARTITION BY indicates the column(s) to be used for grouping. In the above example as the DEPARTMENTNO changes the cumulative sum is reset. |
Subscribe to:
Posts (Atom)