Teradata Analytical Window Functions

Pradeep

Teradata Analytical Window Functions

Teradata provides many ordered analytical window functions which can be used to fulfil various user analytical requirements.

Some of the commonly used analytical functions

Sum
Count
Avg
Min
Max
MSum
MAvg
MDiff
CSum

Click here to check more window analytical functions

Syntax: Basic syntax of Ordered analytical window function


analytical_function_name([column_name]) OVER ([PARTITION BY COLUMN1] [ORDER BY COLUMN2] [ROWS BETWEEN n FOLLOWING|PRECEDING(start window) AND m FOLLOWING|PRECEDING|CURRENT ROW)(end window)])

Ordered analytical functions can be used in the following database objects

Views
INSERT/SELECT
Macros

Restriction on ordered analytical functions

Cannot be used on clob/blob datatypes.
Cannot be used in Sub-queries.
Cannot be used in WHERE clause but rows can be filtered using QUALIFY clause for these function.
Distinct cannot be used along with analytical functions.

Window features: These are most important part of ordered analytical functions and should be understood properly in order to effectively use them.

PARTITION BY: This is optional and can be used to further categorized data and perform analysis within sub categories. Example: In order to calculate sum of salary for each Department along with individual detail, partition by dept number is required.


Dept No Emp No  salary  Total_Department_Salary
------  ------  ------  ----------------------
10      11      15000   20000
10      12      5000    20000
20      22      10023   30023
20      23      10000   30023
20      24      10000   30023
30      31      13230   23230
30      32      10000   23230

ORDER BY: This is optional and can be used to specify order in which rows should be processed.
ROWS: This is also optional and should be used when user want to peek into other rows data to compute current row value.
```
ROWS BETWEEN n FOLLOWING|PRECEDING(start window) AND m FOLLOWING|PRECEDING|CURRENT ROW(end window)
```
ROW attribute require user to specify start window and end window of row. PRECEDING, FOLLOWING and CURRENT ROW values are available to peek into previous rows, following rows and current row respectively. User can either specify number(e.g 1,2,3) to check that many number of previous / following rows or specify UNBOUNDED to check all the previous / following rows.

Analytical functions: Click here to get the table and data used in the below examples

SUM: This function is used to add values of particular column within specified pattern / number of rows.


Syntax: 
SUM(column_name) OVER ([PARTITION BY COLUMN1] [ORDER BY COLUMN2] [ROWS BETWEEN n FOLLOWING|PRECEDING(start window) AND m FOLLOWING|PRECEDING|CURRENT ROW)(end window)])

Example 1: Total sum of salary within department against each of employee of that department


SELECT emp_name,salary,dept_no,
SUM(salary) over (PARTITION BY dept_no ORDER BY emp_name ) As Total_Dept_salary
FROM tutorial_db.employee;

emp_name    salary  dept_no     Total_Dept_salary
---------  -------  -------  --------------------
CLARK      2450.00      100               8750.00
MILLER     1300.00      100               8750.00
PRADEEP    5000.00      100               8750.00
ADAMS      1100.00      200              10875.00
FORD       3000.00      200              10875.00
JONES      2975.00      200              10875.00
SCOTT      3000.00      200              10875.00
SMITH       800.00      200              10875.00

Example 2: Cumulative salary within each department


SELECT emp_name,salary,dept_no,
SUM(salary) over (PARTITION BY dept_no ORDER BY emp_name  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) As Custom_Dept_salary
FROM tutorial_db.employee; --Window here: all the previous rows  and current row

emp_name    salary      dept_no    Cumulative_salary
--------- --------  -----------  --------------------
CLARK      2450.00          100               2450.00
MILLER     1300.00          100               3750.00
PRADEEP    5000.00          100               8750.00
ADAMS      1100.00          200               1100.00
FORD       3000.00          200               4100.00
JONES      2975.00          200               7075.00
SCOTT      3000.00          200              10075.00
SMITH       800.00          200              10875.00
ALLEN      1600.00          300               1600.00
BLAKE      2850.00          300               4450.00
JAMES       950.00          300               5400.00
MARTIN     1250.00          300               6650.00
TURNER     1500.00          300               8150.00
WARD       1250.00          300               9400.00

