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
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)])
Syntax:
RANK() OVER ([PARTITION BY COLUMN1] [ORDER BY COLUMN2] [ROWS BETWEEN n FOLLOWING|PRECEDING(start window) AND m FOLLOWING|PRECEDING|CURRENT ROW)(end window)])
SELECT emp_name,salary,dept_no,
RANK() over (PARTITION BY dept_no ORDER BY salary ) As Rank_by_salary
FROM tutorial_db.employee;
emp_name salary dept_no Rank_by_salary
-------- -------- ----------- -----------------
MILLER 1300.00 100 1
CLARK 2450.00 100 2
PRADEEP 5000.00 100 3
SMITH 800.00 200 1
ADAMS 1100.00 200 2
JONES 2975.00 200 3
FORD 3000.00 200 4
SCOTT 3000.00 200 4
JAMES 950.00 300 1
WARD 1250.00 300 2
MARTIN 1250.00 300 2
TURNER 1500.00 300 4
ALLEN 1600.00 300 5
BLAKE 2850.00 300 6
Syntax:
DENSE_RANK() OVER ([PARTITION BY COLUMN1] [ORDER BY COLUMN2] [ROWS BETWEEN n FOLLOWING|PRECEDING(start window) AND m FOLLOWING|PRECEDING|CURRENT ROW)(end window)])
SELECT emp_name,salary,dept_no,
DENSE_RANK() over (PARTITION BY dept_no ORDER BY salary ) As Dense_Rank_by_salary
FROM tutorial_db.employee;
emp_name salary dept_no Dense_Rank_by_salary
--------- ------- ----------- --------------------
MILLER 1300.00 100 1
CLARK 2450.00 100 2
PRADEEP 5000.00 100 3
SMITH 800.00 200 1
ADAMS 1100.00 200 2
JONES 2975.00 200 3
FORD 3000.00 200 4
SCOTT 3000.00 200 4
JAMES 950.00 300 1
MARTIN 1250.00 300 2
WARD 1250.00 300 2
TURNER 1500.00 300 3
ALLEN 1600.00 300 4
BLAKE 2850.00 300 5
Syntax:
ROW_NUMBER() OVER ([PARTITION BY COLUMN1] [ORDER BY COLUMN2] [ROWS BETWEEN n FOLLOWING|PRECEDING(start window) AND m FOLLOWING|PRECEDING|CURRENT ROW)(end window)])
SELECT emp_name,salary,dept_no,
ROW_NUMBER() over (PARTITION BY dept_no ORDER BY salary,emp_name ) As row_number_Dept_salary
FROM tutorial_db.employee;
emp_name salary dept_no row_number_Dept_salary
--------- -------- ----------- -----------------
MILLER 1300.00 100 1
CLARK 2450.00 100 2
PRADEEP 5000.00 100 3
SMITH 800.00 200 1
ADAMS 1100.00 200 2
JONES 2975.00 200 3
FORD 3000.00 200 4
SCOTT 3000.00 200 5
JAMES 950.00 300 1
MARTIN 1250.00 300 2
WARD 1250.00 300 3
TURNER 1500.00 300 4
ALLEN 1600.00 300 5
BLAKE 2850.00 300 6
Syntax:
LAG(expression_column, n , default_value) OVER ([PARTITION BY COLUMN1] [ORDER BY COLUMN2])
expression_column -> Lag value to be checked for column
n -> Value of "n" will be` 1 if not specified, will be equivalent to "n PRECEDING"
default_value -> If no row satisfy the window condition then default_value specified in the function will be returned and if value is not specified then null will be returned.
SELECT emp_name,salary,dept_no,
LAG(salary,2,-1) over (PARTITION BY dept_no ORDER BY salary ) As lag_salary
FROM tutorial_db.employee;
emp_name salary dept_no lag_salary
---------- --------- ----------- ----------
MILLER 1300.00 100 -1.00
CLARK 2450.00 100 -1.00
PRADEEP 5000.00 100 1300.00
SMITH 800.00 200 -1.00
ADAMS 1100.00 200 -1.00
JONES 2975.00 200 800.00
FORD 3000.00 200 1100.00
SCOTT 3000.00 200 2975.00
JAMES 950.00 300 -1.00
MARTIN 1250.00 300 -1.00
WARD 1250.00 300 950.00
TURNER 1500.00 300 1250.00
ALLEN 1600.00 300 1250.00
BLAKE 2850.00 300 1500.00
Note: Some value are -1 because they are not satisfying window condition as there is no previous to previous row to get value within their partition.
SELECT emp_name,salary,dept_no,
LAG(salary) over (PARTITION BY dept_no ORDER BY salary ) As lag_salary
FROM tutorial_db.employee;
emp_name salary dept_no lag_salary
-------- -------- ----------- ----------
MILLER 1300.00 100 ?
CLARK 2450.00 100 1300.00
PRADEEP 5000.00 100 2450.00
SMITH 800.00 200 ?
ADAMS 1100.00 200 800.00
JONES 2975.00 200 1100.00
FORD 3000.00 200 2975.00
SCOTT 3000.00 200 3000.00
JAMES 950.00 300 ?
MARTIN 1250.00 300 950.00
WARD 1250.00 300 1250.00
TURNER 1500.00 300 1250.00
ALLEN 1600.00 300 1500.00
BLAKE 2850.00 300 1600.00
Note: Some value are null because they are not satisfying window condition as there is no previous row to get value within their partition.
Syntax:
LEAD(expression_column, n , default_value) OVER ([PARTITION BY COLUMN1] [ORDER BY COLUMN2])
expression_column -> Lag value to be checked for column
n -> Value of "n" will be` 1 if not specified, will be equivalent to "n PRECEDING"
default_value -> If no row satisfy the window condition then default_value specified in the function will be returned and if value is not specified then null will be returned.
SELECT emp_name,salary,dept_no,
LEAD(salary,2,-1) over (PARTITION BY dept_no ORDER BY salary ) As lead_salary
FROM tutorial_db.employee;
emp_name salary dept_no lead_salary
---------- --------- ----------- -----------
MILLER 1300.00 100 5000.00
CLARK 2450.00 100 -1.00
PRADEEP 5000.00 100 -1.00
SMITH 800.00 200 2975.00
ADAMS 1100.00 200 3000.00
JONES 2975.00 200 3000.00
FORD 3000.00 200 -1.00
SCOTT 3000.00 200 -1.00
JAMES 950.00 300 1250.00
MARTIN 1250.00 300 1500.00
WARD 1250.00 300 1600.00
TURNER 1500.00 300 2850.00
ALLEN 1600.00 300 -1.00
BLAKE 2850.00 300 -1.00
Note: Some value are -1 because they are not satisfying window condition as there is no next to next row to get value within their partition.
SELECT emp_name,salary,dept_no,
LEAD(salary) over (PARTITION BY dept_no ORDER BY salary ) As lead_salary
FROM tutorial_db.employee;
emp_name salary dept_no lead_salary
-------- ------- ----------- -----------
MILLER 1300.00 100 2450.00
CLARK 2450.00 100 5000.00
PRADEEP 5000.00 100 ?
SMITH 800.00 200 1100.00
ADAMS 1100.00 200 2975.00
JONES 2975.00 200 3000.00
FORD 3000.00 200 3000.00
SCOTT 3000.00 200 ?
JAMES 950.00 300 1250.00
MARTIN 1250.00 300 1250.00
WARD 1250.00 300 1500.00
TURNER 1500.00 300 1600.00
ALLEN 1600.00 300 2850.00
BLAKE 2850.00 300 ?
Note: Some value are null because they are not satisfying window condition as there is no next row to get value within their partition.
Syntax:
FIRST_VALUE(column_name) OVER ([PARTITION BY COLUMN1] [ORDER BY COLUMN2] [ROWS BETWEEN n FOLLOWING|PRECEDING(start window) AND m FOLLOWING|PRECEDING|CURRENT ROW)(end window)])
SELECT emp_name,salary,dept_no,
FIRST_VALUE(salary) over (PARTITION BY dept_no ORDER BY emp_name ) As first_salary
FROM tutorial_db.employee;
emp_name salary dept_no first_salary
-------- -------- ----------- ------------
CLARK 2450.00 100 2450.00
MILLER 1300.00 100 2450.00
PRADEEP 5000.00 100 2450.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 1100.00
ALLEN 1600.00 300 1600.00
BLAKE 2850.00 300 1600.00
JAMES 950.00 300 1600.00
MARTIN 1250.00 300 1600.00
TURNER 1500.00 300 1600.00
WARD 1250.00 300 1600.00
Syntax:
LAST_VALUE(column_name) OVER ([PARTITION BY COLUMN1] [ORDER BY COLUMN2] [ROWS BETWEEN n FOLLOWING|PRECEDING(start window) AND m FOLLOWING|PRECEDING|CURRENT ROW)(end window)])
SELECT emp_name,salary,dept_no,
LAST_VALUE(salary) over (PARTITION BY dept_no ORDER BY emp_name) As last_salary
FROM tutorial_db.employee;
emp_name salary dept_no last_salary
-------- -------- ----------- -----------
CLARK 2450.00 100 2450.00
MILLER 1300.00 100 1300.00
PRADEEP 5000.00 100 5000.00
ADAMS 1100.00 200 1100.00
FORD 3000.00 200 3000.00
JONES 2975.00 200 2975.00
SCOTT 3000.00 200 3000.00
SMITH 800.00 200 800.00
ALLEN 1600.00 300 1600.00
BLAKE 2850.00 300 2850.00
JAMES 950.00 300 950.00
MARTIN 1250.00 300 1250.00
TURNER 1500.00 300 1500.00
WARD 1250.00 300 1250.00
SELECT emp_name,salary,dept_no,
LAST_VALUE(salary) over (PARTITION BY dept_no ORDER BY emp_name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) As last_salary
FROM tutorial_db.employee;
emp_name salary dept_no last_salary
-------- -------- ----------- -----------
CLARK 2450.00 100 5000.00
MILLER 1300.00 100 5000.00
PRADEEP 5000.00 100 5000.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 1250.00
BLAKE 2850.00 300 1250.00
JAMES 950.00 300 1250.00
MARTIN 1250.00 300 1250.00
TURNER 1500.00 300 1250.00
WARD 1250.00 300 1250.00
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
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
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