1 RANK()函数

返回数据项在分组中的排名，排名相等会在名次中留下空位

 

2 DENSE_RANK()函数

返回数据项在分组中的排名，排名相等会在名次中不会留下空位

3 NTILE()函数

返回n分片后的值

4 ROW_NUMBER()

为每条记录返回一个数字

5 分析函数案例实战

针对分组后，对组内数据进行排序

(1)Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的， 同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

(2)Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。

(3)Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。

1、创建表

create table student_info(
S_ID  int,
COURSE string,
SCORE int,
CLASS_ID int
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t';

 

 

2、初始化数据

1000 math 70 1
1000 china 80 1
1000 oracle 90 1
1001 math 80 1
1001 china 75 1
1001 oracle 78 1
1002 math 81 2
1002 china 76 2
1002 oracle 77 2
1003 math 80 2
1003 china 75 2
1003 oracle 78 2

3、统计查询

排名/排序的时候，有时候，我们会想到利用伪列rownum，利用rownum确实可以解决某些场景下的问题(但是相对也比较复杂)，而且有些

场景下的问题却很难解决

 

3.1 每个班级同一个科目的成绩排名

 

select S_ID,CLASS_ID,COURSE,SCORE, 
rank() over (partition by CLASS_ID,COURSE  order by SCORE desc ) rk,
dense_rank() over (partition by CLASS_ID,COURSE  order by SCORE desc ) drk,
row_number() over (partition by CLASS_ID,COURSE  order by SCORE desc ) rn
from student_info
 
s_id    class_id         course  score   rk      drk     rn
1000    1         china   80      1       1       1
1001    1        china   75      2       2       2
1001    1        math    80      1       1       1
1000    1         math    70      2       2       2
1000    1        oracle  90      1       1       1
1001    1        oracle  78      2       2       2
1002    2        china   76      1       1       1
1003    2        china   75      2       2       2
1002    2        math    81      1       1       1
1003    2        math    80      2       2       2
1003    2        oracle  78      1       1       1
1002    2        oracle  77      2       2       2

 

3.2 统计每门课程的成绩排名

s

elect S_ID,COURSE,SCORE, 
rank() over (partition by COURSE  order by SCORE desc )  rk ,
dense_rank() over (partition by course order by score desc) drk,
row_number() over(partition by course order by score desc) rn
from student_info;
 
s_id    course  score   rk      drk     rn
1000    china   80      1       1       1
1002    china   76      2       2       2
1001    china   75      3       3       3
1003    china   75      3       3       4
1002    math    81      1       1       1
1001    math    80      2       2       2
1003    math    80      2       2       3
1000    math    70      4       3       4
1000    oracle  90      1       1       1
1001    oracle  78      2       2       2
1003    oracle  78      2       2       3
1002    oracle  77      4       3       4

 

3.3 统计每门课程的成绩排名前2名的人员

select * from (
select S_ID,COURSE,SCORE, 
rank() over (partition by COURSE  order by SCORE desc )  rk ,
dense_rank() over (partition by course order by score desc) drk,
row_number() over(partition by course order by score desc) rn
from student_info) t
where t.drk<=2
 
 
t.s_id  t.course        t.score t.rk    t.drk   t.rn
1000    china    80      1       1       1
1002    china    76      2       2       2
1002    math     81      1       1       1
1001    math     80      2       2       2
1003    math     80      2       2       3
1000    oracle    90      1       1       1
1001    oracle   78      2       2       2
1003    oracle    78      2       2       3

 

注意：如果只是简单的想到去用rownum <= 2 得到的结果显然不可能是正确的。