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

WINDOWS CLAUSE trong hàm phân tích

Chúng ta xét 2 ví dụ sau để hiểu rõ hơn về Windows Clause.

• Lấy danh sách nhân viên trong đó cho biết tại thời điểm nhân viên đó ký hợp đồng lao động thì nhân viên đó là nhân viên thứ mấy được ký hợp đồng lao động trong năm
• Lấy danh sách nhân viên trong đó cho biết tại thời điểm nhân viên đó ký hợp đồng lao động thì trước đó n ngày và sau đó m ngày có bao nhiêu nhân viên cũng được ký hợp đồng lao động

---

Với ví dụ 1, trước khi đi tìm câu query ta hãy nhìn bảng kết quả để hiểu rõ hơn về yêu cầu đầu bài:

DEPTNO	ENAME	YEAR	COUNT_IN_YEAR
20	SMITH	1980	1
30	ALLEN	1981	1
30	WARD	1981	2
20	JONES	1981	3
30	BLAKE	1981	4
10	CLARK	1981	5
30	TURNER	1981	6
30	MARTIN	1981	7
10	KING	1981	8
20	FORD	1981	9
30	JAMES	1981	10
10	MILLER	1982	1
20	SCOTT	1982	2
20	ADAMS	1983	1

Và đây là câu query cho bảng kết quả trên:

1 2 3 4 5 6 7 8 9 10 11

SELECT deptno, ename, TO_CHAR (hiredate, 'YYYY') "YEAR", COUNT ( *) OVER (PARTITION BY TO_CHAR (hiredate, 'YYYY') ORDER BY hiredate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) count_in_year FROM emp GROUP BY deptno, ename, hiredate;

Một số điểm lưu ý cho câu query trên:

• PARTITION BY TO_CHAR (hiredate,'YYYY'): mệnh đề này chia năm ký hợp đồng của các nhân viên ra thành từng phân nhóm (partition)
• ORDER BY hiredate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: mệnh đề này kết hợp với hàm COUNT cho biết số nhiên viên được ký hợp đồng từ đầu năm (UNBOUNDED PRECEDING) đến thời điểm nhân viên đó được ký (CURRENT ROW) là bao nhiêu người

---

Với ví dụ 2, trước khi đi tìm câu query ta hãy nhìn bảng kết quả để hiểu rõ hơn về yêu cầu đầu bài:

DEPTNO	ENAME	HIREDATE	30_DAYS_BEFORE	20_DAYS_AFTER	COUNT_IN_RANGE
20	SMITH	17/12/1980	17/11/1980	06/01/1981	1
30	ALLEN	20/02/1981	21/01/1981	12/03/1981	2
30	WARD	22/02/1981	23/01/1981	14/03/1981	2
20	JONES	02/04/1981	03/03/1981	22/04/1981	1
30	BLAKE	01/05/1981	01/04/1981	21/05/1981	2
10	CLARK	09/06/1981	10/05/1981	29/06/1981	1
30	TURNER	08/09/1981	09/08/1981	28/09/1981	2
30	MARTIN	28/09/1981	29/08/1981	18/10/1981	2
10	KING	17/11/1981	18/10/1981	07/12/1981	3
30	JAMES	03/12/1981	03/11/1981	23/12/1981	3
20	FORD	03/12/1981	03/11/1981	23/12/1981	3
10	MILLER	23/01/1982	24/12/1981	12/02/1982	1
20	SCOTT	09/12/1982	09/11/1982	29/12/1982	1
20	SMITH	17/12/1980	17/11/1980	06/01/1981	1

Và đây là câu query cho bảng kết quả trên:

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

SELECT deptno, ename, TO_CHAR (hiredate, 'dd/mm/yyyy') hiredate, TO_CHAR ( (hiredate - 30), 'dd/mm/yyyy') "30 days before", TO_CHAR ( (hiredate + 20), 'dd/mm/yyyy') "20 days after", COUNT ( *) OVER ( ORDER BY hiredate RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND INTERVAL '20' DAY FOLLOWING) count_in_range FROM emp;

Một số điểm lưu ý cho câu query trên:

• INTERVAL '30' DAY PRECEDING: trước ngày HIREDATE 30 ngày
• INTERVAL '20' DAY FOLLOWING: sau ngày HIREDATE 20 ngày
• RANGE BETWEEN ... AND ...: các dòng từ kết quả câu SELECT thỏa mãn điều kiện trong khoảng từ ... đến ...

Xét kết quả của 1 dòng tô vàng đầu tiên để hiểu về mệnh đề này: trong khoảng trước 30 ngày (01/04/1981) và sau 20 ngày (21/05/1981) kể từ ngày BLAKE được ký hợp đồng (01/05/2014) có bao nhiêu nhân viên cũng được ký hợp đồng trong khoảng thời gian này. Ta thấy có tổng cộng là 2 nhân viên: JONES và BLAKE

Read More