rank,over的用法

 

rank() ,  dense_rank() , row_number()  这三个函数都是的作用   为每条数据产生一个从1到N 的自然数， 区别就是 碰到相同数据时的排序

rank() :  遇到相同数据时排序是一样的， 但是在下一条数据会空出N个来， 例如有三个第一名，下一个就是第四名。  

dense_rank() :  遇到相同数据排名是一样的 ， 不会再下一个空出来 ，   例如 有3个第一名下一个 还是第二名。     

row_number():  排序依次递增，  不受相同数据的影响。

 

 

select  * from  temp  

 

select    rank()  over(  order by  c1 desc  )  rk    ,t.* from  temp  t      where  c4 ='数学'   --只排出 数学的成绩名次

select    rank() over(partition by c4   order  by c1 desc   ) rk   ,  t.* from   temp  t     -- 查询 按照课程排出名词

 

select    dense_rank() over(partition by c4   order  by c1 desc   ) rk   ,  t.* from   temp  t   
  -- 查询 按照课程排出名词   用dense_rank()  不跳过相同的排名   也就是说名次会有两个第二名  

 row_number()

select    row_number()  over(  order by  c1 desc  )  rk    ,t.* from  temp  t     -- row_number() 为每条记录产生一个排序

插入一笔新的成绩为null  的数据

select    rank() over(partition by c4   order  by c1 desc   ) rk   ,  t.* from   temp  t     -- 查询  发现成绩为null 的排序是1 

select    rank() over(partition by c4   order  by c1 desc nulls last  ) rk   ,  t.* from   temp  t     
-- 查询 加上 nulls last  说明吧 null 的 放到最后 

 

与 sum()  的用法

select   t.* ,  sum(c1) over(partition by     c2  ) s  from  temp t   -- 按照学生名 求总成绩

select   t.* ,  sum(c1) over( order by  c2    ) s  from  temp t    -- 按照学生总成绩   求"连续的"总成绩

select    t.*  ,  (sum(c1) over(partition by c2 )) "csum",  sum(c1)over() "sum" ,  
   100* round(  (sum(c1) over(partition by c2 )) / (sum(c1)over() )  ,  4) ||'%' bb   from  temp  t     --  查学生总分 占所有学生总分数的比例