经常发现一些网友问分析函数的问题，虽然分析函数比较多，有一些分析函数也确实比较难以理解，会给初学者有很大的心理压力，其实对于大部分的分析函数，一般都不会有机会用到，所以掌握常用的几个分析函数就可以啦。
常用的分析函数如下所列:
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-08-07 20:15:16发表:
Great work!
free hifi ringtones | free verizon ringtones | ringtones for alltel | free ringtones us cellular | free t mobile ringtones | ringtones download | christian ringtones | motorola ringtones | cricket free ringtones | 24 ringtones
72.36.211.* 于 2007-08-07 20:14:42发表:
Great work!
ringtones verizon | free samsung ringtones | hudba zdarma | logo sonnerie | bollywood ringtones | free ringtones for nokia | cingular free ringtones | ringtone 24 | ringtones gratis | free sprint downloads
72.36.211.* 于 2007-08-07 20:14:27发表:
Nice site!
sprint ringtones | ringtones mp3 free | nextel ringtone | sonneries samsung | mp3 to ringtone converter | ringtones for nextel | melodie mobil | cellular south ringtones | cep telefonuna melodi | us cellular ringtones
72.36.211.* 于 2007-08-07 20:14:16发表:
Good design!
rihanna ringtones | jamster ringtones | 24 klingelton | free ringtones for alltel | ctu ring tone | free polyphonic ringtone download | nokia free ringtones | hudba mp3 | ringtone motorola | free sprint pcs ringtones
72.36.211.* 于 2007-08-06 21:14:02发表:
Nice site!
free phone downloads | download free ringtones | sonneries gsm | christian music ringtones | free mp3 download | free samsung ringtones | gospel ringtones | free downloadable t mobile ringtones | motorola mp3 | dzwonki na siemensa
72.36.211.* 于 2007-08-06 21:13:18发表:
Nice site!
wwe ringtones | ringtone motorola | cricket ringtones | shakira ringtones | mosquito ring tone | free ringtones for verizon | tonos para celular | sprint phones | mp3 ringtone converter | free bollywood ringtones
72.36.211.* 于 2007-08-06 21:12:46发表:
Well done!
free motorola ringtones | nelly furtado ringtones | ringtones free | sms ringtones | free anime ringtones | free ring tones | free sprint pcs ringtones | harry potter ringtones | toques reais | ringtones verizon
72.36.211.* 于 2007-08-06 21:12:29发表:
Nice site!
motorola free ringtones | handy klingelton | free nokia downloads | ringtones for alltel | logo sonnerie | free music ringtones | polyphonic ringtones | melodie zdarma | akon ringtones | download free nokia ringtone
72.36.211.* 于 2007-08-06 21:12:18发表:
Nice site!
melodie mobil | tracfone ringtones | 50 cent ringtones | free t mobile ringtones | ringtones download | country music ringtones | dzwonki true tone | free mp3 tones | free composer ringtones | sonneries mobile
72.36.211.* 于 2007-08-06 01:26:27发表:
Thank you!
slot machine free | free casino games | casino on net | casino free | on line gambling | casino on line | poker web sites | online blackjack free | fun slots | online poker usa
72.36.211.* 于 2007-08-06 01:25:42发表:
Great work!
blackjack free games | free slot downloads | new no deposit online casinos | play bingo now | play bingo for free | free online poker game | games online | roulette | bingo gambling | black jack download
72.36.211.* 于 2007-08-06 01:25:15发表:
Well done!
free casino bonus | texas holdem | monopoly slots | free monopoly online | free slots | play free online slots | poker | free slots no downloads | no download casino | pai gow poker
72.36.211.* 于 2007-08-06 01:25:01发表:
Thank you!
free money casinos | poker bonus | free online slot machines | no deposit bonuses | online roulette | free online slot machine | free casinos | bingo world | casino online | online bingo scotland
72.36.211.* 于 2007-08-06 01:24:51发表:
Well done!
russian roulette | online baccarat | free online bingo | play online bingo free | video poker free | internet casino | free strip black jack | play texas holdem | play slots for fun | free slot machine downloads
60.176.194.* 于 2007-08-05 09:48:55发表:
铝箔铝箔铝箔
vibrating condom ringvibrating condom ringvibrating condom ring
婚纱摄影
婚纱摄影
婚纱摄影
山特山特山特
恒温恒湿机恒温恒湿机恒温恒湿机
工业除湿机工业除湿机
工业除湿机工业除湿机
工业除湿机
山特UPS山特UPS山特UPS
72.36.211.* 于 2007-08-05 05:23:55发表:
Great work!
nhl odds | line football betting | bet on college basketball | 2008 party win election bets | hockey bets | free fantasy baseball | english premier league bet | rugby betting uk | wnba spreads | online uk football betting
72.36.211.* 于 2007-08-05 05:23:26发表:
Great work!
place bet | nfl prediction | kevin garnett futures | free nfl football picks | horse racing betting tip | online sports book | online football bet | nascar allstate 400 odds | boxing props | nfl sports betting
72.36.211.* 于 2007-08-05 05:22:31发表:
Thank you!
nfl underdog | free bet offer | indy racing league honda | uk football bet | pga futures | spread betting | betting calculator | chicago bears bet | wnba bet | cfb betting
72.36.211.* 于 2007-08-05 05:22:12发表:
Great work!
nfl betting lines | mlb odds | bet on rugby | best bet | internet sportsbook | bet tips | 2007 2008 nfl schedule | nascar odds to win | 07 draft nfl | betting on college sports
72.36.211.* 于 2007-08-05 05:22:01发表:
Nice site!
american sport betting | football lines | soccer bet | on line sports book | golf open championship | horse racing wagering | 2007 cricket world cup bet | nfl football betting | mlb bet | formula one betting
72.36.211.* 于 2007-08-03 21:31:45发表:
Great work!
free slots games | online blackjack | video slots | online games | free money no deposit | poker usa | bingo game yahoo | free slots online | slots for free | download slots
72.36.211.* 于 2007-08-03 21:31:18发表:
Thank you!
slots of fun | free internet poker | play bingo online | download free poker games | free video | games slots | cleopatra slots | russian roulette games | new no deposit online casinos | internet casinos
72.36.211.* 于 2007-08-03 21:30:21发表:
Nice site!
free slots | free poker websites | play poker | free online bingo site | video slot games | casino free game online | free texas holdem poker | free online slot machine games | live poker | online roulette game
72.36.211.* 于 2007-08-03 21:29:46发表:
Well done!
roulette free | world series poker | video poker free | bingo gala uk | casinos online | free slim slots | bingo games online | online casino | free texas hold em online | free bingo site
72.36.211.* 于 2007-08-03 21:29:33发表:
Thank you!
play free blackjack online | video slot machines | black jack | internet gambling | slot machines games | poker | free texas hold em | texas poker | black jack games | keno online
72.36.211.* 于 2007-08-02 21:55:43发表:
Thank you!
sports betting | wnba bet | rugby league betting | 2008 nba champions | basketball college handicapping | online sports betting | betting web site | yankee baseball | bet odds | nfl super bowl
72.36.211.* 于 2007-08-02 21:55:15发表:
Nice site!
football game odds | twenty20 cricket betting | soccer betting | uk football betting | sport spread betting | golf props | bet cricket | nfl betting odds | online sport betting | greyhound bet
72.36.211.* 于 2007-08-02 21:54:18发表:
Thank you!
2007 nfl draft | football betting tip | handicapping horse racing | 2007 fedex cup futures | virtual betting | wnba spreads | ncaa baseball | pga futures | line football betting | online football bet
72.36.211.* 于 2007-08-02 21:53:46发表:
Well done!
british open playoff | football betting odds | betting lines football | best online sports book | free bet | nfl spreads | formula 1 bet | nfl record | formula 1 betting | mlb pennant
72.36.211.* 于 2007-08-02 21:53:31发表:
Good design!
sporting bet | betting line | nba betting | baseball lines | baseball betting lines | sports books | sport betting online | bet sports | 2007 golf major futures | f1 championship odds
76.222.30.* 于 2007-08-02 10:32:28发表:
cell phone ringtone cell phone ringtone http://www.123unlockedcellphones.info/online/1090.html service gsm france service gsm france http://www.achat-telephoneportable.info/service-gsm-france.asp 6265i cell nokia phone review 6265i cell nokia phone review http://www.12mobile-cell-phones.info/book/1498 wedding ring online wedding ring online http://www.123-schmuck.info/files/1303.html pilote telephone portable sagem my 501 pilote telephone portable sagem my 501 http://www.fr-bestcellphones.info/4/pilote-telephone-portable-sagem-my-501.html nokia cell phone accessory nokia cell phone accessory http://www.it-cell-phones.info/2006/1404.asp grossiste bijoux fantaisie pour professionnel grossiste bijoux fantaisie pour professionnel http://www.fr-bijoux.info/1730 blood diamonds of africa blood diamonds of africa http://www.1-jewellery-box.info/posts/blood-diamonds-of-africa.php pokemon diamant rom pokemon diamant rom http://www.1schmuckshop.info/2006/pokemon-diamant-rom cubic zerconia rings cubic zerconia rings http://www.costume-jewellery-fr.info/news/cubic-zerconia-rings.asp
75.176.91.* 于 2007-08-02 10:31:32发表:
bijoux collier argent bijoux collier argent http://www.fr-jewelry-1.info/new/1466.asp bague bague napoleon or bague bague napoleon or http://www.1bijoux-or.info/bague-bague-napoleon-or.php black gold library black gold library http://www.123jewellery-online.info/phorum/black-gold-library.html lord of the ring movie lord of the ring movie http://www.ita-silver-jewelry.info/tools/lord-of-the-ring-movie sterling silver jewelry sterling silver jewelry http://www.1-gioiellionline.info/sterling-silver-jewelry.html lg vx9800 telefono movil lg vx9800 telefono movil http://www.escelulares.info/blog/1611.php download gioco cellulare gratis download gioco cellulare gratis http://www.telefoni-cellulari.info/download-gioco-cellulare-gratis.php free used cell phone free used cell phone http://www.123mobile-cell-phones.info/new/1406.asp suoneria telefonino cellulare suoneria telefonino cellulare http://www.vendita-cellulari.info/suoneria-telefonino-cellulare.asp antenne telephone mobile antenne gsm antenne telephone mobile antenne gsm http://www.comparatiftelephoneportable.info/new/1678.php
60.177.54.* 于 2007-08-02 04:06:27发表:
西点券。。
西点券。。
西安花店。。
物流公司。。
物镜。。
舞蹈用品。。
舞蹈鞋。。
武夷山旅游。。
武夷山旅游。。
武夷山旅游。。
武夷山旅游。。
武夷山。。
武夷山。。
武汉网站建设。。
武汉花店。。
五金模具。。
吴羽化学。。
无线点菜。。
无线餐饮。。
无痛人流。。
无痛人流。。
无痛人工流产。。
无痛流产。。
无痛流产。。
无精。。
无缝管。。
无缝管。。
无缝钢管。。
污水提升泵。。
污水泵。。
72.36.211.* 于 2007-08-01 22:13:01发表:
Thank you!
casino con postepay | casino en ligne | jugar ruleta | casino en ligne | beste casino spiele | deutsche casinos | trucchi per vincere al casino | guida ai casino | spiele online casinos | juegos de casino
72.36.211.* 于 2007-08-01 22:12:33发表:
Well done!
casinos jeux gratuits | juegos de casino | casino jeu | come vincere al casino | casinos en ligne | casino boni | deutsche online casinos | apostar casino | migliori casino italiani | trucchi per vincere al casino
72.36.211.* 于 2007-08-01 22:11:36发表:
Great work!
online kasino | probablidades casinos | vincere al casino | jeu casino | giochi del casino | guadagnare al casino | online casino spiele | guadagnare al casino | spiele online casinos | migliori bonus del casino
72.36.211.* 于 2007-08-01 22:11:08发表:
Thank you!
jeux gratuits | online casino spiele | beste casino spiele | scommettere al casino | scommettere al casino | casino online italiani | jugar ruleta | jeux casinos | online spielbank | beste casino spiele
72.36.211.* 于 2007-08-01 22:10:55发表:
Nice site!
jeu argent | migliori bonus del casino | apostar casino | casino jeu | casino virtuel | deutsche online casinos | casino con postepay | giochi del casino | juegos gratis | casino en ligne
60.177.54.* 于 2007-08-01 17:10:39发表:
礼服。。
离子计。。
离心机。。
离心机。。
离心机。。
离心泵。。
离心泵。。
冷镦机。。
类风湿性关节炎。。
类风湿关节炎。。
类风湿。。
雷蒙磨。。
乐清人才网。。
浪涌保护器。。
兰州电机厂。。
昆明旅行社。。
昆明康辉旅行社。。
昆明高尔夫。。
溃疡性结肠炎。。
矿用卡车。。
快装脚手架。。
快速熔断器。。
快速门。。
扣板。。
口译。。
空压机。。
空气压缩机。。
空分制氧。。
空调移机。。
空调维修。。
60.177.54.* 于 2007-08-01 06:08:45发表:
北京鲜花。。
北京鲜花。。
北京无痛人流。。
北京网站制作。。
北京庆典礼仪。。
北京汽车租赁公司。。
北京汽车租赁公司。。
北京汽车租赁。。
北京美容院。。
北京律师。。
北京礼品公司。。
北京礼品公司。。
北京礼品。。
北京礼品。。
北京礼品。。
北京礼品。。
北京驾校。。
北京家教。。
北京婚纱摄影。。
北京婚纱礼服。。
北京婚庆公司。。
北京婚庆公司。。
北京婚庆。。
北京婚庆。。
北京婚庆。。
北京婚礼场地布置。。
北京婚礼场地布置。。
北京会议。。
北京花店。。
北京国际快递公司。。