Wednesday 27 March 2013

OLAP - Part 7 - RANK with Qualify and Group BY


RANK with ‘Qualify’ and ‘GROUP BY’:

  1. The QUALIFY clause allows restriction of which rankings will be output in the final result.

  1. QUALIFY performs like the HAVING clause by requesting a specific range in the output.

  1. Example 1:

select employeeid,salary,rank(salary) from employee
qualify rank(salary) < 4

Employeeid
Salary
Rank(Salary)
12
12000
1
11
11000
2
10
10000
3

Note that the Qualify restricts the result only to the rows with rank lesser than 4.

  1. Example 2 :

select employeeid ,DepartmentNO, salary,rank(salary) from employee
group by DepartmentNO
qualify rank(salary) < 3

Employeeid
DepartmentNo
Salary
Rank(Salary)
9
100
9000
1
5
100
5000
2
10
200
10000
1
6
200
6000
2
11
300
11000
1
7
300
7000
2
12
400
12000
1
8
400
8000
2

Above query restricts the result to 1st two rank for each department.

Note that here one more feature is being used . ie GROUP BY.
The GROUP BY isn't doing an aggregation. It is actually changing the scope of the query. It also causes an ascending sort.

The default sort sequence is descending by ranking column .
Due to GROUP BY, the sort is by salary ascending within department. With in a given department the sort is descending by the Ranked column .ie salary.

The GROUP BY clause may be used in conjunction with a RANK function to change the scope of the ranking. 

Without a GROUP BY clause, the scope defaults to the RANK column.
In the above example 1 the scope is the ranking column itself.

As shown in Example 2 ‘GROUP BY” adds a major sorting column as well.
Ascending Sort is performed first on the column in GROUP BY and then sorted descending on the ranked column.


  1. Sometimes we may need to use low order ranking .For example: To find out lowest 3 salaries .This is accomplished by changing the default order of the rank column from descending to ascending.

For this we use RANK(column ASC).
         Example:


select employeeid,DepartmentNO,salary,rank(salary asc) from employee

qualify rank(salary asc ) < 4

Employeeid
DepartmentNo
Salary
Rank(Salary ASC)
1
100
1000
1
2
200
2000
2
3
300
3000
3



The QUALIFY clause <= 4 means produce a list of the ‘first 4’ whatever the sequencing of the list. Qualify Does not look at the absolute mathematical value. Instead it looks only at the rank value irrespective of whether ranking uses descending(default) ordering or ascending ordering.
So if we are using rank with ascending sort on the column then it produces lowest 3 records.

  1. Beware of below query

select employeeid,DepartmentNO,salary,rank(salary asc) from employee

qualify rank(salary) < 4

The select part contains RANK with ascending for ordering, which means the lowest value will get 1st rank. However qualify does not use ascending . So while returning back the row it will return 1st 3 rows with higher salary and their ranks.

Employeeid
DepartmentNo
Salary
Rank(Salary ASC)
12
400
12000
12
11
300
11000
11
10
200
10000
10

To understand this better we can query as follows

select employeeid,
          DepartmentNO,
          salary,
          rank(salary),
          rank(salary asc) from employee

qualify rank(salary) < 4

Employeeid
DepartmentNo
Salary
Rank(Salary)
Rank(Salary ASC)
12
400
12000
1
12
11
300
11000
2
11
10
200
10000
3
10

Now we can see that the column used for qualifying is the 4th column.

  1. Though with RANK we get a default ordering either by the ranking column or GROUP BY column, We can use ORDER BY to change the display ordering. Note that this ordering is applied at the end after the result set is ready.

When order by clause is used, the ORDER BY becomes the major sort in the results list.

select employeeid,DepartmentNO,hiredate,salary,rank(salary) from employee
order by hiredate;

Employeeid
DepartmentNo
Hiredate
Salary
Rank(Salary)
8
400
1/1/1992
8000
5
4
400
1/1/1996
4000
9
7
300
6/1/1996
7000
6
3
300
1/1/1997
3000
10
6
200
3/1/1997
6000
7
10
200
1/1/1998
10000
3
2
200
1/1/1998
2000
11
5
100
2/1/1998
5000
8
1
100
1/1/1999
1000
12
9
100
4/1/1999
9000
4
11
300
5/1/1999
11000
2
12
400
8/1/1999
12000
1


No comments:

Post a Comment