经常发现一些网友问分析函数的问题，虽然分析函数比较多，有一些分析函数也确实比较难以理解，会给初学者有很大的心理压力，其实对于大部分的分析函数，一般都不会有机会用到，所以掌握常用的几个分析函数就可以啦。
常用的分析函数如下所列:
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
200.75.107.* 于 2007-06-08 20:40:00发表:
9c2df6b11f7722310eba916fde673640 http://cazzo-in-culo-tra-uomo.hwqegr.org/ http://sicilia-cronaca-01-11-2006.akermn.org/ http://soluzione-prove-esame-abilitazione-geometra.pauhzy.org/ http://video-manga-porno-da-scaricare.akermn.org/ http://donna-ke-si-scopano-cavallo.iumzde.org/ http://no-dig-and-costo-and-prezzo.akermn.org/ http://scart-director-27s-cut-miglia.pauhzy.org/ http://vendita-foto-camera-digitale-konica-minolta.mbxbva.org/ http://risalire-all-intestatario-numero-cellulare.pauhzy.org/ http://il-fuoco-di-d-annunzio.iumzde.org/ e44c2d91c99facb894d3b26e91151560
72.36.192.* 于 2007-06-08 07:52:56发表:
Well done!
no deposit casino | free poker on line | empire poker | free sims slots | online slot machines | free money no deposit casinos | free online roulette | download blackjack | slim slots free | online baccarat
72.36.192.* 于 2007-06-08 07:52:25发表:
Great work!
computer poker | free casino slot games | play roulette free | poker sites | free no deposit casinos | strip blackjack | blackjack game | texas hold em online | black jack game | download poker
72.36.192.* 于 2007-06-08 07:51:25发表:
Well done!
sim slots | poker websites | free online casino slots | free no download slots | online poker usa | free online slot machine games | three card poker | slots for free | cleopatra slot machine | free blackjack
72.36.192.* 于 2007-06-08 07:51:08发表:
Good design!
free online slots | deposit bonus casino | bonus casino free online | free bingo online | strip blackjack free | virtual poker | free online slot games | free poker | best bonus casino | texas holdem free online
72.36.192.* 于 2007-06-08 07:50:57发表:
Well done!
black jack free | casino game | casino free | best poker sites | free strip black jack | slot machine | casino on line | online gambling sites | free slot machine downloads | monopoly slot machine
200.127.195.* 于 2007-06-07 23:11:44发表:
f1eb369a85813f60eddb912e61eee6ed http://risotto-con-nero-delle-seppia.sjfnnx.org/ http://borse-di-studio-2005-2006.ouwnql.org/ http://digitale-terrestre-7-carta-piu.fjhozm.org/ http://case-in-vendita-in-campania.ouwnql.org/ http://allevamento-boxer-colla-dell-infinito.tpfcwv.org/ http://contratto-commercio-diritto-studio-modulistica.yixkrt.org/ http://web-design-sviluppo-grafica-mmm.sjfnnx.org/ http://donna-quarantenni-che-fanno-sesso.kkwhbs.org/ http://concorso-internet-foto-culo-donna.ulhxdx.org/ http://crisi-d-identita-identita-professionale.kkwhbs.org/ 2e2f8656ca7971267ae7180fc612fe21
72.36.192.* 于 2007-06-07 09:43:49发表:
Nice site!
star wars ringtones | us cellular ring tones | free nextel downloads | ctu ringtone verizon | 24 ctu ringtone verizon | free ringtones for t mobile | free anime ringtones | free christian ringtones | free verizon ringtones | cingular free ringtones
72.36.192.* 于 2007-06-07 09:42:33发表:
Good design!
wav ringtones | jamster ringtones | cellular south | mosquito ringtone | kyocera ringtones | free composer ringtones | ringtones samsung | sanyo ringtones | download ringtones | free ringtones
72.36.192.* 于 2007-06-07 09:41:18发表:
Thank you!
motorola ringtone | centennial wireless ringtones | midi ringtone | free ringtone | free polyphonic ringtone download | free ringtones for sprint phones | ringtone download | gospel ringtones | free alltel ringtones | free ringtones cingular
72.36.192.* 于 2007-06-07 09:40:01发表:
Good design!
ringtones nokia | u s cellular ringtones | free tracfone ringtones | tmobile free ringtones | ringtone websites | ringtone motorola | lg free ringtones | free mobile ringtones | nextel ringtone | free ringtones for lg
72.36.192.* 于 2007-06-07 09:38:58发表:
Thank you!
download free nokia ringtone | us cellular ringtones | free ringtones for nokia | free nascar ringtones | online ringtone converter | free ctu ringtone | free ringtone download | centennial ringtones | free polyphonic | free ring tones
200.92.142.* 于 2007-06-07 01:53:36发表:
ceb9a2ec7962bc0fe5206caecd5111b4 http://configurazione-collegamento-internet-lg-8120.dpydtd.info/ http://giudice-pace-ricorso-cancellazione-protesto.vkzwxs.info/ http://garante-privacy-29-dicembre-2005.vkzwxs.info/ http://banca-popolare-alto-adige-it.zjtbra.info/ http://finanza-aziendale-sandro-sandri-emanuele.ytqkdb.info/ http://buono-fruttiferi-postale-indicizzati-scadenza-anno.urajxu.info/ http://lettore-smart-card-hard-disk.dpydtd.info/ http://fratello-rienzo-costruzione-s-croce-molise.rwikgt.info/ http://macchina-giardino-leo-marx-prezzo.hwqovr.info/ http://ferita-postoperatoria-dell-ernia-inguinale.lgrhpd.info/ 6dea66dd0952ca77d762129bda0df247
60.176.83.* 于 2007-06-07 00:55:00发表:
72.36.192.* 于 2007-06-06 07:59:02发表:
Good design!
us poker sites | free casino slot games | no deposit bonus | free money no deposit | free online slot machine | free online poker | blackjack download | free casino bonus | online poker game | free slots for fun
72.36.192.* 于 2007-06-06 07:58:29发表:
Thank you!
casino slots | games online | online free poker | slots of fun | black jack online | texas holdem free | free internet poker | free casino slot | slots free | free online bingo
72.36.192.* 于 2007-06-06 07:57:40发表:
Well done!
free texas holdem online | video poker machines | free poker games online | no download slots | play poker | free online poker game | free no deposit casino | online slots free | new online casinos | play bingo online
72.36.192.* 于 2007-06-06 07:57:19发表:
Good design!
blackjack online | free online strip blackjack | 3 card poker | free online gambling | casino online | computer poker | games on line | online roulette | texas hold em | free games online
72.36.192.* 于 2007-06-06 07:56:48发表:
Good design!
free online texas holdem | pacific poker | slots online | best bonus casino | online black jack game | free slot machine | free slots | free poker games | download poker games | online blackjack
201.211.130.* 于 2007-06-06 04:46:54发表:
c7a89ed29cf6757dc949b8c4a77ef7fd http://spiegazione-secondo-libro-dell-eneide.duajwe.info/ http://ada-satragni-parla-annamaria-franzoni.xaotvu.info/ http://crema-idratante-hydraphase-legere-50-ml.yyunae.info/ http://boot-avviare-windows-xp-predefinito-vista.lbvsgo.info/ http://hentai-w-t-c-h.duajwe.info/ http://testo-piccoli-problemi-di-cuore.qirjux.info/ http://camillo-benso-conte-cavour-discorso-parlamentari.duajwe.info/ http://apprendistato-professionalizzante-lombardia-studio-professionale.duajwe.info/ http://stampante-hp-laser-jet-4250-n.xaotvu.info/ http://corso-aggiornamento-allenatore-pallavolo-como.qirjux.info/ 11bac96dbb32ab2fd1a6f4018c996a56
60.176.197.* 于 2007-06-05 10:36:43发表:
84.126.225.* 于 2007-06-05 10:02:52发表:
175d2869b47d931347cadaa3c60e8e63 http://illy-fossile.boixkk.info/ http://tredici-obelisco-egiziano-italia.kbucdn.info/ http://fenomeno-geofisico-ascendente.dhvvfi.info/ http://bournemouth-immagine-cittadina.boixkk.info/ http://partenope-s-r-nola.boixkk.info/ http://edizioni-didattiche-gulliver.wkermn.info/ http://startac-130-usato.wkermn.info/ http://adesivo-fluorescenti-torino.dhvvfi.info/ http://pisciata-ginecologo.fwpjkf.info/ http://camerino-nascoste-webcam.wkermn.info/ 4080af707aca2bbb96231fb1b4743d28
72.36.192.* 于 2007-06-05 07:57:14发表:
Nice site!
sim slots | no download slots | internet gambling | casino bonuses | online casino games | free slot downloads | free three card poker | blackjack for free | strip blackjack free | sims slots
72.36.192.* 于 2007-06-05 07:56:38发表:
Great work!
slot game | free play slot machines | flash casinos | free poker games | free online bingo | free casino | free poker websites | bingo games | online texas hold em | world series of poker download
72.36.192.* 于 2007-06-05 07:55:49发表:
Thank you!
texas holdem free | free slot machine games | black jack download | free blackjack | casinos on line | on line poker | play black jack | free slot play | free online casino slots | play blackjack online
72.36.192.* 于 2007-06-05 07:55:29发表:
Great work!
free sims slots | video poker | free casino game | free slot games online | free gambling | cleopatra slots | blackjack games | free slim slots | slot machines free | free poker game
72.36.192.* 于 2007-06-05 07:55:02发表:
Nice site!
no deposit casinos | casino download | online texas holdem | fun slots | roulette | free slot machine | roulette online | best poker sites | best bonus casino | internet casino
60.176.83.* 于 2007-06-04 20:50:16发表:
cheapest wow gold。。
lord of the rings gold。。
rs money。。
buy world of warcraft gold。。
cheap gold wow。。
rs2 money。。
lineage 2 adena。。
rs2 gold。。
adena。。
runescape gold。。
eq2 gold。。
l2 adena。。
everquest2 plat。。
everquest2 platinum。。
everquest2 gold。。
eq2 plat。。
eq2 platinum。。
Magic cube。。
Magic cube。。
201.209.25.* 于 2007-06-04 15:43:15发表:
cca65a6eae1ca6d053ad7565f097bafc http://studio-o-ricerca-franchising-padova.nfvzoo.org/ http://lettera-ai-ragazzo-dell-acr.vprmbs.org/ http://storia-dito-amore-non-c.dqiqbg.org/ http://adattatore-video-media-player-video-ipod.dqiqbg.org/ http://lettera-al-datore-di-lavoro.nfvzoo.org/ http://allattamento-al-seno-allergia-al-latte.vprmbs.org/ http://tutto-gratis-cazzo-musculosi-gay.dlmpxx.org/ http://dublino-temple-bar-music-centre.vprmbs.org/ http://cambiare-display-samsung-sgh-e700.dqiqbg.org/ http://elezione-rsu-modulistica-commissione-elettorale.dqiqbg.org/ e2344a7b53a49ae4d6fdb2a64dbf9945
72.36.192.* 于 2007-06-04 07:58:13发表:
Well done!
roulette game | online baccarat | no deposit casino | gambling online | sims free slots | no deposit bonus casinos | free money no deposit | casino game download | free online texas holdem | download slots
72.36.192.* 于 2007-06-04 07:57:36发表:
Well done!
poker free | free poker sites | free online poker game | free poker on line | free online games | free games on line | free video slots | online black jack game | video slots | sims slots
72.36.192.* 于 2007-06-04 07:56:51发表:
Good design!
black jack download | free slots game | slot machine | online roulette free | internet blackjack | games download | play roulette | free blackjack | free bingo games | video slots free
72.36.192.* 于 2007-06-04 07:56:38发表:
Well done!
bonus casino free online | gambling sites | poker sites | free strip blackjack | free online casino | strip blackjack game | casinos online | free slot games no download | online slots free | play slot machines for free
72.36.192.* 于 2007-06-04 07:56:05发表:
Great work!
free slots for fun | free on line poker | no deposit bingo | blackjack games | free casino games | free play slots | slot games | free video poker | play black jack | texas holdem free
81.34.228.* 于 2007-06-03 19:39:25发表:
6a38b88265b0ea531d1f3d941a0bda5c http://rubinettohansa.seyzuo.org/matita-waterproof-dior/index.htm http://schemapuntocrocepreciousmoments.nlamku.org/norvegese-inventato-preservativo/index.htm http://pagilegialla.beajbg.org/atrezzi-karate/index.htm http://googleitwwwikeait.inkrxe.org/centro-alzheimer-volpiano/index.htm http://telaiobicilook386.akqcvy.org/geda-distributore-automatico/index.htm http://selfcareliberoitpassword.seyzuo.org/tata-fujitsu-listino-prezzo/index.htm http://valoreriferimentocromogranina.nlamku.org/democrito-scacchi/index.htm http://recordatiamianto.inkrxe.org/gerusalemme-arabesque-restaurant/index.htm http://organizzazionebanchettomonreale.seyzuo.org/barolomeo-diaz/index.htm http://tecnicaingrossarepene.beajbg.org/shampoo-anticaduta-dercos-prezzo/index.htm 83869c431dabc6ba13fe3e3c64cc8ac5
60.176.197.* 于 2007-06-03 15:39:12发表:
FX。。
FX。。
FX。。
消?者金融。。
消?者金融。。
キャッシング。。
フットサル。。
?格。。
?婚式。。
FXシステムトレ?ド。。
?婚。。
??学校。。
アダルト?画。。
キャッシング。。
プロバイダ?。。
72.36.192.* 于 2007-06-03 07:54:16发表:
Nice site!
paradise poker | internet casino | free poker download | poker games free | casino free game online | poker games online | free games on line | play free blackjack online | blackjack games | free slots downloads
72.36.192.* 于 2007-06-03 07:53:41发表:
Good design!
free slots | casino free online | fun slots | play free roulette | slots for fun | online gambling sites | free slot games online | internet bingo | black jack games | slots free
72.36.192.* 于 2007-06-03 07:53:00发表:
Well done!
video poker | free three card poker | free video | no deposit bonuses | free poker | free blackjack game | games download | free slots machines | slot machines free | online holdem
72.36.192.* 于 2007-06-03 07:52:41发表:
Great work!
online slots free | online poker sites | blackjack game | play bingo | free online slot machine games | slot machine free | free online texas holdem | online casinos | free slots games | play poker for free