一、 分区函数Partition By与row_number()、rank()、dense_rank()的用法（获取分组（分区）中前几条记录）
一、数据准备
--1、创建学生成绩表 id int, --主键 Grade int, --班级 Score int --分数 CREATE TABLE Student3(id INT, grade INT,score int) CLUSTERED by(id) INTO 7 BUCKETS STORED AS ORC tblproperties('transactional'='true'); insert into Student1 values(1,1,88); insert into Student1 values(2,1,66); insert into Student1 values(3,1,75); insert into Student1 values(4,2,30); insert into Student1 values(5,2,70); insert into Student1 values(6,2,80); insert into Student1 values(7,2,60); insert into Student1 values(8,3,90); insert into Student1 values(9,3,70); insert into Student1 values(10,3,80); insert into Student1 values(11,3,80); --向普通表中入数据 CREATE TABLE Student(id INT, grade INT,score int); INSERT INTO TABLE student SELECT * FROM student1; SELECT * FROM Student;
二、分区函数partition by与row_number()的用法
--2、1不分班按学生成绩排名 SELECT *,row_number() over(ORDER BY score desc) assequence FROM student;SELECT *,row_number() over(PARTITION BY grade ORDER BY score desc) AS sequence FROM student;
--2、3获取每个班的前一名 SELECT * FROM ( SELECT *,row_number() OVER(PARTITION BY grade ORDER BY score DESC) AS SEQUENCE FROM student ) squence_table WHERE squence_table.SEQUENCE<=1;
三、分区函数partition by与排序rank()的用法
--1、分班后按学生成绩排名，该语句是对分数相同的记录进行了同一排名，比如：两个80分的并列第2名，因此，第4名就没用了 SELECT *,rank() over(PARTITION BY grade ORDER BY score desc) AS SEQUENCE FROM student;--2、获取每个班的前2名，该语句是对分数相同的记录进行了同一排名，例如：两个80分并列第2 SELECT * FROM ( SELECT *,rank() over(PARTITION BY grade ORDER BY score desc) AS SEQUENCE FROM student ) squence_table WHERE squence_table.SEQUENCE<=2;
三、分区函数partition by与 dense_rank()的用法 --1、分班后按学生成绩排名，该语句是对分数相同的记录进行了同一排名，比如：两个80分的并列第2名,而位列排名第4位的同学，成绩排名则从第3开始， --如果是row_number，排名则为90-1,80-2,80-3,70-4;如果是rank()则为；90-1,80-2,80-2,70-4 --使用dense_rank()则为90-1,80-2,80-2,70-3 SELECT *,dense_rank() over(PARTITION BY grade ORDER BY score desc) AS SEQUENCE FROM student;
--2、获取每个班的前3名，该语句是对分数相同的记录进行同一排名 SELECT * FROM ( SELECT *,dense_rank() over(PARTITION BY grade ORDER BY score desc) AS SEQUENCE FROM student )squence_table WHERE squence_table.SEQUENCE<=3;
举例：同一班级下：成绩依次为90,80,80,70 row_numer()对于同一分组、相同分数的排名:比如：第一名为90，第二名80，第三名80，第四名70 rank()对于同一分组、相同分数的排名：比如：第一名为90，第二名80,80并列，第四名70，则没有第三 dense_rank()对于同一分组、相同分数的排名：比如：第一名为90，第二名为80,80并列，第三名为70