Oracle數(shù)據(jù)庫分析函數(shù)用法
1、什么是窗口函數(shù)？
窗口函數(shù)也屬于分析函數(shù)。Oracle從8.1.6開始提供窗口函數(shù)，窗口函數(shù)用于計(jì)算基于組的某種聚合值，
窗口函數(shù)指定了分析函數(shù)工作的數(shù)據(jù)窗口大小，這個(gè)數(shù)據(jù)窗口大小可能會(huì)隨著行的變化而變化。
與聚合函數(shù)的不同之處是：對于每個(gè)組返回多行，而聚合函數(shù)對于每個(gè)組只返回一行
基本語法: ?分析函數(shù)? over (partition by ?用于分組的列名? order by ?用于排序的列名?)。 語法中的?分析函數(shù)?主要由序列函數(shù)（rank、dense_rank和row_number等組成） 與聚合函數(shù)（sum、avg、count、max和min等）作為窗口函數(shù)組成。
從窗口函數(shù)組成上看，它是group by 和 order by的功能組合，group by分組匯總后改變了表的行數(shù)，一行只有一個(gè)類別，而partiition by則不會(huì)減少原表中的行數(shù)。
恰如窗口函數(shù)的組成，它同時(shí)具有分組和排序的功能，且不減少原表的行數(shù)。
OVER 關(guān)鍵字表示把函數(shù)當(dāng)成窗口函數(shù)而不是聚合函數(shù)。SQL 標(biāo)準(zhǔn)允許將所有聚合函數(shù)用做窗口函數(shù)，使用 OVER 關(guān)鍵字來區(qū)分這兩種用法。
2、窗口函數(shù)——開窗
OVER 關(guān)鍵字后的括號中經(jīng)常添加選項(xiàng)用以改變進(jìn)行聚合運(yùn)算的窗口范圍。如果 OVER 關(guān)鍵字后的括號中的選項(xiàng)為空，則窗口函數(shù)會(huì)對結(jié)果集中的所有行進(jìn)行聚合運(yùn)算。
分析函數(shù) over(partition by 列名 order by 列名 rows between 開始位置 and 結(jié)束位置)
為什么叫開窗呢？
因?yàn)樵趏ver()括號中的，partition() 函數(shù)可以將查詢到的數(shù)據(jù)進(jìn)行單獨(dú)開一個(gè)窗口處理。譬如，查詢每個(gè)班級的學(xué)生的排名情況，查詢每個(gè)國家的歷年人口等，諸如此類，都是在查詢到的每一個(gè)班級、每一個(gè)國家中都開一個(gè)窗口，單獨(dú)去執(zhí)行命令。
rows和range分別表示選擇后幾行、選擇數(shù)據(jù)范圍
理解 rows between 含義,也叫做window子句：
preceding：往前following：往后current row：當(dāng)前行unbounded：無邊界，unbounded precending 表示從最前面的起點(diǎn)開始， unbounded following：表示到最后面的終點(diǎn)注：不加 partition by 的話則把整個(gè)數(shù)據(jù)集當(dāng)作一個(gè)分區(qū)，不加 order by的話會(huì)對某些函數(shù)統(tǒng)計(jì)結(jié)果產(chǎn)生影響，如sum()
3、一些分析函數(shù)的使用方法
1.聚合函數(shù)
| 聚合函數(shù) | 定義 |
|---|---|
| sum() | 求和 |
| max() | 求最大值 |
| min() | 求最小值 |
| avg() | 求平均值 |
| count() | 統(tǒng)計(jì)數(shù) |
2.序列函數(shù)
| 序列函數(shù) | 定義 |
|---|---|
| row_number() | 按照值排序時(shí)產(chǎn)生一個(gè)自增編號，值相等時(shí)不會(huì)重復(fù)，不會(huì)產(chǎn)生空位 |
| rank() | 按照值排序時(shí)產(chǎn)生一個(gè)自增編號，值相等時(shí)會(huì)重復(fù)，會(huì)產(chǎn)生空位 |
| dense_rank() | 按照值排序時(shí)產(chǎn)生一個(gè)自增編號，值相等時(shí)會(huì)重復(fù)，不會(huì)產(chǎn)生空位 |
row_number()
select * ，row_number()over(oder by 成績 desc) as 排名 from 班級表
查詢結(jié)果：
+------------+--------+------+------+------+ | 姓名 | 性別 | 班級 | 成績 | 排名 | +------------+--------+------+------+------+ | 張三 | 男 | 1 | 100 | 1 | | 李四 | 女 | 3 | 100 | 2 | | 張三 | 女 | 1 | 100 | 3 | | 王五 | 女 | 2 | 99 | 4 | | 趙四 | 男 | 2 | 90 | 5 | | 孫六 | 男 | 2 | 90 | 6 | | 喜羊羊 | 男 | 3 | 85 | 7 | | 美羊羊 | 女 | 4 | 82 | 8 | | 懶洋洋 | 女 | 1 | 80 | 9 | | 慢羊羊 | 女 | 2 | 70 | 10 | +------------+--------+------+------+------+
rank()
select * ，rank()over(oder by 成績 desc) as 排名 from 班級表
查詢結(jié)果：
+------------+--------+------+------+------+ | 姓名 | 性別 | 班級 | 成績 | 排名 | +------------+--------+------+------+------+ | 張三 | 男 | 1 | 100 | 1 | | 李四 | 女 | 3 | 100 | 1 | | 張三 | 女 | 1 | 100 | 1 | | 王五 | 女 | 2 | 99 | 4 | | 趙四 | 男 | 2 | 90 | 5 | | 孫六 | 男 | 2 | 90 | 5 | | 喜羊羊 | 男 | 3 | 85 | 7 | | 美羊羊 | 女 | 4 | 82 | 8 | | 懶洋洋 | 女 | 1 | 80 | 9 | | 慢羊羊 | 女 | 2 | 70 | 10 | +------------+--------+------+------+------+
dense_rank()
select * ，row_number()over(oder by 成績 desc) as 排名 from 班級表
查詢結(jié)果：
+------------+--------+------+------+------+ | 姓名 | 性別 | 班級 | 成績 | 排名 | +------------+--------+------+------+------+ | 張三 | 男 | 1 | 100 | 1 | | 李四 | 女 | 3 | 100 | 1 | | 張三 | 女 | 1 | 100 | 1 | | 王五 | 女 | 2 | 99 | 2 | | 趙四 | 男 | 2 | 90 | 3 | | 孫六 | 男 | 2 | 90 | 3 | | 喜羊羊 | 男 | 3 | 85 | 4 | | 美羊羊 | 女 | 4 | 82 | 5 | | 懶洋洋 | 女 | 1 | 80 | 6 | | 慢羊羊 | 女 | 2 | 70 | 7 | +------------+--------+------+------+------+
3.其他類
| 其他類 | 定義 |
|---|---|
| percent_rank() | 分組內(nèi)當(dāng)前行的rank值-1/分組內(nèi)總行數(shù)-1 |
| lag() | 用于統(tǒng)計(jì)窗口內(nèi)往上第n行值 第一個(gè)參數(shù)為列名，第二個(gè)參數(shù)為往上第n行（可選，默認(rèn)為1），第三個(gè)參數(shù)為默認(rèn)值（當(dāng)往上第n行為NULL時(shí)候，取默認(rèn)值，如不指定，則為NULL |
| lead() | 用于統(tǒng)計(jì)窗口內(nèi)往下第n行值 第一個(gè)參數(shù)為列名，第二個(gè)參數(shù)為往下第n行（可選，默認(rèn)為1），第三個(gè)參數(shù)為默認(rèn)值（當(dāng)往下第n行為NULL時(shí)候，取默認(rèn)值，如不指定，則為NULL |
| ntile() | 用于將分組數(shù)據(jù)按照順序切分成n片，返回當(dāng)前切片值 |
| first_value() | 取分組內(nèi)排序后，截止到當(dāng)前行，第一個(gè)值 |
| last_value() | 取分組內(nèi)排序后，截止到當(dāng)前行，最后一個(gè)值 |
| cume_dist() | 返回小于等于當(dāng)前值的行數(shù)/分組內(nèi)總行數(shù) |
4、OVER()參數(shù)——分組函數(shù)
partition by 子句：
窗口函數(shù)的 over 關(guān)鍵字后括號中的可以使用 partition by 子句來定義行的分區(qū)來供進(jìn)行聚合計(jì)算。 與 group by 子句不同，partition by 子句創(chuàng)建的分區(qū)是獨(dú)立于結(jié)果集的，創(chuàng)建的分區(qū)只是供進(jìn)行 聚合計(jì)算的，而且不同的窗口函數(shù)所創(chuàng)建的分區(qū)也不互相影響。
5、OVER()參數(shù)——排序函數(shù)
order by 子句：
窗口函數(shù)中可以在over關(guān)鍵字后的選項(xiàng)中使用order by 子句來指定排序規(guī)則，而且有的窗口函數(shù)還 要求必須指定排序規(guī)則。使用order by 子句可以對結(jié)果集按照指定的排序規(guī)則進(jìn)行排序，并且在一個(gè) 指定的范圍內(nèi)進(jìn)行聚合運(yùn)算。 語法：ORDER BY字段名 RANGE|ROWS BETWEEN邊界規(guī)則1 AND 邊界規(guī)則2
PARTITION BY子句和ORDER BY 可以共同使用，從而可以實(shí)現(xiàn)更加復(fù)雜的功能
到此這篇關(guān)于Oracle數(shù)據(jù)庫分析函數(shù)用法的文章就介紹到這了,更多相關(guān)Oracle函數(shù)內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信