快捷導(dǎo)航

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

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

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

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

窗口函數(shù)也屬于分析函數(shù)。Oracle從8.1.6開始提供窗口函數(shù)，窗口函數(shù)用于計算基于組的某種聚合值，
窗口函數(shù)指定了分析函數(shù)工作的數(shù)據(jù)窗口大小，這個數(shù)據(jù)窗口大小可能會隨著行的變化而變化。
與聚合函數(shù)的不同之處是：對于每個組返回多行，而聚合函數(shù)對于每個組只返回一行

基本語法: ?分析函數(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ù)，一行只有一個類別，而partiition by則不會減少原表中的行數(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)常添加選項用以改變進(jìn)行聚合運算的窗口范圍。如果 OVER 關(guān)鍵字后的括號中的選項為空，則窗口函數(shù)會對結(jié)果集中的所有行進(jìn)行聚合運算。

分析函數(shù) over(partition by 列名 order by 列名 rows between 開始位置 and 結(jié)束位置)

為什么叫開窗呢？

因為在over()括號中的，partition() 函數(shù)可以將查詢到的數(shù)據(jù)進(jìn)行單獨開一個窗口處理。譬如，查詢每個班級的學(xué)生的排名情況，查詢每個國家的歷年人口等，諸如此類，都是在查詢到的每一個班級、每一個國家中都開一個窗口，單獨去執(zhí)行命令。

rows和range分別表示選擇后幾行、選擇數(shù)據(jù)范圍
理解 rows between 含義,也叫做window子句：

preceding：往前following：往后current row：當(dāng)前行unbounded：無邊界，unbounded precending 表示從最前面的起點開始， unbounded following：表示到最后面的終點注：不加 partition by 的話則把整個數(shù)據(jù)集當(dāng)作一個分區(qū)，不加 order by的話會對某些函數(shù)統(tǒng)計結(jié)果產(chǎn)生影響，如sum()

3、一些分析函數(shù)的使用方法

1.聚合函數(shù)

聚合函數(shù)	定義
sum()	求和
max()	求最大值
min()	求最小值
avg()	求平均值
count()	統(tǒng)計數(shù)

2.序列函數(shù)

序列函數(shù)	定義
row_number()	按照值排序時產(chǎn)生一個自增編號，值相等時不會重復(fù)，不會產(chǎn)生空位
rank()	按照值排序時產(chǎn)生一個自增編號，值相等時會重復(fù)，會產(chǎn)生空位
dense_rank()	按照值排序時產(chǎn)生一個自增編號，值相等時會重復(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)計窗口內(nèi)往上第n行值第一個參數(shù)為列名，第二個參數(shù)為往上第n行（可選，默認(rèn)為1），第三個參數(shù)為默認(rèn)值（當(dāng)往上第n行為NULL時候，取默認(rèn)值，如不指定，則為NULL
lead()	用于統(tǒng)計窗口內(nèi)往下第n行值第一個參數(shù)為列名，第二個參數(shù)為往下第n行（可選，默認(rèn)為1），第三個參數(shù)為默認(rèn)值（當(dāng)往下第n行為NULL時候，取默認(rèn)值，如不指定，則為NULL
ntile()	用于將分組數(shù)據(jù)按照順序切分成n片，返回當(dāng)前切片值
first_value()	取分組內(nèi)排序后，截止到當(dāng)前行，第一個值
last_value()	取分組內(nèi)排序后，截止到當(dāng)前行，最后一個值
cume_dist()	返回小于等于當(dāng)前值的行數(shù)/分組內(nèi)總行數(shù)

4、OVER()參數(shù)——分組函數(shù)

partition by 子句：

窗口函數(shù)的 over 關(guān)鍵字后括號中的可以使用 partition by 子句來定義行的分區(qū)來供進(jìn)行聚合計算。
與 group by 子句不同，partition by 子句創(chuàng)建的分區(qū)是獨立于結(jié)果集的，創(chuàng)建的分區(qū)只是供進(jìn)行
聚合計算的，而且不同的窗口函數(shù)所創(chuàng)建的分區(qū)也不互相影響。

5、OVER()參數(shù)——排序函數(shù)

order by 子句：

窗口函數(shù)中可以在over關(guān)鍵字后的選項中使用order by 子句來指定排序規(guī)則，而且有的窗口函數(shù)還
要求必須指定排序規(guī)則。使用order by 子句可以對結(jié)果集按照指定的排序規(guī)則進(jìn)行排序，并且在一個
指定的范圍內(nèi)進(jìn)行聚合運算。
語法：ORDER BY字段名  RANGE|ROWS  BETWEEN邊界規(guī)則1  AND  邊界規(guī)則2

PARTITION BY子句和ORDER BY 可以共同使用，從而可以實現(xiàn)更加復(fù)雜的功能

到此這篇關(guān)于Oracle數(shù)據(jù)庫分析函數(shù)用法的文章就介紹到這了,更多相關(guān)Oracle函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: