Mysql_窗口函数之排序函数rank()、dense_rank()、row_number()

谁践踏了优雅 2021-09-24 15:22 437阅读 0赞

### 文章目录 ###

*   *   *  基础语法
         *  窗口函数理解
         *  实例

**重要：Mysql8.0+版本支持窗口函数。**

### 基础语法 ###

窗口函数中，排序函数分三种：

rank() over(partition by 分区字段 order by 排序字段 desc/asc)
    
    dense_rank()over(partition by 分区字段 order by 排序字段 desc/asc)
    
    row_number()over(partition by 分区字段 order by 排序字段 desc/asc)

*  rank()函数，当指定字段数值相同，则会产生相同序号记录，且产生序号间隙。
 *  dense\_rank()函数，当指定字段数值相同，则会产生相同序号记录，且不会产生序号间隙
 *  row\_number()函数，不区分是否记录相同，产生自然序列

### 窗口函数理解 ###

*  over() 用来指定函数执行窗口范围，如果后面括号内无任何内容，则指窗口范围是满足 where 条件所有行。
 *  partition by，指定按照某字段进行分组，窗口函数是在不同的分组分别执行。不会减少原表中的行数
 *  order by，指定按照某字段进行排序

关于窗口函数是在不同的分组分别执行，不会减少源表中的行数的理解：  
Scores表:

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>class_id</th> 
   <th>score</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>001</td> 
   <td>1</td> 
   <td>95</td> 
  </tr> 
  <tr> 
   <td>002</td> 
   <td>2</td> 
   <td>87</td> 
  </tr> 
  <tr> 
   <td>003</td> 
   <td>1</td> 
   <td>92</td> 
  </tr> 
  <tr> 
   <td>004</td> 
   <td>3</td> 
   <td>87</td> 
  </tr> 
  <tr> 
   <td>005</td> 
   <td>1</td> 
   <td>86</td> 
  </tr> 
  <tr> 
   <td>006</td> 
   <td>2</td> 
   <td>93</td> 
  </tr> 
  <tr> 
   <td>007</td> 
   <td>3</td> 
   <td>91</td> 
  </tr> 
 </tbody> 
</table>

窗口函数查询：

select class_id,count(id)over(partition by class_id order by class_id) as count from Scores;

结果：

<table> 
 <thead> 
  <tr> 
   <th>class_id</th> 
   <th>count</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>2</td> 
  </tr> 
 </tbody> 
</table>

group by分组函数查询

select class_id,count(id) as count_id from Scores group by class_id order by class_id;

结果：

<table> 
 <thead> 
  <tr> 
   <th>class_id</th> 
   <th>count_id</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>2</td> 
  </tr> 
 </tbody> 
</table>

### 实例 ###

leetcode题目：求Scores表的分数排序，如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。  
查询：

select Score,dense_rank()over(order by Score desc) as "Rank" from Scores

结果：

<table> 
 <thead> 
  <tr> 
   <th>score</th> 
   <th>Rank</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>95</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>93</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>92</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>91</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>87</td> 
   <td>5</td> 
  </tr> 
  <tr> 
   <td>87</td> 
   <td>5</td> 
  </tr> 
  <tr> 
   <td>86</td> 
   <td>7</td> 
  </tr> 
 </tbody> 
</table>

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODg1MTk3MA_size_16_color_FFFFFF_t_70_pic_center]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODg1MTk3MA_size_16_color_FFFFFF_t_70_pic_center]: /images/20210923/15e8c1281ab34398a70843d0b0167c26.png