添加链接
link之家
链接快照平台
  • 输入网页链接,自动生成快照
  • 标签化管理网页链接
SQL算法:Top N

SQL算法:Top N

Top N 问题——排名函数

  • 编写sql语句实现每班前三名,分数一样并列(排名函数),同时求出前三名按名次排序的分差
  • 数据
-- sid class score
1 1901 90 
2 1901 90 
3 1901 83 
4 1901 60 
5 1902 66 
6 1902 23 
7 1902 99 
8 1902 67 
9 1902 87
-- 待求结果数据如下:
class   score   rank    lagscore 
1901    90      1       0   
1901    90      1       0 
1901    83      2       -7    --比前一名差的分数
1901    60      3       -23 
1902    99      1       0 
1902    87      2       -12 
1902    67      3       -20
  • 建表语句
create table stu(
    sno int,
    class string,
     score int 
row format delimited fields terminated by ' ';
  • 加载数据
load data local inpath '/home/hadoop/data/stu.dat' into table stu;
思路:
-- 1、上排名函数,分数一样并列,所以用dense_rank
-- 2、将上一行数据下移,相减即得到分数差
-- 3、处理 NULL
  • 1. 上排名函数,分数一样的数据并列,
with tmp as (
 select sno, class, score,
       dense_rank() over (partition by class order by score desc) 
          as rank
  from stu 
) 
  • 2 上行函数,将数据向下移动,相减求分差
select sno, class, score,
       dense_rank() over (partition by class order by score desc) 
          as rank,
--将数据下移,相减求分差