COUNT: This function is used to count number of rows within specified pattern / number of rows.


Syntax: 
COUNT(column_name) OVER ([PARTITION BY COLUMN1] [ORDER BY COLUMN2] [ROWS BETWEEN n FOLLOWING|PRECEDING(start window) AND m FOLLOWING|PRECEDING|CURRENT ROW)(end window)])

Example 1: Total count of employees within department against each of employee of that department.


SELECT emp_name,salary,dept_no,
COUNT(emp_name) over (PARTITION BY dept_no) As Total_Emps
FROM tutorial_db.employee;

emp_name    salary  dept_no  Total_Emps
---------  -------  -------  ----------
PRADEEP    5000.00      100         3
CLARK      2450.00      100         3
MILLER     1300.00      100         3
JONES      2975.00      200         5
SMITH       800.00      200         5
FORD       3000.00      200         5
ADAMS      1100.00      200         5
SCOTT      3000.00      200         5

Example 2: Number of employees left for processing within department.


SELECT emp_name,salary,dept_no,
COUNT(emp_name) over (PARTITION BY dept_no ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING ) As Remaining_cnt
FROM tutorial_db.employee
order by dept_no,Remaining_cnt desc; --Window here: 1 following row to current row to all the following rows

emp_name     salary      dept_no  Remaining_cnt
--------- ---------  -----------  -------------
MILLER      1300.00          100              2
CLARK       2450.00          100              1
PRADEEP     5000.00          100              0
SCOTT       3000.00          200              4
ADAMS       1100.00          200              3
FORD        3000.00          200              2
SMITH        800.00          200              1
JONES       2975.00          200              0
BLAKE       2850.00          300              5
WARD        1250.00          300              4
JAMES        950.00          300              3
ALLEN       1600.00          300              2
TURNER      1500.00          300              1
MARTIN      1250.00          300              0

AVG: This function is used to find average value of particular column within specified pattern / number of rows.


Syntax: 
AVG(column_name) OVER ([PARTITION BY COLUMN1] [ORDER BY COLUMN2] [ROWS BETWEEN n FOLLOWING|PRECEDING(start window) AND m FOLLOWING|PRECEDING|CURRENT ROW)(end window)])

Example 1: Average salary within department against each of employee of that department


SELECT emp_name,salary,dept_no,
Avg(salary) over (PARTITION BY dept_no ORDER BY emp_name ) As Total_Dept_salary
FROM tutorial_db.employee;

emp_name    salary      dept_no     Avg_Dept_salary
--------- --------  -----------  --------------------
CLARK      2450.00          100               2916.67
MILLER     1300.00          100               2916.67
PRADEEP    5000.00          100               2916.67
ADAMS      1100.00          200               2175.00
FORD       3000.00          200               2175.00
JONES      2975.00          200               2175.00
SCOTT      3000.00          200               2175.00
SMITH       800.00          200               2175.00
ALLEN      1600.00          300               1566.67
BLAKE      2850.00          300               1566.67
JAMES       950.00          300               1566.67
MARTIN     1250.00          300               1566.67
TURNER     1500.00          300               1566.67
WARD       1250.00          300               1566.67

Example 2: Cumulative Average salary within each department


SELECT emp_name,salary,dept_no,
AVG(salary) over (PARTITION BY dept_no ORDER BY emp_name  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) As Cum_avg_Dept_salary
FROM tutorial_db.employee; --Window here: all the previous rows  and current row


