analytic_function([ arguments ]) over (analytic_clause)
Analytic_function是分析函数的名称；arguments是分析函数的参数；over用来标识函数是一个分析函数；既可作为聚集函数又可作为分析函数，over关键字是必须的，是分析函数就必须使用。Over后面的()中是analytic_clause，即使analytic_clause的三个部分全不使用，()也必不可省，否则Oracle因不能识别函数为分析函数而报错。
analytic_clause用来确定分析函数的操作规则。包括三个子句。语法如下：
[query_partition_clause] [order_by_clause [windowing_clause] ] 这里：
query_partition_clause是查询分组子句；
order_by_clause是分组排序子句；
windowing_clause是窗口范围子句。
分析函数在查询结果集确定之后才开始进行计算，Analytic_clause就是用来定义函数怎样对查询结果集进行分组计算的。根据Oracle对查询和分析函数的处理方法可知，在select和order by子句中都可以使用分析函数。
[@more@]1.【lpad和rpad】
用法说明：lpad 在列的左边粘贴字符; rpad 在列的右边粘贴字符;
--不够的字符用*来填满
Select lpad('dz',10,'*') From Dual;
输出结果：********dz
Select Rpad('dz',10,'*') From Dual;
输出结果：dz********
Select Lpad(Rpad('dz', 7, '*'), 12, '*') From Dual;
输出结果： *****dz*****
2.【OVER (PARTITION BY ...)】
◆2.1 用法说明：对字段b分区、然后将字段b相同的值对应的字段C的数据进行上下累加求合
Create Table test_over(a Varchar2(5),b Varchar2(5),c Varchar2(5))
Select * From test_over
A B C
- - -
1 1 1
1 2 2
1 3 3
2 2 5
3 4 6
4 1 8
Select a, b, c, Sum(c) Over(Partition By b) c_Sum From Test_Over
输出结果：
A B C C_SUM
-- -- -- ------
1 1 1 9
4 1 8 9
1 2 2 7
2 2 5 7
1 3 3 3
3 4 6 6
如果用sum，group by 则无法得到B列的值;
如果不需要对某个栏位的值分割,那就要用 null, 就是将C的栏位值sum一遍,然后放在每行后面
Select a, b, c, Sum(c) Over(Partition By Null) c_Sum From Test_Over
A B C C_SUM
-- -- -- ------
1 1 1 25
1 2 2 25
1 3 3 25
2 2 5 25
3 4 6 25
4 1 8 25
--------------------------------------------------------------------------
◆2.2 求个人工资占部门工资的百分比
SQL> select * from salary;
NAME DEPT SAL
------ ------ ----
a 10 2000
b 10 3000
c 10 5000
d 20 4000
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
--------------------------------------------------------------------------
◆2.3 统计某商店的营业额
date sale
--- ---
1 20
2 15
3 14
4 18
5 30
规则：按天统计：每天都统计前面几天的总额
得到的结果：
DATE SALE SUM
---- ---- ------
1 20 20 --1天
2 15 35 --1天＋2天
3 14 49 --1天＋2天＋3天
4 18 67 ...
5 30 97 ...
Select t.In_Date, Sum(t.Num) Over(Order By In_Date Range Unbounded Preceding) "累计值"
From Test t
--------------------------------------------------------------------------
◆2.4 开窗函数:
指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
over(order by salary)按照salary排序进行累计，order by是个默认的开窗函数
over(partition by deptno)按照部门分区
over(order by salary rows between 2 preceding and 4 following)
每行对应的数据窗口是之前2行，之后4行
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.【Lag和Lead函数】
Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。
lag(column_x,number1,XX)用来返回当前数据行的字段column_x的前第number1行数据,
当须返回的字段为空或不存在时，返回 XX 。
lead(column_x,number1,XX)用来返回当前数据行的字段column_x的后第number1行数据,
当须返回的字段为空或不存在时，返回 XX 。
--LAG(EXPRESSION,,)
with a as (select 1 id,'a' name from dual
union
select 2 id,'b' name from dual
union
select 3 id,'c' name from dual
union
select 4 id,'d' name from dual
union
select 5 id,'e' name from dual
) Select id,name,(lag(id,1,'')over(order by name)) lag from a
显示结果：
id name lag
-- ---- ---
1 a
2 b 1
3 c 2
4 d 3
5 e 4
--LEAD(EXPRESION,,)
with a as (select 1 id,'a' name from dual
union
select 2 id,'b' name from dual
union
select 3 id,'c' name from dual
union
select 4 id,'d' name from dual
union
select 5 id,'e' name from dual)
Select id,name,lead(id,1,'')over(order by name) from a
显示结果：
id name lead
-- ---- ---
1 a 2
2 b 3
3 c 4
4 d 5
5 e
利用lag和lead函数,我们可以在同一行中显示前n行的数据,也可以显示后n行的数据,适于分析比对
取出每个月的上个月和下个月的话费总额
Select Area_Code,
Bill_Month,
Local_Fare Cur_Local_Fare,
Lag(Local_Fare, 2, 0) Over(Partition By Area_Code Order By Bill_Month) Pre_Local_Fare,
Lag(Local_Fare, 1, 0) Over(Partition By Area_Code Order By Bill_Month) Last_Local_Fare,
Lead(Local_Fare, 1, 0) Over(Partition By Area_Code Order By Bill_Month) Next_Local_Fare,
Lead(Local_Fare, 2, 0) Over(Partition By Area_Code Order By Bill_Month) Post_Local_Fare
From (Select Area_Code, Bill_Month, Sum(Local_Fare) Local_Fare
From t
Group By Area_Code, Bill_Month)
AREA_CODE BILL_MONTH CUR_LOCAL_FARE PRE_LOCAL_FARE LAST_LOCAL_FARE NEXT_LOCAL_FARE POST_LOCAL_FARE
--------- ---------- -------------- -------------- --------------- --------------- ---------------
5761 200405 13060.433 0 0 13318.93 13710.265
5761 200406 13318.93 0 13060.433 13710.265 14135.781
5761 200407 13710.265 13060.433 13318.93 14135.781 0
5761 200408 14135.781 13318.93 13710.265 0 0
5762 200405 12643.791 0 0 12795.06 13224.297
5762 200406 12795.06 0 12643.791 13224.297 13376.468
5762 200407 13224.297 12643.791 12795.06 13376.468 0
5762 200408 13376.468 12795.06 13224.297 0 0
5763 200405 13060.433 0 0 13318.93 13710.265
5763 200406 13318.93 0 13060.433 13710.265 14135.781
5763 200407 13710.265 13060.433 13318.93 14135.781 0
5763 200408 14135.781 13318.93 13710.265 0 0
5764 200405 12487.791 0 0 13295.187 13444.093
5764 200406 13295.187 0 12487.791 13444.093 13929.694
5764 200407 13444.093 12487.791 13295.187 13929.694 0
5764 200408 13929.694 13295.187 13444.093 0 0
5765 200405 25057.736 0 0 26058.46 26301.881
5765 200406 26058.46 0 25057.736 26301.881 27130.638
5765 200407 26301.881 25057.736 26058.46 27130.638 0
5765 200408 27130.638 26058.46 26301.881 0 0
20 rows selected.
4.【row_number()、rank()、dense_rank()的使用】
SQL> break on deptno skip 1 -- 为效果更明显，把不同部门的数据隔段显示。
select deptno,ename,sal from emp order by deptno, sal desc;
原表信息：
DEPTNO ENAME SAL
---------- ---------- ----------
10 KING 5000
CLARK 2450
MILLER 1300
20 SCOTT 3000
FORD 3000
JONES 2975
ADAMS 1100
SMITH 800
30 BLAKE 2850
ALLEN 1600
TURNER 1500
WARD 1250
MARTIN 1250
JAMES 950
已选择14行。
--------------------------------------------------------------------------
◆4.1 使用row_number()查出各部门薪水最高的三个员工姓名、薪水，多于三个的只取三个
Select * From (Select Deptno,
Row_Number() Over(Partition By Deptno Order By Sal Desc)Rw,Ename, Sal From Emp)
Where Rw <= 3;
效果如下：
DEPTNO RW ENAME SAL
---------- ---------- ---------- ----------
10 1 KING 5000
2 CLARK 2450
3 MILLER 1300
20 1 SCOTT 3000
2 FORD 3000
3 JONES 2975
30 1 BLAKE 2850
2 ALLEN 1600
3 TURNER 1500
体会：row_number()为每一条分组纪录返回一个数字,返回的主要是"行"的信息,并没有按照sal排名，
如 20 1 SCOTT 3000
2 FORD 3000
scott与ford薪水一样多，ford前面的2仅仅是行数、记录条数的感念。
--------------------------------------------------------------------------
下面是实际工作中的例表，按局向分区，取各区局前5名的欠费额排名情况
Select *
From (Select district_id,charge/100,
Row_Number() Over(Partition By district_id Order By Charge Desc) Rw
From Acct_Item_Owe_Detail
Where Acct_Month = 200806
And Lan_Id = 1005)
Where Rw <= 5
----------------------------------------------------------------
◆4.2 使用rank()查出各部门薪水前三名的员工姓名、薪水。
Select *
From (Select Deptno,
Rank() Over(Partition By Deptno Order By Sal Desc) Rk,
Ename,
Sal
From Emp)
Where Rk <= 3
效果如下：
DEPTNO RK ENAME SAL
---------- ---------- ---------- ----------
10 1 KING 5000
2 CLARK 2450
3 MILLER 1300
20 1 SCOTT 3000
1 FORD 3000
3 JONES 2975
30 1 BLAKE 2850
2 ALLEN 1600
3 TURNER 1500
----------------------------------------------------------------
◆4.3 使用rank()统计各班成绩第一名的同学信息
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()是连续排序，有两个第二名时仍然跟着第三名
----------------------------------------------------------------
◆4.4 使用dense_rank()查出各部门薪水前三名的员工姓名、薪水。
Select *
From (Select Deptno,
Dense_Rank() Over(Partition By Deptno Order By Sal Desc) Drk,
Ename,
Sal
From Emp)
Where Drk <= 3
效果如下：
DEPTNO DRK ENAME SAL
---------- ---------- ---------- ----------
10 1 KING 5000
2 CLARK 2450
3 MILLER 1300
20 1 SCOTT 3000
1 FORD 3000
2 JONES 2975
3 ADAMS 1100
30 1 BLAKE 2850
2 ALLEN 1600
3 TURNER 1500
5.【sum(...) over ... 的使用】
SQL> break on deptno skip 1 -- 为效果更明显，把不同部门的数据隔段显示。
原表信息：Select Deptno, Ename, Sal From Emp Order By Deptno;
DEPTNO ENAME SAL
---------- ---------- ----------
10 CLARK 2450
KING 5000
MILLER 1300
20 SMITH 800
ADAMS 1100
FORD 3000
SCOTT 3000
JONES 2975
30 ALLEN 1600
BLAKE 2850
MARTIN 1250
JAMES 950
TURNER 1500
WARD 1250
--------------------------------------------------------------------------
◆5.1 使用 sum(sal) over (order by ename)... 查询员工的薪水“连续”求和,
注意 over (order by ename)如果没有order by子句，求和就不是“连续”的，放在一起，体会一下不同之处：
SQL> break on '' -- 取消数据分段显示
SQL> Select Deptno, Ename, Sal,
Sum(Sal) Over(Order By Ename) 连续求和,
Sum(Sal) Over() 总和, -- 此处sum(sal) over () 等同于sum(sal)
100 * Round(Sal / Sum(Sal) Over(), 4) "份额(%)"
From Emp
效果如下：
DEPTNO ENAME SAL 连续求和 总和 份额(%)
---------- ---------- ---------- ---------- ---------- ----------
20 ADAMS 1100 1100 29025 3.79
30 ALLEN 1600 2700 29025 5.51
30 BLAKE 2850 5550 29025 9.82
10 CLARK 2450 8000 29025 8.44
20 FORD 3000 11000 29025 10.34
30 JAMES 950 11950 29025 3.27
20 JONES 2975 14925 29025 10.25
10 KING 5000 19925 29025 17.23
30 MARTIN 1250 21175 29025 4.31
10 MILLER 1300 22475 29025 4.48
20 SCOTT 3000 25475 29025 10.34
20 SMITH 800 26275 29025 2.76
30 TURNER 1500 27775 29025 5.17
30 WARD 1250 29025 29025 4.31
--------------------------------------------------------------------------
◆5.2 使用子分区查出各部门薪水连续的总和。
SQL> break on deptno skip 1 -- 为效果更明显，把不同部门的数据隔段显示。
SQL> 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 CLARK 2450 2450 8750 28 2450 29025 8.44
KING 5000 7450 8750 57.14 7450 29025 17.23
MILLER 1300 8750 8750 14.86 8750 29025 4.48
20 ADAMS 1100 1100 10875 10.11 9850 29025 3.79
FORD 3000 4100 10875 27.59 12850 29025 10.34
JONES 2975 7075 10875 27.36 15825 29025 10.25
SCOTT 3000 10075 10875 27.59 18825 29025 10.34
SMITH 800 10875 10875 7.36 19625 29025 2.76
30 ALLEN 1600 1600 9400 17.02 21225 29025 5.51
BLAKE 2850 4450 9400 30.32 24075 29025 9.82
JAMES 950 5400 9400 10.11 25025 29025 3.27
MARTIN 1250 6650 9400 13.3 26275 29025 4.31
TURNER 1500 8150 9400 15.96 27775 29025 5.17
WARD 1250 9400 9400 13.3 29025 29025 4.31
体会：在"... from emp;"后面不要加order by子句，使用的分析函数的(partition by deptno order by sal)里已经有排序的语句了，如果再在句尾添加排序子句，一致倒罢了，不一致，结果就令人费劲了。
6.【GROUPING_ID 的使用】
当指定GROUP BY 时，GROUPING_ID 只能在 SELECT
group by ROLLUP(A, B, C)，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，
然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作
GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。
Select Country, Province, Sex, Sum(Num)
From t
Group By Rollup(Country, Province, Sex)
Having Grouping_Id(Country, Province, Sex) < 2
COUNTRY PROVINCE SEX SUM(NUM)
------------ --------------- ---- ----------
中国 中国广东 男 1200
中国 中国广东 女 1000
中国 中国广东 2200
中国 中国湖南 男 600
中国 中国湖南 女 1800
中国 中国湖南 2400
中国 中国江苏 男 1600
中国 中国江苏 女 1800
中国 中国江苏 3400
这里用grouping_id是过滤掉不满足你需求的纪录：
grouping_id(country,province,sex) 应该有8种组合，依次是
country, province, sex
0 0 0
0 0 1
0 1 0
0 1 1
1 0 0
1 0 1
1 1 0
1 1 1
上面8种组合依次代表0-7,
如果纪录中country,province,sex中sex为空时，则对应编码为1，country,province不为空则对应编码为0,
那么grouping_id(country,province,sex)的值为2进制的001,也就是1，它代表的也是国家的一个省的合计即
country province sex SUM(NUM)
中国 中国广东 （null） 2200
如果是下面这条纪录的话,grouping_id(country,province,sex)就为011,即3，是国家的合计
country province sex SUM(NUM)
中国 (null) (null) 8000
这里不会出现这8种组合，你可以试试用cube(country,province,sex)这样8种组合应该会都出现，事实上，这里计算grouping_id并不是按空值来判断的，因为计算省合计的时候，性别有男和女2种，所以统计的时候性别列就为空，相应的grouping_id编码也就为1，就算原始纪录中存在相应列为空也不会影响grouping_id的值
--再看一个grouping实例效果select case when grouping(a.serv_id) = 0 and grouping(a.acc_nbr) = 1 and grouping(a.acct_month) = 1 and grouping(a.district_id) = 1
then '合计' else to_char(a.serv_id) end serv_id,
decode(grouping(a.acc_nbr), 1, null, a.acc_nbr) acc_nbr,
decode(grouping(a.acct_month), 1, null, a.acct_month) acct_month,
decode(grouping(a.district_id), 1,null, district_id) district_id,
(Select Name From Org Where Org_Id = a.District_Id) Org,
sum(a.charge)/100 charge
from Acct_Item_Owe_Detail a
where a.lan_id = 1001
group by rollup(a.serv_id,a.acc_nbr,a.acct_month,a.district_id)
having grouping_id(a.serv_id,a.acc_nbr,a.acct_month,a.district_id) in (0,7)
--------------------------------------------------------------
serv_id acc_nbr acct_month district_id org charge
1 51000160000 6553125 200704 10107 655局 28.72
2 51000160000 6553125 200705 10107 655局 26.08
3 合计 54.8
4 51000410000 6332148 200802 10104 633局 2.54
5 合计 2.54
6 51000480000 8555420 200708 10112 市局 5
7 51000480000 8555420 200709 10112 市局 5
8 合计 10
9 51300010000 8261085 200511 40102 当阳 -0.16
10 合计 -0.16
... ...
我们用0表示 Not Null 用1表示 Null， 下面是位向量值算法
serv_id acc_nbr acct_month district_id 位向量 grouping()值
Not Null || Not Null || Not Null || Not Null || 0000 || 0
Not Null || Not Null || Not Null || Null || 0001 || 1
Not Null || Not Null || Null || Not Null || 0010 || 2
Not Null || Not Null || Null || Null || 0011 || 3
Not Null || Null || Not Null || Not Null || 0100 || 4
Not Null || Null || Not Null || Null || 0101 || 5
Not Null || Null || Null || Not Null || 0110 || 6
Null || Not Null || Not Null || Not Null || 1000 || 7
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/14887683/viewspace-1010814/ ，如需转载，请注明出处，否则将追究法律责任。
