快捷導(dǎo)航

Oracle數(shù)據(jù)庫分析函數(shù)用法

更新時(shí)間：2021年12月24日 10:30:28 作者：ette

大家好，本篇文章主要講的是Oracle數(shù)據(jù)庫分析函數(shù)用法，感興趣的同學(xué)趕快來看一看吧，對你有幫助的話記得收藏一下，方便下次瀏覽

1、什么是窗口函數(shù)？

窗口函數(shù)也屬于分析函數(shù)。Oracle從8.1.6開始提供窗口函數(shù)，窗口函數(shù)用于計(jì)算基于組的某種聚合值，
窗口函數(shù)指定了分析函數(shù)工作的數(shù)據(jù)窗口大小，這個(gè)數(shù)據(jù)窗口大小可能會隨著行的變化而變化。
與聚合函數(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則不會減少原表中的行數(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ù)會對結(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的話會對某些函數(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í)不會重復(fù)，不會產(chǎn)生空位
rank()	按照值排序時(shí)產(chǎn)生一個(gè)自增編號，值相等時(shí)會重復(fù)，會產(chǎn)生空位
dense_rank()	按照值排序時(shí)產(chǎn)生一個(gè)自增編號，值相等時(shí)會重復(fù)，不會產(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)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: