Mệnh đề ROWS và RANGE trong hàm phân tích

Ví dụ 1: xét ví dụ với mệnh đề ROWS

1 2 3 4 5 6 7 8

SELECT deptno "Deptno", ename "Ename", sal "Sal", SUM (sal) OVER (PARTITION BY deptno ORDER BY ename) "No Sliding", SUM (sal) OVER (PARTITION BY deptno ORDER BY ename ROWS 2 PRECEDING) "Sliding Total" FROM emp ORDER BY deptno, ename;

Kết quả:

Deptno	Ename	Sal	No_Sliding	Sliding_Total
10	CLARK	2450	2450	2450
10	KING	5000	7450	7450
10	MILLER	1300	8750	8750
20	ADAMS	1100	1100	1100
20	FORD	3000	4100	4100
20	JONES	2975	7075	7075
20	SCOTT	3000	10075	8975
20	SMITH	800	10875	6775
30	ALLEN	1600	1600	1600
30	BLAKE	2850	4450	4450
30	JAMES	950	5400	5400
30	MARTIN	1250	6650	5050
30	TURNER	1500	8150	3700
30	WARD	1250	9400	4000

Với kết quả trên, xét nhóm các dòng tô vàng (Deptno=20) ta chú ý các điểm sau đây:

• Cột No_Sliding - kết quả của câu lệnh "SUM(sal) OVER (PARTITION BY deptno ORDER BY ename)": tích lũy lương của dòng hiện tại. Cụ thể:
  No_Sliding của JONES là 7075=1100+3000+2975
  No_Sliding của SMITH là 10875=1100+3000+2975+3000+800
• Cột Sliding_Total - kết quả của câu lệnh "SUM(sal) OVER (PARTITION BY deptno ORDER BY ename ROWS 2 PRECEDING)": tích lũy lương của dòng hiện tại = tích lũy lương từ dòng hiện tại và tổng lương 2 dòng trước đó. Cụ thể
  Sliding_Total của ADAMS là 1100=0+0+1100
  Sliding_Total của FORD là 4100=0+1100+3000
  Sliding_Total của JONES là 7075=1100+3000+2975
  Sliding_Total của SCOTT là 8975=3000+2975+3000
  Sliding_Total của SMITH là 6775=2975+3000+800
• Nếu muốn tính tích lũy lương của n dòng trước và m dòng sau dòng hiện tại, ta dùng mệnh đề sau "SUM(sal) OVER (PARTITION BY deptno ORDER BY ename ROWS BETWEEN n PRECEDING AND m FOLLOWING)"

---

Ví dụ 2: xét ví dụ với mệnh đề RANGE

1 2 3 4 5 6 7 8 9

SELECT deptno, ename, sal, sal - 200 "200 Preceding", sal + 200 "200 Following", COUNT (*) OVER (ORDER BY sal RANGE BETWEEN 200 PRECEDING AND 200 FOLLOWING) sal_round_200 FROM emp;

Kết quả:

Deptno	Ename	Sal	200 Preceding	200 Following	Sal_Round_200
20	SMITH	800	600	1000	2
30	JAMES	950	750	1150	3
20	ADAMS	1100	900	1300	5
30	WARD	1250	1050	1450	4
30	MARTIN	1250	1050	1450	4
10	MILLER	1300	1100	1500	5
30	TURNER	1500	1300	1700	3
30	ALLEN	1600	1400	1800	2
10	CLARK	2450	2250	2650	1
30	BLAKE	2850	2650	3050	4
20	JONES	2975	2775	3175	4
20	SCOTT	3000	2800	3200	4
20	FORD	3000	2800	3200	4
10	KING	5000	4800	5200	1

Với kết quả trên, ta xét 2 dòng tô vàng và chú ý các điểm sau:

• Dòng tô vàng thứ 1, Sal_Round_200=5: đếm số dòng có mức lương nằm trong khoảng 900 - 1300 (gồm JAMES, ADAMS, WARD, MARTIN, MILLER)
• Dòng tô vàng thứ 2, Sal_Round_200=3: đếm số dòng có mức lương nằm trong khoảng 1300 - 1700 (gồm MILLER, TURNER, ALLEN)