orace评级函数

cume_dist()：

功能描述：计算一行在组中的相对位置，返回大于0 小于等于1的数，例如在一个4行组中，分布是1/4 ,2/4 ,3/4  ，4/4

如果有重复的就乘以重复的个数，如第一行和第二行重复 分布为 (1/4)*2,(1/4)*2, 3/4 ,4/4

下面是例子：

表 all_sales

select 
*
 from all_sales t
where t.year=2003
and t.month =1
and t.amount is not null
and t.emp_id=21

=========================================
1	2003	1	1	21	10034.84
2	2003	1	2	21	1034.84
3	2003	1	3	21	1034.84
4	2003	1	4	21	3034.84

 

运行sql:
select 
t.prd_type_id
,t.amount
,CUME_DIST() over (order by t.amount asc) as cume_dist
 from all_sales t
where t.year=2003
and t.month =1
and t.amount is not null
and t.emp_id=21
================================
1	2	1034.84	0.5
2	3	1034.84	0.5
3	4	3034.84	0.75
4	1	10034.84	1

 

PERCENT_RANK()是某个值相对于一组值的百分比排名，大于等于0 小于等于1

select 
t.prd_type_id
,t.amount
,CUME_DIST() over (order by t.amount asc) as cume_dist
,percent_rank() over(order by t.amount desc )as percent_rank 
 from all_sales t
where t.year=2003
and t.month =1
and t.amount is not null
and t.emp_id=21
=================================
1	1	10034.84	1	0
2	4	3034.84	0.75	0.333333333333333
3	2	1034.84	0.5	0.666666666666667
4	3	1034.84	0.5	0.666666666666667

如果重复的话则记录的是小值，但是 cume_dist()记录的是大值。

 

NTILTE()函数记录N分片的值。

select 
t.prd_type_id
,t.amount
,CUME_DIST() over (order by t.amount asc) as cume_dist
,percent_rank() over(order by t.amount asc )as percent_rank 
,NTILE(5) OVER(order by t.amount desc )as ntile
 from all_sales t
where t.year=2003
and t.month =1
and t.amount is not null
and t.emp_id=21
=========================================
1	1	10034.84	1	1	1
2	4	3034.84	0.75	0.666666666666667	2
3	2	1034.84	0.5	0	3
4	3	1034.84	0.5	0	4

 

select 
t.prd_type_id
,t.amount
,CUME_DIST() over (order by t.amount asc) as cume_dist
,percent_rank() over(order by t.amount asc )as percent_rank 
,NTILE(3) OVER(order by t.amount desc )as ntile
 from all_sales t
where t.year=2003
and t.month =1
and t.amount is not null
and t.emp_id=21
==============================
1	1	10034.84	1	1	1
2	4	3034.84	0.75	0.666667	1
3	2	1034.84	0.5	0	2
4	3	1034.84	0.5	0	3

 感觉是排名函数 并且固定了排名的总个数。

 

row_number():

从1开始为每个分组返回一个数字

select 
t.prd_type_id
,t.amount
,CUME_DIST() over (order by t.amount asc) as cume_dist
,percent_rank() over(order by t.amount asc )as percent_rank 
,NTILE(2) OVER(order by t.amount desc )as ntile
,row_number() over(order by t.amount desc) as row_number
 from all_sales t
where t.year=2003
and t.month =1
and t.amount is not null
and t.emp_id=21
=============================
1	1	10034.84	1	1	1	1
2	4	3034.84	0.75	0.666667	1	2
3	2	1034.84	0.5	0	2	3
4	3	1034.84	0.5	0	2	4

 

 

Percentile_disc(x)和Percentile_cont(x)的作用与cume_dist()和percent_rank()相反:

select 
PERCENTILE_DISC(0.25) WITHIN GROUP(ORDER BY t.amount desc)
as percentile_disc
,
PERCENTILE_CONT(0.666666666666667) WITHIN GROUP(ORDER BY t.amount desc)
as percentile_cont
FROM all_sales t
where year =2003
and t.month =1
and t.amount is not null
and t.emp_id=21
==================================
1	10034.84	1034.84

 与下面的sql正好相反：

 

select 
t.prd_type_id
,t.amount
,CUME_DIST() over (order by t.amount desc) as cume_dist
,percent_rank() over(order by t.amount desc )as percent_rank 
 from all_sales t
where t.year=2003
and t.month =1
and t.amount is not null
and t.emp_id=21
======================
1	1	10034.84	0.25	0	XXXX		U
2	4	3034.84	0.5	0.333333333333333	XXXX		U
3	3	1034.84	1	0.666666666666667	XXXX		U
4	2	1034.84	1	0.666666666666667	XXXX		U