一、聚合類開窗函數(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）。
運(yùn)行效果：

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

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

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

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

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

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)計(jì)范圍

范圍值：

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

范圍關(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：表示從第一行到最后一行，效果與累加一樣。

運(yùn)行效果：

二、排名類開窗函數(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;

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

三者的共同點(diǎn)與不同點(diǎn)

共同點(diǎn)：

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

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

不同點(diǎn)：

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

經(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號(hào)用戶，有三天是連續(xù)登入的，所以我們要查詢的就是該用戶的信息。

分析解題思路：

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

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

運(yùn)行效果：

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

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

運(yùn)行效果：

3.將以上得到的結(jié)果集當(dāng)做一個(gè)子表，用子查詢的方式再對(duì)該表按user_id，jg進(jìn)行分組，并統(tǒng)計(jì)出現(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;

運(yùn)行效果：

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

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

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

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

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

運(yùn)行效果：

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

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

3、拓展

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

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

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

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

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

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

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

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

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

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

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

1、ntile(切分?jǐn)?shù)量)OVER(開窗說明 )

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

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

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

例：按工資降序排序，分為三個(gè)級(jí)別（NTILE(3)），系統(tǒng)會(huì)自動(dòng)劃分。運(yùn)行效果：

總結(jié) 

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

最新評(píng)論