经常发现一些网友问分析函数的问题，虽然分析函数比较多，有一些分析函数也确实比较难以理解，会给初学者有很大的心理压力，其实对于大部分的分析函数，一般都不会有机会用到，所以掌握常用的几个分析函数就可以啦。
常用的分析函数如下所列:
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
65.110.43.* 于 2007-02-13 20:01:25发表:
Thank you!
bollywood ringtones | christian ringtones | nextel ringtone | free ringtones for lg | funny ringtone mp3 | free nokia ringtone | free ringtones samsung | ringtones for t mobile | 50 cent ringtones | free ringtones download
65.110.43.* 于 2007-02-13 20:01:06发表:
Well done!
nextel i730 ringtone software | free nokia poly ringtone | ringtones for a samsung | motorola mp3 ringtones | 24 ctu ringtone | cisco voice ip ringtones | ringtones for motorola | 24 ringtones | free download nokia ringtones | free sprint sanyo ringtones
64.89.16.* 于 2007-02-12 23:27:28发表:
online kasinos online kasinos http://velbert-blog.de/13.12.2006/online-kasinos/ giocare poker http://www.speakers.diablogando.it/main.php?id=giopoker giocare poker cartas poker cartas poker http://cartaspoker.zoomblog.com bonus http://www.bloguez.com/bonus8/ bonus http://promozioniinlineamiglior.splinder.com/ promozioni in linea migliori promozioni in linea migliori
72.232.63.* 于 2007-02-12 17:25:15发表:
Thank you!
free online casino slots | free sim slots | online games | free roulette online | free video slots | download party poker | play slots for free | free money casinos | texas holdem | video poker free
65.110.43.* 于 2007-02-12 17:24:39发表:
Good design!
world poker | no deposit casino bonus | casino slot games | free online slots no downloads | free texas hold em online | russian roulette games | black jack strip | online blackjack | free poker games online | free slot downloads
66.235.162.* 于 2007-02-12 17:24:12发表:
Thank you!
casino bonus | strip blackjack games | slot machine free | free slots machines | free roulette | play texas hold em | on line gambling | casino free games | play texas holdem | free sims slots
65.110.43.* 于 2007-02-12 17:23:58发表:
Good design!
party poker download | internet blackjack | download casino | free games online | free poker game | bingo games | strip blackjack free | no deposit bonus | roulette online | free casino cash
66.235.160.* 于 2007-02-12 17:23:36发表:
Well done!
slot game | black jack game | world series poker | online strip blackjack | online bingo games | free online slots games | no deposit casino | free slot games | poker free download | free poker game download
0.0.0.* 于 2007-02-12 07:51:31发表:
http://www.webspace-kostenlos.com/forexsignal/ forex signal forex signal lone star ringtones lone star ringtones http://mywebpage.netscape.com/lstarringtones/ download ringtones download ringtones http://downloadringtones.blog.com/ remind ringtone remind ringtone http://remind-ringtone.iespana.es http://www.oddworldz.com/eamringtones/ eamon ringtones eamon ringtones
66.235.162.* 于 2007-02-11 17:52:19发表:
Nice site!
casinos on line | strip blackjack game | free sims slots | casino on net | cleopatra slots | slim slots | roulette games | texas hold em | texas hold em | free money casino
65.110.43.* 于 2007-02-11 17:51:43发表:
Nice site!
internet poker | poker game free | 3 card poker | download free poker | free online casino slots | play bingo online | gambling machines | roulette | free bonus slots | online poker usa
66.235.163.* 于 2007-02-11 17:51:05发表:
Thank you!
free slot games no download | free three card poker | casino games online | video slots | free casino | online games | free casino downloads | free gambling | play slot machines for free | casino game
66.235.163.* 于 2007-02-11 17:50:30发表:
Thank you!
free online slot games | play texas holdem free | poker stars download | free black jack | monopoly slot machine | on line casinos | play texas hold em | no deposit online casinos | poker software | strip blackjack
65.110.43.* 于 2007-02-11 17:50:08发表:
Thank you!
online free slots | video poker free | download poker games | texas poker | flash casino | free online poker | fun slots | russian roulette | free online bingo | online slot machines
0.0.0.* 于 2007-02-11 12:43:35发表:
http://beatlesringtones.site.io/ beatles ringtones beatles ringtones salles de jeux http://groups.google.com/group/salles-de-jeux/topics salles de jeux poker gratuito poker gratuito http://pokergratuito.splinder.com/ n sync ringtone n sync ringtone http://nsyncringtone.iespana.es http://b.domaindlx.com/callerringtones/ caller ringtones caller ringtones
213.240.225.* 于 2007-02-11 12:29:29发表:
free hindi ringtones free hindi ringtones http://www.blogate.info/freehindiringtones http://boarconfesionalrington.bitacoras.com dashboard confessional ringtone dashboard confessional ringtone marilyn manson ringtone ringtone http://ringtone.bitacoras.com bonus de casino en linea bonus de casino en linea http://bonusdecasinoenlinea.blogia.com forex broker online forex broker http://www.powow.com/forexbroker/
0.0.0.* 于 2007-02-11 04:37:21发表:
free polyphonic ringtone free polyphonic ringtone http://freepolyphonicringtone.blog.dada.net/ jeu de poker en ligne jeu de poker en ligne http://jeudepokerenligne.aceblog.fr/ free sprint ringtones http://www.web-space.tv/freesprintringtones/ free sprint ringtones kostenloses spiel online kostenloses spiel online http://kostenlspielon.ko.ohost.de bright eyes ringtones bright eyes ringtones http://www.web-space.tv/brighteyesringtones/
65.110.43.* 于 2007-02-11 01:43:57发表:
Great work!
play blackjack free | casino online | free no deposit casinos | games slots | free online gambling | poker sites | slots download | free texas holdem | free fun slots | slot game
65.110.43.* 于 2007-02-11 01:35:36发表:
Good design!
free texas hold em online | poker bonus | free online games | online slot machines | fun slots | free online slot machine | online poker usa | free 3 card poker | slot games free | free black jack
65.110.43.* 于 2007-02-11 01:35:09发表:
Nice site!
play roulette online | download slots | casino game download | poker games download | free online casino | star poker | us poker sites | texas holdem online | online games | poker online free
65.110.43.* 于 2007-02-11 01:34:28发表:
Well done!
play roulette for free | sims slots | black jack games | black jack | poker games free | free online strip blackjack | bingo games | blackjack online free | free slot machine game | free internet poker
0.0.0.* 于 2007-02-10 20:07:56发表:
black eyed pea ringtone darude ringtone http://www.maximo.ucla.edu/Members/cooltonesring777/ringtone-2.html crank yankers ringtones crank yankers ringtones http://loyno.edu/~jgharris/articles/crank-yankers-ringtones.html http://loyno.edu/~jgharris/articles/halloween-movie-ringtones.html halloween movie ringtones halloween movie ringtones icp ringtones http://loyno.edu/~jgharris/articles/icp-ringtones.html icp ringtones ringtones for motorola ringtones for motorola http://loyno.edu/~jgharris/articles/ringtones-for-motorola.html
203.145.131.* 于 2007-02-10 19:44:32发表:
http://loyno.edu/~jgharris/articles/ringtones-with-the-words.html ringtones with the words ringtones with the words http://loyno.edu/~jgharris/articles/2.99-nextel-ringtones.html 2.99 nextel ringtones 2.99 nextel ringtones sexringtones http://loyno.edu/~jgharris/articles/sexringtones.html sexringtones finalfantasy 207 20ringtones finalfantasy 207 20ringtones http://loyno.edu/~jgharris/articles/finalfantasy-207-20ringtones.html eminem ringtones bright eyes ringtones http://ustream.wcu.edu:8900/Members/ringsandtones/ringtones.html
0.0.0.* 于 2007-02-10 11:07:09发表:
http://loyno.edu/~jgharris/articles/razr-ringtone-codes.html razr ringtone codes razr ringtone codes download free cingular ringtone http://www.maximo.ucla.edu/Members/cooltonesring777/free-ringtone.html download free cingular ringtone k-os crabbuckit ringtone k-os crabbuckit ringtone http://loyno.edu/~jgharris/articles/k-os-crabbuckit-ringtone.html motorollaringtones http://loyno.edu/~jgharris/articles/motorollaringtones.html motorollaringtones http://loyno.edu/~jgharris/articles/misfits-ringtones.html misfits ringtones misfits ringtones
216.7.178.* 于 2007-02-10 11:03:00发表:
islamic ringtones islamic ringtones http://loyno.edu/~jgharris/articles/islamic-ringtones.html bollywood ringtones http://loyno.edu/~jgharris/articles/bollywood-ringtones.html bollywood ringtones bob & tom ringtones bob & tom ringtones http://loyno.edu/~jgharris/articles/bob-&-tom-ringtones.html ringtones for free ringtones for free http://loyno.edu/~jgharris/articles/ringtones-for-free.html http://loyno.edu/~jgharris/articles/under-the-boardwalk-ringtone.html under the boardwalk ringtone under the boardwalk ringtone
72.232.250.* 于 2007-02-10 02:40:08发表:
Well done!
noble poker | blackjack free | play free blackjack | texas hold em | slot machines | no deposit bonuses | free online video slots | poker game online | strip blackjack free | play slots for free
72.232.250.* 于 2007-02-10 02:39:32发表:
Great work!
free slot game | texas holdem poker | free online blackjack | free slots machines | free online roulette | aussie slots | poker texas holdem | no download casino | free slots games | poker for free
72.232.250.* 于 2007-02-10 02:38:56发表:
Thank you!
slots on line | texas holdem online | flash casinos | us poker sites | free casino cash | black jack online | blackjack game | new no deposit online casinos | casino no deposit | play texas holdem online
65.110.43.* 于 2007-02-10 02:38:31发表:
Great work!
casino bonuses | free online texas holdem | free slot games no download | play bingo | roulette | roulette games | free slot | download free poker games | poker download | online casinos
65.110.43.* 于 2007-02-10 02:38:02发表:
Great work!
free poker sites | free sim slots | new no deposit casinos | casino game | monopoly online | free video | monopoly slot machine | play free online slots | casino free games | free online poker game
65.110.43.* 于 2007-02-09 05:20:22发表:
Good design!
free casino games online | online bingo games | games online | pokerstars download | caribbean stud poker | free slot machine play | free online casino | game slot | blackjack software | games slots
65.110.43.* 于 2007-02-09 05:18:28发表:
Great work!
poker game | poker star | roulette free games | free gambling games | online blackjack free | free casino slots | slots download | play texas hold em | texas holdem | online poker sites
65.110.43.* 于 2007-02-09 05:16:40发表:
Great work!
online poker free | free video slots | free online slots no downloads | gambling sites | play casino games | poker game free | free money casinos | casino games | free games on line | poker usa
72.232.63.* 于 2007-02-09 05:16:07发表:
Thank you!
slot machine game | flash casino games | blackjack online | free bingo online | roulette game | free slot machine downloads | online poker games | strip blackjack free | download free poker game | free slots
72.232.63.* 于 2007-02-09 05:15:55发表:
Nice site!
play bingo | online free blackjack | free slot machines games | download poker game | slot machine | texas holdem free online | free sim slots | free poker money | best online casinos | slots games
72.232.250.* 于 2007-02-07 18:03:57发表:
Nice site!
video poker download | download poker game | multiplayer poker | black jack free | game slot | free texas holdem poker | online slot games | casino game | free money casinos | download free poker games
72.232.250.* 于 2007-02-07 18:03:27发表:
Well done!
free online poker games | free money casino | free casino slots | free online slot machines | free casino bonus | no deposit bingo | free roulette games | strip blackjack online | roulette download | poker game download
65.110.43.* 于 2007-02-07 18:02:50发表:
Nice site!
free roulette online | free bingo games | play free online slots | free online bingo | blackjack online free | free video slot games | video slot machine | free slot machines | free on line poker | strip blackjack
72.232.250.* 于 2007-02-07 18:02:11发表:
Nice site!
free blackjack | play blackjack | free online casino slots | free poker games online | free casino games | online slots | online casino | free games on line | slots free | free slot
72.232.250.* 于 2007-02-07 18:01:29发表:
Good design!
poker for free | slot machine games free | casino bonus | free download poker | video poker free | online bingo games | black jack game | virtual poker | cleopatra slots | play slots for fun