首页 > 数据库 >[MySQL笔记]窗口函数

[MySQL笔记]窗口函数

时间:2024-10-23 19:21:03浏览次数:1  
标签:窗口 函数 over partition rank 笔记 MySQL order

什么是窗口函数

窗口函数(Window Function),又被叫做分析函数(Analytics Function)。
窗口函数允许用户在不显式分组查询的情况下对结果集进行分组和聚合计算。
窗口函数能够为结果集中的每一行计算类似排名、行号、百分比和移动聚合函数等值。
窗口函数原则上只能写在select子句中。
同时具有分组和排序的功能,不减少原表的行数

窗口函数语法

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

<窗口函数>的位置,可以放以下两种函数:
1) 专用窗口函数,包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
2) 聚合函数,如sum. avg, count, max, min等

PARTITION BY:你只需将它看成GROUP BY子句,但是在窗口函数中,你要写PARTITION BY
ORDER BY:ORDER BY和普通查询语句中的ORDER BY没什么不同。

窗口函数

聚合窗口函数:

SUM(): 计算总和。
AVG(): 计算平均值。
COUNT(): 计算行数或非空值的数量。
MAX(): 计算最大值。
MIN(): 计算最小值。

排名窗口函数:

ROW_NUMBER(): 为结果集中的每一行分配一个唯一的序号。
RANK(): 为结果集中的每一行分配一个排名,可以出现并列的情况。
DENSE_RANK(): 类似于 RANK(),但不留 gaps,当有并列时,顺序不跳过数字。
NTILE(n): 将结果集分割成 n 个相等的部分,并为每部分分配一个编号。

偏移窗口函数:

LEAD(value, offset, default): 返回当前行之后的某一行的值。
LAG(value, offset, default): 返回当前行之前的某一行的值。

分析窗口函数:

CUME_DIST(): 计算当前行的累积分布。
PERCENT_RANK(): 返回当前行的百分比排名。

案例

案例1、获取性别的平均GPA

如果要按性别获取平均GPA,可以使用聚合函数并运行以下查询:

SELECT Gender, AVG(GPA) as avg_gpa FROM students GROUP BY Gender

然后再将结果join到初始表,但这需要两个步骤。
但如果我们使用窗口函数,我们则可以一步到位,并得到相同的结果:

SELECT *,   AVG(GPA) OVER (PARTITION BY Gender) as avg_gpa FROM students

通过上面的查询,我们正在按性别对数据进行划分,并计算每种性别的平均GPA。然后,它将创建一个称为avg_gpa的新列,并为每行附加关联的平均GPA。

案例2、为每个班级学生按成绩排名

例如下图,是班级表中的内容

如果我们想在每个班级内按成绩排名,得到下面的结果

得到上面结果的sql语句代码如下:

select *,
   rank() over (partition by 班级
                 order by 成绩 desc) as ranking
from 班级表

我们来解释下这个sql语句里的select子句。rank是排序的函数。要求是“每个班级内按成绩排名”,这句话可以分为两部分:
1)每个班级内:按班级分组
partition by用来对表分组。在这个例子中,所以我们指定了按“班级”分组(partition by 班级)
2)按成绩排名
order by子句的功能是对分组后的结果进行排序,默认是按照升序(asc)排列。在本例中(order by 成绩 desc)是按成绩这一列排序,加了desc关键词表示降序排列。
通过下图,我们就可以理解partiition by(分组)和order by(在组内排序)的作用了。

窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么,为什么还要用窗口函数呢?
这是因为,group by分组汇总后改变了表的行数,一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。

案例3、rank()、dense_rank()、row_number()

专用窗口函数rank, dense_rank, row_number有什么区别呢?

它们的区别我举个例子,你们一下就能看懂:

select *,
   rank() over (order by 成绩 desc) as ranking,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num
from 班级表

得到结果:

