新特性解读 | 窗口函数的适用场景

2020-10-10 537

作者：赵佳慧

爱可生售后团队成员，主要负责公司运维平台故障诊断。喜爱技术，努力在 IT 行业中磨练自己。

本文来源：原创投稿 *爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

一、窗口函数

“窗口”可理解为记录集合。“窗口函数”可理解为在满足某种条件的记录集合上执行的特殊函数，对于每条记录都要在此窗口内执行。

二、快速理解窗口函数

通过举例，快速理解窗口函数。

2.1 举例

1）创建表 user，表 user 的数据如下：

mysql> select * from user;+‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+| id | name | address | createtime |+‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+| 1 | aa | cun | 2020‐06‐01 00:00:00 || 2 | bb | cun | 2020‐06‐01 00:00:00 || 3 | bb | shi | 2020‐06‐01 01:00:00 || 4 | bb | shi | 2020‐06‐01 01:00:00 || 5 | cc | cun | 2020‐06‐01 01:00:00 || 6 | tt | cun | 2020‐06‐03 01:00:00 || 7 | eee | cun | 2020‐06‐04 01:00:00 || 8 | eee | cun | 2020‐06‐04 01:00:00 || 9 | xx | shen | 2020‐06‐02 01:00:00 |+‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+9 rows in set (0.00 sec)

2）窗口函数的使用

SELECT createtime,row_number() over(order by createtime) AS crFROM user ;+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐+| createtime | ll |+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐+| 2020‐06‐01 00:00:00 | 1 || 2020‐06‐01 00:00:00 | 2 || 2020‐06‐01 01:00:00 | 3 || 2020‐06‐01 01:00:00 | 4 || 2020‐06‐01 01:00:00 | 5 || 2020‐06‐02 01:00:00 | 6 || 2020‐06‐03 01:00:00 | 7 || 2020‐06‐04 01:00:00 | 8 || 2020‐06‐04 01:00:00 | 9 | +‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐+9 rows in set (0.00 sec)

row_number() over(order by createtime) as cr 这部分为窗口函数。over（order by createtime）为窗口规范，函数 row_number() 即对窗口的数据进行编号。所以上述 sql 的意思为：先对 createtime 进行排序，然后对每行数据进行编号。

三、窗口函数的适用场景

下面举例说明在哪些场景下适用窗口函数。

3.1 数据准备

1）创建用户表 user 并插入数据。

mysql> select * from user;+‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+| id | name | address | createtime |+‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+| 1 | 李四 | 广州 | 2020‐06‐01 00:00:00 || 2 | 老三 | 广西 | 2020‐06‐01 00:00:00 || 3 | 张三 | 广州 | 2020‐06‐01 01:00:00 || 4 | 王五 | 上海 | 2020‐06‐01 01:00:00 || 5 | 吴迪 | 深圳 | 2020‐06‐01 01:00:00 || 6 | 赵六 | 北京 | 2020‐06‐03 01:00:00 || 7 | 刘六 | 北京 | 2020‐06‐04 01:00:00 || 8 | 刘开 | 江西 | 2020‐06‐04 01:00:00 || 9 | 张劳 | 上海 | 2020‐06‐02 01:00:00 |+‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+9 rows in set (0.00 sec)

2）创建交易表 transaction 并插入数据。

mysql> select * from transaction;+‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+| id | userid | amount | paydate |+‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+| 1 | 3 | 23 | 2020‐07‐01 || 2 | 1 | 34 | 2020‐07‐01 || 3 | 3 | 56 | 2020‐07‐01 || 4 | 3 | 32 | 2020‐07‐01 || 5 | 2 | 100 | 2020‐07‐03 || 6 | 3 | 100 | 2020‐07‐03 || 7 | 2 | 198 | 2020‐07‐01 || 8 | 4 | 209 | 2020‐07‐01 || 9 | 4 | 209 | 2020‐07‐03 || 10 | 4 | 209 | 2020‐07‐10 || 11 | 3 | 239 | 2020‐07‐14 || 12 | 6 | 100 | 2020‐07‐14 || 13 | 3 | 100 | 2020‐07‐30 || 14 | 3 | 140 | 2020‐07‐30 || 15 | 3 | 148 | 2020‐07‐30 || 16 | 3 | 89 | 2020‐07‐30 |+‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+16 rows in set (0.00 sec)

3.2 场景一

在拥有用户表和交易表的前提下，可以计算出每个用户的交易量占总交易量的百分比。由此我们可以快速得出每个用户的交易占比。

