敗家貓

簡介: oracle的世界中暫時還沒有提供分頁的功能，可讓我們很容易的取得到像1000筆到1100筆之間資料的動作，也許在以後會提供吧
目地: 使用oracle提供的分析函數簡化sql寫法

先說明一下標準的top-n寫法:

select   *         from           (your_query)     where rownum <= 10;

範列:

select  *       from         (select rownum rum,a.*          from rownum <= :M )     where rum >= :N ;

rownum :  oracle內部的虛擬欄位，會自已幫你帶上的
:M :代表輸入的數值
:N: 代表輸入的數值
(這個樣子就可得到所需要的區間資料了)

---

下面就是這次的重點所在了 (下列的結果都使用標準的top-n來替代)
row_number():
ROW_NUMBER()分析函數與ROWNUM 虛擬欄位功效非常相似，它們都能夠給輸出的行編號。下列將會介照oracle增強的地方

測試用的原始資料
select * from emp; ( create table的script跟insert 的sql請見附件)

EMPLOYEE_ID FIRST_NAME JOB_ID SALARY MANAGER_ID 198 Donald SH_CLERK 2600 124 199 Douglas SH_CLERK 2600 124 200 Jennifer AD_ASST 4400 101 201 Michael MK_MAN 13000 100 202 Pat MK_REP 6000 201 203 Susan HR_REP 6500 101 204 Hermann PR_REP 10000 101 205 Shelley AC_MGR 12000 101 206 William AC_ACCOUNT 8300 205 100 Steven AD_PRES 24000   101 Neena AD_VP 17000 100 102 Lex AD_VP 17000 100 103 Alexander IT_PROG 9000 102 104 Bruce IT_PROG 6000 103 105 David IT_PROG 4800 103 106 Valli IT_PROG 4800 103 107 Diana IT_PROG 4200 103 108 Nancy FI_MGR 12000 101 109 Daniel FI_ACCOUNT 9000 108 110 John FI_ACCOUNT 8200 108 111 Ismael FI_ACCOUNT 7700 108 112 Jose Manuel FI_ACCOUNT 7800 108 113 Luis FI_ACCOUNT 6900 108 114 Den PU_MAN 11000 100 115 Alexander PU_CLERK 3100 114 116 Shelli PU_CLERK 2900 114 117 Sigal PU_CLERK 2800 114 118 Guy PU_CLERK 2600 114 119 Karen PU_CLERK 2500 114 120 Matthew ST_MAN 8000 100 121 Adam ST_MAN 8200 100 122 Payam ST_MAN 7900 100 123 Shanta ST_MAN 6500 100 124 Kevin ST_MAN 5800 100 125 Julia ST_CLERK 3200 120 126 Irene ST_CLERK 2700 120 127 James ST_CLERK 2400 120 128 Steven ST_CLERK 2200 120 129 Laura ST_CLERK 3300 121 130 Mozhe ST_CLERK 2800 121 131 James ST_CLERK 2500 121 132 TJ ST_CLERK 2100 121 133 Jason ST_CLERK 3300 122 134 Michael ST_CLERK 2900 122 135 Ki ST_CLERK 2400 122 136 Hazel ST_CLERK 2200 122 137 Renske ST_CLERK 3600 123 138 Stephen ST_CLERK 3200 123 139 John ST_CLERK 2700 123 140 Joshua ST_CLERK 2500 123 141 Trenna ST_CLERK 3500 124 142 Curtis ST_CLERK 3100 124 143 Randall ST_CLERK 2600 124 144 Peter ST_CLERK 2500 124 145 John SA_MAN 14000 100 146 Karen SA_MAN 13500 100 147 Alberto SA_MAN 12000 100 148 Gerald SA_MAN 11000 100 149 Eleni SA_MAN 10500 100 150 Peter SA_REP 10000 145 151 David SA_REP 9500 145 152 Peter SA_REP 9000 145 153 Christopher SA_REP 8000 145 154 Nanette SA_REP 7500 145 155 Oliver SA_REP 7000 145 156 Janette SA_REP 10000 146 157 Patrick SA_REP 9500 146 158 Allan SA_REP 9000 146 159 Lindsey SA_REP 8000 146 160 Louise SA_REP 7500 146 161 Sarath SA_REP 7000 146 162 Clara SA_REP 10500 147 163 Danielle SA_REP 9500 147 164 Mattea SA_REP 7200 147 165 David SA_REP 6800 147 166 Sundar SA_REP 6400 147 167 Amit SA_REP 6200 147 168 Lisa SA_REP 11500 148 169 Harrison SA_REP 10000 148 170 Tayler SA_REP 9600 148 171 William SA_REP 7400 148 172 Elizabeth SA_REP 7300 148 173 Sundita SA_REP 6100 148 174 Ellen SA_REP 11000 149 175 Alyssa SA_REP 8800 149 176 Jonathon SA_REP 8600 149 177 Jack SA_REP 8400 149 178 Kimberely SA_REP 7000 149 179 Charles SA_REP 6200 149 180 Winston SH_CLERK 3200 120 181 Jean SH_CLERK 3100 120 182 Martha SH_CLERK 2500 120 183 Girard SH_CLERK 2800 120 184 Nandita SH_CLERK 4200 121 185 Alexis SH_CLERK 4100 121 186 Julia SH_CLERK 3400 121 187 Anthony SH_CLERK 3000 121 188 Kelly SH_CLERK 3800 122 189 Jennifer SH_CLERK 3600 122 190 Timothy SH_CLERK 2900 122 191 Randall SH_CLERK 2500 122 192 Sarah SH_CLERK 4000 123 193 Britney SH_CLERK 3900 123 194 Samuel SH_CLERK 3200 123 195 Vance SH_CLERK 2800 123 196 Alana SH_CLERK 3100 124 197 Kevin SH_CLERK 3000 124