从上面的结果可以看出:
rank函数:这个例子中是5位,5位,5位,8位,也就是如果有并列名次的行,会占用下一名次的位置。比如正常排名是1,2,3,4,但是现在前3名是并列的名次,结果是:1,1,1,4。
dense_rank函数:这个例子中是5位,5位,5位,6位,也就是如果有并列名次的行,不占用下一名次的位置。比如正常排名是1,2,3,4,但是现在前3名是并列的名次,结果是:1,1,1,2。
row_number函数:这个例子中是5位,6位,7位,8位,也就是不考虑并列名次的情况。比如前3名是并列的名次,排名是正常的1,2,3,4。

加行号

SELECT ROW_NUMBER() over (order by id) as rowNumber,order_master.ID from order_master limit 10;


按照部门分区,计算员工薪水在部门内的排名:

select
  department,
  staff_name,
  salary,
  row_number() over(
    partition by department
    order by
      salary desc
  ) as salary_rank
order by
  department,
  salary_rank

sum()函数

按照部门分区,获取每个员工薪水在部门内的占比。

SELECT
  department,
  staff_name,
  salary,
  round ( salary * 1.0 / sum(salary) over(partition by department), 3) AS salary_percentage 

cume_dist函数

cume_dist函数用于统计窗口分区内各个值的累计分布。即计算窗口分区内值小于等于当前值的行数占窗口内总行数的比例。返回值范围为(0,1]。

select
  object,
  object_size,
  cume_dist() over (
    partition by object
    order by
      object_size
  ) as cume_dist
from  oss-log-store

ntile函数

ntile函数用于将窗口分区内数据按照顺序分成N组。
语法:

ntile(n) over (
    [partition by partition_expression]
    [order by order_expression]
)

将指定对象中的数据分成3组。

select
  object,
  object_size,
  ntile(3) over (
    partition by object
    order by
      object_size
  ) as ntile
from  oss-log-store

rank

函数用于窗口分区内值的排名。相同值拥有相同的排名,排名不是连续的,例如有两个相同值的排名为1,则下一个值的排名为3。
按照部门分区,计算员工薪水在部门内的排名。

select
  department,
  staff_name,
  salary,
  rank() over(
    partition by department
    order by
      salary desc
  ) as salary_rank
order by
  department,
  salary_rank

first_value函数

first_value函数用于返回各个窗口分区内第一行的值。
获取目标OSS Bucket中各个对象的最小值。

select
  object,
  object_size,
  first_value(object_size) over (
    partition by object
    order by
      object_size 
     range between unbounded preceding and unbounded following
  ) as first_value
from  oss-log-store

last_value函数

last_value函数用于返回各个窗口分区内最后一行的值。同上

lag函数

lag函数用于返回窗口分区内位于当前行上方第offset行的值。
语法:

lag(x, offset, default_value) over (
   [partition by partition_expression]
   [order by order_expression]
   [frame]
)

参数说明:

  • x:列名,可以为任意数据类型。
  • offset:偏离量。如果offset为0,则返回当前行的值。
  • default_value:如果不存在指定的偏离行,则返回default_value。

按天统计网站访问UV,获取每天网站访问UV相比前一天的增长情况。

select
  day,
  UV,
  UV * 1.0 /(lag(UV, 1, 0) over()) as diff_percentage
from  (
    select
      approx_distinct(client_ip) as UV,
      date_trunc('day', __time__) as day
    from  log
    group by
      day
    order by
      day asc
  )

lead函数

函数用于返回窗口分区内位于当前行下方第offset行的值。
语法

lead(x, offset, default_value) over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

计算2021-08-26当天,当前一小时网站访问UV与后一小时的占比情况。

select
  time,
  UV,
  UV * 1.0 /(lead(UV, 1, 0) over()) as diff_percentage
from  (
    select
      approx_distinct(client_ip) as uv,
      date_trunc('hour', __time__) as time
    from    log
    group by
      time
    order by
      time asc
  )

nth_value函数

nth_value函数用于返回窗口分区中第offset行的值。
语法

nth_value(x, offset) over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

按照部门分区,统计各个部门中薪水第二高的员工。

select
  department,
  staff_name,
  salary,
  nth_value(staff_name, 2) over(
    partition by department
    order by
      salary desc
      range between unbounded preceding and unbounded following
  ) as second_highest_salary from log

参考:
https://help.aliyun.com/zh/sls/user-guide/window-functions

