Teradata Analytical Window Functions Part 2

Teradata Analytical Window Functions Part 2

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

Other 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 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