高级SQL分析函数-窗口函数(1)- 排名计算

窗口函数


      在SQL 语句中，经常使用聚合函数来计算统计一些业务数据结果，比如每个业务地区的业务总数，每个班级的学生平均分，每个分类的最大值等等。上述例子中我们会使用 SUM(),AVG(),Max() 等函数在一个个分组上得到每个分组的业务指标。而今天介绍的窗口函数和聚合函数相比，同样的它们代指的都是一族函数(非某一个函数),但不同点的，两者的使用方法，适用场景都有差异。本系列旨在通过实际的例子来介绍窗口函数的常用函数以及其针对性场景。本章节内容主要介绍 窗口函数中的 RANK、DENSE_RANK 和 ROW_NUMBER 函数以及其对应的排名筛选场景。


数据库要求

窗口函数作为SQL标准当中的高级分析特性,目前主流的数据库都实现了对应的函数方法，现将具体版本记录如下:
1. Mysql (>=8.0)
2. PostgreSQL(>=8.4)
3. SQL Server(>2005)
4. Oracle(>8i)
5. SQLite(>3.25.0)

数据准备


我们准备的数据场景是，学生考试成绩，表结构中有关于 学生名、班级、科目、得分的具体信息，本文将针对该表信息获取如下统计指标
1. 所有学生中各个科目的前2名
2. 每个班级中各个科目的前2名
3. 每个班级中的总分排名前2名


         

首先将附件中 csv 数据导入到 数据库当中，这里我们给数据库起名为 score_data。


SQL

     为了获得各个不同科目各自的前2名，我们需要先使用 Rank() 函数来给每个学生在各自科目的分区打上成绩排名, 执行如下SQL 语句:

1. select sd.*, RANK() over(partition  by subject  order by score desc) as _rank from score_data sd;

复制代码

   
可以看到，执行结果里面已经根据各个科目的成绩得到了排名字段 _rank, 接下来只需要使用过滤掉 _rank 字段大于2的部分即可。

1. select * from (
   
2. select sd.*, RANK() over(partition  by subject  order by score desc) as _rank from score_data sd
   
3. ) tmp
   
4. where tmp._rank <=2

复制代码

     
       到目前为止我们就得到了各个科目的排名前二，然后我们具体说明下 RANK() over(partition by {group} over by {value}) 的计算规则。 在这个语句中需要指定的是 partition by 后面的分区字段,以及order by 后面的排序字段，partition by 的语法类似于 group by 语句，根据不同的类别来分组，但是在窗口函数中的 partition by 是不影响原有数据的行数的, 也就是说 group by 会先对数据进行划分分组，然后在每个分组上运算聚合函数得到一个标量值，而窗口函数的 分组只是划定了数据计算范围，然后在每个分组上运行对应的窗口函数, 再本例中我们根据 科目 subject 的不同指定了不同的计算域，随即在每个域内开始执行 rank 排名运算，当切换到下一个科目计算域时,会重新从排名1开始计算新域的排名值，最后计算结果上 聚合函数得到的值的行数一定和 聚合分类得个数一致，例如本例中，如果执行如下sql:

1. select subject,Max(score) from score_data group by subject

复制代码

     得到结果行数和 subject 得种类个数是一致得，但是 窗口函数得到得结果是无损得，即在原本表得每一行上划定计算窗口，窗口内计算完值之后，返回到该行作为新字段。
      至于 RANK() over(partition by {group} over by {value}) 语句中得 order by 子句则易知,控制Rank 函数得执行顺序，order by score desc 就控制了整个排名是按照分数字段从高到低来排序得。

DENSE_RANK

      重新回过头来看刚刚得到得各科排名前2得结果，我们发现 数学科目得第2名有两位，原因是张三和李四得数学成绩是一致得，这点很容易理解，那数学排名在张三、李四之后得同学得排名应该是 第3还是第4呢，这就需要提到另一个 排名函数 DENSE_RANK, 它得计算语法和 RANK 基本一致，唯一不同的点在于, Rank 计算时会得到 成绩高于当前行的记录的总行数, 结合场景来说就是 成绩比当前行高的学生数, 而 DENSE_RANK 则是计算成绩高于当前行的去重记录的总行数, 代换到场景里面就是 按成绩去重后,得到的排名数。

ROW_NUMBER

还有个场景就是需要对每一行指定一个序号，不需要根据重复值给不同行同一个排名，这时候就可以使用 ROW_NUMBER() 函数，该函数主要对排序后的每一行根据顺序来指定行号。

其他指标

       每个班级中各个科目的前2名
       关于我们要计算的2、3指标 在理解了 RANK 函数的运算规则后，也就简单了。我们只需要在原有分区 subject 基础上加上 class 班级的分区规则即可。

1. select * from (
   
2. select sd.*, RANK() over(partition  by subject, class  order by score desc) as _rank from score_data sd
   
3. ) tmp
   
4. where tmp._rank <=2

复制代码

      


       每个班级中的总分排名前2名

1. select class,name,SUM(score) AS total_score,
   
2. RANK() over (PARTITION by class order by SUM(score) desc)
   
3. from score_data sd group by class,name

复制代码

     
       在这个指标的计算中，需要把 聚合函数 和 排名函数结合起来使用，因为每个人的总成绩被拆分为了多个科目的和，所以需要在班级和科目的联合分组维度上进行聚合，把数据压缩到每人总分的颗粒度。

Last
窗口函数是 SQL 函数中非常强大的工具，尤其是在报表统计等场景领域，更是最锋利的瑞士军刀，包括 Wyn 仪表板的快速计算等分析功能背后也用到了大量的窗口函数，这个系列分享出来和大家共勉。