Blog Archive

Sunday 28 April 2013

Teradata MIN Window function


MINIMUM Window Function:

The MINIMUM Window function reports the minimum value for the defined group.
If we don’t use PARTITION BY then all the rows are considered as a group and it gives minimum value in the entire group.

Only difference between the aggregate MINIMUM and MINIMUM window is that with MINIMUM window function we get details rows also.

SELECT
EMPLOYEEID,
DEPARTMENTNO,
SALARY,
MIN(SALARY) OVER (ORDER BY DEPARTMENTNO )
FROM EMPLOYEE2;

 
Employeeid
DepartmentNo
Salary
Group Min(Salary)
9
100
9000
1000
1
100
1000
1000
5
100
5000
1000
2
200
2000
1000
10
200
10000
1000
124
200
12345.11
1000
6
200
6000
1000
7
300
7000
1000
11
300
11000
1000
3
300
3000
1000
4
400
4000
1000
8
400
8000
1000
12
400
12000
1000
144
400
12345.11
1000

We can get the same result using the following:

SELECT
EMPLOYEEID,
DEPARTMENTNO,
SALARY,
MIN(SALARY) OVER (ORDER BY DEPARTMENTNO ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) as minsal 
FROM EMPLOYEE2;


We can use the same function to find out what percentage of minimum salary is the current salary. Following is how we do that:


SELECT
EMPLOYEEID,
DEPARTMENTNO,
SALARY,
MIN(SALARY) OVER (ORDER BY DEPARTMENTNO ) as minsal ,(salary/minsal) * 100 FROM EMPLOYEE2;

Employeeid
DepartmentNo
Salary
minsal
((Salary/minsal)*100)
1
100
1000
1000
100
5
100
5000
1000
500
9
100
9000
1000
900
2
200
2000
1000
200
6
200
6000
1000
600
10
200
10000
1000
1000
124
200
12345.11
1000
1235
3
300
3000
1000
300
7
300
7000
1000
700
11
300
11000
1000
1100
12
400
12000
1000
1200
4
400
4000
1000
400
144
400
12345.11
1000
1235
8
400
8000
1000
800

If we didn’t not have this technique we would need to calculate the minimum first and store in a volatile table and then use this result to calculate the percentage.
 
Minimum Window function with Partition:
As explained earlier We can use PARTITION BY clause to produce grouping.

Following query provides minimum per department.


 
SELECT
EMPLOYEEID,
DEPARTMENTNO,
SALARY,
MIN(SALARY) OVER (PARTITION BY  DEPARTMENTNO
ORDER BY DEPARTMENTNO ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) as minsal 
FROM EMPLOYEE2;



Employeeid
DepartmentNo
Salary
minsal
1
100
1000
1000
5
100
5000
1000
9
100
9000
1000
2
200
2000
2000
10
200
10000
2000
6
200
6000
2000
124
200
12345.11
2000
3
300
3000
3000
7
300
7000
3000
11
300
11000
3000
12
400
12000
4000
144
400
12345.11
4000
4
400
4000
4000
8
400
8000
4000



No comments:

Post a Comment