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
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)])
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
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.
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)])
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
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
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)])
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
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
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)])
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
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
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)])
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
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
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)])
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
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
Syntax:
MSUM(aggregation_column, n, order_by_column[,other_order_by_columns])
Here n specify window feature "ROWS n-1 PRECEDING"
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
Syntax:
MSUM(aggregation_column, n, order_by_column[,other_order_by_columns])
Here n specify window feature "ROWS n-1 PRECEDING"
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
Syntax:
MDIFF(aggregation_column, n, order_by_column[,other_order_by_columns])
Here 'n' specify nth previous row
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.
Syntax:
CSUM(aggregation_column, order_by_column[,other_order_by_columns])
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.
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