👨?🎓 博主介紹：
IT邦德，江湖人稱jeames007，10年DBA作業經驗
中國DBA聯盟(ACDU)成員，目前從事DBA及程式編程
（Web\java\Python）作業，主要服務于生產制造
現擁有 Oracle 11g  OCP/OCM、
Mysql、Oceanbase（OBCA）認證
分布式TBase\TDSQL資料庫、國產達夢資料庫以及紅帽子認證

擅長主流資料Oracle、MySQL、PG 運維開發，備份恢復，
安裝遷移，性能優化、故障應急處理等，

前言

🍁 一、分析函式語法

function_name(<argument>,<argument>...) over(<partition_Clause><order by_Clause><windowing_Clause>);

🍃 1.1 引數詳解

function_name()：函式名稱
argument：引數
over( )：開窗函式
partition_Clause：磁區子句，資料記錄集分組，group by…
order by_Clause：排序子句，資料記錄集排序，order by…
windowing_Clause：開窗子句，定義分析函式在操作行的集合，三種開窗方式：rows、range、Specifying
注：使用開窗子句時一定要有排序子句！！！

🍃 1.2 常用分析函式

row_number() over(partition by … order by …)
rank() over(partition by … order by …)
dense_rank() over(partition by … order by …)
count() over(partition by … order by …)
max() over(partition by … order by …)
min() over(partition by … order by …)
sum() over(partition by … order by …)
avg() over(partition by … order by …)
first_value() over(partition by … order by …)
last_value() over(partition by … order by …)
lag() over(partition by … order by …)
lead() over(partition by … order by …)

🍁 二、匯總

1、count() over() ：統計磁區中各組的行數，partition by 可選，order by 可選
2、sum() over() ：統計磁區中記錄的總和，partition by 可選，order by 可選
3、avg() over() ：統計磁區中記錄的平均值，partition by 可選，order by 可選
4、min() over() ：統計磁區中記錄的最小值，partition by 可選，order by 可選
5、 max() over() ：統計磁區中記錄的最大值，partition by 可選，order by 可選

🍃 2.1 總計數

–總計數,統計所有行的總數
select ename,deptno,count(*) over() from emp;

🍃 2.2 遞加累計求和

–,先排序，再遞加累計求和
select ename,deptno,sal,hiredate,sum(sal) over(order by hiredate) from emp;

🍃 2.3 分組求平均值

–先分組，再對組內求平均值
select ename,deptno,job,sal,avg(sal) over(partition by job) from emp;

🍃 2.4 分組遞加求最大

–先分組排序，再對組內求最大值
select ename,deptno,job,hiredate,sal,
max(sal)over(partition by job order by hiredate ) from emp;

🍁 三、排序

🍃 3.1 無重復值排序

row_number() over() ：排序，無重復值，partition by 可選，order by 必選
select row_number() over(order by a.sal desc) rak,a.* from emp a;
當SAL相同時，按順序排序

–先分組，再對組內排序，無重復值
select row_number() over(partition by deptno order by a.sal desc) rak,a.* from emp a;

🍃 3.2 排序連續

dense_rank() ：連續排序，partition by 可選，order by 必選
–當值相同時，排序是連續的
select dense_rank() over(partition by deptno order by a.sal desc) rak,a.* from emp a;

🍃 3.3 排序跳躍

rank() over() ：跳躍排序，partition by 可選，order by 必選
當值相同時，排序是跳躍的
select rank() over(partition by deptno order by a.sal desc) rak,a.* from emp a;、

🍁 四、KEEP函式

max(#) keep(dense_rank first order by # desc)
min(#) keep(dense_rank last order by # desc)
從DENSE_RANK回傳的集合中取出排在最后面或者最前面的一個值的行
可能多行，因為值可能相等，因此完整的語法需要在開始處加上一個集合函式以從中取出記錄

select job, max(sal) keep(dense_rank first order by sal desc), max(sal) keep(dense_rank last order by sal desc) from emp group by job;

🍁 五、開窗函式

指定了分析函式作業的資料視窗大小，這個資料視窗大小可能會隨著行的變化而變化
50 preceding:前50行
150 following:后150行
UNBOUNDED ：不受控的，無限的
current row 當前行

// 從當前行到最后的資料
between current row and unbounded following
//前面所有行與當前行的累加
rows between unbounded preceding and current row
//前一行的值+當前行的值+后一行的值
rows between 1 preceding and 1 following
//開始到結束的所有資料
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

🍃 5.1 ROW視窗

rows是物理視窗,在一組內基于任意變化或固定的視窗中，可以用該子句來讓分析函式計算它的值
這將在一組內創建一個變化的視窗，請注意，要使用視窗，必須使用ORDER BY 子句

select deptno,ename,sal,deptno,
sum(sal) over(
         partition by deptno
         order by ename
         rows 2 preceding
)sliding_total
from emp

🍃 5.2 Range視窗

range是邏輯視窗,是對范圍進行統計
是指定當前行對應值的范圍取值，列數不固定，只要行值在范圍內，對應列都包含在內
有數字，記住precding,就是減，following是加， 統計在這個范圍內的資料

rows表示行，就是前n行，后n行
而range表示的是具體的值，比這個值小n的行，比這個值大n的行
range between是以當前值為錨點進行計算

range between 4 preceding AND 7 following
表示：如果當前值為10的話就取前后的值在6到17之間的資料，
sum(close) range between 100 preceding and 200 following
表示：通過欄位差值來進行選擇，如當前行的 close 欄位值是 200，
那么這個視窗大小的定義就會選擇磁區中 close 欄位值落在 100 至 400 區間的記錄（行），

select ename,hiredate,sal,deptno,
sum(sal) over(partition by deptno order by sal asc range 100 preceding) sum_sal
from emp;

大家點贊、收藏、關注、評論啦 👇🏻👇🏻👇🏻微信公眾號👇🏻👇🏻👇🏻