Oracle统计分析函数集之一(转载)

最新推荐文章于 2021-04-10 15:26:24 发布

congjulv6650

最新推荐文章于 2021-04-10 15:26:24 发布

阅读量58

点赞数

原文：http://www.cnblogs.com/lizw/archive/2007/04/26/729004.html

分析函数over 及开窗函数

一：分析函数over
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是
对于每个组返回多行，而聚合函数对于每个组只返回一行。
下面通过几个例子来说明其应用。
1：统计某商店的营业额。
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 .

2:统计各班成绩第一名的同学信息
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是连续排序，有两个第二名时仍然跟着第三名

3.分类统计 (并显示信息)
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
得到结果：
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列值

＝＝＝＝＝
二：开窗函数
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
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 1 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)

利用ORACLE的MINUS函数和OVER函数，直接通过视图实现两个记录集的比较

1 前言
-------------------------------------------------------------
在程序设计过程中，往往遇到比较两个记录集的差异。如，判断原来传入的订单资料与后来传入的订单资料之间的差异，并且将差异的数据显示给用户。
实现的方式有多种，如编程存储过程返回游标，在存储过程中对两批数据进行比较...等等，当然返回差异数据的方式多种多样，既可以是游标，又可以临时表或其它方式。
本文主要论述利用ORACLE的MINUS函数和OVER函数，直接通过视图实现两个记录集的比较。

-------------------------------------------------------------
2 实现步骤
-------------------------------------------------------------
2.1 利用MINUS函数，判断原始表与比较表的增量差异<设，两个记录集分别以表的方式存在，为表A和表B。其中，A表为原始表，B表为后来产生的比较表，即要与A表进行比较的数据表>
增量差异指，A中存在的记录，哪些在B表中没有的，也就是说，A表的记录被修改或删除

2.2 利用MINUS函数，判断比较表与原始表的增量差异
即B表中存在的记录，哪些在A表中没有，也就是说，B表新增的或A表修改的记录

2.3 连接A-B的增量差异表和B-A的增量差异表，利用OVER函数判断数据重复的次数
如果数据重复次数为2，则该记录的标识为“修改”；
如果数据重复次数为1，且出现在A-B的增量差异表中，则该记录的标识为“删除”；
如果数据重复次数为1，且出现在B-A的增量差异表中，则该记录的标识为“新增”

-------------------------------------------------------------
3 实例演练
-------------------------------------------------------------
--3.1 创建数据表和实例环境<设原始记录集为数据表A，比较记录集为数据表B，当然实际应用过程中，参与比较的通常是视图，不会是数据表>
--测试环境配置
Drop Table a;
Drop Table b;
Create Table a(a1 Numeric(28),a2 Varchar2(10));
Create Table b(b1 nUMERIC(28),b2 VarChar2(10));
Insert Into a Values (1,'a');
Insert Into a Values (2,'ba');
Insert Into a Values (3,'ca');
Insert Into a Values (4,'da');
Insert Into b Values (1,'a');
Insert Into b Values (2,'bba');
Insert Into b Values (3,'ca');
Insert Into b Values (5,'dda');
Insert Into b Values (6,'Eda');
Commit;
Select * from a;
Select * From b;

--3.2 创建比较视图
Create Or replace View VW_Test_Minus as
--标识重复出现的次数(次数=1->删除或新增，次数=2->修改)
SELECT A1
,a2
,t --A表/B表标识
,ROW_NUMBER() OVER (PARTITION BY A1 ORDER BY A1) RN --记录重复次数
FROM
(
Select a1,a2,'A表' T --查看A表存在，B表没有的记录(修改或删除)
from
(
(Select * from a )
Minus
(Select * From b)
) a2b
Union --联合A表与B表不相同的记录集
Select b1,b2,'B表' T --查看B表存在，A表没有的记录(修改或新增)
from
(
(Select * from b )
Minus
(Select * From a)
) b2a
) F;
/

--3.3 比较结果集
Select a1
,a2
,T
,Rn
,Decode(Rn --标识记录变化
,2,'修改'
,Decode(T
,'A表','删除'
,'新增')) Mark
From VW_Test_Minus
Where Rn=(Select Count(*) From VW_Test_Minus V Where V.a1=VW_Test_Minus.a1)
;

-------------------------------------------------------------
4 后记
-------------------------------------------------------------
许多DBA都特别痛恨那些希望通过一句SELECT语句来实现复杂用户需求的编码人员，使用MINUS和OVER函数来实现数据比较，在执行效率上，可能会存在问题。
本文的目的，并不在于讨论程序运行的效率，而在于抛砖引玉，引起大家对OVER函数的重视和对MINUS函数的认知。