emp_name   salary      dept_no    Cum_avg_Dept_salary
-------- --------  -----------  --------------------
CLARK     2450.00          100               2450.00
MILLER    1300.00          100               1875.00
PRADEEP   5000.00          100               2916.67
ADAMS     1100.00          200               1100.00
FORD      3000.00          200               2050.00
JONES     2975.00          200               2358.33
SCOTT     3000.00          200               2518.75
SMITH      800.00          200               2175.00
ALLEN     1600.00          300               1600.00
BLAKE     2850.00          300               2225.00
JAMES      950.00          300               1800.00
MARTIN    1250.00          300               1662.50
TURNER    1500.00          300               1630.00
WARD      1250.00          300               1566.67

MIN: This function is used to find minimum value of particular column within specified pattern / number of rows.


Syntax: 
MIN(column_name) OVER ([PARTITION BY COLUMN1] [ORDER BY COLUMN2] [ROWS BETWEEN n FOLLOWING|PRECEDING(start window) AND m FOLLOWING|PRECEDING|CURRENT ROW)(end window)])

Example 1: Minimum salary within department against each of employee of that department


SELECT emp_name,salary,dept_no,
Min(salary) over (PARTITION BY dept_no ORDER BY emp_name ) As Min_Dept_salary
FROM tutorial_db.employee;

emp_name  salary      dept_no        Min_Dept_salary
-------- -------  -----------  --------------------
CLARK    2450.00          100               1300.00
MILLER   1300.00          100               1300.00
PRADEEP  5000.00          100               1300.00
ADAMS    1100.00          200                800.00
FORD     3000.00          200                800.00
JONES    2975.00          200                800.00
SCOTT    3000.00          200                800.00
SMITH     800.00          200                800.00
ALLEN    1600.00          300                950.00
BLAKE    2850.00          300                950.00
JAMES     950.00          300                950.00
MARTIN   1250.00          300                950.00
TURNER   1500.00          300                950.00
WARD     1250.00          300                950.00

Example 2: Cumulative Minimum salary within each department


SELECT emp_name,salary,dept_no,
Min(salary) over (PARTITION BY dept_no ORDER BY emp_name  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) As Cum_Min_Dept_salary
FROM tutorial_db.employee; --Window here: all the previous rows  and current row

emp_name    salary      dept_no    Cum_Min_Dept_salary
--------- --------  -----------  --------------------
CLARK      2450.00          100               2450.00
MILLER     1300.00          100               1300.00
PRADEEP    5000.00          100               1300.00
ADAMS      1100.00          200               1100.00
FORD       3000.00          200               1100.00
JONES      2975.00          200               1100.00
SCOTT      3000.00          200               1100.00
SMITH       800.00          200                800.00
ALLEN      1600.00          300               1600.00
BLAKE      2850.00          300               1600.00
JAMES       950.00          300                950.00
MARTIN     1250.00          300                950.00
TURNER     1500.00          300                950.00
WARD       1250.00          300                950.00

MAX: This function is used to find maximum value of particular column within specified pattern / number of rows.


Syntax: 
MAX(column_name) OVER ([PARTITION BY COLUMN1] [ORDER BY COLUMN2] [ROWS BETWEEN n FOLLOWING|PRECEDING(start window) AND m FOLLOWING|PRECEDING|CURRENT ROW)(end window)])

Example 1: Maximum salary within department against each of employee of that department


SELECT emp_name,salary,dept_no,
MAX(salary) over (PARTITION BY dept_no ORDER BY emp_name ) As Max_Dept_salary
FROM tutorial_db.employee;

emp_name   salary      dept_no     Max_Dept_salary
--------- -------  -----------  --------------------
CLARK     2450.00          100               5000.00
MILLER    1300.00          100               5000.00
PRADEEP   5000.00          100               5000.00
ADAMS     1100.00          200               3000.00
FORD      3000.00          200               3000.00
JONES     2975.00          200               3000.00
SCOTT     3000.00          200               3000.00
SMITH      800.00          200               3000.00
ALLEN     1600.00          300               2850.00
BLAKE     2850.00          300               2850.00
JAMES      950.00          300               2850.00
MARTIN    1250.00          300               2850.00
TURNER    1500.00          300               2850.00
WARD      1250.00          300               2850.00

