Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是
对于每个组返回多行,而聚合函数对于每个组只返回一行。
1:统计某商店的营业额。
date sale
1 20
2 15
3 14
4 18
5 30
规则:按天统计:每天都统计前面几天的总额
c:/>select id,sale ,sum(sale) over (partition by id,sale order by sale) from t1;
ID SALE SUM(SALE)OVER(PARTITIONBYID,SALEORDERBYSALE)
---------- ---------- --------------------------------------------
1 20 20
2 15 15
3 14 14
4 18 18
5 30 30
c:/>select id,sale ,sum(sale) over (order by sale) from t1;
ID SALE SUM(SALE)OVER(ORDERBYSALE)
---------- ---------- --------------------------
3 14 14
2 15 29
4 18 47
1 20 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
c:/>select name,class ,score,max(score) over(partition by class order by score d
esc) from t2;
NAME CLASS SCORE MAX(SCORE)OVER(PARTITIONBYCLASSORDERBYSCOREDESC)
-------------------- ---------- ---------- ---------------
dss 1 95 95
fda 1 80 95
ffd 1 78 95
gds 2 92 92
cfe 2 74 92
gf 3 99 99
ddd 3 99 99
3dd 3 78 99
asdf 3 55 99
adf 3 45 99
已选择10行。
select * from
(
select name,class,s,rank()over(partition by class order by s desc) mm from t2
)
where mm=1
NAME CLASS SCORE MM
-------------------- ---------- ---------- ----------
dss 1 95 1
gds 2 92 1
ddd 3 99 1
gf 3 99 1
注意:
1.在求第一名成绩的时候,不能用row_number(),因为如果同班有两个并列第一,row_number()只返回一个结果
2.rank()和dense_rank()的区别是:
--rank()是跳跃排序,有两个第二名时接下来就是第四名
--dense_rank()l是连续排序,有两个第二名时仍然跟着第三名
Rank() over()的用法
创建一个test表,并插入6条数据。
CREATE TABLE test
(
a INT,
b INT,
c CHAR
)
INSERT INTO test VALUES(1,3,'E');
INSERT INTO test VALUES(2,4,'A');
INSERT INTO test VALUES(3,2,'D');
INSERT INTO test VALUES(3,5,'B');
INSERT INTO test VALUES(4,2,'C');
INSERT INTO test VALUES(2,4,'B');
SELECT * from test
a b c
----------- ----------- ----
1 3 E
2 4 A
3 2 D
3 5 B
4 2 C
2 4 B
(6 行受影响)
1、整个结果集是一个分组,以a进行排名
SELECT a,b,c,rank () OVER (ORDER BY a) rank FROM test
a b c rank
----------- ----------- ---- --------------------
1 3 E 1
2 4 A 2
2 4 B 2
3 2 D 4
3 5 B 4
4 2 C 6
(6 行受影响)
2、整个结果集是一个分组,以b进行排名
SELECT a,b,c,rank () OVER (ORDER BY b) rank FROM test
a b c rank
----------- ----------- ---- --------------------
3 2 D 1
4 2 C 1
1 3 E 3
2 4 A 4
2 4 B 4
3 5 B 6
(6 行受影响)
3、以a,b进行分组,在每个组内以b进行排名。分了5个组,第2行跟第3行是一个组,其他的每行是一个组。
在第2行与第3行的组内以b排名,并列为1
SELECT a,b,c,rank () OVER (PARTITION BY a,b ORDER BY b) rank FROM test
a b c rank
----------- ----------- ---- --------------------
1 3 E 1
2 4 A 1
2 4 B 1
3 2 D 1
3 5 B 1
4 2 C 1
(6 行受影响)
4、以a,b进行分组,在每个组内以c进行排名。分了5个组,第2行跟第3行是一个组,其他的每行是一个组。
在第2行与第3行的组内以c排名,由于c列一个是A,一个是B,所以Rank分别为1、2。
SELECT a,b,c,rank () OVER (PARTITION BY a,b ORDER BY c) rank FROM test
a b c rank
----------- ----------- ---- --------------------
1 3 E 1
2 4 A 1
2 4 B 2
3 2 D 1
3 5 B 1
4 2 C 1
(6 行受影响)
总结:1、partition by用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组。
first_value,last_value用法
原始数据:
SQL>select * from a;
NO NAME SCOTT
-- ---------- ----------
1 LIFEN 86
2 HONGGE 76
3 AIKAI 90
4 LIFEN 73
5 KUJUHD 69
6 LIFEN 93
7 KOU 80
8 HONGGE 83
......................
select T.*, first_value(scoot) over (partition by name order by scott) F,last_value(scoot) over (partition by name order by scott) L from a T order by no ;
NO NAME SCOTT F L
-- ---------- ---------- ----- -----
1 LIFEN 86 73 86
2 HONGGE 76 76 76
3 AIKAI 90 90 90
4 LIFEN 73 73 73
5 KUJUHD 69 69 69
6 LIFEN 93 73 93
7 KOU 80 80 80
8 HONGGE 83 76 83
可以看出 能得到姓名相同中的最小分数 却不能得到最大分数 也就是last_value這函數里存在order和partition會得不到預期的結果﹐
可以將order by 放在最后面
找出最高分数 (如果有重复姓名)和最低分数的NO
SQL>SELECT T.*,
2 first_value(scott) OVER (PARTITION BY NAME) lowest_scott,
3 last_value(scott) OVER (PARTITION BY NAME) highest_scott
4 FROM a T
5 ORDER BY scott;
NO NAME SCOTT F L
---------- -------------------- ---------- ---------- ----------
5 kujuhd 69 69 69
4 lifen 73 93 73
2 hongge 76 76 83
7 kou 80 80 80
8 hongge 83 76 83
1 lifen 86 93 73
3 aikai 90 90 90
6 lifen 93 93 73
已选择8行。
第二种方法 就是
select T.*, first_value(scoot) over (partition by name order by scott) F,
last_value(scoot) over (partition by name order by scott rows between unbounded preceding and unbounded following) L from a T order by no ;
ps:分析函数分析的时候是在一个分析窗口中进行的,默认的窗口是当前组的第一行到当前行,如果让窗口是整个组,需要指定窗口子句
删除姓名重复的 分数最高的 保留下来
SQL>delete from a where no not in (select first_value(no) over (partition by name order by scott desc ) no from a );
select deptno,ename,hiredate,count(1) over (partition by deptno order by hiredate nulls
first range 100 preceding) cnt_range,count(1) over(partition by deptno order by hiredate
nulls first rows 2 preceding) cnt_rows from emp order by deptno,hiredate