分组排序后取指定顺序数据 - 开窗/分析 函数

          和聚合函数对应着看，聚合函数是通过指定一列来统计单行数据的。开窗函数是多行。

建表（Oracle）：

create table t_score(
   stuId varchar2(20),
   stuName varchar2(50),
   classId number,
   score float
);
insert into t_score(stuId,stuName,classId,score) values('111','小王',1,92);
insert into t_score(stuId,stuName,classId,score) values('123','小李',1,90);
insert into t_score(stuId,stuName,classId,score) values('134','小钱',1,92);
insert into t_score(stuId,stuName,classId,score) values('145','小顺',1,100);
insert into t_score(stuId,stuName,classId,score) values('121','小A',2,92);
insert into t_score(stuId,stuName,classId,score) values('131','小B',2,90);
insert into t_score(stuId,stuName,classId,score) values('141','小C',3,92);
insert into t_score(stuId,stuName,classId,score) values('151','小D',3,100);

分析函数：

 

 场景: 查询每个班的第一名

        步骤一：每个班的学生在自己的班级里排序

select
	        -- 表的原始字段 
					stuId, 
					stuName, 
					classId, 
					score,
					-- 多查一个排序字段
          row_number() over(partition by classId order by score desc) rn
          from t_score

         

        步骤二：找到想要的排名，以第一名为例

-- row_number() 不存在并列，一定是1.2.3....N
select *
  from (select
	        -- 表的原始字段 
					stuId, 
					stuName, 
					classId, 
					score,
					-- 多查一个排序字段
					-- row_number是一个内置函数，用来生成序号， over加在聚合函数后面表示row_number当作窗口函数（多行），而不是聚合函数（一行），partition by表示用指定字段进行分区，即依据字段值的不同来做分区表（以classid分区，则可以看作每个班级都是一个独立的分区表），因此后面的order by也是在分区表中生效，不会在全表里排序
          row_number() over(partition by classId order by score desc) rn
          from t_score)
where rn = 1; -- rn 就是序号， rn=1表示排序后的第一个，rn=2表示第二个，以此类推

        分析：首先通过窗口函数把每个班的学生按班级为单位做了分区，然后用分数做降序排列。这样就得到了一个有序号的，以班级为单位的分区表。最后只要找排名（rn=？）即可。

补充：rank（）函数，统计并列情况

-- rank 存在并列的情况， 1.2.2.4....N
-- dense_rank 1.2.2.3.4...N
select stuId,
        stuName,
        classId,
        score,
-- 聚合函数rank后面加上over() 表示这是一个开窗函数，即查出来是多行（group by出来是一行），每行都有一个开窗函数的结果，partition by是指以哪个字段做分区，classid做分区，则表示1.2.3 三个班都是独立的分区表，后面的order by也是在独立分区表里面排序
        rank() over(partition by classId order by score desc) rn 
   from t_score;

 使用rank函数会把数据相同的几项的序号认为是一样的，后面的排序会跳过并列的数量。