日本免费高清视频-国产福利视频导航-黄色在线播放国产-天天操天天操天天操天天操|www.shdianci.com

學無先后，達者為師

網站首頁 編程語言 正文

Oracle數據庫分析函數用法_oracle

作者：ette ? 更新時間： 2022-03-16 編程語言

1、什么是窗口函數？

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

基本語法: ?分析函數? over (partition by ?用于分組的列名? order by ?用于排序的列名?)。
語法中的?分析函數?主要由序列函數（rank、dense_rank和row_number等組成）
與聚合函數（sum、avg、count、max和min等）作為窗口函數組成。

從窗口函數組成上看，它是group by 和 order by的功能組合，group by分組匯總后改變了表的行數，一行只有一個類別，而partiition by則不會減少原表中的行數。
恰如窗口函數的組成，它同時具有分組和排序的功能，且不減少原表的行數。
OVER 關鍵字表示把函數當成窗口函數而不是聚合函數。SQL 標準允許將所有聚合函數用做窗口函數，使用 OVER 關鍵字來區分這兩種用法。

2、窗口函數——開窗

OVER 關鍵字后的括號中經常添加選項用以改變進行聚合運算的窗口范圍。如果 OVER 關鍵字后的括號中的選項為空，則窗口函數會對結果集中的所有行進行聚合運算。

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

為什么叫開窗呢？

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

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

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

3、一些分析函數的使用方法

1.聚合函數

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

2.序列函數

序列函數 定義
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/分組內總行數-1
lag() 用于統計窗口內往上第n行值 第一個參數為列名，第二個參數為往上第n行（可選，默認為1），第三個參數為默認值（當往上第n行為NULL時候，取默認值，如不指定，則為NULL
lead() 用于統計窗口內往下第n行值 第一個參數為列名，第二個參數為往下第n行（可選，默認為1），第三個參數為默認值（當往下第n行為NULL時候，取默認值，如不指定，則為NULL
ntile() 用于將分組數據按照順序切分成n片，返回當前切片值
first_value() 取分組內排序后，截止到當前行，第一個值
last_value() 取分組內排序后，截止到當前行，最后一個值
cume_dist() 返回小于等于當前值的行數/分組內總行數

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

partition by 子句：

窗口函數的 over 關鍵字后括號中的可以使用 partition by 子句來定義行的分區來供進行聚合計算。
與 group by 子句不同，partition by 子句創建的分區是獨立于結果集的，創建的分區只是供進行
聚合計算的，而且不同的窗口函數所創建的分區也不互相影響。

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

order by 子句：

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

PARTITION BY子句和ORDER BY 可以共同使用，從而可以實現更加復雜的功能

原文鏈接：https://blog.csdn.net/u012191784/article/details/122108042

欄目分類
最近更新