先抓前3筆，並以salary為排序 (這個功能可使用標準的top-n取代)

select * from ( select employee_id,salary,manager_id,row_number() over ( order by salary desc ) rn from emp ) where rn <=3 order by salary desc ;

EMPLOYEE_ID	SALARY	MANAGER_ID	RN
100	24000		1
101	17000	100	2
102	17000	100	3

除了row_number()之外還有rank()、dense_rank()這二個很相近的函數可使用，直接拿sql跑來看，較容易看的出差別

select employee_id,salary,manager_id, row_number() over ( order by salary desc ) rn, rank() over ( order by salary desc ) rnk, dense_rank() over ( order by salary desc ) drink from emp order by salary desc ;

由出來的結果，我們可以看到row_number是連續的數值加下去
而rank是會把相同的salary列為同一級的數值，如employee_id 為101、102 在rnk是為2
dense_rank跟rank是非常相似的，他們的差別是間隔數都為1，並不像rank的間隔數不定；我們可以看到employee_id 145、146在rnk是4、5，而drink是3、4

EMPLOYEE_ID	SALARY	MANAGER_ID	RN	RNK	DRINK
100	24000		1	1	1
101	17000	100	2	2	2
102	17000	100	3	2	2
145	14000	100	4	4	3
146	13500	100	5	5	4
201	13000	100	6	6	5
205	12000	101	7	7	6
108	12000	101	8	7	6
147	12000	100	9	7	6
168	11500	148	10	10	7
114	11000	100	11	11	8
148	11000	100	12	11	8
174	11000	149	13	11	8
149	10500	100	14	14	9
162	10500	147	15	14	9
204	10000	101	16	16	10
169	10000	148	17	16	10
150	10000	145	18	16	10
156	10000	146	19	16	10
170	9600	148	20	20	11
151	9500	145	21	21	12
163	9500	147	22	21	12
157	9500	146	23	21	12
103	9000	102	24	24	13
158	9000	146	25	24	13
152	9000	145	26	24	13
109	9000	108	27	24	13
175	8800	149	28	28	14
176	8600	149	29	29	15
177	8400	149	30	30	16
206	8300	205	31	31	17
110	8200	108	32	32	18
121	8200	100	33	32	18
120	8000	100	34	34	19
159	8000	146	35	34	19
153	8000	145	36	34	19
122	7900	100	37	37	20
112	7800	108	38	38	21
111	7700	108	39	39	22
154	7500	145	40	40	23
160	7500	146	41	40	23
171	7400	148	42	42	24
172	7300	148	43	43	25
164	7200	147	44	44	26
155	7000	145	45	45	27
161	7000	146	46	45	27
178	7000	149	47	45	27
113	6900	108	48	48	28
165	6800	147	49	49	29
203	6500	101	50	50	30
123	6500	100	51	50	30
166	6400	147	52	52	31
167	6200	147	53	53	32
179	6200	149	54	53	32
173	6100	148	55	55	33
202	6000	201	56	56	34
104	6000	103	57	56	34
124	5800	100	58	58	35
105	4800	103	59	59	36
106	4800	103	60	59	36
200	4400	101	61	61	37
107	4200	103	62	62	38
184	4200	121	63	62	38
185	4100	121	64	64	39
192	4000	123	65	65	40
193	3900	123	66	66	41
188	3800	122	67	67	42
137	3600	123	68	68	43
189	3600	122	69	68	43
141	3500	124	70	70	44
186	3400	121	71	71	45
129	3300	121	72	72	46
133	3300	122	73	72	46
125	3200	120	74	74	47
180	3200	120	75	74	47
194	3200	123	76	74	47
138	3200	123	77	74	47
115	3100	114	78	78	48
196	3100	124	79	78	48
181	3100	120	80	78	48
142	3100	124	81	78	48
187	3000	121	82	82	49
197	3000	124	83	82	49
116	2900	114	84	84	50
134	2900	122	85	84	50
190	2900	122	86	84	50
117	2800	114	87	87	51
130	2800	121	88	87	51
183	2800	120	89	87	51
195	2800	123	90	87	51
126	2700	120	91	91	52
139	2700	123	92	91	52
198	2600	124	93	93	53
143	2600	124	94	93	53
199	2600	124	95	93	53
118	2600	114	96	93	53
119	2500	114	97	97	54
131	2500	121	98	97	54
140	2500	123	99	97	54
182	2500	120	100	97	54
191	2500	122	101	97	54
144	2500	124	102	97	54
127	2400	120	103	103	55
135	2400	122	104	103	55
128	2200	120	105	105	56
136	2200	122	106	105	56
132	2100	121	107	107	57

我們來做點較深入點的範列
假設我們要以manager_id為分組，以salary的高低做排序，每組的開頭要由1為起始數值

--先設定 那個欄位跟起始數值為多少 break on manager_id skip 1; select employee_id,salary,manager_id, row_number() over ( partition by manager_id order by salary desc ) rn, rank() over ( partition by manager_id order by salary desc ) rnk, dense_rank() over ( partition by manager_id order by salary desc ) drink from emp order by manager_id,salary desc ;

結果如下，可以看到資料以manager_id為群組，然後以salary為排序，然後每組的最高salary的編號為1
而排列號碼的原因請參考上面的範列

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

心得:
1、每版oracle都會提供一些加強的函數，多瞭解一些函數，可簡化一些sql的處理流程，也會讓程式碼更有可讀性
2、可利用這些函數的特性處理刪除資料重覆值的問題

附記:
select manager_id,count(*) from emp group by manager_id;

Plan
SELECT STATEMENT CHOOSE Cost: 4 Bytes: 72 Cardinality: 18 IO Cost: 4
	2 SORT GROUP BY Cost: 4 Bytes: 72 Cardinality: 18 IO Cost: 4
		1 TABLE ACCESS FULL HR.EMP Object Instance: 1 Cost: 2 Bytes: 428 Cardinality: 107 IO Cost: 2

在10g中 group by將不會進行sort，而是採用hash group by，所以原本在9i中是靠 group by 做排序的程式碼將需要再調整

補充資料:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/toc.htm