• SQL窗口函数
  • 基本语法
  • rank()/dense_rank()/row_number() 排名
  • 聚合函数作为窗口函数

SQL窗口函数

在日常工作中，经常会遇到需要在每组内排名，比如下面的业务需求：

• 排名问题：每个部门按业绩来排名
• topN问题：找出每个部门排名前N的员工进行奖励
  面对这类需求，就需要使用sql的高级功能窗口函数了。

窗口函数的特点

• 同时具有分组和排序的功能
• 不减少原表的行数

窗口函数会把表进行分组但不会合并，只是相同组的会按照排序挨着。
窗口函数是因为partition by分组后的结果称为“窗口”，“范围”的意思。

基本语法

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

口函数

• 专用窗口函数，rank, dense_rank, row_number等专用窗口函数。
• 聚合函数，如sum. avg, count, max, min等

rank()/dense_rank()/row_number() 排名

假设我们想在每个班级内按成绩排名

select *,
   rank() over (partition by 班级 -- 按班级分块，一个班的放在一起
                 order by 成绩 desc ) as ranking -- 班级内部按成绩降序排列 ranking新增列为其排名
from 班级表

分组之后，同组的并没有合并，只是挨着放了

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

分析：
1.没有分区，那么整个表表示一个区。
2.
rank() 有并列名次的行，会占用下一名次的位置
dense_rank() 有并列名次的行，不占用下一名次的位置
row_number() 不考虑并列名次的情况，按顺序依次下去

参考文章:https://zhuanlan.zhihu.com/p/92654574

聚合函数作为窗口函数

函数函数需要指定聚合的列名。

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

聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号，在使用sum窗口函数后的结果，是对0001，0002，0003，0004号的成绩求和，若是0005号，则结果是0001号~0005号成绩的求和，以此类推。
比如0005号后面的聚合窗口函数结果是：学号0001~0005五人成绩的总和、平均、计数及最大最小值。
这样使用窗口函数有什么用呢？
聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。