Kết hợp hàm FIRST, LAST với RANK và DENSE_RANK

Xét ví dụ sau: Lấy danh sách nhân viên cho biết với mỗi nhóm công việc thì mức lương tương ứng của nhân viên trong nhóm công việc đó là bao nhiêu kèm theo mức lương cao nhất và thấp nhất của nhóm đó.
Để hiểu rõ hơn về yêu cầu trên, ta nhìn vào bảng kết quả sau đây:

ENAME	JOB	SAL	Lowest	Highest
FORD	ANALYST	3000	3000	3000
SCOTT	ANALYST	3000	3000	3000
SMITH	CLERK	800	800	1300
JAMES	CLERK	950	800	1300
ADAMS	CLERK	1100	800	1300
MILLER	CLERK	1300	800	1300
CLARK	MANAGER	2450	2450	2975
BLAKE	MANAGER	2850	2450	2975
JONES	MANAGER	2975	2450	2975
KING	PRESIDENT	5000	5000	5000
MARTIN	SALESMAN	1250	1250	1600
WARD	SALESMAN	1250	1250	1600
TURNER	SALESMAN	1500	1250	1600
ALLEN	SALESMAN	1600	1250	1600

Và đây là kết quả câu SELECT:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SELECT deptno, ename, hiredate, sal,SELECT ename, job, sal, MIN (sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY job) "Lowest", MAX (sal) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY job) "Highest" FROM emp ORDER BY job, sal; RANK () OVER (PARTITION BY deptno ORDER BY sal) rank_sal, DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal) dense_rank_sal FROM emp ORDER BY deptno;

Một vài lưu ý:

• Mệnh đề KEEP (DENSE_RANK FIRST ORDER BY sal): cho phép chúng ta giữ lại giá trị lương đầu tiên
• Mệnh đề KEEP (DENSE_RANK LAST ORDER BY sal): cho phép chúng ta giữ lại giá trị lương cuối cùng 

---

Xét 1 ví dụ khác: Lấy danh sách tổng lương theo phòng ban của công ty kèm theo tổng lương của phòng ban cao nhất và thấp nhất trong công ty.
Đây là bảng kết quả:

COMPANY	DEPTNO	DEPT_SAL	Lowest	Highest
ABC Company Ltd.	10	8750	8750	10875
ABC Company Ltd.	30	9400	8750	10875
ABC Company Ltd.	20	10875	8750	10875

Và đây là câu SELECT:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SELECT company, deptno, dept_sal, MIN (dept_sal) KEEP (DENSE_RANK FIRST ORDER BY dept_sal) OVER (PARTITION BY company) "Lowest", MAX (dept_sal) KEEP (DENSE_RANK LAST ORDER BY dept_sal) OVER (PARTITION BY company) "Highest" FROM (SELECT 'ABC Company Ltd.' company, deptno, SUM (sal) dept_sal FROM emp GROUP BY deptno) ORDER BY dept_sal;

Chúng ta thấy rằng để có thể sử dụng được KEEP ... FIRST/LAST buộc chúng ta phải có mệnh đề PARTITION BY. Trong trường hợp này khi đã nhóm tổng lương của các phòng ban lại với nhau chúng ta không có chung 1 nhóm nào để có thể sử dụng được với KEEP ... FIRST/LAST. Do vậy chúng ta thêm 1 cột chung để nhóm, đó chính là cột COMPANY.

Read More

Cách sử dụng hàm ROW_NUMBER, RANK và DENSE_RANK

Bài viết này sẽ hướng dẫn cách sử dụng hàm ROW_NUMBER, RANK và DENSE_RANK.
Xét các yêu cầu sau:

• Lấy danh sách nhân viên dựa vào ngày ký hợp đồng cho biết nhân viên đó là người thứ mấy trong phòng được ký hợp đồng
• Lấy danh sách nhân viên dựa vào lương cho biết nhân viên đó xếp hạng thứ mấy trong phòng

---

 Với yêu cầu 1, ta có bảng kết quả sau:

DEPTNO	ENAME	HIREDATE	SAL	NUM
10	CLARK	09/06/1981	2450	1
10	KING	17/11/1981	5000	2
10	MILLER	23/01/1982	1300	3
20	SMITH	17/12/1980	800	1
20	JONES	02/04/1981	2975	2
20	FORD	03/12/1981	3000	3
20	SCOTT	09/12/1982	3000	4
20	ADAMS	12/01/1983	1100	5
30	ALLEN	20/02/1981	1600	1
30	WARD	22/02/1981	1250	2
30	BLAKE	01/05/1981	2850	3
30	TURNER	08/09/1981	1500	4
30	MARTIN	28/09/1981	1250	5
30	JAMES	03/12/1981	950	6

Và đây là câu SELECT kết hợp với ROW_NUMBER để thực hiện:

1 2 3 4 5 6 7

SELECT deptno, ename, hiredate, sal, ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY hiredate) num FROM emp ORDER BY deptno, num;

---

Với yêu cầu số 2, ta có bảng kết quả sau:

DEPTNO	ENAME	HIREDATE	SAL	RANK_SAL	DENSE_RANK_SAL
10	MILLER	1/23/1982	1300	1	1
10	CLARK	6/9/1981	2450	2	2
10	KING	11/17/1981	5000	3	3
20	SMITH	12/17/1980	800	1	1
20	ADAMS	1/12/1983	1100	2	2
20	JONES	4/2/1981	2975	3	3
20	SCOTT	12/9/1982	3000	4	4
20	FORD	12/3/1981	3000	4	4
30	JAMES	12/3/1981	950	1	1
30	MARTIN	9/28/1981	1250	2	2
30	WARD	2/22/1981	1250	2	2
30	TURNER	9/8/1981	1500	4	3
30	ALLEN	2/20/1981	1600	5	4
30	BLAKE	5/1/1981	2850	6	5

Và đây là câu SELECT kết hợp với RANK và DENSE_RANK để thực hiện:

1 2 3 4 5 6 7 8

SELECT deptno, ename, hiredate, sal, RANK () OVER (PARTITION BY deptno ORDER BY sal) rank_sal, DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal) dense_rank_sal FROM emp ORDER BY deptno;

Nhìn vào kết quả trên ta thấy rằng kết quả của hàm RANK và DENSE_RANK có những điểm như sau:

• Giống nhau: với những dòng có cùng SAL thì sẽ có cùng hạng
• Khác nhau:
  + Hàm RANK sẽ bỏ qua thứ hạng tiếp theo cho những dòng có cùng hạng trước đó
  + Hàm DENSE_RANK không bỏ qua thứ hạng tiếp theo cho những dòng có cùng hạng trước đó

Read More