一、聚合類開窗函數(shù)

1、sum(字段) over(開窗說明)

該函數(shù)是聚合類最常用的。

開窗說明：partition by–分組，并且沒有去重效果，order by—排序。開窗說明可以不寫。

聚合類開窗函數(shù)可以與未分組（group by）的字段一起顯示。

select ename,sum(sal) over(partition by deptno order by sal),deptno from emp;

例：以上查詢?yōu)?，各部門工資累加之后的結(jié)果。如：10部門，第一行為第一行累加到當(dāng)前行的結(jié)果（1300），第二行為第一行累加到當(dāng)前行的結(jié)果（1300+2450=3750），第三行為第一行累加到當(dāng)前行的結(jié)果（1300+2450+5000 = 8750）。
運行效果：

sum(sal) over()不分組不排序則，獲取全公司工資的總和，使用效果與聚合函數(shù)sum()一致，但是結(jié)果有多條。運行效果：

sum(sal) over(order by sal )只排序，默認是從第一行累加到當(dāng)前行，如：第一行為運行效果：

sum(sal) over(partition by deptno)只分組，查詢結(jié)果為各部門的工資總和以及各部門每個員工的工資。

2、min()、max()、avg()、count()，用法與sum()一致

只需改變函數(shù)名，通過查詢后的數(shù)據(jù)就可看出數(shù)據(jù)的特征，其他函數(shù)不怎么用就不全部舉例了，下面就舉兩個例子。

select ename,max(sal) over(partition by deptno order by empno),sal,deptno from emp;

查詢跟部門內(nèi)工資最高的員工。根據(jù)部門分組，比較組內(nèi)第一行到組內(nèi)當(dāng)前的最高工資，如：10部門，第一行為2450，則最高為2450,。第二行為5000，比2450大，所以最高工資為5000，第三行為1300，比5000小，則最高工資還是5000。

select ename,min(sal) over(partition by deptno order by empno),sal,deptno from emp;

查詢跟部門內(nèi)工資最高低的員工。根據(jù)部門分組，比較組內(nèi)第一行到組內(nèi)當(dāng)前的最低工資，如：10部門，第一行為2450，則最低為2450。第二行為5000，比2450大，所以最低工資還是為2450，第三行為1300，比2450小，所以最低工資就位1300。

3、拓展：統(tǒng)計范圍

范圍值：

current row：當(dāng)前行
n preceding：向上n行
n following：向下n行
unbounded preceding：起點開始，第一行開始
unbounded following：到終點，到最后一行

范圍關(guān)鍵字：rows between and

SELECT SUM(SAL) OVER(ORDER BY EMPNO) S1,
SUM(SAL) OVER(ORDER BY EMPNO ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) S2,
SUM(SAL) OVER(ORDER BY EMPNO ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) S3,
SUM(SAL) OVER(ORDER BY EMPNO ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) S4,
SAL,EMPNO FROM EMP;

• s1：表示累加。
• s2：表示當(dāng)前行到最后一行，也是累減的效果，第一行是從第一行開始到最后一行全部員工工資相加的結(jié)果，第二行是從第二行開始到最后一行全部員工工資相加的結(jié)果，第三行則是從第三行開始到最后一行全部員工工資相加的結(jié)果，以此類推。
• s3：表示上一行到下一行，第一行的結(jié)果為第一行的上一行（無）、第一行、第一行的下一行（也就是第二行）相加的結(jié)果，0+800+1600=2400，第二行的結(jié)果則為第一行、第二行、第三行相加的結(jié)果，800+1600+1250=3650，以此類推。
• s4：表示從第一行到最后一行，效果與累加一樣。

運行效果：

二、排名類開窗函數(shù)

row_number() over(開窗說明)、rank() over(開窗說明)、dense_rank() over(開窗說明)

SELECT ROW_NUMBER() OVER(ORDER BY SAL) s1,
RANK() OVER(ORDER BY SAL) s2,
DENSE_RANK() OVER(ORDER BY SAL) s3,
SAL
FROM EMP;

按照員工工資進行排名，通過觀察結(jié)果集數(shù)據(jù)可看出數(shù)據(jù)的特征所在。

三者的共同點與不同點

共同點：

函數(shù)后小括號都不寫任何東西；

三者的開窗說明中，必須包含order by 關(guān)鍵字，不寫則會報錯；

不同點：

ROW_NUMBER 生成一組連續(xù)且不重復(fù)的序號 123456；
RANK 有可能生成一組重復(fù)且不連續(xù)的序號 123356；
DENSE_RANK 有可能生成一組重復(fù)且連續(xù)的序號 123345；
序號總數(shù)會變少

經(jīng)典題型演練

查詢用戶連續(xù)登入三天及三天以上的用戶信息：

先建表以及插入數(shù)據(jù)：

