分析函數
它是Oracle分析函數專門針對類似于"經營總額"、"找出一組中的百分之多少" 或"計算排名前幾位"等問題設計的。
分析函數運行效率高，使用方便。
分析函數是基于一組行來計算的。這不同于聚集函數且廣泛應用于OLAP環境中。
Oracle從8.1.6開始提供分析函數，分析函數用于計算基于組的某種聚合值，它和聚合函數的不同之處是
對于每個組返回多行，而聚合函數對于每個組只返回一行。
語法：
<analytic-function>(<argument>,<argument>,...)
over(
<query-partition-clause>
<order-by-clause>
<windowing-clause>
)
其中：
1 over是關鍵字，用于標識分析函數。
2 <analytic-function>是指定的分析函數的名字。Oracle分析函數很多。
3 <argument>為參數，分析函數可以選取0-3個參數。
4 分區子句<query-partition-clause>的格式為：
partition by<value_exp>[,value_expr]...
關鍵字partition by子句根據由分區表達式的條件邏輯地將單個結果集分成N組。這里的"分區partition"和"組group"
都是同義詞。
5 排序子句order-by-clause指定數據是如何存在分區內的。其格式為：
order[siblings]by{expr|position|c_alias}[asc|desc][nulls first|nulls last]
其中：
(1)asc|desc:指定了排列順序。
(2)nulls first|nulls last:指定了包含空值的返回行應出現在有序序列中的第一個或最后一個位置。
6窗口子句windowing-clause
給出一個固定的或變化的數據窗口方法，分析函數將對這些數據進行操作。在一組基于任意變化或固定的窗口中，
可用該子句讓分析函數計算出它的值。
格式：
{rows|range}
{between
{unbounded preceding|current row |<value_expr>{preceding|following}
}and
{unbounded preceding|current row |<value_expr>{preceding|following}
}|{unbounded preceding|current row |<value_expr>{preceding|following
}}
(1)rows|range:此關鍵字定義了一個window。
(2)between...and...:為窗品指一個起點和終點。
(3)unbounded preceding：指明窗口是從分區(partition)的第一行開始。
(4)current row:指明窗口是從當前行開始。
create table emp(
deptno varchar2(20),--部門編碼
ename varchar2(20),--人名
sal number(10));--工資
insert into emp values('10','andy1',2000);
insert into emp values('10','andy2',3000);
insert into emp values('10','andy3',2000);
insert into emp values('20','leno1',4000);
insert into emp values('20','leno2',8000);
insert into emp values('20','leno3',6000);
insert into emp values('30','jack1',5000);
insert into emp values('30','jack2',6000);
insert into emp values('30','jack3',7000);
1 連續求和
select deptno,ename,sal,sum(sal) over(order by ename) 連續求和 from emp;
DEPTNO ENAME SAL 連續求和
-------------------- -------------------- ----------- ----------
10 andy1 2000 2000
10 andy2 3000 5000
10 andy3 2000 7000
30 jack1 5000 12000
30 jack2 6000 18000
30 jack3 7000 25000
20 leno1 4000 29000
20 leno2 8000 37000
20 leno3 6000 43000
2 不連續求和
select deptno,ename,sal,sum(sal) over() 不連續求和 from emp;
DEPTNO ENAME SAL 不連續求和
-------------------- -------------------- ----------- ----------
10 andy1 2000 43000
10 andy2 3000 43000
10 andy3 2000 43000
20 leno1 4000 43000
20 leno2 8000 43000
20 leno3 6000 43000
30 jack1 5000 43000
30 jack2 6000 43000
30 jack3 7000 43000
3.
select deptno,ename,sal,
sum(sal) over(order by ename) 連續求和,
sum(sal) over() 總和,
100*round(sal/sum(sal) over(),4) "份額(%)"
from emp
/
DEPTNO ENAME SAL 連續求和 總和 份額(%)
-------------------- -------------------- ----------- ---------- ---------- ----------
10 andy1 2000 2000 43000 4.65
10 andy2 3000 5000 43000 6.98
10 andy3 2000 7000 43000 4.65
30 jack1 5000 12000 43000 11.63
30 jack2 6000 18000 43000 13.95
30 jack3 7000 25000 43000 16.28
20 leno1 4000 29000 43000 9.3
20 leno2 8000 37000 43000 18.6
20 leno3 6000 43000 43000 13.95
4.使用子分區查詢。
按部門薪水連續的總和.
(1)select deptno,sum(sal) over (partition by deptno order by ename) 按部門連續求總和 from emp;
DEPTNO 按部門連續求總和
-------------------- ----------------
10 2000
10 5000
10 7000
20 4000
20 12000
20 18000
30 5000
30 11000
30 18000
(2)按部門求總和
select deptno,sum(sal) over (partition by deptno) 按部門連續求總和 from emp ;
DEPTNO 按部門求總和
-------------------- ----------------
10 7000
10 7000
10 7000
20 18000
20 18000
20 18000
30 18000
30 18000
30 18000
(3)不按部門連續求總和
select deptno,sum(sal) over (order by deptno,ename) 不按部門連續求總和 from emp ;
DEPTNO 不按部門連續求總和
-------------------- ------------------
10 2000
10 5000
10 7000
20 11000
20 19000
20 25000
30 30000
30 36000
30 43000
(4)不按部門，求所有員工總和，效果等同于sum(sal)
select deptno,sum(sal) over (order by deptno,ename) 不按部門連續求總和 from emp ;
DEPTNO 不按部門連續求總和
-------------------- ------------------
10 2000
10 5000
10 7000
20 11000
20 19000
20 25000
30 30000
30 36000
30 43000
(5)select deptno,ename,sal,
sum(sal) over (partition by deptno order by ename) 部門連續求和,--各部門的薪水"連續"求和
sum(sal) over (partition by deptno) 部門總和, -- 部門統計的總和，同一部門總和不變
100*round(sal/sum(sal) over (partition by deptno),4) "部門份額(%)",
sum(sal) over (order by deptno,ename) 連續求和, --所有部門的薪水"連續"求和
sum(sal) over () 總和, -- 此處sum(sal) over () 等同于sum(sal)，所有員工的薪水總和
100*round(sal/sum(sal) over (),4) "總份額(%)"
from emp;
DEPTNO ENAME SAL 部門連續求和 部門總和 部門份額(%) 連續求和 總和 總份額(%)
-------------------- -------------------- ----------- ------------ ---------- ----------- ---------- ---------- ----------
10 andy1 2000 2000 7000 28.57 2000 43000 4.65
10 andy2 3000 5000 7000 42.86 5000 43000 6.98
10 andy3 2000 7000 7000 28.57 7000 43000 4.65
20 leno1 4000 4000 18000 22.22 11000 43000 9.3
20 leno2 8000 12000 18000 44.44 19000 43000 18.6
20 leno3 6000 18000 18000 33.33 25000 43000 13.95
30 jack1 5000 5000 18000 27.78 30000 43000 11.63
30 jack2 6000 11000 18000 33.33 36000 43000 13.95
30 jack3 7000 18000 18000 38.89 43000 43000 16.28
(6)TOP-N查詢
6.1查詢各部門中工資最高的記錄
select * from(select deptno,ename,sal,row_number() over(partition by deptno order by sal desc) topn from emp) where topn=1;
DEPTNO ENAME SAL TOPN
-------------------- -------------------- ----------- ----------
10 andy2 3000 1
20 leno2 8000 1
30 jack3 7000 1
6.2按薪水高低對每個員工在本部門和整個公司內的排名進行排序。
select deptno,ename,sal,dense_rank() over(partition by deptno order by sal desc nulls last) as dept_ranking,
dense_rank() over(order by sal desc nulls last) as company_ranking
from emp;
DEPTNO ENAME SAL DEPT_RANKING COMPANY_RANKING
-------------------- -------------------- ----------- ------------ ---------------
20 leno2 8000 1 1
30 jack3 7000 1 2
20 leno3 6000 2 3
30 jack2 6000 2 3
30 jack1 5000 3 4
20 leno1 4000 3 5
10 andy2 3000 1 6
10 andy1 2000 2 7
10 andy3 2000 2 7
5 窗口windows
窗口子句是數據的滑動窗口，該窗口的分析函數將窗口看成一組。
select deptno "部門ID",ename "部門名稱",sal "工資",
sum(sal) over(partition by deptno order by ename rows 2 preceding) "sliding total"
from emp order by deptno,ename;
partition by deptno:相當于group by deptno
rows 2:表示前兩行相加
preceding:表示從每個部門的第一行開始。
6 范圍窗口
Range windows僅對數據值和日期類型數據有效。(sal)
select deptno,ename,sal, count(*) over(order by sal asc range 3 preceding) 總計
7 行窗口
是物理單元，包含在窗口中的物理行數。對數據類型沒有限制。
計算每個記錄與其之前的2個記錄的平均工資。
set numformat 9999
select ename,sal,
avg(sal) over(order by deptno asc rows 2 preceding) avgasc,
count(*) over(order by deptno asc rows 2 preceding) cntasc,
avg(sal) over(order by deptno desc rows 2 preceding) avgdes,
count(*) over(order by deptno desc rows 2 preceding) cntdes
from emp order by deptno;
ENAME SAL AVGASC CNTASC AVGDES CNTDES
-------------------- ----------- ---------- ---------- ---------- ----------
andy1 2000 2000 1 3666.66666 3
andy2 3000 2500 2 5666.66666 3
andy3 2000 2333.33333 3 2333.33333 3
leno1 4000 3000 3 5333.33333 3
leno2 8000 4666.66666 3 6333.33333 3
leno3 6000 6000 3 6000 3
jack1 5000 6333.33333 3 5500 2
jack2 6000 5666.66666 3 6000 1
jack3 7000 6000 3 6000 3
8 確定每組中的第一行或最后一行
使用first_vale和last_value函數可從一組中選擇每一行和最后一行
統計工資每個部門最低或最高的員工信息。
select deptno,ename,sal,first_value(ename) over(partition by deptno order by sal asc) as min_sal_has
from emp
order by deptno,ename;
select deptno,ename,sal,first_value(ename) over(partition by deptno order by sal desc) as min_sal_has
from emp
order by deptno,ename;
9 統計各班成績第一名的同學信息 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
--
得到結果：
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()只返回一個結果
2.rank()和dense_rank()的區別是：
--rank()是跳躍排序，有兩個第二名時接下來就是第四名
--dense_rank()l是連續排序，有兩個第二名時仍然跟著第三名
二：開窗函數
開窗函數指定了分析函數工作的數據窗口大小，這個數據窗口大小可能會隨著行的變化而變化，舉例如下：
1：
over（order by salary） 按照salary排序進行累計，order by是個默認的開窗函數
over（partition by deptno）按照部門分區
2：
over（order by salary range between 5 preceding and 5 following）
每行對應的數據窗口是之前行幅度值不超過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）
得出的結果是
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一個數，所以sum=9 ；
3：其它：
over（order by salary rows between 2 preceding and 4 following）
每行對應的數據窗口是之前2行，之后4行
4：下面三條語句等效：
over（order by salary rows between unbounded preceding and unbounded following）
每行對應的數據窗口是從第一行到最后一行，等效：
over（order by salary range between unbounded preceding and unbounded following）
等效
over(partition by null)