经常发现一些网友问分析函数的问题，虽然分析函数比较多，有一些分析函数也确实比较难以理解，会给初学者有很大的心理压力，其实对于大部分的分析函数，一般都不会有机会用到，所以掌握常用的几个分析函数就可以啦。
常用的分析函数如下所列:
row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)
示例：
14:33:29 SQL> select type,qty from test;
TYPE QTY
---------- ----------
1 3
1 6
2 5
2 9
2 7
14:33:36 SQL> select type,qty,to_char(row_number() over(partition by type order by qty))||'/'||to_char(count(*) over(partition by type)) as cnt2 from test;
TYPE QTY CNT2
---------- ---------- ------------
1 3 1/2
1 6 2/2
2 5 1/3
2 7 2/3
2 9 3/3
72.36.192.* 于 2007-05-21 07:43:45发表:
Good design!
mp3 to ringtone converter | free cellular ringtones | free ringtones alltel | free christian ringtones | ringtones lg | free verizon ringtones | blackberry ringtones | ringtones for motorola q | free real ringtones for cingular | nokia ringtone
72.36.192.* 于 2007-05-21 07:43:20发表:
Nice site!
free nascar ringtones | free mp3 tones | free midi ringtone | free anime ringtones | nextel downloads | download free polyphonic ringtones | free sanyo ringtones | free ringtones for samsung | motorola q ringtones | ringtone creator
72.36.192.* 于 2007-05-21 07:42:46发表:
Well done!
mobile ringtones | ringtones for suncom | ringtones for nokia | ringtones verizon | 24 ctu ringtone | simpsons ringtone | cell phone ringtones | ringtones free | free midi ringtones | lg free ringtones
72.36.192.* 于 2007-05-21 07:42:27发表:
Nice site!
sprint pcs ringtones | ringtone composer | free cell phone ring tones | real ringtones | mosquito ring tone | motorola ring tones | tracfone free ringtones | free boost ringtones | ringtone mp3 | sms ringtones
72.36.192.* 于 2007-05-21 07:41:54发表:
Thank you!
free ringtone download | free sprint downloads | monophonic ringtones | free monophonic ringtones | kyocera ringtones | free music ringtones | country ringtones | ringtone free | free ringtones for verizon | free ringtone downloads
125.120.135.* 于 2007-05-21 07:21:01发表:
矫形外科医院。。
流水线。。
毛周角化症。。
christmas decorations。。
电子机票预定。。
中英文翻译。。
标书翻译。。
??制服。。
批?。。
隔??。。
假?。。
植?。。
婚?。。
婚??影。。
片姓名?。。
水晶指甲。。
83.39.84.* 于 2007-05-21 03:32:31发表:
8dbc482fdad9d859d4ee8bb79046e2a5 http://dcsellc.udxpzb.net/2005-07-06.html http://anarchyonlinezip.zikpwk.net/2005-09-09.html http://baglioredevelopment.udxpzb.net/2005-08-12.html http://tengani.smhiru.net/2005-09-13.html http://fobook.utwikd.net/2005-08-21.html http://marthasoap.pbcqvd.net/2005-08-08.html http://2ndsights.iuwexi.net/2005-08-12.html http://databasenewsletters.smhiru.net/2005-10-03.html http://quandt-beachwear.utwikd.net/2005-08-15.html http://mrwebby.iuwexi.net/2005-07-14.html b242eb585f2503f10c8eb79a53604d31
72.36.192.* 于 2007-05-20 07:47:42发表:
Great work!
nextel ringtone program | free tracfone ringtones | us cellular ring tones | mp3 ringtone converter | samsung ringtones | lg free ringtones | ringtones wav | free ringtones sprint | polyphonic ringtone | ringtone mp3
72.36.192.* 于 2007-05-20 07:47:04发表:
Thank you!
blackberry ringtones | nextel ringtones | verizon ring tones | ringtone uploader | free midi ringtones | ctu 24 ringtone | free ringtones alltel | free ringtones for cricket | lg ringtones | kyocera ringtones
72.36.192.* 于 2007-05-20 07:46:27发表:
Good design!
free hi fi ringtones | ringtones mp3 | free ringtone | free mp3 tones | christian ringtones | tmobile free ringtones | free real ringtones | alltel free ringtones | star wars ringtone | free nokia ringtone
72.36.192.* 于 2007-05-20 07:46:12发表:
Thank you!
ctu ringtones | samsung free ringtones | harry potter ringtone | free motorola ringtone downloads | us cellular free ringtones | free suncom ringtones | free cellular one ringtones | sprint pcs ringtones | composer ringtones | free cingular real ring tone
72.36.192.* 于 2007-05-20 07:45:40发表:
Good design!
free nokia downloads | free ringtones for cellular south | mp3 ringtones free | free boost mobile ringtones | midi ringtones | verizon ringtones free | motorola mp3 ringtones | ringtones | free ringtones for verizon | free hifi ringtones
72.36.192.* 于 2007-05-19 07:47:57发表:
Thank you!
sanyo ringtones | ringtone nokia | cellphone ringtones | mosquito ringtone | free cricket ringtones | harry potter ringtones | free motorola downloads | nokia free ringtones | nokia free downloads | free nextel ringtone downloads
72.36.192.* 于 2007-05-19 07:47:32发表:
Nice site!
nextel downloads | mosquito ringtone | free ringtones for tracfones | free ringtone | free ringtones for boost mobile | free midi ringtones | free ringtone | verizon ctu ringtone | motorola razr v3 ringtones | simpsons ringtone
72.36.192.* 于 2007-05-19 07:46:55发表:
Good design!
free samsung ringtones | star wars ringtone | motorola ring tones | free wav ringtones | free verizon ringtones | christian music ringtones | free cellular south ringtones | ringtones download | free 24 ctu ringtone | composer ringtones
72.36.192.* 于 2007-05-19 07:46:36发表:
Thank you!
free gospel ringtones | ringtones samsung | free ringtones for suncom | arabic ringtones | ring tones | ringtones verizon | wwe ringtones | ringtones for sprint phones | ringtone converter | cell phone ringtones
72.36.192.* 于 2007-05-19 07:46:06发表:
Good design!
free real tone ringtones | ringtones for us cellular | sprint ringtones | free ringtones lg | free polyphonic | sony ericsson ringtones | free 24 ringtone | t mobile ringtones | nascar ringtones | free us cellular ring tones
72.36.192.* 于 2007-05-18 07:43:37发表:
Thank you!
strip blackjack online | play roulette for free | free casino money | black jack | free download poker | casino free | poker game download | cleopatra slots | game slot | free games on line
72.36.192.* 于 2007-05-18 07:43:08发表:
Nice site!
bonus poker | video slots | free no deposit casino | no deposit online casino | play black jack | new free money online casinos | online gambling sites | free online slots no download | black jack free | download free poker game
72.36.192.* 于 2007-05-18 07:42:39发表:
Great work!
new casinos | free online slot machine games | free monopoly online | free poker | free online gambling | online roulette | bonus casino online | slots download | games download | play slots for fun
72.36.192.* 于 2007-05-18 07:42:15发表:
Good design!
casino game download | play blackjack | play free blackjack online | us poker sites | absolute poker | poker game free | free internet poker | online black jack game | free money no deposit | online slot machines
72.36.192.* 于 2007-05-18 07:41:46发表:
Well done!
slot machine | free video slots | blackjack software | online blackjack | free online slot machines | free slim slots | slim slots free | texas hold em free | free slots games | download party poker
88.6.146.* 于 2007-05-18 07:26:57发表:
cc4b1d85d5df2ffa6c9233dd4d4aa762 http://circolare-ministero-interno-settimana-corta.ynoxmw.org/ http://voto-ai-migliore-gioco-strategia.csapok.org/ http://i-file-audio-di-bombolo.nofnhx.org/ http://biglietto-d-invito-ricorrenza-com.shqsxs.org/ http://campi-di-concentramento-in-germania.ynoxmw.org/ http://albergo-2-o-3-stella-trentino.weejwl.org/ http://albergo-intrattenimento-bambino-val-susa.csapok.org/ http://garni-arnica-madonna-di-campiglio.ynoxmw.org/ http://jeep-grand-cherokee-regione-marche.csapok.org/ http://addestratori-di-cani-a-roma.csapok.org/ af5e5529e610c2f14667e2377e4b1e8c
220.184.70.* 于 2007-05-17 17:27:51发表:
hey,find wow gold click here
72.36.192.* 于 2007-05-17 07:47:14发表:
Great work!
free ringtones for tracfones | free ringtones sprint | real tone ringtones | lg free ringtones | free centennial wireless ringtones | arabic ringtones | free mp3 ringtone | ringtones nokia | free ringtones for alltel | sony ericsson ringtones
72.36.192.* 于 2007-05-17 07:46:47发表:
Thank you!
24 ctu ringtone verizon | free mp3 ringtones download | free cingular cell phone ringtones | nextel ringtones | ringtone midi | free cellular south ringtones | kyocera ringtones | free lg ringtones | download ringtones | tmobile free ringtones
72.36.192.* 于 2007-05-17 07:46:10发表:
Good design!
free mp3 download | free kyocera ringtones | ringtone motorola | sprint free ringtones | motorola keypress ringtones | cellphone ringtones | free ringtone | wav ringtones | verizon ring tones | mobile ringtones
72.36.192.* 于 2007-05-17 07:45:50发表:
Well done!
download free ringtones | free ringtones cingular | ringtones midi | country ringtones | music ringtones | mp3 ringtone converter | ring tones | ringtones for tracfones | polyphonic ringtone | crazy frog ringtone
72.36.192.* 于 2007-05-17 07:45:23发表:
Great work!
free mobile phone ringtones | boost mobile ringtones | free sms ringtones | christian music ringtones | free ringtones for nextel | free ringtones for samsung | ctu ringtone | ringtone download | free polyphonic | 24 ringtone free
125.120.139.* 于 2007-05-17 00:41:05发表:
起名…
replica watch…
外国?替…
?替 取引…
压滤机…
排污泵…
水泵…
传感器…
光电传感器…
电脑维护…
网络监控…
网络管理软件…
聊天记录…
空压机…
灯具…
中国福利彩票…
217.136.201.* 于 2007-05-16 21:56:12发表:
287a175505650b335bcb4e466e886332 http://centro-infanzia-riconosciuti-regione-veneto.hfnghd.co.in/ http://tipi-di-carattere-di-scrittura.znuawz.biz/ http://ordine-dei-farmacisti-provincia-genova.hfnghd.name/ http://il-sottosviluppo-del-terzo-mondo.znuawz.name/ http://deliberazione-della-giunta-regionale-del-veneto.znuawz.net/ http://rimedio-naturale-contro-epatite-c.hfnghd.co.in/ http://midi-file-karaoke-io-canto.znuawz.com/ http://strano-caso-cane-morto-mezzanotte.hfnghd.biz/ http://capodanno-villa-dei-principi-roma.hfnghd.biz/ http://comune-di-santa-maria-di-castellabate.hfnghd.biz/ c5b410f967c066628d7832ce0ac5b28e
72.36.192.* 于 2007-05-16 07:43:02发表:
Well done!
gambling machines | free slim slots | online bingo | free texas holdem | poker machines | download poker | free online slots games | download party poker | poker usa | no deposit bonus
72.36.192.* 于 2007-05-16 07:42:31发表:
Nice site!
casino slot games | black jack games | poker sites | blackjack free games | online blackjack free | slot machine games free | free online slots no download | black jack game | russian roulette game | free money no deposit casinos
72.36.192.* 于 2007-05-16 07:41:59发表:
Good design!
free slots no download | poker texas | play slots for free | download free poker games | online slots | casino games online | games on line | slots download | casino on net | poker online free
72.36.192.* 于 2007-05-16 07:41:36发表:
Nice site!
casino free | free slot | play free online slots | pokerstars download | online free poker | play online poker | live poker | casino deposit bonus | free poker game | video slot machine
72.36.192.* 于 2007-05-16 07:41:05发表:
Good design!
internet casino | on line casinos | play slot machines for free | multiplayer poker | texas holdem free online | free poker games online | free download poker | free slot games | free casino cash | free roulette games
200.109.152.* 于 2007-05-16 06:59:14发表:
9670ebd4050f8525f596c685e7bc4d3d http://antro-dei-single-metropolis.fflnuc.co.in/ http://torneo-annovazzi.fflnuc.com/ http://copenaghen-kgb.hzqpsj.net/ http://avvocato-matrimonialisti-napoli.fflnuc.net/ http://sangue-pazzo-marco-tullio-giordana.fflnuc.biz/ http://fabulae-atellane.fflnuc.com/ http://sallustio-micipsa-adotta-giugurta.fflnuc.biz/ http://6680-mediaworld-padova.fflnuc.com/ http://vesto-orsetto.hzqpsj.com/ http://tiralatte-medela-elettrico.fflnuc.com/ e1c77cc030a7259f186177a086fb8a83
72.36.192.* 于 2007-05-15 07:43:13发表:
Well done!
monopoly slots | online roulette game | free roulette games | free online casino game | slot game | free online casino games | free online slot games | new no deposit online casinos | free texas holdem | strip black jack
72.36.192.* 于 2007-05-15 07:42:45发表:
Thank you!
no limit texas hold em | free strip blackjack | casino free games | online casino bonus | no deposit bingo | free black jack | free blackjack game | online poker sites | monopoly slot machine | poker machines
72.36.192.* 于 2007-05-15 07:42:11发表:
Nice site!
slot machines free | flash casino games | poker bonus | free poker game | free slot machines games | world series of poker download | online free slots | blackjack software | sims free slots | deposit bonus casino