Teradata Analytical Window Functions

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 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 Restriction on ordered analytical functions
Window features: These are most important part of ordered analytical functions and should be understood properly in order to effectively use them.


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



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