经常发现一些网友问分析函数的问题,虽然分析函数比较多,有一些分析函数也确实比较难以理解,会给初学者有很大的心理压力,其实对于大部分的分析函数,一般都不会有机会用到,所以掌握常用的几个分析函数就可以啦。
常用的分析函数如下所列:
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-28 08:08:12发表:
Thank you!
black jack | play texas holdem online | slot machines | poker sites | online gambling | free online slots no downloads | fun slots | free slots | poker download | free casino downloads
85.155.18.* 于 2007-05-27 18:32:11发表:
2a686f0976af8646677e8220a1605061 http://lcitij.info/canil/canil-sao-paulo-teckel.html http://sxrzpn.info/tapete/tapete-de-borracha-para-carro.html http://lcitij.info/anzen/anzen-fabrica.html http://wfcqxw.info/estacao/estacao-radar-meteorologico.html http://xwqumn.info/pciconcursos/pciconcursos-ministerio-agricultura.html http://wfcqxw.info/foto/foto-cidade-de-tatui.html http://wfcqxw.info/empresa/empresa-transporte-pantera-ltda.html http://lcitij.info/fabrica/fabrica-de-formatura.html http://xwqumn.info/compra/compra-bafometro.html http://ovvkft.info/mudar/mudar-msn-ingles-portugues.html 6d9dd05b81c19c63ae8e87cbbcfe2050
72.36.192.* 于 2007-05-27 08:02:08发表:
Well done!
free gambling | online free blackjack | free slots game | texas hold em online | usa online casino | free cleopatra slots | slot machine free | cleopatra slot | free multi line slots | texas holdem poker
72.36.192.* 于 2007-05-27 08:01:35发表:
Nice site!
free blackjack | no deposit casino bonus | free online slots no download | online poker sites | online casino games | free poker websites | internet blackjack | play money poker | on line gambling | free online black jack
72.36.192.* 于 2007-05-27 08:00:48发表:
Thank you!
video poker machines | free online video slots | no download free slots | free texas hold em online | blackjack games | free blackjack | free slot games | play free roulette | free on line poker | online poker game
72.36.192.* 于 2007-05-27 08:00:30发表:
Great work!
on line poker | black jack game | online gambling sites | free video slot games | bonus poker | free texas hold em | download free poker games | free slot game | free texas holdem | internet casino
72.36.192.* 于 2007-05-27 07:59:55发表:
Great work!
free poker games | casino free game online | poker texas holdem | poker online free | blackjack game | no deposit casinos | free online casino games | no deposit bonuses | free online bingo | strip blackjack
213.39.163.* 于 2007-05-27 02:45:54发表:
7af6aaa321d05825a90cde4ec637b737 http://pegekq.info/arte/arte-kleiton.html http://ifrtox.info/piso/piso-laminado-de-madeira.html http://ifrtox.info/letra/letra-marina-elali.html http://grpytd.info/armazem/armazem-da-moto.html http://xvqeoy.info/estilo/estilo-letra-tatuagem.html http://xvqeoy.info/flogao/flogao-diante-do-trono.html http://grpytd.info/brasa/brasa-lia.html http://xvqeoy.info/bufe/bufe-de-massas.html http://xvqeoy.info/campones/campones-r-j.html http://mnopyi.info/ducato/ducato-cargo.html 899833c87d41a40d77c99858b4681e10
217.216.178.* 于 2007-05-26 12:46:59发表:
a10e2997d429fc9eb4ead96ccef49c4e http://sxrzpn.info/download/download-de-musica-vida-reluz.html http://sxrzpn.info/insituto/insituto-porto-alegre-metodista.html http://wfcqxw.info/transa/transa-banheiro.html http://sxrzpn.info/nome/nome-das-estrela-do-ceu.html http://xwqumn.info/animal/animal-brasileiros-estao-extincao.html http://sxrzpn.info/radio/radio-px-voyager-9000.html http://ovvkft.info/musica/musica-flash-back-anos-70-80-90.html http://ovvkft.info/tabela/tabela-calculo-grau-desnivel.html http://ovvkft.info/mensagem/mensagem-para-o-dia-do-trabalho.html http://ovvkft.info/veicular/veicular-6945-hp.html 3c6c60ce2277246c0f4063c97808fccb
72.36.192.* 于 2007-05-26 08:01:02发表:
Good design!
black jack download | play slot machines for free | free online slot machines | free bonus slots | play blackjack | free black jack game | free online poker game | black jack free | empire poker | free poker on line
72.36.192.* 于 2007-05-26 08:00:24发表:
Good design!
casino games online | free bingo | blackjack online free | free online casinos | sim slots | online poker free | texas hold em free | no deposit casino bonuses | blackjack for free | play online poker
72.36.192.* 于 2007-05-26 07:59:42发表:
Great work!
free slot machine game | three card poker | pokerstars download | casino open usa | slot machine | black jack online | free cleopatra slots | free slot downloads | online baccarat | texas hold em online
72.36.192.* 于 2007-05-26 07:59:23发表:
Well done!
texas holdem poker | free monopoly slots | slots online | internet casino | poker stars | play free slots | video slots free | roulette game | online poker game | online roulette free
72.36.192.* 于 2007-05-26 07:58:51发表:
Thank you!
slot machine | casino games free | play roulette free | free online casino slots | bingo online | free no download slots | slot machine games free | free roulette games | free online bingo games | new no deposit online casinos
85.124.209.* 于 2007-05-25 20:43:04发表:
866f357c93fef05ff0559806afd434ee http://final-fantasy-8-i-trucchi.lxcjch.org/ http://offerta-reale-deposito-bancario-impugnazione-sentenza.lxcjch.org/ http://gazzetta-ufficiale-17-10-2006.pmdxoz.org/ http://trasformatore-basso-profilo-alta-tensione.nuusjq.org/ http://presse-da-stiro-uso-domestico.nuusjq.org/ http://gara-concorso-manutenzione-verde-provincia-varese.lxcjch.org/ http://copertina-fiorello-baldini-visti-dietro.qumpvr.org/ http://borsa-di-milano-in-tempo-reale.lxcjch.org/ http://casa-feria-padre-trinitari-roma.sfmyzx.org/ http://dpr-131-86-art-54.pmdxoz.org/ f4e92eaca3a0992e5377af9d5fb45ea4
72.36.192.* 于 2007-05-25 08:02:42发表:
Nice site!
paradise poker | casino on line | online roulette game | casino deposit bonus | black jack games | play money poker | black jack download | free online casino games | free casino games online | slot machines
72.36.192.* 于 2007-05-25 08:02:09发表:
Well done!
internet casinos | blackjack for free | download free poker games | free video | internet casino | games online | no deposit bonus | poker free | free black jack game | free texas hold em
72.36.192.* 于 2007-05-25 08:01:25发表:
Nice site!
free texas holdem online | games on line | online baccarat | free online poker | texas poker | free slots | blackjack software | free poker games | new casinos | free black jack games
72.36.192.* 于 2007-05-25 08:01:20发表:
Nice site!
free online poker game | online video slots | free blackjack | play free roulette | free online slot machines | free slots for fun | texas hold em | free slots | free slots machines | casino on net
72.36.192.* 于 2007-05-25 08:00:35发表:
Nice site!
free on line slots | free multi line slots | free poker downloads | free online poker | free slim slots | slot machine game | noble poker | flash casino games | casino games online | free casino bonus
125.120.137.* 于 2007-05-25 00:43:42发表:
工作流
工作流
paper cutting machine
类风湿关节炎
类风湿性关节炎
短信群发
烟灰缸
Magic cube
武汉网站建设
粉碎机
烟灰缸
包装机械
190.10.213.* 于 2007-05-25 00:23:55发表:
538bb2896a0a2bd630ba5ef54902a363 http://ristorante-prima-comunione-provincia-milano.ljznde.org/ http://rai-valle-d-aosta-it.beoqvk.org/ http://quella-villa-accanto-al-cimitero.rsxmtx.org/ http://link-http-www-80vogliadite-forumcommunity-net.ljznde.org/ http://cartolina-gratis-animata-buon-anno.ljznde.org/ http://dku-2-cavo-cavo-vari-cellulare.lwozoc.org/ http://museo-eremitani-padova-mostra-mantegna.ljznde.org/ http://diario-di-viaggio-a-berlino.ljznde.org/ http://crea-scritta-glitter-on-line.hgfrvc.org/ http://scaricare-gioco-dei-lemmings-pc.ikqtqu.org/ 46517f671cf87061af6ace763c7eda9d
81.202.218.* 于 2007-05-24 08:08:46发表:
6c8b03bdda55624e7a2cd2bcac42c51b http://ingrosso-intimo-on-line-andria.mbduev.org/ http://associazione-degli-industriali-di-rimini.mbduev.org/ http://elenco-lavorazioni-insalubri-prima-classe.pmdxoz.org/ http://confessione-ereditiera-libro-paris-hilton.sfmyzx.org/ http://messaggio-copia-windows-non-autentica.qumpvr.org/ http://consorzio-tutela-dell-olio-extra-vergine.sfmyzx.org/ http://presidio-ospedaliero-maria-ausiliatrice-torino.nuusjq.org/ http://camping-bella-italia-peschiera-del-garda.nuusjq.org/ http://approccio-dei-giovane-societa-d-oggi.lxcjch.org/ http://camera-di-commercio-italiana-barcellona.itwasb.org/ 7798902e03c54f1db3af807b5937ee1b
72.36.192.* 于 2007-05-24 07:45:15发表:
Thank you!
free phone ringtones | motorola mp3 | free nascar ringtones | voice ringtones | mosquito ringtone | arabic ringtones | razr v3 ringtones | ringtones for motorola q | free midi ringtones | free composer ringtones
72.36.192.* 于 2007-05-24 07:44:42发表:
Good design!
free star wars ringtones | nokia ringtones | ringtones for tracfones | free sprint pcs ringtones | mobile ringtones | free hifi ringtones | verizon ring tones | country music ringtones | cheap ringtones | 24 ctu ringtone verizon
72.36.192.* 于 2007-05-24 07:44:06发表:
Great work!
free nextel downloads | free anime | ringtones wav | free ringtones for samsung | free ringtones for us cellular | ringtones for alltel | free ringtone converter | free ringtones for sprint phones | free ringtone download | free cell phone ring tones
72.36.192.* 于 2007-05-24 07:43:49发表:
Good design!
hifi ringtones | free centennial wireless ringtones | wwe ringtones | ringtones for suncom | free ringtones for nokia | free lg ringtones | free ringtones for t mobile | ringtone ctu | tmobile ringtones | ringtones for cingular
72.36.192.* 于 2007-05-24 07:43:14发表:
Good design!
download free ringtone | verizon ctu ringtone | free ringtones for boost mobile | centennial ringtones | harry potter ringtone | free real ringtones | free nextel ringtone downloads | free nokia ringtone | midi ringtone | free ringtones for cricket
88.24.187.* 于 2007-05-23 15:53:53发表:
5ccc51c01d852ad3ff2defc95b7edf07 http://soussimpression.smhiru.net/2005-07-27.html http://athleticinteractivemedia.udxpzb.net/2005-07-18.html http://nj-realtor.zikpwk.net/2005-07-30.html http://kennedyandlincoln.ipnwxi.net/2005-10-06.html http://gjom.smhiru.net/2005-07-26.html http://marthasoap.pbcqvd.net/2005-09-22.html http://growthfactorsupport.iuwexi.net/2005-07-27.html http://ehurricaneshelter.pbcqvd.net/2005-07-07.html http://horoscopefrancais.smhiru.net/2005-07-15.html http://ajorganicgroves.zikpwk.net/2005-08-11.html e7000c4d06986984b665ec9d15ae719a
72.36.192.* 于 2007-05-23 07:45:38发表:
Good design!
on line poker | casino on net | casino free games | free slot machine downloads | poker online free | free poker game download | poker games free | free poker software | play free blackjack online | play roulette free
72.36.192.* 于 2007-05-23 07:45:09发表:
Well done!
video slot machines | blackjack strip | free texas holdem poker | sims slots | online gambling | free slot machine games | internet casino | roulette free | free bingo online | new no deposit casino
72.36.192.* 于 2007-05-23 07:44:34发表:
Great work!
online baccarat | online free slots | no deposit online casinos | online casino bonus | no limit texas hold em | texas poker | monopoly slot machine | on line casinos | free casino downloads | free casino slots
72.36.192.* 于 2007-05-23 07:44:18发表:
Thank you!
free blackjack game | paradise poker | new no deposit online casinos | black jack games | poker texas | black jack strip | free texas hold em online | texas hold em online | free black jack game | free slots on line
72.36.192.* 于 2007-05-23 07:43:49发表:
Good design!
holdem poker | free texas holdem | play casino games | free online casinos | slots for free | cleopatra slot | free online slot machine games | strip blackjack online | download blackjack | flash casino
72.36.192.* 于 2007-05-22 07:45:56发表:
Well done!
internet blackjack | paradise poker | texas hold em poker | roulette download | online roulette free | online slot machines | play bingo | free online video slots | free poker | poker download
72.36.192.* 于 2007-05-22 07:45:24发表:
Well done!
play roulette for free | roulette games | casino open usa | full tilt poker | stud poker | slot games | free poker software | texas holdem free online | video slot machines | free online gambling
72.36.192.* 于 2007-05-22 07:44:49发表:
Great work!
online holdem | casino deposit bonus | strip blackjack games | computer poker | free online casino slots | free casino games | slots free | no deposit bingo | play free slots | free casino money
72.36.192.* 于 2007-05-22 07:44:31发表:
Well done!
free slot games | play online poker | free online slots games | free poker download | free blackjack | download party poker | free poker sites | free strip blackjack | free slots | cleopatra slots
72.36.192.* 于 2007-05-22 07:43:57发表:
Good design!
free casino bonus | no deposit casinos | free online games | online slot games | poker sites | no download free slots | best poker sites | free video slots | blackjack download | games on line
84.122.75.* 于 2007-05-21 20:50:40发表:
1c34894fe2361c285a07403bbc9b0e5d http://vietti-www-delos-fantascienza-com.osjckd.org/ http://daniele-de-rosso-tamara-pisnoli.vzlvrv.org/ http://video-erotici-da-vedere-subito.osjckd.org/ http://minh-city-paese-si-trova.nofnhx.org/ http://dizionario-enciclopedico-salute-medicina-treccani.shqsxs.org/ http://base-musicale-x-karaoke-gratis.ynoxmw.org/ http://comune-motta-san-giovanni-it.osjckd.org/ http://novella-giovanni-verga-lupa-descrizione-lupa.nofnhx.org/ http://spettatori-incassi-serie-2006-07.ynoxmw.org/ http://comiso-chiesa-maria-ss-stella.csapok.org/ 417a8203d1b04948a6eb96aa4fb99866