rank 开窗函数_开窗函数(分析函数)

开窗函数(分析函数)

2.7.1 特点:开窗函数也就是在满足某种条件的记录集合上执行的特殊函数;

对于每条记录都要在此窗口内执行函数;

有的函数随着记录不同,窗口大小都是固定的,这种属于静态窗口;

有的函数则相反,不同的记录对应着不同的窗口,这种动态变化的窗口叫滑动窗口;

开窗函数的本质还是聚合运算,只不过它更具灵活性,它对数据的每一行,都使用与该行相关的行进行计算并返回计算结果。

2.7.2 开窗函数和普通聚合函数的区别聚合函数是将多条记录聚合为一条;而开窗函数是每条记录都会执行,有几条记录执行完还是几条

聚合函数也可以用于开窗函数中。

2.7.3 语法开窗函数名([]) over([partition by ] [order by [desc]] [])partition by子句:按照指定字段进行分区,两个分区由边界分隔,开窗函数在不同的分区内分别执行,在跨越分区边界时重新初始化。

order by子句:按照指定字段进行排序,开窗函数将按照排序后的记录顺序进行编号。可以和partition by子句配合使用,也可以单独使用。

frame子句:当前分区的一个子集,用来定义子集的规则,通常用来作为滑动窗口使用。

关于over的参数over参数为空,没有指定分区、排序、滑动窗口时,开窗函数将整个表作为一个区,默认计算的是所有值;

over指定了分区,未指定排序和滑动窗口,开窗函数默认计算分区内的所有值;

over指定了分区、排序,未指定滑动窗口,开窗函数默认计算的时第一行到当前行的值;

2.7.4 滑动窗口范围指定的两种方式方式一:基于行

对于滑动窗口的范围指定,通常使用 between frame_start and frame_end 语法来表示行范围,frame_start和frame_end可以支持如下关键字,来确定不同的动态行记录:current row 边界是当前行,一般和其他范围关键字一起使用

unbounded preceding 边界是分区中的第一行

unbounded following 边界是分区中的最后一行

expr preceding 边界是当前行减去expr的值

expr following 边界是当前行加上expr的值

移动平均可用来消除差额较大的情况,平滑数据方式二:基于范围

和基于行类似,但有些范围不是直接可以用行数来表示的,比如希望窗口范围是一周前的订单开始,截止到当前行,则无法使用rows来直接表示,此时就可以使用范围来表示窗口:interval 7 day preceding。

2.7.5 开窗函数分类动态窗口函数:first_value() / last_value()/nth_value()/聚合函数用于开窗

如没有指定排序和滑动窗口范围,默认计算的是分区内的所有记录。 指定分区和排序后,如没有指定滑动窗口范围,默认计算的是分区内的第一行到当前行。静态窗口函数:row_number() / rank() / dense_rank()/percent_rank() / cume_dist()/lag() / lead()/ntile()

不管是否指定滑动窗口范围,窗口都是固定的,所以指定的滑动窗口范围无效。

2.7.6 按照函数功能不同,MySQL支持的开窗函数分为如下几类:序号函数:row_number() / rank() / dense_rank()

函数名显示内容显示样式row_number()显示分区中不重复不间断的序号1,2,3,4...dense_rank()显示分区中重复不间断的序号1,1,2,3,3...rank()显示分区中重复间断的序号1,1,3,4...分布函数:percent_rank() / cume_dist()percent_rank():和之前的rank()函数相关,每行计算:(rank - 1) / (rows - 1)

其中,rank为rank()函数产生的序号,rows为当前窗口的记录总行数。rank() over(partition by user_no order by amount desc) rank1,

percent_rank() over(partition by user_no order by amount desc) percentcume_dist(): 分组内小于等于当前rank值的行数/分组内总行数

示例:查询大于等于当前订单金额的订单比例rank() over(partition by user_no order by amount desc) rank1,

cume_dist() over(partition by user_no order by amount desc) cume前后函数:lag() / lead()

分区中位于当前行前n行(lag)/后n行(lead)的记录值

示例:查询上一个订单距离当前订单的间隔天数lag(create_date,1) over(partition by user_no order by create_date) last_date头尾函数:first_value() / last_value()

分区中的第一个/最后一个指定参数的值

示例:查询截止到当前订单,按照日期排序第一个订单和最后一个订单的订单金额first_value(amount) over(partition by user_no order by create_date) first_amount,

last_value(amount) over(partition by user_no order by create_date) last_amount其他函数:nth_value() / nfile()nth_value(expr,n):返回窗口中第N个expr的值,expr可以是表达式,也可以是列名

示例:每个用户订单中显示金额排名第二和第三的订单金额nth_value(amount,2) over(partition by user_no order by amount) second_amount,

nth_value(amount,3) over(partition by user_no order by amount) third_amount

2. nfile(n):将分区中的有序数据分为n个桶,记录桶号

示例:将每个用户的订单按照订单金额分成3组

