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