Oracle數(shù)據(jù)庫分析函數(shù)用法
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ù)當成窗口函數(shù)而不是聚合函數(shù)。SQL 標準允許將所有聚合函數(shù)用做窗口函數(shù)，使用 OVER 關(guān)鍵字來區(qū)分這兩種用法。
2、窗口函數(shù)——開窗
OVER 關(guān)鍵字后的括號中經(jīng)常添加選項用以改變進行聚合運算的窗口范圍。如果 OVER 關(guān)鍵字后的括號中的選項為空，則窗口函數(shù)會對結(jié)果集中的所有行進行聚合運算。
分析函數(shù) over(partition by 列名 order by 列名 rows between 開始位置 and 結(jié)束位置)
為什么叫開窗呢？
因為在over()括號中的，partition() 函數(shù)可以將查詢到的數(shù)據(jù)進行單獨開一個窗口處理。譬如，查詢每個班級的學(xué)生的排名情況，查詢每個國家的歷年人口等，諸如此類，都是在查詢到的每一個班級、每一個國家中都開一個窗口，單獨去執(zhí)行命令。
rows和range分別表示選擇后幾行、選擇數(shù)據(jù)范圍
理解 rows between 含義,也叫做window子句：
preceding：往前following：往后current row：當前行unbounded：無邊界，unbounded precending 表示從最前面的起點開始， unbounded following：表示到最后面的終點注：不加 partition by 的話則把整個數(shù)據(jù)集當作一個分區(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)當前行的rank值-1/分組內(nèi)總行數(shù)-1 |
| lag() | 用于統(tǒng)計窗口內(nèi)往上第n行值 第一個參數(shù)為列名，第二個參數(shù)為往上第n行（可選，默認為1），第三個參數(shù)為默認值（當往上第n行為NULL時候，取默認值，如不指定，則為NULL |
| lead() | 用于統(tǒng)計窗口內(nèi)往下第n行值 第一個參數(shù)為列名，第二個參數(shù)為往下第n行（可選，默認為1），第三個參數(shù)為默認值（當往下第n行為NULL時候，取默認值，如不指定，則為NULL |
| ntile() | 用于將分組數(shù)據(jù)按照順序切分成n片，返回當前切片值 |
| first_value() | 取分組內(nèi)排序后，截止到當前行，第一個值 |
| last_value() | 取分組內(nèi)排序后，截止到當前行，最后一個值 |
| cume_dist() | 返回小于等于當前值的行數(shù)/分組內(nèi)總行數(shù) |
4、OVER()參數(shù)——分組函數(shù)
partition by 子句：
窗口函數(shù)的 over 關(guān)鍵字后括號中的可以使用 partition by 子句來定義行的分區(qū)來供進行聚合計算。 與 group by 子句不同，partition by 子句創(chuàng)建的分區(qū)是獨立于結(jié)果集的，創(chuàng)建的分區(qū)只是供進行 聚合計算的，而且不同的窗口函數(shù)所創(chuàng)建的分區(qū)也不互相影響。
5、OVER()參數(shù)——排序函數(shù)
order by 子句：
窗口函數(shù)中可以在over關(guān)鍵字后的選項中使用order by 子句來指定排序規(guī)則，而且有的窗口函數(shù)還 要求必須指定排序規(guī)則。使用order by 子句可以對結(jié)果集按照指定的排序規(guī)則進行排序，并且在一個 指定的范圍內(nèi)進行聚合運算。 語法：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)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Oracle11g server 64bit服務(wù)器端安裝在Windows Server2008 Enterprise上,安裝Oracle11g client 32bit,通過SQL Plus以sysdba身份連接數(shù)據(jù)庫,并且創(chuàng)建表空間、用戶、授權(quán)成功,在連接數(shù)據(jù)庫時出現(xiàn)問題,下面講解ORA-12154無法解析指定的連接標識符問題,感興趣的朋友一起看看2024-01-01
本節(jié)主要介紹了Oracle 獲得以百分號結(jié)尾的腳本有三種寫法，以獲得ID=4443的數(shù)據(jù)行為例2014-07-07
本文介紹下，oracle中排名函數(shù)的用法，通過實例學(xué)習(xí)oracle排名函數(shù)的具體用法，有需要的朋友可以參考下2014-06-06
oracle表空間的創(chuàng)建及dmp 文件的導(dǎo)入(推薦)
這篇文章主要介紹了oracle表空間的創(chuàng)建及dmp 文件的導(dǎo)入,需非常不錯，具有參考借鑒價值，要的朋友可以參考下2017-08-08
本文介紹了通過Oracle10g發(fā)送Email的實例，該Procedure支持中文郵件、HTML代碼、多收件人。 ,需要的朋友可以參考下2014-01-01
plsql配置tnsnames.ora的實現(xiàn)方法
這篇文章主要介紹了plsql配置tnsnames.ora的實現(xiàn)方法，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-09-09
oracle?mysql?拼接值遇到的坑及雙豎線?||?concat詳解
在Oracle中，字符串拼接有兩種方法，分別是CONCAT()函數(shù)和“||”拼接，本文給大家講解oracle?mysql?拼接值遇到的坑及雙豎線?||?concat詳解，感興趣的朋友跟隨小編一起看看吧2023-04-04
在Oracle數(shù)據(jù)庫中，主要有兩種日志操作模式，分別為非歸檔模式與歸檔模式。默認情況下，數(shù)據(jù)庫采用的是非歸檔模式。作為一個合格的數(shù)據(jù)庫管理員，應(yīng)當深入了解這兩種日志操作模式的特點，并且在數(shù)據(jù)庫建立時，選擇合適的操作模式2013-01-01
Oracle用decode函數(shù)或CASE-WHEN實現(xiàn)自定義排序
這篇文章主要介紹了Oracle用decode函數(shù)或CASE-WHEN實現(xiàn)自定義排序功能，本文通過實例代碼給大家介紹的非常詳細，具有一定的參考借鑒價值,需要的朋友可以參考下2020-05-05
這篇文章主要為大家詳細介紹了Oracle 10g安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-01-01