经常发现一些网友问分析函数的问题,虽然分析函数比较多,有一些分析函数也确实比较难以理解,会给初学者有很大的心理压力,其实对于大部分的分析函数,一般都不会有机会用到,所以掌握常用的几个分析函数就可以啦。
常用的分析函数如下所列:
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-06-03 07:52:10发表:
Nice site!
free no deposit casino | gambling sites | cleopatra slot | free poker game | free texas hold em online | casinos on line | free online poker | free money no deposit | black jack game | free online poker games
83.53.129.* 于 2007-06-03 01:58:14发表:
9ae1f2b884b4a3addb2e32471fcf2a53 http://gallenoattilio.lskson.org/uragano-ivan-agghiacciante/index.htm http://canettisefarditi.bdizoa.org/deltamotore-paracadute/index.htm http://gileragp800consegna.shxghd.org/olympus-pesca-line-stopper/index.htm http://truccocossacks1.sdibjo.org/egisto-bontempi/index.htm http://steleprestino.sdibjo.org/pompa-biraghi/index.htm http://diddlinaprincipessa.sdibjo.org/credito-cooperativo-di-lesmo/index.htm http://valanviaggiocom.sdibjo.org/analisi-metrica-sonetto-lxi-canzoniere-petrarca/index.htm http://truchithesimpsfuorituttops2.ksibgs.org/moxie-panca/index.htm http://filmmessicanibunuel.ksibgs.org/veschini-ponte-san-giovanni/index.htm http://centrocefaleegrottaferrata.ksibgs.org/coltello-corno-berti/index.htm 691e5261e7f26fe9bfca38d324fb1940
72.36.192.* 于 2007-06-02 07:59:45发表:
Nice site!
texas hold em online | free online casino game | free video slot games | best usa casino online | play bingo | video slot machines | best online casino games | roulette game | no deposit online casinos | free slot machine downloads
72.36.192.* 于 2007-06-02 07:59:08发表:
Nice site!
free slot machine | free poker games | monopoly online | online poker game | video poker machines | play slots | free casino slot games | play roulette for free | free bingo | free casino games
72.36.192.* 于 2007-06-02 07:58:17发表:
Nice site!
free on line slots | slots online | slots for fun | free poker games | slot machine games free | free play slot machines | download free poker | holdem poker | casino games free | free online blackjack
72.36.192.* 于 2007-06-02 07:57:52发表:
Great work!
free slots no download | free slot games | free video slots | play free blackjack online | pacific poker | black jack game | free online games | texas hold em poker | three card poker | poker for fun
72.36.192.* 于 2007-06-02 07:57:23发表:
Nice site!
free online video slots | online blackjack | slot machine game | black jack strip | free online texas holdem | free slot games online | free poker | world series of poker download | free online strip blackjack | free slots download
201.234.168.* 于 2007-06-02 05:08:47发表:
8babf296d0b645d5ef9c8d1dfae4ae6a http://boschwfo1662lavatrice.akqcvy.org/nankang-pneumatico/index.htm http://conversionelibbrechili.akqcvy.org/ibox-tim-itmms/index.htm http://ditloide404nt.akqcvy.org/benq-notebook-joybook-s53w-y01-white/index.htm http://sugherificiomodena.nlamku.org/colite-mucomembranosa/index.htm http://schemacavolptwindowscommander.beajbg.org/dellera-arma/index.htm http://schemapuntocrocepreciousmoments.nlamku.org/assistenza-caldaia-pensotti-bari/index.htm http://pagilegialla.beajbg.org/borsa-tracolla-caf-e8-noire/index.htm http://salottomoddado.akqcvy.org/liceale-si-masturbano/index.htm http://tritacarneamb.seyzuo.org/soldo-patrician-3/index.htm http://googleitwwwikeait.inkrxe.org/antitrust-test-diagnostico-diabete/index.htm 63aa5c5d6850cbd0ab7a0b3644130d9e
201.232.169.* 于 2007-06-01 11:41:31发表:
ab466ab5ea080d685de4b236fa47ed12 http://gaggiaespressomacchinacaffc3a8.uzghnh.org/appendiabito-muro-singolo/index.htm http://marcobrucknercamminifede.pdjkai.org/gazebo-esagonale/index.htm http://produttorestendibiancheriaalluminio.leikrf.org/lavoretto-natalizio-mollette/index.htm http://tronydesenzano.gkgobd.org/albergo-bristol-fiumalbo/index.htm http://qqvestito.pdjkai.org/spurgo-aria-termosifone/index.htm http://eutimilsmettere.uzghnh.org/argo-r410-condizionatore-ventilatore/index.htm http://pigiamadonnasalopette.mljuyb.org/prezziario-agricoltura-foresta-regione-puglia/index.htm http://transinculatiasino.leikrf.org/carta-topografica-cormons/index.htm http://ghddrogacapello.gkgobd.org/escort-italia-suddivisa-regione/index.htm http://cooppescatoriposillipo.gkgobd.org/disco-roy-montesarchio/index.htm 5447788e0ee79eeca3d64876f41eb1cf
72.36.192.* 于 2007-06-01 07:59:06发表:
Nice site!
casinos online | download poker game | casino poker | texas holdem | no deposit casino | free casino slots | absolute poker | play roulette free | bingo online | no deposit bonus casinos
72.36.192.* 于 2007-06-01 07:58:32发表:
Thank you!
free bonus slots | slots online | download poker | video slot machine | free bingo online | free slot machine downloads | casino free | play texas holdem free | hold em poker | online poker free
72.36.192.* 于 2007-06-01 07:57:42发表:
Great work!
free poker software | video slots | free monopoly online | us poker sites | free texas hold em online | blackjack free games | casino games online | strip blackjack free | free blackjack | blackjack strip
72.36.192.* 于 2007-06-01 07:57:17发表:
Great work!
free money casinos | 3 card poker | free texas holdem poker | free games on line | play black jack | slot machine | free slots | slot machine software | free slot machine | new no deposit casinos
72.36.192.* 于 2007-06-01 07:56:47发表:
Thank you!
gambling machines | video slot games | video poker | casino free games | free casino games | party poker | casino online download | world series poker | casino deposit bonus | black jack game
60.177.54.* 于 2007-06-01 07:03:52发表:
前列腺炎
凿岩台车
化工机械
黄金海岸
北戴河
硬度计
测距仪
万用表
电子天平
水分测定仪
温度计
温湿度计
示波器
红外热像仪
月饼
月饼
取名字
72.36.192.* 于 2007-05-31 08:01:23发表:
Great work!
free motorola downloads | music ringtones | cingular wireless ringtones | free ringtones for nextel | anime ringtones | free mp3 download | star wars ringtone | motorola mp3 | razr ringtones | crazy frog ring tone
72.36.192.* 于 2007-05-31 08:00:54发表:
Well done!
jamster ringtones | 24 ctu ringtone verizon | free composer ringtones | motorola ring tones | alltel free ringtones | ringtone websites | free country ringtones | free ringtone | funny ringtones free | free ringtones for tracfone
72.36.192.* 于 2007-05-31 08:00:02发表:
Nice site!
free nokia ringtone | free wwe ringtones | sprint phones | christian music ringtones | t mobile ringtones | free ringtones for suncom | free harry potter ringtones | centennial ringtones | nextel downloads | free midi ringtone
72.36.192.* 于 2007-05-31 07:59:41发表:
Thank you!
harry potter ringtones | ringtones wav | free cellular south ringtones | free ring tones | ringtones free | free 24 ringtone | ringtones mp3 | real ringtones | free hifi ringtones | alltel ringtones
72.36.192.* 于 2007-05-31 07:59:13发表:
Thank you!
free samsung ringtones | free ctu ringtone | real music ringtones | polyphonic ringtone | free real ringtones | free lg ringtones | ringtone jukebox | free sprint pcs ringtones | ringtone midi | verizon ctu ringtone
125.120.137.* 于 2007-05-31 07:03:03发表:
wow power leveling,Lineage 2 wow power leveling.
wow power leveling lvl service
and cheap wow power leveling,wow power leveling
sale for youWe offerwow power leveling
72.36.192.* 于 2007-05-30 08:01:54发表:
Well done!
free poker games | usa online casino | free poker online | play blackjack free | games slots | bingo online | free games on line | slot games | video poker machines | on line poker
72.36.192.* 于 2007-05-30 08:01:21发表:
Well done!
texas holdem | multiplayer poker | poker for free | free texas holdem online | texas hold em free | new no deposit online casinos | hold em poker | free slot downloads | slot machine free | free video slot games
72.36.192.* 于 2007-05-30 08:00:30发表:
Nice site!
free casino online | monopoly online | online slot games | cleopatra slot machine | three card poker | free slots | poker games online | play free slots online | paradise poker | free online black jack
72.36.192.* 于 2007-05-30 08:00:07发表:
Thank you!
sims slots | no deposit bingo | slots of fun | poker game online | texas holdem free online | free online poker games | free video | free poker game download | new no deposit casinos | free slots downloads
72.36.192.* 于 2007-05-30 07:59:38发表:
Well done!
free joker poker | slots download | play blackjack | on line casinos | free casino downloads | online slot machines | online casino | video slots | online roulette | online black jack game
200.84.3.* 于 2007-05-30 03:34:38发表:
5fee6d9f71e1f55fa12c12fc339de61c http://wfcqxw.org/garota/garota-de-luxo-em-sao-paulo.html http://pegekq.org/via/via-dolorosa-em-mp3.html http://ovvkft.org/aluguel/aluguel-salao-festa-londrina.html http://pegekq.org/ribeiraopreto/ribeiraopreto-cesd-br.html http://pegekq.org/gostou/gostou-amiga.html http://wfcqxw.org/travesti/travesti-de-campina.html http://mnopyi.org/prestacao/prestacao-servico-brasileiros-japao.html http://mnopyi.org/tv/tv-10-polegada-radio-fm-1030mac-semp-toshiba.html http://wfcqxw.org/renato/renato-texeira.html http://ovvkft.org/historia/historia-dos-bairro-de-sao-paulo.html a91f06099d8916d08fc86aebeef191c8
72.36.192.* 于 2007-05-29 08:03:36发表:
Good design!
mosquito ringtone | ringtones for alltel | cheap ringtones | free ctu ringtone | free nextel ringtone downloads | ringtone creator | cellphone ringtones | free country ringtones | 24 ring tones | ringtones mp3
72.36.192.* 于 2007-05-29 08:03:00发表:
Thank you!
24 ringtone | samsung free ringtones | ringtones for nextel | bollywood ringtones | centennial wireless | tmobile ringtones | motorola ringtone | free real tone ringtones | free midi ringtone | free ringtones sprint
72.36.192.* 于 2007-05-29 08:02:13发表:
Good design!
online ringtone converter | free ringtones alltel | free polyphonic ringtone download | mtv ringtones | cellular south | free cellular one ringtones | ringtones lg | nokia free downloads | lg free ringtones | free ringtones for cricket
72.36.192.* 于 2007-05-29 08:01:53发表:
Great work!
blackberry ringtones | us cellular free ringtones | free anime ringtones | ringtone websites | free ringtones motorola | sprint pcs ringtones | christian ringtones | funny ringtones | ringtones samsung | free downloadable ringtones
72.36.192.* 于 2007-05-29 08:01:18发表:
Thank you!
ctu ringtone verizon | free motorola downloads | verizon ringtones free | free star wars ringtones | download free motorola ringtone | nokia ringtone | gospel ringtones | free boost ringtones | ringtones for nokia | download free polyphonic ringtones
60.177.54.* 于 2007-05-29 07:59:11发表:
性功能障碍
东莞液压设备
昆明高尔夫
自动门
加湿机
200.84.21.* 于 2007-05-29 02:17:02发表:
63b790f8594cb677a61d7b6a7fd923c1 http://lcitij.org/consultoria/consultoria-treinamento-holistica.html http://xvqeoy.org/dando/dando-o-anus.html http://xvqeoy.org/lixeira/lixeira-vulcao.html http://sxrzpn.org/cg/cg-titan-150.html http://lcitij.org/av/av-orozimbo-maia.html http://xvqeoy.org/marfinite/marfinite-no-rio-de-janeiro.html http://grpytd.org/reportagem/reportagem-degradacao-d-meio-ambiente.html http://xwqumn.org/bateria/bateria-instrumento-musicais.html http://sxrzpn.org/site/site-do-senai-de-sao-paulo.html http://grpytd.org/itapemirim/itapemirim-transporte-rodoviarios.html ea84313ff4cf4b8bb8ec851c693c83a5
60.176.82.* 于 2007-05-28 22:09:11发表:
world of warcraft power leveling guide
Lineage 2 power level
Runescape money
eq2 gold
gold wow
everquest 2 gold
eq2 plat
archlord gold
lineage 2 adena
wow gold
gw gold
wow power leveling
wow powerleveling
leather sofa
81.202.185.* 于 2007-05-28 10:37:01发表:
e023df5d1406bb89fa8b239a4d4ecb5f http://ifrtox.info/sistema/sistema-operacional-sbs.html http://grpytd.info/estrada/estrada-do-dende.html http://xvqeoy.info/preferencialmente/preferencialmente-sexo-feminino.html http://xvqeoy.info/espingarda/espingarda-tres-canos.html http://xvqeoy.info/calculo/calculo-de-matriz.html http://xvqeoy.info/insaio/insaio-fotografico.html http://mnopyi.info/inflamacao/inflamacao-bexiga.html http://grpytd.info/caminhonetas/caminhonetas-3-4-ford-gm.html http://xvqeoy.info/jogo/jogo-brincadeira-material-reciclaveis.html http://grpytd.info/condicao/condicao-falar-fluencia.html 921da3b25f91ff5411abb8e73f72697f
72.36.192.* 于 2007-05-28 08:10:32发表:
Great work!
play black jack | online bingo games | online holdem | poker websites | slot machine | online blackjack free | cleopatra slot machine | poker online free | free slot machine games online | free casino cash
72.36.192.* 于 2007-05-28 08:09:57发表:
Good design!
casino black jack | online black jack game | new online casinos | online roulette | no deposit online casino | noble poker | free slot games online | online baccarat | download poker games | free sim slots
72.36.192.* 于 2007-05-28 08:09:09发表:
Good design!
party poker | free online poker game | no deposit casinos | play roulette for free | star poker | free poker games | free roulette games | slot machine software | play free blackjack | free casino
72.36.192.* 于 2007-05-28 08:08:41发表:
Thank you!
free slots game | free slots no downloads | casino games online | online poker game | poker games | free texas hold em | free online gambling | casino bonus | new no deposit casinos | texas holdem free online