标签:窗口,函数,over,partition,rank,笔记,MySQL,order
From: https://www.cnblogs.com/fanfan-90/p/18496639

相关文章

  • 【算法笔记】前缀和算法原理深度剖析(超全详细版)
    【算法笔记】前缀和算法原理深度剖析(超全详细版)......
  • MySQL 存储引擎
    一、MySQL架构连接层         最上层是一些客户端和连接服务,主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为每个安全接入的用户端验证它所具有的操作权限。服务层         第二层架构主要完成大多数的核心服务功能,如SQL接口,并......
  • 七月在线公开课笔记-六-
    七月在线公开课笔记(六)【七月在线】机器学习就业训练营16期-P12:在线直播:3-图像与文本基础_ev-IT自学网100-BV1Z9T5ewEKL呃各位同学大家晚上好,然后我们今天呢就给大家讲解,我们的文本和图像基础啊,嗯这个呢就是很多同学比较关心,因为我们现在很多的一个呃岗位呢,上网工程师的岗......
  • 七月在线公开课笔记-九-
    七月在线公开课笔记(九)【七月在线】机器学习就业训练营16期-P8:在线直播:8-XGBoost精讲_ev-IT自学网100-BV1Z9T5ewEKL嗯如果没有问题的话,我们就准备开始好吧,额按照咱们这个课程安排啊,今天呢我们要介绍的是超级boost模型呃,这个模型呢其实我们从第一次上课的时候,就介绍到了这......
  • 七月在线公开课笔记-二十一-
    七月在线公开课笔记(二十一)人工智能—推荐系统公开课(七月在线出品)-P16:快速入门推荐系统串讲-七月在线-julyedu-BV1Ry4y127CV今天跟大家分享的是深入浅出推荐系统啊,然后我们会围绕着推荐系统,它的核心内容呃,想召回排序重排,这些核心模块进行展开介绍,那首先做下自我介绍。我......
  • 七月在线公开课笔记-二十五-
    七月在线公开课笔记(二十五)人工智能—机器学习公开课(七月在线出品)-P11:机器学习项目实施方法论-七月在线-julyedu-BV1W5411n7fgOkay。嗯,时间应该到了哈,那这样的话我们就正式开始好吗?没有问题的话,我们就开始,好吧。啊,是这样,这个非常高兴啊能够有机会呃。......
  • 七月在线公开课笔记-二十四-
    七月在线公开课笔记(二十四)人工智能—机器学习中的数学(七月在线出品)-P18:随机梯度下降法的困难与变种-七月在线-julyedu-BV1Vo4y1o7t1我们稍微再简介一下我后面这个部分的内容,这部分当然可能更深入一些。大家我们去年有一个公开公开课,就专门讲这个大家也可以找一找叫做这个......
  • 七月在线公开课笔记-二十三-
    七月在线公开课笔记(二十三)人工智能—机器学习中的数学(七月在线出品)-P1:Taylor展式与拟牛顿-七月在线-julyedu-BV1Vo4y1o7t1这次我们探讨它的展示与它的相关应用,如米牛顿。我们首先给出塔的展示的本身的,它的定义,它的展示的公式的本身。然后我们利用它来计算某一些函数的近似......
  • 七月在线公开课笔记-二十七-
    七月在线公开课笔记(二十七)人工智能—机器学习公开课(七月在线出品)-P25:【公开课】数据挖掘与机器学习基础-七月在线-julyedu-BV1W5411n7fg可以是吧?好,那么我们稍等一下啊,稍等一下我们。在8点钟我们就准时开始我们的一个直播的内容。对。那么各位同学之前有过这个积极学习和深......
  • 七月在线公开课笔记-二十六-
    七月在线公开课笔记(二十六)人工智能—机器学习公开课(七月在线出品)-P18:世界杯数据分析案例-七月在线-julyedu-BV1W5411n7fg然后我们来做个分析吧,所以大家喜欢做一些比赛的分析,对吧?然后大家最关注的当然是决赛半决赛啊,可能也多多分析一下,分析到4分之1决赛对吧?好,所以然刚才已......