此函数在数据分析中应用较多,比如由于数据量大,需要将数据平均分配到N个并行的进程分别计算,此时就可以用nfile(n)对数据进行分组,由于记录数不一定被n整除,所以数据不一定完全平均,然后将不同桶号的数据再分配。

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
<div style="color:rgba(0,0,0,.75);"> <span style="color:#4d4d4d;"> </span> <div style="color:rgba(0,0,0,.75);"> <span style="color:#4d4d4d;"> </span> <div style="color:rgba(0,0,0,.75);"> <div style="color:rgba(0,0,0,.75);"> <span style="color:#4d4d4d;">当前课程中商城项目的实战源码是我发布在 GitHub 上的开源项目 newbee-mall (新蜂商城),目前已有 6300 多个 star,</span><span style="color:#4d4d4d;">本课程是一个 Spring Boot 技术栈的实战类课程,课程共分为 3 大部分,前面两个部分为基础环境准备和相关概念介绍,第三个部分是 Spring Boot 商城项目功能的讲解,让大家实际操作并实践上手一个大型的线上商城项目,并学习到一定的开发经验以及其中的开发技巧。<br /> 商城项目所涉及的功能结构图整理如下:<br /> </span> </div> <div style="color:rgba(0,0,0,.75);">   </div> <div style="color:rgba(0,0,0,.75);"> <p style="color:#4d4d4d;"> <img alt="modules" src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9uZXdiZWUtbWFsbC5vc3MtY24tYmVpamluZy5hbGl5dW5jcy5jb20vcG9zdGVyL3N0b3JlL25ld2JlZS1tYWxsLXMucG5n?x-oss-process=image/format,png" /> </p> </div> <p style="color:rgba(0,0,0,.75);"> <strong><span style="color:#e53333;">课程特色</span></strong> </p> <p style="color:rgba(0,0,0,.75);">   </p> <div style="color:rgba(0,0,0,.75);">   </div> <div style="color:rgba(0,0,0,.75);"> <ul> <li> 对新手开发者十分友好,无需复杂的操作步骤,仅需 2 秒就可以启动这个完整的商城项目 </li> <li> 最终的实战项目是一个企业级别的 Spring Boot 大型项目,对于各个阶段的 Java 开发者都是极佳的选择 </li> <li> 实践项目页面美观且实用,交互效果完美 </li> <li> 教程详细开发教程详细完整、文档资源齐全 </li> <li> 代码+讲解+演示网站全方位保证,向 Hello World 教程说拜拜 </li> <li> 技术栈新颖且知识点丰富,学习后可以提升大家对于知识的理解和掌握,可以进一步提升你的市场竞争力 </li> </ul> </div> <p style="color:rgba(0,0,0,.75);">   </p> <p style="color:rgba(0,0,0,.75);"> <span style="color:#e53333;">课程预览</span> </p> <p style="color:rgba(0,0,0,.75);">   </p> <div style="color:rgba(0,0,0,.75);">   </div> <div style="color:rgba(0,0,0,.75);"> <p style="color:#4d4d4d;"> 以下为商城项目的页面和功能展示,分别为: </p> </div> <div style="color:rgba(0,0,0,.75);"> <ul> <li> 商城首页 1<br /> <img alt="" src="https://img-bss.csdnimg.cn/202103050347585499.gif" /> </li> <li> 商城首页 2<br /> <img alt="" src="https://img-bss.csdn.net/202005181054413605.png" /> </li> <li>   </li> <li> 购物车<br /> <img alt="cart" src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9uZXdiZWUtbWFsbC5vc3MtY24tYmVpamluZy5hbGl5dW5jcy5jb20vcG9zdGVyL3Byb2R1Y3QvY2FydC5wbmc?x-oss-process=image/format,png" /> </li> <li> 订单结算<br /> <img alt="settle" src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9uZXdiZWUtbWFsbC5vc3MtY24tYmVpamluZy5hbGl5dW5jcy5jb20vcG9zdGVyL3Byb2R1Y3Qvc2V0dGxlLnBuZw?x-oss-process=image/format,png" /> </li> <li> 订单列表<br /> <img alt="orders" src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9uZXdiZWUtbWFsbC5vc3MtY24tYmVpamluZy5hbGl5dW5jcy5jb20vcG9zdGVyL3Byb2R1Y3Qvb3JkZXJzLnBuZw?x-oss-process=image/format,png" /> </li> <li> 支付页面<br /> <img alt="" src="https://img-bss.csdn.net/201909280301493716.jpg" /> </li> <li> 后台管理系统登录页<br /> <img alt="login" src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9uZXdiZWUtbWFsbC5vc3MtY24tYmVpamluZy5hbGl5dW5jcy5jb20vcG9zdGVyL3Byb2R1Y3QvbWFuYWdlLWxvZ2luLnBuZw?x-oss-process=image/format,png" /> </li> <li> 商品管理<br /> <img alt="goods" src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9uZXdiZWUtbWFsbC5vc3MtY24tYmVpamluZy5hbGl5dW5jcy5jb20vcG9zdGVyL3Byb2R1Y3QvbWFuYWdlLWdvb2RzLnBuZw?x-oss-process=image/format,png" /> </li> <li> 商品编辑<br /> <img alt="" src="https://img-bss.csdnimg.cn/202103050348242799.png" /> </li> </ul> </div> </div> </div> </div>
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页