sql的窗口函数

窗口函数必备的特征

• 同时具有分组和排序功能
• 不减少原来表的行数
• 语法遵循 -----------窗口函数 over (partition by <列名> order by <列名>)

常用的窗口函数 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 班级表

聚合窗口函数 sum(),avg(),count(),max(),min()

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 班级表

参考链接:
https://www.zhihu.com/tardis/bd/art/92654574?source_id=1001