溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
什么是over分析函數(shù)，針對這個問題，這篇文章詳細介紹了相對應(yīng)的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。
over(Partition by...) 一個超級牛皮的ORACLE特有函數(shù)。
天天都用ORACLE，用了快2年了。最近才接觸到這個功能強大而靈活的函數(shù)。真實慚愧啊！
oracle的分析函數(shù)over 及開窗函數(shù)
一：分析函數(shù)over
Oracle從8.1.6開始提供分析函數(shù)，分析函數(shù)用于計算基于組的某種聚合值，它和聚合函數(shù)的不同之處是
對于每個組返回多行，而聚合函數(shù)對于每個組只返回一行。
下面通過幾個例子來說明其應(yīng)用。
1：統(tǒng)計某商店的營業(yè)額。
date sale
1 20
2 15
3 14
4 18
5 30
規(guī)則：按天統(tǒng)計：每天都統(tǒng)計前面幾天的總額
得到的結(jié)果：
DATE SALE SUM
----- -------- ------
1 20 20 --1天
2 15 35 --1天＋2天
3 14 49 --1天＋2天＋3天
4 18 67 .
5 30 97 .
2:統(tǒng)計各班成績第一名的同學信息
NAME CLASS S
----- ----- ----------------------
fda 1 80
ffd 1 78
dss 1 95
cfe 2 74
gds 2 92
gf 3 99
ddd 3 99
adf 3 45
asdf 3 55
3dd 3 78
通過：
--
select * from
(
select name,class,s,rank()over(partition by class order by s desc) mm from t2
)
where mm=1
--
得到結(jié)果：
NAME CLASS S MM
----- ----- ---------------------- ----------------------
dss 1 95 1
gds 2 92 1
gf 3 99 1
ddd 3 99 1
注意：
1.在求第一名成績的時候，不能用row_number()，因為如果同班有兩個并列第一，row_number()只返回一個結(jié)果
2.rank()和dense_rank()的區(qū)別是：
--rank()是跳躍排序，有兩個第二名時接下來就是第四名
--dense_rank()l是連續(xù)排序，有兩個第二名時仍然跟著第三名
3.分類統(tǒng)計 (并顯示信息)
A B C
-- -- ----------------------
m a 2
n a 3
m a 2
n b 2
n b 1
x b 3
x b 2
x b 4
h b 3
select a,c,sum(c)over(partition by a) from t2
得到結(jié)果：
A B C SUM(C)OVER(PARTITIONBYA)
-- -- ------- ------------------------
h b 3 3
m a 2 4
m a 2 4
n a 3 6
n b 2 6
n b 1 6
x b 3 9
x b 2 9
x b 4 9
如果用sum，group by 則只能得到
A SUM(C)
-- ----------------------
h 3
m 4
n 6
x 9
無法得到B列值
＝＝＝＝＝
select * from test
數(shù)據(jù):
A B C
1 1 1
1 2 2
1 3 3
2 2 5
3 4 6
---將B欄位值相同的對應(yīng)的C 欄位值加總
select a,b,c, SUM(C) OVER (PARTITION BY B) C_Sum
from test
A B C C_SUM
1 1 1 1
1 2 2 7
2 2 5 7
1 3 3 3
3 4 6 6
---如果不需要已某個欄位的值分割,那就要用 null
eg: 就是將C的欄位值summary 放在每行后面
select a,b,c, SUM(C) OVER (PARTITION BY null) C_Sum
from test
A B C C_SUM
1 1 1 17
1 2 2 17
1 3 3 17
2 2 5 17
3 4 6 17
求個人工資占部門工資的百分比
SQL> select * from salary;
NAME DEPT SAL
---------- ---- -----
a 10 2000
b 10 3000
c 10 5000
d 20 4000
SQL> select name,dept,sal,sal*100/sum(sal) over(partition by dept) percent from salary;
NAME DEPT SAL PERCENT
---------- ---- ----- ----------
a 10 2000 20
b 10 3000 30
c 10 5000 50
d 20 4000 100
二：開窗函數(shù)
開窗函數(shù)指定了分析函數(shù)工作的數(shù)據(jù)窗口大小，這個數(shù)據(jù)窗口大小可能會隨著行的變化而變化，舉例如下：
1：
over（order by salary） 按照salary排序進行累計，order by是個默認的開窗函數(shù)
over（partition by deptno）按照部門分區(qū)
2：
over（order by salary range between 5 preceding and 5 following）
每行對應(yīng)的數(shù)據(jù)窗口是之前行幅度值不超過5，之后行幅度值不超過5
例如:對于以下列
aa
1
2
2
2
3
4
5
6
7
9
sum(aa)over（order by aa range between 2 preceding and 2 following）
得出的結(jié)果是
AA SUM
---------------------- -------------------------------------------------------
1 10
2 14
2 14
2 14
3 18
4 18
5 22
6 18
7 22
9 9
就是說，對于aa=5的一行 ，sum為 5-1<=aa<=5+2 的和
對于aa=2來說 ，sum=1+2+2+2+3+4=14 ；
又如 對于aa=9 ，9-1<=aa<=9+2 只有9一個數(shù)，所以sum=9 ；
3：其它：
over（order by salary rows between 2 preceding and 4 following）
每行對應(yīng)的數(shù)據(jù)窗口是之前2行，之后4行
4：下面三條語句等效：
over（order by salary rows between unbounded preceding and unbounded following）
每行對應(yīng)的數(shù)據(jù)窗口是從第一行到最后一行，等效：
over（order by salary range between unbounded preceding and unbounded following）
等效
over(partition by null)
常用的分析函數(shù)如下所列:
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 ...)
示例
SQL> select type,qty from test;
TYPE QTY
---------- ----------
1 6
2 9
SQL> select type,qty,to_char(row_number() over(partition by type order by qty))||'/'||to_char(count(*) over(partition by type)) as cnt2 from test;
TYPE QTY CNT2
---------- ---------- ------------
3 1/2
1 6 2/2
2 5 1/3
7 2/3
2 9 3/3
SQL> select * from test;
---------- -------------------------------------------------
1 11111
2 22222
3 33333
4 44444
SQL> select t.id,mc,to_char(b.rn)||'/'||t.id)e
2 from test t,
(select rownum rn from (select max(to_number(id)) mid from test) connect by rownum <=mid ))L
4 where b.rn<=to_number(t.id)
order by id
ID MC TO_CHAR(B.RN)||'/'||T.ID
--------- -------------------------------------------------- ---------------------------------------------------
1 11111 1/1
2 22222 1/2
2 22222 2/2
3 33333 1/3
3 33333 2/3
3 33333 3/3
44444 1/4 44444 2/4
4 44444 3/4CNOUG4 44444 4/4
10 rows selected
*******************************************************************
關(guān)于partition by
這些都是分析函數(shù)，好像是8.0以后才有的 row_number()和rownum差不多，功能更強一點（可以在各個分組內(nèi)從1開時排序） rank()是跳躍排序，有兩個第二名時接下來就是第四名（同樣是在各個分組內(nèi)） dense_rank()l是連續(xù)排序，有兩個第二名時仍然跟著第三名。相比之下row_number是沒有重復值的 lag（arg1,arg2,arg3): arg1是從其他行返回的表達式 arg2是希望檢索的當前行分區(qū)的偏移量。是一個正的偏移量，時一個往回檢索以前的行的數(shù)目。 arg3是在arg2表示的數(shù)目超出了分組的范圍時返回的值。
1.
select deptno,row_number() over(partition by deptno order by sal) from emp order by deptno;
2.
select deptno,rank() over (partition by deptno order by sal) from emp order by deptno;
3.
select deptno,dense_rank() over(partition by deptno order by sal) from emp order by deptno;
4.
select deptno,ename,sal,lag(ename,1,null) over(partition by deptno order by ename) from emp ord er by deptno;
5.
select deptno,ename,sal,lag(ename,2,'example') over(partition by deptno order by ename) from em p
order by deptno;
6.
select deptno, sal,sum(sal) over(partition by deptno) from emp;--每行記錄后都有總計值 select deptno, sum(sal) from emp group by deptno;
7. 求每個部門的平均工資以及每個人與所在部門的工資差額
select deptno,ename,sal ,
round(avg(sal) over(partition by deptno)) as dept_avg_sal,
round(sal-avg(sal) over(partition by deptno)) as dept_sal_diff
from emp;
關(guān)于什么是over分析函數(shù)問題的解答就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關(guān)注億速云行業(yè)資訊頻道了解更多相關(guān)知識。
免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼
