第1页 / 共38页
第2页 / 共38页
第3页 / 共38页
第4页 / 共38页
第5页 / 共38页
第6页 / 共38页
第7页 / 共38页
第8页 / 共38页
oracle分组排序统计高级用法.docx
Oracle 统计函数
--Rank 和 Dense_Rank
分类:Oracle 数据库
聚合函数 RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。
在 9i 版本之前，只有分析功能（analytic ），即从一个查询结果中计算每一行的排序值，是基于 order_by_clause
子句中的 value_exprs 指定字段的。
其语法为：
RANK ( ) OVER ( [query_partition_clause] order_by_clause )
在 9i 版本新增加了合计功能（aggregate），即对给定的参数值在设定的排序查询中计算出其排序值。这些参数
必须是常数或常值表达式，且必须和 ORDER BY 子句中的字段个数、位置、类型完全一致。
其语法为：
RANK ( expr [, expr]... ) WITHIN GROUP
( ORDER BY
expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...
)
例子 1：
有表 Table 内容如下
COL1 COL2
1 1
2 1
3 2
3 1
4 1
4 2
5 2
5 2
6 2
分析功能：列出 Col2 分组后根据 Col1 排序,并生成数字列。比较实用于在成绩表中查出各科前几名的信息。
SELECT a.*,RANK() OVER(PARTITION BY col2 ORDER BY col1) "Rank" FROM table a;
结果如下：
COL1 COL2 Rank
1
2
3
4
1
2
3
3
5
1 1
2 1
3 1
4 1
3 2
4 2
5 2
5 2
6 2
例子 2：
TABLE：A （科目，分数）
数学，80
语文，70
数学，90
数学，60
数学，100
语文，88
语文，65
语文，77
现在我想要的结果是：（即想要每门科目的前 3 名的分数）
数学，100
数学，90
数学，80
语文，88
语文，77
语文，70
那么语句就这么写：
select * from (select rank() over(partition by 科目 order by 分数 desc) rk,a.* from a) t
where t.rk<=3;
例子 3：
合计功能：计算出数值(4,1)在 Orade By Col1,Col2 排序下的排序值，也就是 col1=4,col2=1 在排序以后的位置
SELECT RANK(4,3) WITHIN GROUP (ORDER BY col1,col2) "Rank" FROM table;
结果如下：
Rank
4
dense_rank 与 rank()用法相当，但是有一个区别：dence_rank 在并列关系是，相关等级不会跳过。rank 则跳过
例如：表
A
a
a
a
b
b
b
b
例如：当 rank 时为：
select m.a,m.b,m.c,rank() over(partition by a order by b) liu from test3 m
A
a
a
a
b
b
b
b
而如果用 dense_rank 时为：
C
wang
shu
kai
du
ying
cai
99
C
kai
shu
wang
ying
du
99
cai
B
cai
jin
liu
lin
yang
yang
yao
B
liu
jin
cai
yang
lin
yao
yang
LIU
1
2
3
1
2
2
4
select m.a,m.b,m.c,dense_rank() over(partition by a order by b) liu from test3 m
A
a
a
a
b
b
b
b
]]> 0.01239
C
kai
shu
wang
ying
du
99
cai
B
cai
jin
liu
lin
yang
yang
yao
LIU
1
2
3
1
2
2
3
Oracle 从 8.1.6 开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每
个组返回多行，而聚合函数对于每个组只返回一行。
语法：
Sql 代码
(,,...)
over(
)
说明：
<1> over 是关键字，用于标识分析函数。
<2> 是指定的分析函数的名字。
<3> 为参数，分析函数可以选取 0-3 个参数。
<4> 分区子句的格式为：
partition by[,value_expr]...
关键字 partition by 子句根据由分区表达式的条件逻辑地将单个结果集分成 N 组。这里的"分区 partition"和"组
group" 都是同义词。
<5> 排序子句 order-by-clause 指定数据是如何存在分区内的。其格式为：
order[siblings]by{expr|position|c_alias}[asc|desc][nulls first|nulls last]
其中：
A.asc|desc:指定了排列顺序。
B.nulls first|nulls last:指定了包含空值的返回行应出现在有序序列中的第一个或最后一个位置。
<6>窗口子句 windowing-clause
给出一个固定的或变化的数据窗口方法，分析函数将对这些数据进行操作。在一组基于任意变化或固定的窗口
中，
可用该子句让分析函数计算出它的值。
格式：
{rows|range}
{between
{unbounded preceding|current row |{preceding|following}
}and
{unbounded preceding|current row |{preceding|following}
}|{unbounded preceding|current row |{preceding|following
}}
A.rows|range:此关键字定义了一个 window。
B.between...and...:为窗品指一个起点和终点。
C.unbounded preceding：指明窗口是从分区(partition)的第一行开始。
D.current row:指明窗口是从当前行开始。
开窗函数：
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
over（order by salary） 按照 salary 排序进行累计，order by 是个默认的开窗函数
over（partition by deptno）按照部门分区
over（order by salary range between 50 preceding and 150 following）
每行对应的数据窗口是之前行幅度值不超过 50，之后行幅度值不超过 150
over（order by salary rows between 50 preceding and 150 following）
每行对应的数据窗口是之前 50 行，之后 150 行
over（order by salary rows between unbounded preceding and unbounded following）每行对应的数据窗口是从
第一行到最后一行，等效：
over（order by salary range between unbounded preceding and unbounded following）
1、Oracle ROLLUP 和 CUBE 用法
Oracle 的 GROUP BY 语句除了最基本的语法外，还支持 ROLLUP 和 CUBE 语句。如果是 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 操作。也可以 Group by Rollup(A,(B,C)) ，Group by A
Rollup(B,C)，……
如何来区分到底是根据那个字段做的汇总呢,这时候,oracle 的 grouping 函数就粉墨登场了.如果当前的汇总记
录是利用该字段得出的,grouping 函数就会返回 1,否则返回 0。
示例：
Sql 代码
select nvl(area_code,'合计') area_code,sum(local_fare) local_fare
from t
group by rollup(nvl(area_code,'合计'));
Sql 代码
select area_code,bill_month,sum(local_fare) local_fare
from t
group by cube(area_code,bill_month)
order by area_code,bill_month nulls last;
Sql 代码
select decode(grouping(area_code),1,'all area',to_char(area_code)) area_code,
decode(grouping(bill_month),1,'all month',bill_month) bill_month,
sum(local_fare) local_fare
from t
group by cube(area_code,bill_month)
order by area_code,bill_month nulls last;
简单点说：为了生成数据统计以及横向小计统计，可以在 GROUP BY 子句中使用 ROLLUP 操作符。为了生
成数据统计、横向小计、纵向小计结果，可以使用 CUBE 操作符。
2、Rank 的用法
功能描述：根据 ORDER BY 子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。组
内的数据按 ORDER BY 子句排序，然后给每一行赋一个号，从而形成一个序列，该序列从 1 开始，往后累加。
每次 ORDER BY 表达式的值发生变化时，该序列也随之增加。有同样值的行得到同样的数字序号（认为 null 时
相等的）。然而，如果两行的确得到同样的排序，则序数将随后跳跃。若两行序数为 1，则没有序数 2，序列将
给组中的下一行分配值 3，DENSE_RANK 则没有任何跳跃。
rank()是跳跃排序，比如有两个第二名时接下来就是第四名（同样是在各个分组内）
dense_rank()是连续排序，比如有两个第二名时仍然跟着第三名。
104548.72 1
2
54225.41
2
54225.41
53156.77
4
5
52039.62
示例：
Sql 代码
select area_code,sum(local_fare) local_fare,
rank() over (order by sum(local_fare) desc) fare_rank
from t
group by area_code;
结果：
AREA_CODE LOCAL_FARE FARE_RANK
---------- -------------- ----------
5765
5761
5763
5764
5762
Sql 代码
select area_code,sum(local_fare) local_fare,
dense_rank() over (order by sum(local_fare) desc ) fare_rank
from t
group by area_code;
结果：
AREA_CODE LOCAL_FARE FARE_RANK
---------- -------------- ----------
5765
5761
5763
5764
5762
ROW_NUMBER
功能描述：返回有序组中一行的偏移量，从而可用于按特定标准排序的行号。
Sql 代码
select area_code,sum(local_fare) local_fare,
row_number() over (order by sum(local_fare) desc ) fare_rank
from t
group by area_code;
104548.72 1
2
54225.41
2
54225.41
53156.77
3 这是这里出现了第三名
4
52039.62
结果：
AREA_CODE LOCAL_FARE FARE_RANK
---------- -------------- ----------
5765
5761
5763
5764
rank()示例：
a. 取出数据库中最后入网的 n 个用户
Sql 代码
select user_id,tele_num,user_name,user_status,create_date
from (
select user_id,tele_num,user_name,user_status,create_date,
rank() over (order by create_date desc) add_rank
from user_info
)
where add_rank <= :n;
b.根据 object_name 删除数据库中的重复记录
create table t as select obj#,name from sys.obj$;
再 insert into t1 select * from t1 数次.
Sql 代码
delete from t1 where rowid in (
select row_id from (
select rowid row_id,row_number() over (partition by obj# order by rowid ) rn
) where rn <> 1
);
c. 取出各地区的话费收入在各个月份排名.
Sql 代码
select bill_month,area_code,sum(local_fare) local_fare,
rank() over (partition by bill_month order by sum(local_fare) desc) area_rank
from t
group by bill_month,area_code
结果：
BILL_MONTH AREA_CODE LOCAL_FARE AREA_RANK
--------------- --------------- -------------- ----------
200405
200405
200405
200405
200405
200406
200406
200406
200406
25057.74
13060.43
13060.43
12643.79
12487.79
26058.46
13318.93
13318.93
13295.19
5765
5761
5763
5762
5764
5765
5761
5763
5764
104548.72
54225.41
54225.41
53156.77
1
2
3
4
1
2
2
4
5
1
2
2
4
200406
200407
200407
200407
200407
200407
3、First/Last 的用法
5762
5765
5761
5763
5764
5762
12795.06
26301.88
13710.27
13710.27
13444.09
13224.30
5
1
2
2
4
5
First 功能描述：从 DENSE_RANK 返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等），
因此完整的语法需要在开始处加上一个集合函数以从中取出记录。
Last 功能描述：从 DENSE_RANK 返回的集合中取出排在最后面的一个值的行（可能多行，因为值可能相等），
因此完整的语法需要在开始处加上一个集合函数以从中取出记录。
示例：下面例子中 DENSE_RANK 按部门分区，再按佣金 commission_pct 排序，FIRST 取出佣金最低的对应的
所有行，然后前面的 MIN 函数从这个集合中取出薪水最低的值；LAST 取出佣金最高的对应的所有行，然后前面
的 MAX 函数从这个集合中取出薪水最高的值。
Sql 代码
SELECT last_name, department_id, salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Best"
FROM employees
WHERE department_id in (20,80)
ORDER BY department_id, salary;
结果：
LAST_NAME DEPARTMENT_ID SALARY Worst Best
------------------------- ------------- ---------- ---------- ----------
Fay 20 6000 6000 13000
Hartstein 20 13000 6000 13000
Kumar 80 6100 6100 14000
Banda 80 6200 6100 14000
Johnson 80 6200 6100 14000
4、FIRST_VALUE/LAST_VALUE 的用法
FIRST_VALUE、LAST_VALUE 是两个分析函数。返回结果集中排在第一位和最后一位的值。语法是：
FIRST_VALUE (expr) OVER ( analytic_clause)
示例：
计算按部门分区按薪水排序的数据窗口的第一个值对应的名字，如果薪水的第一个值有多个，则从多个对应的名
字中取缺省排序的第一个名字。
Sql 代码
SELECT department_id, last_name, salary, FIRST_VALUE(last_name)
OVER (PARTITION BY department_id ORDER BY salary ASC ) AS lowest_sal
FROM employees
WHERE department_id in(20,30);
结果：
DEPARTMENT_ID LAST_NAME SALARY LOWEST_SAL
------------- ------------------------- ---------- --------------
20 Fay 6000 Fay
20 Hartstein 13000 Fay
30 Colmenares 2500 Colmenares
30 Himuro 2600 Colmenares
30 Tobias 2800 Colmenares
30 Baida 2900 Colmenares
30 Khoo 3100 Colmenares
30 Raphaely 11000 Colmenares
5、Lag/Lead 的用法
功能描述：可以访问结果集中的其它行而不用进行自连接。它允许去处理游标，就好像游标是一个数组一样。
在给定组中可参考当前行之前的行，这样就可以从组中与当前行一起选择以前的行。Offset 是一个正整数，其默
认值为 1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行），其相反的函数是 LEAD。
示例：lag 和 lead 函数介绍取出每个月的上个月和下个月的话费总额
Sql 代码
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
6.RATIO_TO_REPORT 用法
功能描述：该函数计算 expression/(sum(expression))的值，它给出相对于总数的百分比，即当前行对
sum(expression)的贡献。
格式：RATIO_TO_REPORT (expr) OVER (query_partition_clause)
示例：计算每个员工的工资占该类员工总工资的百分比
Sql 代码
SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr
相关推荐
2023年江西萍乡中考道德与法治真题及答案.doc
2012年重庆南川中考生物真题及答案.doc
2013年江西师范大学地理学综合及文艺理论基础考研真题.doc
2020年四川甘孜小升初语文真题及答案I卷.doc
2020年注册岩土工程师专业基础考试真题及答案.doc
2023-2024学年福建省厦门市九年级上学期数学月考试题及答案.doc
2021-2022学年辽宁省沈阳市大东区九年级上学期语文期末试题及答案.doc
2022-2023学年北京东城区初三第一学期物理期末试卷及答案.doc
2018上半年江西教师资格初中地理学科知识与教学能力真题及答案.doc
2012年河北国家公务员申论考试真题及答案-省级.doc
2020-2021学年江苏省扬州市江都区邵樊片九年级上学期数学第一次质量检测试题及答案.doc
2022下半年黑龙江教师资格证中学综合素质真题及答案.doc