create table logintest(user_id number,log_date date);
insert into logintest values(111,to_date(‘2021-06-01',‘yyyy-mm-dd'));
insert into logintest values(111,to_date(‘2021-06-02',‘yyyy-mm-dd'));
insert into logintest values(111,to_date(‘2021-06-03',‘yyyy-mm-dd'));
insert into logintest values(111,to_date(‘2021-06-05',‘yyyy-mm-dd'));
insert into logintest values(111,to_date(‘2021-06-08',‘yyyy-mm-dd'));
insert into logintest values(222,to_date(‘2021-06-01',‘yyyy-mm-dd'));
insert into logintest values(222,to_date(‘2021-06-03',‘yyyy-mm-dd'));
insert into logintest values(222,to_date(‘2021-06-04',‘yyyy-mm-dd'));
insert into logintest values(222,to_date(‘2021-06-06',‘yyyy-mm-dd'));
insert into logintest values(222,to_date(‘2021-06-07',‘yyyy-mm-dd'));
insert into logintest values(333,to_date(‘2021-06-01',‘yyyy-mm-dd'));
insert into logintest values(333,to_date(‘2021-06-02',‘yyyy-mm-dd'));
insert into logintest values(333,to_date(‘2021-06-04',‘yyyy-mm-dd'));
insert into logintest values(333,to_date(‘2021-06-06',‘yyyy-mm-dd'));
insert into logintest values(333,to_date(‘2021-06-07',‘yyyy-mm-dd'));
commit;

查詢用戶登入表

select * from logintest；

從數(shù)據(jù)中可看出111號用戶，有三天是連續(xù)登入的，所以我們要查詢的就是該用戶的信息。

分析解題思路：

1.先利用row_number() over(order by )開窗函數(shù)進行一個組內(nèi)排序，得出一個排序的結(jié)果（展示字段 jg）。

select user_id,log_date,row_number() over(partition by user_id order by log_date) jg from logintest;

運行效果：

2.用登入日期減去這個結(jié)果，會得到一個新的日期，由于登入日期是按從小到大的排序，這個排序結(jié)果也是，連續(xù)登入相差的天數(shù)是1，排序相減也是1，如果是連續(xù)登入的日期減去對應(yīng)的排序結(jié)果最后得到的日期是一樣。

select user_id,log_date,log_date - row_number() over(partition by user_id order by log_date) jg from logintest;

運行效果：

3.將以上得到的結(jié)果集當(dāng)做一個子表，用子查詢的方式再對該表按user_id，jg進行分組，并統(tǒng)計出現(xiàn)相同日期的次數(shù)，最后使用having過濾>=3的用戶信息。

select user_id,count(1) from (
select user_id,log_date,log_date - row_number() over(partition by user_id order by log_date) jg from logintest) p
group by p.user_id,p.jg having count(1) >= 3 order by p.user_id;

運行效果：

三、偏移類開窗函數(shù)

1、lead(字段，偏移值，缺省值) over(開窗說明)–向上偏移

SELECT LEAD(ENAME,1,‘AAA') OVER(PARTITION BY DEPTNO ORDER BY SAL), ENAME,SAL,DEPTNO FROM EMP;

按照部門分組，在組內(nèi)向上偏移一個單位，如：10部門，MILLER原本在第一位，現(xiàn)在進行了向上偏移，MILLER給過濾掉了，CLARK和KING統(tǒng)統(tǒng)向上偏移了一位，第三位空出來的有缺省值’AAA’填補，偏移單位和缺省值可以不寫，默認為1個單位和空。

開窗說明中必須存在關(guān)鍵字order by。

運行效果：

2、lag(字段，偏移值，缺省值) over(開窗說明)–向下偏移

使用方式與lead() over()一致，只是偏移方向改變了而已。

3、拓展

1.FIRST_VALUE(字段)OVER(開窗說明)，獲取某個字段下的第一行數(shù)據(jù)，開窗說明可以不寫。

select first_value(ename) over(partition by deptno order by empno),ename,empno,deptno from emp;

例：進行組內(nèi)排序，獲取各部門中第一行的員工姓名，10部門第一行的員工姓名為CLARK。運行效果：

2.LAST_VALUE(字段)OVER(開窗說明)，獲取某個字段下的最后一行數(shù)據(jù)，用法與first_value() over()一致。

四、占比類開窗函數(shù)

ratio_to_report(字段)OVER(開窗說明)

求某個值在全部范圍內(nèi)所占的比重。

注意：開窗說明中禁止使用order by 關(guān)鍵字，否則就會報錯。

SELECT RATIO_TO_REPORT(SAL) OVER(PARTITION BY DEPTNO),SAL, SUM(SAL) OVER(PARTITION BY DEPTNO) FROM EMP;

例：求各部門下各員工工資所占部門總工資的比重，10部門第一行，所占比重為2450.00/8750 = 0.28，運行效果：

五、切片類開窗函數(shù)

1、ntile(切分數(shù)量)OVER(開窗說明 )

NTILE函數(shù)對一個數(shù)據(jù)分區(qū)中的有序結(jié)果集進行劃分，將其分組為各個桶，并為每個小組分配一個唯一的組編號。

注意：開窗說明中必須包含order by 關(guān)鍵字，否則就會報錯，可搭配partition by 分組使用。

SELECT NTILE(3)OVER(ORDER BY SAL DESC),E.* FROM EMP E;

例：按工資降序排序，分為三個級別（NTILE(3)），系統(tǒng)會自動劃分。運行效果：

總結(jié) 

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

最新評論