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 關鍵字表示把函數(shù)當成窗口函數(shù)而不是聚合函數(shù)。SQL 標準允許將所有聚合函數(shù)用做窗口函數(shù)，使用 OVER 關鍵字來區(qū)分這兩種用法。
2、窗口函數(shù)——開窗
OVER 關鍵字后的括號中經常添加選項用以改變進行聚合運算的窗口范圍。如果 OVER 關鍵字后的括號中的選項為空，則窗口函數(shù)會對結果集中的所有行進行聚合運算。
分析函數(shù) over(partition by 列名 order by 列名 rows between 開始位置 and 結束位置)
為什么叫開窗呢？
因為在over()括號中的，partition() 函數(shù)可以將查詢到的數(shù)據(jù)進行單獨開一個窗口處理。譬如，查詢每個班級的學生的排名情況，查詢每個國家的歷年人口等，諸如此類，都是在查詢到的每一個班級、每一個國家中都開一個窗口，單獨去執(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)計結果產生影響，如sum()
3、一些分析函數(shù)的使用方法
1.聚合函數(shù)
| 聚合函數(shù) | 定義 |
|---|---|
| sum() | 求和 |
| max() | 求最大值 |
| min() | 求最小值 |
| avg() | 求平均值 |
| count() | 統(tǒng)計數(shù) |
2.序列函數(shù)
| 序列函數(shù) | 定義 |
|---|---|
| row_number() | 按照值排序時產生一個自增編號，值相等時不會重復，不會產生空位 |
| rank() | 按照值排序時產生一個自增編號，值相等時會重復，會產生空位 |
| dense_rank() | 按照值排序時產生一個自增編號，值相等時會重復，不會產生空位 |
row_number()
select * ，row_number()over(oder by 成績 desc) as 排名 from 班級表
查詢結果：
+------------+--------+------+------+------+ | 姓名 | 性別 | 班級 | 成績 | 排名 | +------------+--------+------+------+------+ | 張三 | 男 | 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 班級表
查詢結果：
+------------+--------+------+------+------+ | 姓名 | 性別 | 班級 | 成績 | 排名 | +------------+--------+------+------+------+ | 張三 | 男 | 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 班級表
查詢結果：
+------------+--------+------+------+------+ | 姓名 | 性別 | 班級 | 成績 | 排名 | +------------+--------+------+------+------+ | 張三 | 男 | 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() | 分組內當前行的rank值-1/分組內總行數(shù)-1 |
| lag() | 用于統(tǒng)計窗口內往上第n行值 第一個參數(shù)為列名，第二個參數(shù)為往上第n行（可選，默認為1），第三個參數(shù)為默認值（當往上第n行為NULL時候，取默認值，如不指定，則為NULL |
| lead() | 用于統(tǒng)計窗口內往下第n行值 第一個參數(shù)為列名，第二個參數(shù)為往下第n行（可選，默認為1），第三個參數(shù)為默認值（當往下第n行為NULL時候，取默認值，如不指定，則為NULL |
| ntile() | 用于將分組數(shù)據(jù)按照順序切分成n片，返回當前切片值 |
| first_value() | 取分組內排序后，截止到當前行，第一個值 |
| last_value() | 取分組內排序后，截止到當前行，最后一個值 |
| cume_dist() | 返回小于等于當前值的行數(shù)/分組內總行數(shù) |
4、OVER()參數(shù)——分組函數(shù)
partition by 子句：
窗口函數(shù)的 over 關鍵字后括號中的可以使用 partition by 子句來定義行的分區(qū)來供進行聚合計算。 與 group by 子句不同，partition by 子句創(chuàng)建的分區(qū)是獨立于結果集的，創(chuàng)建的分區(qū)只是供進行 聚合計算的，而且不同的窗口函數(shù)所創(chuàng)建的分區(qū)也不互相影響。
5、OVER()參數(shù)——排序函數(shù)
order by 子句：
窗口函數(shù)中可以在over關鍵字后的選項中使用order by 子句來指定排序規(guī)則，而且有的窗口函數(shù)還 要求必須指定排序規(guī)則。使用order by 子句可以對結果集按照指定的排序規(guī)則進行排序，并且在一個 指定的范圍內進行聚合運算。 語法：ORDER BY字段名 RANGE|ROWS BETWEEN邊界規(guī)則1 AND 邊界規(guī)則2
PARTITION BY子句和ORDER BY 可以共同使用，從而可以實現(xiàn)更加復雜的功能
到此這篇關于Oracle數(shù)據(jù)庫分析函數(shù)用法的文章就介紹到這了,更多相關Oracle函數(shù)內容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持本站！
版權聲明：本站文章來源標注為YINGSOO的內容版權均為本站所有，歡迎引用、轉載，請保持原文完整并注明來源及原文鏈接。禁止復制或仿造本網站，禁止在非www.sddonglingsh.com所屬的服務器上建立鏡像，否則將依法追究法律責任。本站部分內容來源于網友推薦、互聯(lián)網收集整理而來，僅供學習參考，不代表本站立場，如有內容涉嫌侵權，請聯(lián)系alex-e#qq.com處理。
關注官方微信