经常发现一些网友问分析函数的问题，虽然分析函数比较多，有一些分析函数也确实比较难以理解，会给初学者有很大的心理压力，其实对于大部分的分析函数，一般都不会有机会用到，所以掌握常用的几个分析函数就可以啦。
常用的分析函数如下所列:
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.211.* 于 2007-04-14 12:06:29发表:
Great work!
mobile ringtones | free ringtones for t-mobile | 24 ctu ringtone verizon | christian ringtones | ringtones for nokia | ringtones mp3 | polyphonic ringtones | free cell phone ringtones | ctu ringtones | free mp3 ringtone downloads
72.36.211.* 于 2007-04-14 12:06:05发表:
Well done!
free ringtones sprint | star wars ringtone | ringtone nokia | nokia free downloads | free sms ringtones | funny ringtones | motorola keypress ringtones | suncom ringtones | free ringtones alltel | absolutely free ringtones
72.36.211.* 于 2007-04-14 12:05:34发表:
Great work!
download free motorola ringtone | ringtones for cingular | free sprint downloads | motorola razr v3 ringtones | free mp3 ringtones download | free motorola ringtones | keypress ringtones | free wav ringtones | simpsons ringtone | cellular south
72.36.211.* 于 2007-04-14 12:05:09发表:
Thank you!
free 24 ringtone | ringtones samsung | free anime ringtones | ringtones for verizon | tracfone free ringtones | free ringtones us cellular | us cellular free ringtones | alltel ringtones | country ringtones | free midi ringtones
72.21.40.* 于 2007-04-13 14:30:07发表:
Great work!
composer ringtones | free boost ringtones | verizon ctu ringtone | nokia free download | ringtone midi | free suncom ringtones | free cellular one ringtones | free phone downloads | ringtones for cingular | 24 ringtone free
72.21.40.* 于 2007-04-13 14:29:34发表:
Nice site!
free star wars ringtones | free sprint pcs ringtones | ringtones for motorola q | free tmobile ringtones | download free polyphonic ringtones | wwe ringtones | free hi fi ringtones | nokia download | free mp3 ringtones | free ringtones verizon
72.21.40.* 于 2007-04-13 14:29:08发表:
Good design!
ringtones for motorola | free cricket ringtones | cell phone ringtones | 24 ctu ringtone verizon | free samsung ringtones | ctu ringtone | nokia ringtone | free real ringtones | free ring tones | free cell phone ring tones
72.21.40.* 于 2007-04-13 14:28:39发表:
Nice site!
verizon ring tones | free mp3 ringtone downloads | midi ringtones | free country ringtones | free ringtones for alltel | free ringtones cingular | funny ringtone | free motorola ringtones | ringtones wav | free christian ringtones
72.21.40.* 于 2007-04-13 14:27:50发表:
Well done!
nextel ringtone | mp3 to ringtone converter | free centennial wireless ringtones | free real music ringtones | monophonic ringtones | u s cellular ringtones | sprint pcs ringtones | free ringtones for t-mobile | free t mobile ringtones | simpsons ringtone
72.21.40.* 于 2007-04-12 13:40:18发表:
Nice site!
casino black jack | slots games | texas hold em | internet bingo | free slot games online | free roulette | free blackjack | monopoly online | free slot machine | gambling games
72.21.40.* 于 2007-04-12 13:39:50发表:
Well done!
online black jack game | casino no deposit | no deposit casinos | casino bonuses | play online poker | casino open usa | free casino downloads | casino game download | download party poker | no download slots
72.21.40.* 于 2007-04-12 13:39:19发表:
Thank you!
download blackjack | free download poker | pacific poker | free fun slots | black jack | free on line poker | play free blackjack | free strip blackjack | texas holdem | play free slots online
72.21.40.* 于 2007-04-12 13:38:54发表:
Great work!
best online casinos | free internet poker | online roulette game | play free online slots | strip blackjack | play free slots | free online slots no downloads | poker games online | poker | casinos online
72.21.40.* 于 2007-04-12 13:38:16发表:
Well done!
free black jack games | online gambling sites | casino free game online | roulette | deposit bonus casino | slot machine software | online poker games | free bingo online | free online poker | no deposit casino bonuses
72.21.40.* 于 2007-04-11 13:57:59发表:
Nice site!
hindi ringtone | boost ringtones | ringtones wav | free mobile downloads | nextel ringtone | free sanyo ringtones | free composer ringtones | free motorola ringtone downloads | free downloadable t mobile ringtones | free monophonic ringtones
72.21.40.* 于 2007-04-11 13:57:37发表:
Well done!
samsung free ringtones | sony ericsson ringtones | free ringtones verizon | free ringtones for t-mobile | 24 ctu ringtone verizon | free country music ringtones | ringtones motorola | motorola q ringtones | ringtone midi | free ringtones for tracfones
72.21.40.* 于 2007-04-11 13:57:02发表:
Good design!
alltel ringtones | free kyocera ringtones | ringtones for motorola q | free ringtone | free ringtones for tmobile | free wwe ringtones | polyphonic ringtone | centennial wireless | free cricket ringtones | hifi ringtones
72.21.40.* 于 2007-04-11 13:56:24发表:
Nice site!
free t mobile ringtones | download free polyphonic ringtones | free ringtones for cingular | anime ringtones | free nokia ringtones | 24 ctu ringtone | free ringtones for samsung | free midi ringtone | cellular south | mosquito ring tone
72.21.40.* 于 2007-04-11 13:55:44发表:
Good design!
gospel ringtones | free cellular ringtones | free ringtones download | suncom ringtones | ringtone uploader | free hi fi ringtones | verizon ring tones | ringtones for cingular | free harry potter ringtones | free ringtones downloads
72.21.40.* 于 2007-04-10 13:06:19发表:
Well done!
cellular south ringtones | free centennial wireless ringtones | free nokia downloads | ringtones for suncom | samsung ringtones | free ringtones for cricket | ringtone 24 | voice ringtone | free cell phone ringtones | ringtones samsung
72.21.40.* 于 2007-04-10 13:05:29发表:
Nice site!
christian music ringtones | motorola free ringtones | sanyo ringtones | free ringtones for sprint | free ringtone | ctu ringtone mp3 | free anime | download free polyphonic ringtones | ringtones for tracfones | free hifi ringtones
72.21.40.* 于 2007-04-10 13:04:47发表:
Good design!
free ringtone converter | free phone downloads | free ring tones | downloadable ringtones | cellphone ringtones | free tmobile ringtones | motorola q ringtones | free ringtones verizon | mp3 ringtone | free ringtone creator
72.21.40.* 于 2007-04-10 13:04:04发表:
Nice site!
24 ring tone | free nextel ringtones | free t mobile ringtones | 24 ringtone | free ringtones | ctu ringtone verizon | cingular ringtones | country ringtones | crazy frog ringtones | free ringtones for sprint phones
72.21.40.* 于 2007-04-10 13:03:29发表:
Well done!
ringtones for verizon | free ringtones for t mobile | cellular one ringtones | free cell phone ring tones | mp3 ringtones download | free 24 ringtones | star wars ringtones | free alltel ringtones | ringtones for nextel | ringtones mp3
72.21.40.* 于 2007-04-09 12:25:06发表:
Great work!
download free ringtones | free ringtones for sprint | alltel free ringtones | free anime ringtones | free country ringtones | lg free ringtones | samsung free ringtones | free real tone ringtones | arabic ringtones | free ringtones for tracfones
72.21.40.* 于 2007-04-09 12:24:46发表:
Good design!
sony ericsson ringtones | free phone downloads | online ringtone converter | free ctu ringtone | free ringtones us cellular | free midi ringtone | gospel ringtones | mp3 ringtones | ringtones for nextel | razr ringtones
72.21.40.* 于 2007-04-09 12:24:14发表:
Good design!
free ringtone | free polyphonic ringtone | free suncom ringtones | free cell phone ring tones | ringtones 24 | free cricket ringtones | free kyocera ringtones | 50 cent ringtones | ringtone websites | free us cellular ring tones
72.21.40.* 于 2007-04-09 12:23:36发表:
Nice site!
boost mobile ringtones | ringtones cingular | free ringtone downloads | ringtones composer | ringtones for tracfone | ringtone creator | free real ringtones for cingular | free ringtones for tmobile | free real music ringtones | alltel ringtones
72.21.40.* 于 2007-04-09 12:23:01发表:
Nice site!
ringtone 24 ctu | ringtone jukebox | ringtones wav | ringtones for verizon | cellular south ringtones | free cell phone ringtones | download free polyphonic ringtones | verizon ctu ringtone | hindi ringtone | motorola keypress ringtones
72.21.40.* 于 2007-04-08 12:04:07发表:
Nice site!
gambling games | free slot machine games online | free monopoly slots | slot machine games | texas holdem | roulette game | computer poker | play slots | strip blackjack online | live poker
72.21.40.* 于 2007-04-08 12:03:40发表:
Good design!
online texas holdem | online slots | pai gow poker | free slot games no download | poker games free | free online strip blackjack | free casino online | slot games free | free casino money | free poker downloads
72.21.40.* 于 2007-04-08 12:03:06发表:
Great work!
online bingo | poker for fun | online casino bonus | new free money online casinos | poker for free | video slots | monopoly online | free bonus slots | casino on net | flash casino
72.21.40.* 于 2007-04-08 12:02:46发表:
Well done!
internet casino games | free online casinos | online free poker | video slot games | no deposit bonuses | free online slots | play roulette | online black jack | sims slots | free poker
72.21.40.* 于 2007-04-08 12:02:12发表:
Nice site!
no download casino | free bingo games | play free blackjack | aussie slots | online free roulette | keno online | casino on line | casino poker | online black jack game | free poker games
72.21.40.* 于 2007-04-07 12:27:34发表:
Nice site!
games download | slot machine game | free online texas hold em | free video poker | video poker | us poker sites | new casinos | free blackjack games | roulette online | poker downloads
72.21.40.* 于 2007-04-07 12:27:18发表:
Thank you!
paradise poker | no deposit bonus casinos | full tilt poker | free roulette games | free slim slots | casino on line | no download free slots | strip blackjack games | black jack download | free black jack games
72.21.40.* 于 2007-04-07 12:26:25发表:
Great work!
best online casino games | casino free | best bonus casino | free poker games online | free texas holdem | free online poker | free strip black jack | blackjack for free | slots download | online black jack
72.21.40.* 于 2007-04-07 12:25:47发表:
Great work!
texas hold em poker | free casino slots | free poker | free slot games online | play roulette for free | pacific poker | holdem poker | game slot | poker game free | free games online
72.21.40.* 于 2007-04-07 12:25:08发表:
Thank you!
play poker for free | roulette games | free online casinos | free play slot machines | poker games online | sims slots | online games | gambling games | slots on line | poker texas holdem
72.21.40.* 于 2007-04-06 13:09:07发表:
Well done!
free poker online | free sims slots | casinos online | online poker free | black jack free | cleopatra slot | poker stars | gambling online | poker download | free online slot machines