Example 2: Cumulative Maximum salary within each department


SELECT emp_name,salary,dept_no,
MAX(salary) over (PARTITION BY dept_no ORDER BY emp_name  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) As Cum_max_salary
FROM tutorial_db.employee; --Window here: all the previous rows  and current row
 
emp_name  salary      dept_no    Cum_max_salary
-------- -------  -----------  --------------------
CLARK    2450.00          100               2450.00
MILLER   1300.00          100               2450.00
PRADEEP  5000.00          100               5000.00
ADAMS    1100.00          200               1100.00
FORD     3000.00          200               3000.00
JONES    2975.00          200               3000.00
SCOTT    3000.00          200               3000.00
SMITH     800.00          200               3000.00
ALLEN    1600.00          300               1600.00
BLAKE    2850.00          300               2850.00
JAMES     950.00          300               2850.00
MARTIN   1250.00          300               2850.00
TURNER   1500.00          300               2850.00
WARD     1250.00          300               2850.00

MSUM: This function is used to find moving sum within specified number of previous rows.


Syntax: 
MSUM(aggregation_column, n, order_by_column[,other_order_by_columns])

Here n specify window feature "ROWS n-1 PRECEDING"

Example 1: Moving total salary against each of employee considering only current and previous employee


SELECT emp_name, salary, dept_no, MSUM(salary,2,emp_name)   
FROM tutorial_db.employee;  -- Here 2 specify "ROWS 1 PRECEDING"

emp_name  salary      dept_no  MSum(salary,2,emp_name)
-------- -------  -----------  -----------------------
ADAMS    1100.00          200                  1100.00
ALLEN    1600.00          300                  2700.00
BLAKE    2850.00          300                  4450.00
CLARK    2450.00          100                  5300.00
FORD     3000.00          200                  5450.00
JAMES     950.00          300                  3950.00
JONES    2975.00          200                  3925.00
MARTIN   1250.00          300                  4225.00
MILLER   1300.00          100                  2550.00
PRADEEP  5000.00          100                  6300.00
SCOTT    3000.00          200                  8000.00
SMITH     800.00          200                  3800.00
TURNER   1500.00          300                  2300.00
WARD     1250.00          300                  2750.00

MAVG: This function is used to find moving average within specified number of previous rows.


Syntax: 
MSUM(aggregation_column, n, order_by_column[,other_order_by_columns])

Here n specify window feature "ROWS n-1 PRECEDING"

Example 1: Moving salary against each of employee considering only current and previous employee


SELECT emp_name, salary, dept_no, MAVG(salary,2,emp_name)   
FROM tutorial_db.employee; -- Here 2 specify "ROWS 1 PRECEDING"

emp_name   salary      dept_no  MAvg(salary,2,emp_name)
-------- --------  -----------  -----------------------
ADAMS     1100.00          200                  1100.00
ALLEN     1600.00          300                  1350.00
BLAKE     2850.00          300                  2225.00
CLARK     2450.00          100                  2650.00
FORD      3000.00          200                  2725.00
JAMES      950.00          300                  1975.00
JONES     2975.00          200                  1962.50
MARTIN    1250.00          300                  2112.50
MILLER    1300.00          100                  1275.00
PRADEEP   5000.00          100                  3150.00
SCOTT     3000.00          200                  4000.00
SMITH      800.00          200                  1900.00
TURNER    1500.00          300                  1150.00
WARD      1250.00          300                  1375.00

MDIFF: This function is used to find moving difference between the given column for the current row and the nth preceding row


Syntax: 
MDIFF(aggregation_column, n, order_by_column[,other_order_by_columns])

Here 'n' specify nth previous row

Example 1: Moving salary difference for current row and previous to previous row value


SELECT emp_name,salary,dept_no,
MDIFF(salary,2,emp_name desc)
FROM tutorial_db.employee;

