SQL--窗口函数

参考文章： 通俗易懂的学会：SQL窗口函数

• 1.窗口函数语法
  <窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)

• 2.窗口函数有以下功能：
  1）同时具有分组（partition by）和排序（order by）的功能
  2）不减少原表的行数，所以经常用来在每组内排名。group by 会减少分组的行数

• 3.注意事项
  窗口函数原则上只能写在select子句中

• 4.rank, dense_rank, row_number有什么区别呢？

select *,
   rank() over (order by 成绩 desc) as ranking,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num
from 班级表

• first value用法

select distinct user_id,
FIRST_VALUE(date) over(partition by user_id order by date desc) as d 
from login

select user_id,date
FROM(
    select user_id,date,row_number() over (partition by user_id order by date desc) as d
    from login 
)a
where d=1

• 聚合函数用法

select *,
   sum(成绩) over (order by 学号) as current_sum,
   avg(成绩) over (order by 学号) as current_avg,
   count(成绩) over (order by 学号) as current_count,
   max(成绩) over (order by 学号) as current_max,
   min(成绩) over (order by 学号) as current_min
from 班级表

题目：


答案：

select id, number,
dense_rank() over(order by number desc) as t_rank
from passing_number
order by number desc, id asc

SELECT *, DENSE_RANK()OVER(ORDER BY number DESC) t_rank
FROM passing_number
ORDER BY t_rank,id