mysql8的组内排名

不念不忘少年蓝@ 2022-10-28 14:21 9阅读 0赞

# 目标效果 #

模拟数据如下：

DROP TABLE IF EXISTS `test_sum`;
    CREATE TABLE `test_sum` (
      `year` SMALLINT NOT NULL,
      `province` VARCHAR(32) NOT NULL,
      `num` INT UNSIGNED NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    INSERT INTO `test_sum` (`year`,`province`,`num`) VALUES
    (2018,'北京',1),
    (2018,'上海',3),
    (2018,'浙江',7),
    (2019,'北京',5),
    (2019,'上海',5),
    (2019,'浙江',11);

目标实现三个场景：

*  结果集会显示全局排名![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9sZWFybi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70]
    
    > 排名分有间隔和无间隔两种情况：主要区别为出现并列排名后，下一个排名是按行号，还是在上一个排名后+1
 *  结果集分几个组后独立排名  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9sZWFybi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 1]
 *  结果集分几个组后显示每行数据、和合计值  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9sZWFybi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 2]

# 实现方案 #

因为不同MySQL版本，实现方案有所不同，下面分开介绍方案。

> 小提示：判断MySQL函数在哪个版本支持，可以在MySQL官方的[《函数和运算符参考文档》][Link 1]中查找、对比不同版本。

## MySQL 8.0 ##

MySQL 8.0提供了窗口函数[1][]。其实现的核心，通过`window`获取结果集、在`window`中可以指定排序和分组，然后主查询通过`over`连接`window`结果集，进行相应运算。窗口函数包括排名等函数。

### 全局排名 ###

只要使用内置的`rank()`、`dense_rank()`函数[2][]即可。

SELECT *,
    	ROW_NUMBER() OVER w AS '行号',
    	RANK()       OVER w AS '排名-有间隔',
    	DENSE_RANK() OVER w AS '排名-无间隔'
    FROM `test_sum` 
    WINDOW w AS (ORDER BY `num`);

`WINDOW`和`OVER`是窗口函数的主要特征。

### 分组排名 ###

分组排名的实现，与上面全局排名的区别，仅仅是`WINDOW`中增加了`PARTITION`：

SELECT *,
    	ROW_NUMBER() OVER w AS '行号',
    	RANK()       OVER w AS '排名-有间隔',
    	DENSE_RANK() OVER w AS '排名-无间隔'
    FROM `test_sum`
    WINDOW w AS (partition by `year` ORDER BY `num`)

### 合计 ###

SELECT *, SUM(`num`) over w AS '分组合计'
    FROM `test_sum` 
    WINDOW w AS (partition by `year`)
    ORDER BY `year`, `province`

> 窗口函数下的分组合计，和之前理解的`group`分组有所不同，区别在于合计函数并不会导致分组的行合并成一行。

### 小结 ###

思考窗口函数的实现思路，看起来像是window查询出后，按照排序、分组在内存中组织临时表（有序、指定分区），然后按分区开始运算主查询函数，运算后的结果按分区键关联到临时表中，然后整合数据输出。

> `window`中指定排序、主查询不指定排序，最终的结果是按`window`的排序来输出，而不是主查询默认的顺序；而多个window都指定了排序时，会使用最后一个的排序（可以尝试同个列用升序和降序），因而有此猜测。  
> 后面会考虑追踪sql执行过程、验证猜想，有结果再补充更新本文。

转载地址：[https://blog.csdn.net/zgdwxp/article/details/102696341][https_blog.csdn.net_zgdwxp_article_details_102696341]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9sZWFybi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70]: /images/20221024/6559d39532de4b3697ad6199aed85cbe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9sZWFybi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 1]: /images/20221024/b50bd2e6f1284353af4556111a760e13.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9sZWFybi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 2]: /images/20221024/42517c110ce64d2380fac538d6a2fdae.png
[Link 1]: https://dev.mysql.com/doc/refman/8.0/en/func-op-summary-ref.html
[1]: https://blog.csdn.net/zgdwxp/article/details/102696341#fn1
[2]: https://blog.csdn.net/zgdwxp/article/details/102696341#fn2
[https_blog.csdn.net_zgdwxp_article_details_102696341]: https://blog.csdn.net/zgdwxp/article/details/102696341