emp_name   salary      dept_no  MDiff(salary,2,emp_name DESC)
-------- --------  -----------  -----------------------------
WARD      1250.00          300                              ?
TURNER    1500.00          300                              ?
SMITH      800.00          200                        -450.00
SCOTT     3000.00          200                        1500.00
PRADEEP   5000.00          100                        4200.00
MILLER    1300.00          100                       -1700.00
MARTIN    1250.00          300                       -3750.00
JONES     2975.00          200                        1675.00
JAMES      950.00          300                        -300.00
FORD      3000.00          200                          25.00
CLARK     2450.00          100                        1500.00
BLAKE     2850.00          300                        -150.00
ALLEN     1600.00          300                        -850.00
ADAMS     1100.00          200                       -1750.00

Note: Some value are null because they are not satisfying window condition as there is no previous to previous row to get value for difference.

CSUM: This is equivalent to SUM window function as its aggregation that specifies "ROWS UNBOUNDED PRECEDING" as window feature.


Syntax: 
CSUM(aggregation_column, order_by_column[,other_order_by_columns])

Example 1: Cumulative salary against each of the employee


SELECT emp_name,salary,dept_no,
CSUM(salary,emp_name)
FROM tutorial_db.employee;

emp_name  salary      dept_no  CSum(salary,emp_name)
-------- -------  -----------  ---------------------
ADAMS    1100.00          200                1100.00
ALLEN    1600.00          300                2700.00
BLAKE    2850.00          300                5550.00
CLARK    2450.00          100                8000.00
FORD     3000.00          200               11000.00
JAMES     950.00          300               11950.00
JONES    2975.00          200               14925.00
MARTIN   1250.00          300               16175.00
MILLER   1300.00          100               17475.00
PRADEEP  5000.00          100               22475.00
SCOTT    3000.00          200               25475.00
SMITH     800.00          200               26275.00
TURNER   1500.00          300               27775.00
WARD     1250.00          300               29025.00

Note: The use of CSUM is discouraged by Teradata. Teradata suggest to use SUM instead of CSUM. CSUM is retained just for backward compatibility with old applications.

QUALIFY: This clause can be used to filter rows based on Analytical functions.


Syntax: 
QUALIFY (analytical_function([column_name]) OVER ([PARTITION BY COLUMN1] [ORDER BY COLUMN2] [ROWS BETWEEN n FOLLOWING|PRECEDING(start window) AND m FOLLOWING|PRECEDING|CURRENT ROW)(end window)])) < | > | = | <> require_value_filter

Example 1: Find top 2 ranked employees by salary within each department.


SELECT emp_name,salary,dept_no,
RANK() over (PARTITION BY dept_no ORDER BY salary ) As Dense_Rank_by_salary
FROM tutorial_db.employee
QUALIFY (DENSE_RANK() over (PARTITION BY dept_no ORDER BY salary ))<=2;

emp_name   salary      dept_no  Dense_Rank_by_salary
-------- --------  -----------  --------------------
MILLER    1300.00          100                     1
CLARK     2450.00          100                     2
SMITH      800.00          200                     1
ADAMS     1100.00          200                     2
JAMES      950.00          300                     1
WARD      1250.00          300                     2
MARTIN    1250.00          300                     2

Example 2: Find employees whose total department salary is greater than 9000.


SELECT emp_name,salary,dept_no,
SUM(salary) over (PARTITION BY dept_no ORDER BY emp_name ) As Total_Dept_salary
FROM tutorial_db.employee
QUALIFY (SUM(salary) over (PARTITION BY dept_no ))>9000;

emp_name   salary      dept_no     Total_Dept_salary
--------- -------  -----------  --------------------
ADAMS     1100.00          200              10875.00
FORD      3000.00          200              10875.00
JONES     2975.00          200              10875.00
SCOTT     3000.00          200              10875.00
SMITH      800.00          200              10875.00
ALLEN     1600.00          300               9400.00
BLAKE     2850.00          300               9400.00
JAMES      950.00          300               9400.00
MARTIN    1250.00          300               9400.00
TURNER    1500.00          300               9400.00
WARD      1250.00          300               9400.00