精华内容
下载资源
问答
  • 新课限时福利 限时福利1:原价 129 元,今日特价仅需 69 元!(7月前课程更新完毕)! ... 限时福利2:购课进答疑群专享柳峰... 为什么需要掌握高性能的MySQL实战? 由于互联网产品用户量大、高...
    展开全文
  • 窗口函数

    2020-04-08 14:18:51
    什么是窗口函数1.1 窗口函数和普通聚合函数1.2 窗口函数语法1. 分区函数2. 排序函数3. 框架元素2. 窗口函数的类型2.1 排序函数2.2 偏移函数2.3 聚合函数2.4 分布函数 1. 什么是窗口函数 窗口可以理解为记录集合,...

    1. 什么是窗口函数

    窗口可以理解为记录集合,窗口函数是在满足某种条件的记录集合上执行的特殊函数。

    • 静态窗口:随着记录不同,窗口大小都是固定的
    • 动态窗口:不同的记录对应着不同的窗口

    1.1 窗口函数和普通聚合函数

    窗口函数 聚合函数
    每条记录都会执行,有几条记录执行完还是几条 多条记录聚合为一条
    每组保留多个值 每组保留一个值
    聚合函数可存在于窗口函数中

    1.2 窗口函数语法

    窗口函数的作用域位于over()子句中,窗口函数有3个元素:分区排序框架

    窗口函数的一般框架

    <窗口函数>
    
    OVER (
    
    [PARTITION BY <列清单>] 
    
    [ORDER BY <排序用清单列>] [ASC/DESC] 
    
    (ROWS | RANGE)<范围条件>
    
    )
    

    默认情况下,用于计算的一组行(“窗口”)是整个数据集,可以使用ORDER BY子句进行排序。然后使用PARTITION BY子句将窗口缩小到数据集内的特定组。

    • 函数子句:
      指明具体操作,如sum求和,first_value取第一个值;

    • partition by子句:
      指明分区字段,如果没有,则将所有数据作为一个分区;

    • order by子句:
      指明了每个分区排序的字段和方式,也是可选的,默认是按照表中的顺序;

    • 窗口子句:
      指明相对当前记录的计算范围,可以向上(preceding),可以向下(following),也可以使用between指明上下边界的值,没有的话默认为当前分区。

    滑动窗口的范围指定方式有:基于行和基于范围

    • 基于行:

      通常使用BETWEEN frame_start AND frame_end语法来表示行范围,frame_start和frame_end可以支持如下关键字,来确定不同的动态行记录
      。CURRENT ROW 边界是当前行,一般和其他范围关键字一起使用
      。UNBOUNDED PRECEDING 边界是分区中的第一行
      。UNBOUNDED FOLLOWING 边界是分区中的最后一行
      。expr PRECEDING 边界是当前行减去expr的值
      。expr FOLLOWING 边界是当前行加上expr的值

    比如,下面都是合法的范围:

    rows BETWEEN 1 PRECEDING AND 1 FOLLOWING 窗口范围是当前行、前一行、后一行一共三行记录。
    rows UNBOUNDED FOLLOWING 窗口范围是当前行到分区中的最后一行。
    rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 窗口范围是当前分区中所有行,等同于不写。

    • 基于范围:

    和基于行类似,但有些范围不是直接可以用行数来表示的,比如希望窗口范围是一周前的订单开始,截止到当前行,则无法使用rows来直接表示,此时就可以使用范围来表示窗口:INTERVAL 7 DAY PRECEDING。Linux中常见的最近1分钟、5分钟负载是一个典型的应用场景。

    有的函数不管有没有frame子句,它的窗口都是固定的,也就是静态窗口,这些函数包括如下:

    • CUME_DIST()
    • DENSE_RANK()
    • LAG()
    • LEAD()
    • NTILE()
    • PERCENT_RANK()
    • RANK()
    • ROW_NUMBER()

    1. 分区函数

    partition by语句定义,对当前计算的窗口函数进行限制,如果没有指定partition by子句,窗口就没有限制,即如果没有指定分区就是将整个查询结果当做一个分区。

    2. 排序函数

    排序元素定义计算的顺序,如果与分区有关,是对分区内排序,但对于不同的函数(排序窗口函数和聚合窗口函数)有点不同

    3. 框架元素

    框架是一个筛选器,对分区内的进一步的限制。适合于聚合函数、偏移函数等。

    2. 窗口函数的类型

    窗口函数有4种函数类型:排序函数、偏移函数、聚合函数和分布函数。

    2.1 排序函数

    • row_number(),rank(),dense_rank()
      不需要指定参数
    • ntile()
      把窗口分区里的数据行分成数量大致相等的块,块的个数由输入的参数决定

    2.2 偏移函数

    • lead(),lag()
      偏移量相对于当前的行
    • first_value(),last_value(),nth_value()
      偏移量相对于窗口的开始和结尾

    2.3 聚合函数

    • sum(),count(),avg(),min(),max()

    2.4 分布函数

    • cume_dist(),percent_rank()
    展开全文
  • PostgreSQL窗口函数

    2021-05-22 17:01:46
    1、什么是窗口函数 窗口函数在和当前行相关的一组表行上执行计算。 这相当于一个可以由聚合函数完成的计算类型。但不同于常规的聚合函数, 使用的窗口函数不会导致行被分组到一个单一的输出行;行保留其独立的身份。...

    1、什么是窗口函数

    窗口函数在和当前行相关的一组表行上执行计算。 这相当于一个可以由聚合函数完成的计算类型。但不同于常规的聚合函数, 使用的窗口函数不会导致行被分组到一个单一的输出行;行保留其独立的身份。 在后台,窗口函数能够访问的不止查询结果的当前行。

    • 可以访问与当前记录相关的多行记录;
    • 不会使多行聚集成一行, 与聚集函数的区别;

    2、窗口函数语法

    window_func() OVER(PARTITION BY [字段] ORDER BY [字段])
    

    窗口函数跟随一个OVER子句,OVER子句决定究竟查询中的哪些行被分离出来由窗口函数处理。其中分区PARTITION BY与排序ORDER BY指令可以可选的,如果不指定,则等同于聚合函数,对全部数据进行计算。

    3、窗口函数示例

    数据的准备

    --建表
    CREATE TABLE empsalary(
    	empno serial NOT null,
    	depname varchar(30),
    	salary int
    )
    --插入数据
    INSERT INTO empsalary(depname,salary) 
    values('develop',4200),
    ('develop',2700),
    ('develop',6700),
    ('develop',3700),
    ('develop',3700),
    ('personnel',4200),
    ('personnel',6500),
    ('personnel',5600),
    ('sales',7600),
    ('sales',4600),
    ('sales',1600);
    
    • 统计每个部门的最高、最低、平均、汇总工资
    SELECT e.*,avg(e.salary) over(PARTITION BY e.depname) FROM empsalary e;
    SELECT e.*,max(e.salary) over(PARTITION BY e.depname) FROM empsalary e;
    SELECT e.*,min(e.salary) over(PARTITION BY e.depname) FROM empsalary e;
    SELECT e.*,sum(e.salary) over(PARTITION BY e.depname) FROM empsalary e;
    

    将上面的语句加上排序,看下又会是什么效果

    SELECT e.*,avg(e.salary) over(PARTITION BY e.depname ORDER BY e.salary) FROM empsalary e;
    
    • 按照每个部门编号
    SELECT e.*,RANK() over(PARTITION BY e.depname ORDER BY e.salary) FROM empsalary e;
    

    通过上面的例子,可以看出使用窗口函数,来统计方便很多。如果不用窗口函数,我们就会使用group by来做一堆的处理才能达到想要的效果。

    展开全文
  • SQL窗口函数

    2021-02-23 19:23:03
    什么是窗口函数? 窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。 窗口函数的基本语法如下: <窗口函数> over (partition by <用于分组的...

    一.窗口函数有什么用?
    在日常工作中,经常会遇到需要在每组内排名,比如下面的业务需求:

    排名问题:每个部门按业绩来排名
    topN问题:找出每个部门排名前N的员工进行奖励

    面对这类需求,就需要使用sql的高级功能窗口函数了。

    二.什么是窗口函数?

    窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。

    窗口函数的基本语法如下:

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

    那么语法中的<窗口函数>都有哪些呢?

    <窗口函数>的位置,可以放以下两种函数:

    1) 专用窗口函数,包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。

    2) 聚合函数,如sum. avg, count, max, min等

    因为窗口函数是对where或者group by子句处理后的结果进行操作,所以窗口函数原则上只能写在select子句中

    三.如何使用?

    接下来,就结合实例,给大家介绍几种窗口函数的用法。

    1.专用窗口函数rank

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

    img

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

    img

    以班级“1”为例,这个班级的成绩“95”排在第1位,这个班级的“83”排在第4位。上面这个结果确实按我们的要求在每个班级内,按成绩排名了。

    得到上面结果的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(在组内排序)的作用了。

    img

    窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么,为什么还要用窗口函数呢?

    这是因为,group by分组汇总后改变了表的行数,一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。

    img

    相信通过这个例子,你已经明白了这个窗口函数的使用:
    
    select *,
       rank() over (partition by 班级
                     order by 成绩 desc) as ranking
    from 班级表
    

    现在我们说回来,为什么叫“窗口”函数呢?这是因为partition by分组后的结果称为“窗口”,这里的窗口不是我们家里的门窗,而是表示“范围”的意思。

    简单来说,窗口函数有以下功能:

    1)同时具有分组和排序的功能

    2)不减少原表的行数

    3)语法如下:

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

    2.其他专业窗口函数

    专用窗口函数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 班级表
    

    得到结果:

    img

    从上面的结果可以看出:

    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。

    这三个函数的区别如下:

    img

    最后,需要强调的一点是:在上述的这三个专用窗口函数中,函数后面的括号不需要任何参数,保持()空着就可以。

    现在,大家对窗口函数有一个基本了解了吗?

    3.聚合函数作为窗口函数

    聚和窗口函数和上面提到的专用窗口函数用法完全相同,只需要把聚合函数写在窗口函数的位置即可,但是函数后面括号里面不能为空,需要指定聚合的列名。

    我们来看一下窗口函数是聚合函数时,会出来什么结果:

    select *,
       sum(成绩) over (order by 学号) as current_sum,
       avg(成绩) over (order by 学号) as current_avg,
       count(成绩) over (order by 学号) as current_count,
       max(成绩) over (order by 学号) as current_max,
       min(成绩) over (order by 学号) as current_min
    from 班级表
    

    得到结果:

    img

    有发现什么吗?我单独用sum举个例子:

    如上图,聚合函数sum在窗口函数中,是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号,在使用sum窗口函数后的结果,是对0001,0002,0003,0004号的成绩求和,若是0005号,则结果是0001号~0005号成绩的求和,以此类推。

    不仅是sum求和,平均、计数、最大最小值,也是同理,都是针对自身记录、以及自身记录之上的所有数据进行计算,现在再结合刚才得到的结果(下图),是不是理解起来容易多了?

    img

    比如0005号后面的聚合窗口函数结果是:学号0001~0005五人成绩的总和、平均、计数及最大最小值。

    如果想要知道所有人成绩的总和、平均等聚合结果,看最后一行即可。

    这样使用窗口函数有什么用呢?

    聚合函数作为窗口函数,可以在每一行的数据里直观的看到,截止到本行数据,统计数据是多少(最大值、最小值等)。同时可以看出每一行数据,对整体统计数据的影响。

    四.注意事项

    partition子句可是省略,省略就是不指定分组,结果如下,只是按成绩由高到低进行了排序:

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

    得到结果:

    img

    但是,这就失去了窗口函数的功能,所以一般不要这么使用。
    四.总结

    1.窗口函数语法

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

    <窗口函数>的位置,可以放以下两种函数:

    1) 专用窗口函数,比如rank, dense_rank, row_number等

    2) 聚合函数,如sum. avg, count, max, min等

    2.窗口函数有以下功能:

    1)同时具有分组(partition by)和排序(order by)的功能

    2)不减少原表的行数,所以经常用来在每组内排名

    3.注意事项

    窗口函数原则上只能写在select子句中

    4.窗口函数使用场景

    1)业务需求“在每组内排名”,比如:

    排名问题:每个部门按业绩来排名
    topN问题:找出每个部门排名前N的员工进行奖励

    其他例子:https://www.cnblogs.com/DataArt/p/9961676.html

    展开全文
  • SQL 窗口函数

    2021-04-25 17:23:33
    什么是窗口函数? 窗口函数(Window Function)是SQL2003标准中定义的一项新特性,并在 SQL2011、SQL2016 中又加以完善,添加了若干处拓展。窗口函数不同于我们熟悉的普通函数和聚合函数,它为每行数据进行一次计算:...
  • 窗口函数(Window Function) SQL2003 标准中定义的一项新特性,并在 SQL2011、SQL2016 中又加以完善,添加了若干处拓展。窗口函数不同于我们熟悉的普通函数和聚合函数,它为每行数据进行一次计算:输入多行(一...
  • MySQL8.0之窗口函数

    2019-10-05 20:32:18
    MySQL8.0之窗口函数一、窗口函数简介1.1 什么是窗口函数1.2 窗口函数功能二、窗口函数语法三、窗口函数示例四、总结 一、窗口函数简介 1.1 什么是窗口函数   MySQL从8.0开始支持窗口函数,这个功能在大多数据库中...
  • 什么是窗口函数? 对数据处理的条数进行设置(窗口大小),对每一条数据都进行开窗。 over函数的用法: (1)over()函数前可以使用什么? ① lag(列名, n, 默认值) 解释:拿前第n行数据,如果第n行数据不存在,赋...
  • Mysql-窗口函数

    2020-10-16 11:26:11
    2.什么是窗口函数 窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。 窗口函数的基本语法如下: <窗口函数>over(partitionby<用于分组的...
  • mysql 8.0 窗口函数

    2021-06-11 14:20:32
    什么是窗口函数 含义:窗口函数也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据进行实时分析处理。 作用: 解决排名问题,e.g.每个(group by or partition)班级内部按成绩排名 解决TOP...
  • 文章目录1 什么是窗口函数?1.1 为什么叫“窗口”函数?1.2 窗口函数特点1.3 窗口函数应用场景1.4 执行顺序2 窗口函数语法(1)partition by 分组(2)order by 排序(3)frame_clause 窗口大小3 常用的窗口函数3.1 ...
  • Hive 的 窗口函数介绍

    2020-12-03 00:46:25
    什么是窗口函数 ? 窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。 窗口函数的基本语法如下: select count(1) over(partition by name)
  • HIVE/SQL窗口函数

    2021-02-22 18:28:14
    什么是窗口函数? 窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。 窗口函数的基本语法如下: <窗口函数> over (partition by <用于分组的...
  • hive 窗口函数,分析函数的一些理解

    千次阅读 2018-03-30 14:33:12
    先解释什么是窗口函数首先,我们要知道什么是窗口子句:需要指定一个窗口的边界,语法是这样的:ROWS betweenCURRENT ROW | UNBOUNDED PRECEDING | [num] PRECEDING AND UNBOUNDED FOLLOWING | [num] FOLLOWING| ...
  • MySQL之窗口函数

    2021-04-24 13:23:26
    目录(1)什么是窗口函数(2)聚合函数+over()函数(3)partition by子句(4)order by子句(5)序列函数(5.1)ntile()函数(5.2)row_number()函数(5.3)lag()和lead()函数(5.4)first_value()和last_value()...
  • sql中窗口函数总结

    2020-08-08 11:17:38
    什么是窗口函数: 叫OLAP(online anallytical processing)联机分析处理 基本语法: <窗口函数> over (partition by <分组的列名> order by <排序的列名>) <窗口函数>包含两种函数: 1.专用...
  • MySQL数分:窗口函数

    2020-04-16 09:53:35
    什么是窗口函数 窗口函数在和当前行相关的一组表行上执行计算。 这相当于一个可以由聚合函数完成的计算类型。但不同于常规的聚合函数, 使用的窗口函数不会导致行被分组到一个单一的输出行;行保留其独立的身份。 在...
  • 什么是窗口函数? 一个窗口函数在一系列与当前行有某种关联的表行上执行一种计算。这与一个聚集函数所完成的计算有可比之处。但是窗口函数并不会使多行被聚集成一个单独的输出行,这与通常的非窗口聚集函数不同。...
  • 2 什么是窗口函数?3 如何使用?3.1 专用窗口函数rank3.2 其他专业窗口函数3.3 聚合函数作为窗口函数4 注意事项5 总结5.1 窗口函数语法5.2 窗口函数有以下功能:5.3 注意事项5.4 窗口函数使用场景 1 窗口函数有什么...
  • 一文讲透窗口函数

    2021-03-10 00:11:30
    01|什么是窗口函数我们都知道 SQL 中的聚合函数,聚合函数顾名思义就是聚集合并的意思,是对某个范围内的数值进行聚合,聚合后的结果是一个值或是各个类别对应的值。如下所示:直接聚合得到的结...
  • SQL篇-常用窗口函数

    2021-05-08 10:54:18
    前言 在我们日常数据分析中,常常遇到要在组内排名、组内取topn等等...1、什么是窗口函数 窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),作用于一个数据集合。窗口函数的一个概念就是当前
  • 一文读懂SQL窗口函数

    2020-12-24 20:18:43
    什么是窗口函数? 窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。 语法: <窗口函数> OVER ([PARTITION BY <列名>]
  • MySQL-窗口函数-排序

    2020-11-18 00:12:47
    01-什么是窗口函数 窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。 窗口函数的基本语法: <窗口函数> over (partition by <用于分组的...
  • SQL Server中的窗口函数

    2017-12-14 15:07:00
    简介 SQL Server 2012之后对窗口函数进行了极大的加强,但对于很多开发人员来说...什么是窗口函数 窗口函数,也可以被称为OLAP函数或分析函数。理解窗口函数可以从理解聚合函数开始,我们知道聚合函数的概念,就...
  • 窗口函数(Window Function)是 SQL2003 标准中定义的一项新特性,并在 SQL2011、SQL2016 中又加以完善,添加了若干处拓展。窗口函数不同于我们熟悉的普通函数和聚合函数,它为每行数据进行一...什么是窗口函数? 窗口
  • hive窗口函数实战(2)

    2020-04-08 17:50:26
    1.什么是窗口函数 在明白窗口函数的用途之前,我们先稍微提一下聚合函数,比如sum, count等常用的聚合函数,作用是将一列中多行的值合并为一行。与之对应的是,窗口函数完成的功能是本行内运算,从而多行的运算结果...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,927
精华内容 1,170
关键字:

什么是窗口函数