sql 如下：

SELECT a.name,a.everymoney,sum(a.everymoney) over() AS totalmoney,a.everymoney/(sum(a.everymoney) over()) AS percentFROM(SELECT u.name,sum(t.amount) everymoneyFROM user uJOIN transaction tON u.id=t.useridGROUP BY u.name) a;
+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+| name | everymoney | totalmoney | percent |+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+| 张三 | 1694 | 6359 | 0.2664 || 李四 | 2014 | 6359 | 0.3167 || 老三 | 1491 | 6359 | 0.2345 || 王五 | 1060 | 6359 | 0.1667 || 赵六 | 100 | 6359 | 0.0157 |+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+5 rows in set (0.00 sec)

通过子查询 a 对用户进行分组计算出每个用户的总交易金额（everymoney），再通过子查询 aa 使用 sum()+over 子句计算出所有用户总的交易金额（totalmoney），最后将每个用户总交易金额比上所有用户总交易金额（a.everymoney/(sum(a.everymoney) over()）。

3.3 场景二

在拥有用户表和交易表的前提下，可以计算出每天交易金额位于第一的用户。

sql 如下：

SELECT a.name,a.paydate,row_number()OVER w AS numFROM(SELECT u.name,paydate,sum(amount) AS totalFROM user uJOIN transaction tON u.id=t.useridGROUP BY u.name,paydate) a window w AS (partition by paydateORDER BY a.total desc);
+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐+| name | paydate | num |+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐+| 王五 | 2020‐07‐01 | 1 || 老三 | 2020‐07‐01 | 2 || 张三 | 2020‐07‐01 | 3 || 李四 | 2020‐07‐01 | 4 || 王五 | 2020‐07‐03 | 1 || 张三 | 2020‐07‐03 | 2 || 老三 | 2020‐07‐03 | 3 || 王五 | 2020‐07‐10 | 1 || 张三 | 2020‐07‐14 | 1 || 赵六 | 2020‐07‐14 | 2 || 张三 | 2020‐07‐30 | 1 |+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐+11 rows in set (0.00 sec)

上述 sql 通过子查询 a 对用户和 paydate 进行分组，计算出每个用户每天的交易金额

（total），再通过窗口函数 row_number() over w as num...window w as (partition by paydate order by a.total) 对数据按 paydate 分组，并按 total 降序排序进行编号。

由于按天分组，按交易金额降序进行编号，所以每个分组中的第一条就是交易额最高的，最终过滤 num=1 的数据即可得出每天交易金额最高的用户。

sql 如下：

SELECT *FROM(SELECT a.name,4a.paydate,row_number()OVER w AS numFROM(SELECT u.name,paydate,sum(amount) AS totalFROM user uJOIN transaction tON u.id=t.useridGROUP BY u.name,paydate) a window w AS (partition by paydateORDER BY a.total)) aaWHERE num=1;+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐+| name | paydate | num |+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐+| 王五 | 2020‐07‐01 | 1 || 王五 | 2020‐07‐03 | 1 || 王五 | 2020‐07‐10 | 1 || 张三 | 2020‐07‐14 | 1 || 张三 | 2020‐07‐30 | 1 |+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐+5 rows in set (0.00 sec)

通过如上的两个例子可以看出，窗口函数能够方便的实现一些排序及统计功能，当然也不局限于使用窗口函数，不过实现的 sql 语句可能会比较复杂。

四、窗口函数一览

MySQL 8.0 新增的窗口函数如下：

CUME_DIST()DENSE_RANK()FIRST_VALUE()LAG()LAST_VALUE()6LEAD()NTH_VALUE()NTILE()PERCENT_RANK()RANK()ROW_NUMBER()

4.1 CUME_DIST() 使用举例

cume_dist()：表示当前行及小于当前行在窗口分区总行数中的占比。

举例：

mysql> select * from user1;+‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+| id | name | address | createtime |+‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+| 1 | aa | cun | 2020‐06‐01 00:00:00 || 2 | bb | cun | 2020‐06‐01 00:00:00 || 3 | bb | shi | 2020‐06‐01 01:00:00 || 4 | bb | shi | 2020‐06‐01 01:00:00 || 5 | cc | cun | 2020‐06‐01 01:00:00 || 6 | tt | cun | 2020‐06‐03 01:00:00 || 7 | eee | cun | 2020‐06‐04 01:00:00 || 8 | eee | cun | 2020‐06‐04 01:00:00 || 9 | xx | shen | 2020‐06‐02 01:00:00 |+‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+9 rows in set (0.00 sec)
SELECT id,CUME_DIST()OVER w AS ll,createtimeFROM user1 window w AS (order by createtime);+‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+| id | ll | createtime |+‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+| 1 | 0.2222222222222222 | 2020‐06‐01 00:00:00 || 2 | 0.2222222222222222 | 2020‐06‐01 00:00:00 || 3 | 0.5555555555555556 | 2020‐06‐01 01:00:00 || 4 | 0.5555555555555556 | 2020‐06‐01 01:00:00 || 5 | 0.5555555555555556 | 2020‐06‐01 01:00:00 || 9 | 0.6666666666666666 | 2020‐06‐02 01:00:00 || 6 | 0.7777777777777778 | 2020‐06‐03 01:00:00 || 7 | 1 | 2020‐06‐04 01:00:00 || 8 | 1 | 2020‐06‐04 01:00:00 |+‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+9 rows in set (0.00 sec)

如上 sql 所示，查看第一行数据值为 0.2222222222222222，因为小于等于 id=1 对应的 createtime 值有两行（id=1 和 id=2），所以经过 cume_dist() 函数计算后为 0.2222222222222222。

其他函数详细解析可前往官网查看：

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

没错！cxuan 对汇编下手了

点击蓝色“Java建设者”关注我哟加个“星标”，及时阅读最新技术文章扉页解读：特拉法尔加·罗和贝波，于两年前作者自己的 Githubhttps://github.com/crisxuan/bestJavaer 已收录此文见文末原文链接这是Java建设者的第 123 篇原创文章汇编代码是计算机的一种低级表示，它是一种低级语言，可以从字面角度去理解它，包括处理数据、管理内存、读写存储设备上的数据，以及利用网络通信等。编译器生成机器码经过了一系列的转换，这些转换遵循编程语言、目标机器的指令集和操作系统。指令集指令集就是指挥计算机工作的指令，因为程序就是按照一定执行顺序排列的指令。因为计算机的执行控制权由 CPU 操作，所以指令集就是 CPU 中用来计算和控制计算机的一系列指令的集合。每个 CPU 在产出时都规定了与硬件电路相互配合工作的指令集。指令集有不少分类，但是一般分为两种，一种是精简指令集，一种是复杂指令集。具体描述如下精简指令集精简指令的英文是 reduced instruction set computer， RISC，原意是精简指令集计算，简称为精简指...

2020-10-11

514

OAuth 2.0 单元测试解决方案

为什么需要单元测试单元测试拥有保证代码质量、尽早发现软件 Bug、简化调试过程、促进变化并简化集成、使流程更灵活等优势。单元测试是针对代码单元的独立测试，核心是“独立”，优势来源也是这种独立性，而所面临的不足也正是因为其独立性：既然是“独立”，就难以测试与其他代码和依赖环境的相互关系。单元测试与系统测试是互补而非代替关系。单元测试的优势，正是系统测试的不足，单元测试的不足，又恰是系统测试的优势。不能将单元测试当做解决所有问题的万金油，而需理解其优势与不足，扬长避短，与系统测试相辅相成，实现测试的最大效益。 OAuth2 系统单元测试困难接口测试依赖于 UPMS (用户权限管理)，无法做到解耦独立 spring-security-test 模块未提供相关标准实现场景复杂既要包含无状态 token 调用，又要保证上线文传递业务解决方案参考 @WithMockUser ，在 Mock 拦截器中自动执行相关的增强（token 获取），并通过扩展 WithSecurityContextFactory 实现上下文 token 的传递。具体可以参考源码 pig-common-test[1]...

2020-10-12

584

发表评论

资源下载

更多资源

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。

WebStorm

WebStorm 是jetbrains公司旗下一款JavaScript 开发工具。目前已经被广大中国JS开发者誉为“Web前端开发神器”、“最强大的HTML5编辑器”、“最智能的JavaScript IDE”等。与IntelliJ IDEA同源，继承了IntelliJ IDEA强大的JS部分的功能。

新特性解读 | 窗口函数的适用场景

二、快速理解窗口函数

2.1 举例

三、窗口函数的适用场景

3.1 数据准备

3.2 场景一

3.3 场景二

四、窗口函数一览

4.1 CUME_DIST() 使用举例

没错！cxuan 对汇编下手了

OAuth 2.0 单元测试解决方案

相关文章

发表评论

资源下载

腾讯云软件源

Nacos

Sublime Text

WebStorm

欢迎您来访！