All about Oracle Technologies

Saturday, 31 January 2015

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)

Saturday, 24 January 2015

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ả:
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