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