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)

Read More

Một số ví dụ về các hàm phân tích trong Oracle

Dưới đây là các dữ liệu mẫu để chuẩn bị cho các ví dụ về Analytic Function

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207

CREATE TABLE emp ( empno NUMBER NOT NULL, ename VARCHAR2 (10), job VARCHAR2 (9), mgr NUMBER (4), hiredate DATE, sal NUMBER (7, 2), comm NUMBER (7, 2), deptno NUMBER (2) ); INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE ('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20); INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE ('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30); INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE ('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30); INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE ('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20); INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE ('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30); INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE ('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30); INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE ('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10); INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE ('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20); INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE ('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10); INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE ('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30); INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, TO_DATE ('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20); INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, TO_DATE ('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30); INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE ('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20); INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, TO_DATE ('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10); CREATE TABLE dept ( deptno NUMBER (2), dname VARCHAR2 (14), loc VARCHAR2 (13) ); INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS'); INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO'); INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON'); CREATE TABLE bonus ( ename VARCHAR2 (10), job VARCHAR2 (9), sal NUMBER, comm NUMBER ); CREATE TABLE salgrade ( grade NUMBER, losal NUMBER, hisal NUMBER ); INSERT INTO salgrade VALUES (1, 700, 1200); INSERT INTO salgrade VALUES (2, 1201, 1400); INSERT INTO salgrade VALUES (3, 1401, 2000); INSERT INTO salgrade VALUES (4, 2001, 3000); INSERT INTO salgrade VALUES (5, 3001, 9999); CREATE TABLE dummy (dummy NUMBER); INSERT INTO dummy VALUES (0); COMMIT;

Ví dụ 1: Calculate a running Total - Tính tổng tích lũy

1 2 3 4 5 6 7 8

SELECT ename "Ename", deptno "Deptno", sal "Sal", SUM (sal) OVER (ORDER BY deptno, ename) "Running Total", SUM (sal) OVER (PARTITION BY deptno ORDER BY ename) "Dept Total", ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY ename) "Seq" FROM emp ORDER BY deptno, ename;

Kết quả:

Ename	Deptno	Sal	Running Total	Dept Total	Seq
CLARK	10	2450	2450	2450	1
KING	10	5000	7450	7450	2
MILLER	10	1300	8750	8750	3
ADAMS	20	1100	9850	1100	1
FORD	20	3000	12850	4100	2
JONES	20	2975	15825	7075	3
SCOTT	20	3000	18825	10075	4
SMITH	20	800	19625	10875	5
ALLEN	30	1600	21225	1600	1
BLAKE	30	2850	24075	4450	2
JAMES	30	950	25025	5400	3
MARTIN	30	1250	26275	6650	4
TURNER	30	1500	27775	8150	5
WARD	30	1250	29025	9400	6

Quan sát kết quả trên ta thấy rằng cột Running Total là tổng tích lũy về Salary của các dòng trước đó, cột Dept Total là tổng tích lũy về Salary các dòng trước đó theo từng phòng ban. Một cách khác để hiểu rõ hơn như sau:

- Các dòng được tô vàng chính là kết quả của câu lệnh sau:

1 2 3 4

SELECT deptno "Deptno", SUM (sal) "Dept Total" FROM emp GROUP BY deptno ORDER BY deptno;

- Dòng được tô đỏ chính là kết quả của câu lệnh sau:

1	SELECT SUM(sal) "Running Total" FROM emp;

---

Ví dụ 2: Top-N Queries - Lấy n dòng đầu tiên từ kết quả của 1 câu SELECT

1 2 3 4 5 6 7 8

SELECT * FROM (SELECT deptno, ename, sal, ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC) top3 FROM emp) WHERE top3 <= 3;

Kết quả:

Deptno	Ename	Sal	Top3
10	KING	5000	1
10	CLARK	2450	2
10	MILLER	1300	3
20	SCOTT	3000	1
20	FORD	3000	2
20	JONES	2975	3
30	BLAKE	2850	1
30	ALLEN	1600	2
30	TURNER	1500	3

Quan sát kết quả trên ta thấy rằng với mỗi phòng ban ta chỉ lấy ra 3 dòng đầu tiên

Read More