精华内容
下载资源
问答
  • 窗口函数

    千次阅读 2019-05-20 11:47:31
    窗口函数也称为OLAP函数,意思是对数据库数据进行实时分析处理。能进行排序并生成序列号。 窗口函数分为:1. sum(),avg(),count(),max(),min()等一般聚合函数 2.rank(),dense_rank(),row_number(),ntile()...

    窗口函数也称为OLAP函数,意思是对数据库数据进行实时分析处理。能进行排序并生成序列号。

    窗口函数分为:1. sum(),avg(),count(),max(),min()等一般聚合函数

                              2.rank(),dense_rank(),row_number(),ntile(),first_value(),last_value(),lag(),lead(),cume_dist(),    percent_rank()

    窗口函数使用语法:fun() OVER([PARTITION BY] col [ORDER BY col])

    窗口函数的应用场景:1. 分区排名  2.动态groupby  3.Top N  4.累计查询   5.层次查询

    WINDOW子句

    • 用于从分区中选择指定的多条记录,供窗口函数处理。Hive 提供了两种定义窗口帧的形式:ROWS 和 RANGE。
    • 对于SELECT *, SUM() OVER (PARTITION BY col1 ORDER BY col2) from t: 1.没指定window子句,指定order by,返回起始行到当前行的累加值;2. 没指定window字句,没指定order by字句,返回所有行的累加值;3)指定ROWS BETWEEN window字句,没指定order by,按ROWS BETWEEN执行
    • PRECEDING:往前
      FOLLOWING:往后
      CURRENT ROW:当前行
      UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED FOLLOWING:表示到后面的终点
    • 例如:ROWS BETWEEN 3 PERCEDING AND 1 FOLLOWING表示当前行+往前3行+往后1行                                                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示从起点到当前行

    RANK(),DENSE_RANK(),ROW_NUMBER()

    • RANK()得到数据项在分组中的排名,排名相等的时候会留下空位:1,2,2,4,...
    • DENSE_RANK()得到数据项在分组中的排名,排名相等的时候不会留下空位:1,2,2,3,...
    • ROW_NUMBER()按照分组中的顺序生成序列,不存在重复的序列:1,2,3,4...

    NTILE()

    • NTILE(N)用于将分组数据按照顺序切分成N片,返回当前切片值,不支持ROWS_BETWEE

    FIRST_VALUE(),LAST_VALUE()

    • FIRST_VALUE(col)取分组排序后,截止到当前行,分组内第一个值
    • LAST_VALUE(col)取分组排序后,截止到当前行,分组内的最后一个值
    • 不支持WINDOW字句

    LAG()、LEAD()

    • LAG(col,n,DEFAULT) 统计窗口内往上第n行值。第一个参数为列名,第二个参数为往上第n行(可选,默认为1),第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)
    • LEAD(col,n,DEFAULT) 统计窗口内往下第n行值。第一个参数为列名,第二个参数为往下第n行(可选,默认为1),第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL)
    • 不支持WINDOW字句

    CUME_DIST(),PERCENT_RANK()

    • CUME_DIST()返回(小于等于当前行值的行数)/(当前分组内的总行数)
    • 应用场景:比如,统计小于等于当前薪水的人数,所占总人数的比例
    • PERCENT_RANK()返回(组内当前行的rank值-1)/(分组内做总行数-1)
    • 不支持WINDOW字句

     

    注:https://blog.csdn.net/huangyinzhao/article/details/80507967                                                                                                             https://blog.csdn.net/weixin_38750084/article/details/82779910

    展开全文
  • MySQL - SQL窗口函数

    千次阅读 多人点赞 2019-11-25 13:08:19
    窗口函数解决的问题包括:1)排名问题2)top N问题 应用工作中, 面试中. 2.学习/操作 //注意,mysql版本8已至此窗口函数这个功能, 如果低于该版本, 会出现SQL报错! 一.窗口函数有什么用...

    1.应用场景

    窗口函数解决的问题包括:

    1)排名问题 

    2)top N问题

    应用工作中, 面试中.

    2.学习/操作

    前言

    MySQL版本8已支持窗口函数这个功能, 如果低于该版本, 会出现SQL报错!

    一.窗口函数有什么用?

    在日常工作中,经常会遇到需要在每组内排名,比如下面的业务需求:

    排名问题:每个部门按业绩来排名

    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

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

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

    以班级“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(在组内排序)的作用了。

    窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。

    那么,为什么还要用窗口函数呢?

    这是因为,group by分组汇总后改变了表的行数,一行只有一个类别。

    而partiition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。

    相信通过这个例子,你已经明白了这个窗口函数的使用:

    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 班级表

    得到结果:

    从上面的结果可以看出:

    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。

    这三个函数的区别如下:

    最后,需要强调的一点是:

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

    Note

    写过一些代码的我们, 都知道函数, 后面都是有个括号的.

    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 班级表;

    得到结果:

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

    如上图,聚合函数sum在窗口函数中,是对自身记录、及位于自身记录以上的数据进行求和的结果。

    比如0004号,在使用sum窗口函数后的结果,是对0001,0002,0003,0004号的成绩求和,若是0005号,则结果是0001号~0005号成绩的求和,以此类推。

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

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

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

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

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

    四. 注意事项

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

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

    得到结果:

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

    五. 总结

    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的员工进行奖励

    下一次会跟大家分享一些窗口函数的面试题,从而让各位在面试、工作中都能遇到这类问题,就想到哦,这用窗口函数就可以解决。

    后续补充

    ....

    3.问题/补充

    TBD

    4.参考

    https://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==&mid=2649247566&idx=1&sn=f9c7018c299498673b38221db2ecd5cd // 通俗易懂的学会:SQL窗口函数

    https://leetcode-cn.com/problems/rank-scores/solution/tu-jie-sqlmian-shi-ti-jing-dian-pai-ming-wen-ti-by/  // leetcode 178. 分数排名

    后续补充

    ...

    展开全文
  • SQL ——窗口函数简介

    万次阅读 2018-11-28 22:23:20
    1、窗口函数的描述 2、窗口函数中的元素 2.1、分区 2.2、排序 2.3、框架 3、支持窗口函数的查询元素 4、窗口函数类别 4.1、排名函数 4.2、分布函数 4.3、偏移函数   1、窗口函数的描述 窗口函数作用于...

    目录

    1、窗口函数的描述

    2、窗口函数中的元素

    2.1、分区

    2.2、排序

    2.3、框架

    3、支持窗口函数的查询元素

    4、窗口函数类别

    4.1、排名函数

    4.2、分布函数

    4.3、偏移函数


     

    1、窗口函数的描述

    窗口函数作用于一个数据行集合。窗口是标准的SQL术语,用来描述SQL语句内OVER子句划定的内容,这个内容就是窗口函数的作用域。而在OVER子句中,定义了窗口所覆盖的与当前行相关的数据行集、行的排序及其他的相关元素。

    标准SQL对窗口函数的第一次支持是在SQL:1999的扩展文档中,当时,它们称为“OLAP”函数。从那以后,每次标准版本的修订都会增强对窗口函数的支持,直到现在的SQL:2003、SQL:2008、SQL:2011。最新的SQL标准版本,已经有了非常丰富和全面的窗口函数,显示出标准委员会对这一概念的坚定,以及从更多窗口函数和更多功能两个方面持续增强支持标准。

    2、窗口函数中的元素

    窗口函数的行为描述出现在函数的OVER子句中,并涉及多个元素。3个核心元素分别是分区、排序和框架。不是所有的窗口函数都支持这3个元素。

    2.1、分区

    分区元素是由PARTITION BY子句定义,并被所有的窗口函数支持。他对当前计算的窗口函数进行限制,仅仅那些在结果集的分区列中与当前行有相同值的行才能进入窗口。如果没有指定PARTITION BY子句,窗口就没有限制。换种说法就是:如果没有显示指定分区,则默认分区就是把整个查询结果集当作一个分区。有一点不太明显,这里提出来:同一个查询中的不同函数,可能会有不同的分区描述。

    2.2、排序

    排序元素定义计算的顺序,如果与分区有关,则是在分区内的顺序。在标准的SQL中,所有函数都支持排序元素。起初SQL SERVER不支持聚合函数中的排序元素,而仅仅支持分区。对聚合函数红排序的支持,是从SQL SERVER 2012 开始的。

    有趣的是,针对不同的函数类别,排序元素有轻微的不同意义。对于排名函数,排序是直观的。而聚合窗口函数的排序和排名窗口的排序略有意义上的不同。在聚合中,与某些人认为的相反,排序与聚合的顺序无关;然而,排序元素为下面将描述的框架选项赋予一定的含义,换句话说,排序元素帮助限定在窗口内的行。

    2.3、框架

    从本质上来说,框架是一个在分区内对行进行进一步限制的筛选器。它适用于聚合窗口函数,也适用于三个偏移函数:FIRST_VALUE、LAST_VALUE、NTH_VALUE。把这个窗口元素想成是基于给定的排序,在当前行所在分区中定义两个点,这两个点形成的框架之间的行才会参与计算。

    在标准的框架描述中,包含一个ROWS或RANGE选项,用来定义框架的开始行和结束行,这两行也可以形成“框架外”(框架内的行被排除在计算外)窗口选项。SQL SERVER 2012 开始支持框架,完全实现ROWS选项,部分实现RANGE选项,尚未实现“框架外”窗口选项。

    ROWS选项允许我们用相对当前行的偏移行数来指定框架的起点和终点。RANGE选项更具灵活性,可以以框架起终点的值于当前行的值的差异来定义偏移行数。“框架外”窗口选项用来定义如何对当前行及具有相同值的行进行处置。

     

    3、支持窗口函数的查询元素

    并不是所有的查询子句都支持窗口函数,相反,仅仅SELECT和ORDER BY 子句支持窗口函数。为帮助理解,我们先看看SQL不同子句的执行顺序:

    1、FROM

    2、WHERE

    3、GROUP BY

    4、HAVING

    5、SELECT

     5.1、Evalute Expressions(判断表达式)

     5.2、删除重复数据

    6、ORDER BY

    7、OFFSET-FETCH/TOP

     

    只有SELECT和ORDER BY 子句直接支持窗口函数。做这个限制的原因是为了避免二义性,因此把(几乎是)查询的最终结果当作窗口的起点。如果窗口函数可以早于SELECT阶段出现,那么通过一些查询表单会无法得到正确的结果。

     

    4、窗口函数类别

    窗口函数分为排名函数、分布函数、偏移函数等。

    4.1、排名函数

    SQL标准支持4种用于排名计算的窗口函数。它们是:ROW_NUMBER、NTILE、RANK和DENSE_RANK。在标准中,前两者是一类,后两者是另一类。

    ROW_NUMBER函数根据指定的顺序,从1开始计算连续的行号。

    NTILE函数把窗口分区里的数据行分成数量大致相等的块(根据输入的块数和指定的窗口排序)。

    注意:为避免有人认为分块和分页相似,这里提醒大家,不要把二者混淆起来。在分页中,页的大小是常量,并且页码是动态的——查询结果集除以页面大小后得到的数值。而分开中,块的数量是常量,块的大小是动态的——行的数量除以设定的块的数量后得到的数值。分页在何处使用是很明显的,分块通常用于分析目的——那些需要涉及基于某种衡量顺序,把数据分到预先设定的固定数量、相同大小的桶(bucket)中。

    RANK(排名)和DENSE_RANK(密集排名)函数的计算和ROW_NUMBER函数类似,唯一不同的是,它们在窗口分区内生成的值不必是唯一的。当窗口排序方向是升序时,RANK函数计算分区内排序值比当前行小的行的数量,在次数量上加1,就是当前行的排名;DENSE_RANK函数计算分区内相异的(distinct)排序值比当前行小的行的数量,在此数量上加1,就是当前行的排名。当窗口排序方向是降序时,RANK函数计算分区内排序属性比当前行大的行的数量,在此数量上加1,就是当前行的排名;DENSE_RANK函数计算分区内相异的(distinct)排序值比当前行大的行的数量,在此数量上加1,就是当前行的排名。

     

    4.2、分布函数

    窗口分布函数主要为静态统计服务提供数据的分布情况。SQL Server 2012引入了两种窗口分布函数的支持:排名分布函数和逆分布函数。排名分布函数有两种:PERCENT_RANK(百分位排名)和CUME_DIST(累积分布),逆分布函数也有两个:PERCENT_CONT(百分位连续)和PERCENTILE_DISC(百分位离散)。

    根据标准SQL,分布函数计算数据行在窗口分区中的相对排名,把它表达成介于0~1之间的比值——我们大多数人把它看做百分比。

    假设rk 为数据行的RANK值,RANK函数的窗口描述与分布函数的窗口描述相同。假设nr为窗口分区内数据行的行数。假设np为领先或与当前行的排序值相同的行的数目(为比当前rk减1大的最小rk值,如果当前的rk为最大值,np则等于nr)。

    PERCENT_RANK(百分位排名)计算公式如下:(rk-1)/(nr-1),PERCENT_RANK(百分位排名)的计算公式如下:np/nr。

    逆分布函数,通常叫百分位,我们可以把它执行的计算当作是排名分布函数的倒数。

    PERCENTILE_DISC(百分位离散)函数(DISC为离散分布模型)返回组中第一个符合条件的值,条件是:其累计分布(CUME_DIST函数)大于等于输入值。

    PERCENT_CONT(百分位连续)函数(CONT为连续分布函数)比较难理解(PERCENT_CONT(@pct) WITHIN GROUP(ORDER BY 分数))。举个简单示例,想象对一行数为偶数的组进行中值计算。我们需要内插值来支持连续分布假定,插入的值落在两个中间点的中间,意味着它是两个中间点的平均值。

     

    4.3、偏移函数

    窗口偏移函数包括两种类型的函数。一种是偏移量是相对于当前行的,这个类别的包括LAG和LEAD函数;另一个类别函数的偏移量是相对于窗口框架的开始和结尾的,这个类别包括FIRST_VALUE、LAST_VALUE和NTH_VALUE。第一类别中的函数(LAG和LEAD)支持窗口分区子句以及窗口排序子句。当然,后者的存在赋予偏移量以逻辑意义。第二类别中的函数(FIRST_VALUE、LAST_VALUE和NTH_VALUE)在支持窗口分区子句和排序子句的基础上,还支持窗口框架子句。

    LAG和LEAD函数允许我们从窗口分区中,根据给定的相对于当前行的前偏移量(LAG)和后偏移量(LEAD),返回对应行的值。如果没有指定,偏移量默认为1。

    FIRST_VALUE和FIRST_VALUE分别返回框架的第一行和最后一行所有查询的值。NTH_VALUE函数作用是中的相对窗口框架第一行或最后一行的偏移量,使得我们可以取得对应这个偏移量的记录值。

     

    PS:1、最近看了关于SQL窗口函数也可作为T-SQL性能优化方案的相关书籍,于是整理了下相关概念。

             2、此篇提到的知识针对SQL SERVER  2012中有的窗口函数。

             3、此篇主要是为了整理窗口函数在T-SQL中的应用案例而做的准备工作。       

    展开全文
  • Hive:窗口函数

    万次阅读 多人点赞 2018-09-20 00:01:35
    本文主要介绍hive中的窗口函数.hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于olap分析(在线分析处理)。 概念 我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、...

    简介

    本文主要介绍hive中的窗口函数.hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于olap分析(在线分析处理)。

    概念

    我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的.但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数.

    在深入研究Over字句之前,一定要注意:在SQL处理中,窗口函数都是最后一步执行,而且仅位于Order by字句之前。

    数据准备

    我们准备一张order表,字段分别为name,orderdate,cost.数据内容如下:

    jack,2015-01-01,10
    tony,2015-01-02,15
    jack,2015-02-03,23
    tony,2015-01-04,29
    jack,2015-01-05,46
    jack,2015-04-06,42
    tony,2015-01-07,50
    jack,2015-01-08,55
    mart,2015-04-08,62
    mart,2015-04-09,68
    neil,2015-05-10,12
    mart,2015-04-11,75
    neil,2015-06-12,80
    mart,2015-04-13,94

    在hive中建立一张表t_window,将数据插入进去.

    实例

    聚合函数+over

    假如说我们想要查询在2015年4月份购买过的顾客及总人数,我们便可以使用窗口函数去去实现

    select name,count(*) over ()
    from t_window
    where substring(orderdate,1,7) = '2015-04'

    得到的结果如下:

    name    count_window_0
    mart    5
    mart    5
    mart    5
    mart    5
    jack    5

    可见其实在2015年4月一共有5次购买记录,mart购买了4次,jack购买了1次.事实上,大多数情况下,我们是只看去重后的结果的.针对于这种情况,我们有两种实现方式

    第一种:distinct

    select distinct name,count(*) over ()
    from t_window
    where substring(orderdate,1,7) = '2015-04'

    第二种:group by

    select name,count(*) over ()
    from t_window
    where substring(orderdate,1,7) = '2015-04'
    group by name

    执行后的结果如下: 
    name count_window_0 
    mart 2 
    jack 2

    partition by子句

    Over子句之后第一个提到的就是Partition By.Partition By子句也可以称为查询分区子句,非常类似于Group By,都是将数据按照边界值分组,而Over之前的函数在每一个分组之内进行,如果超出了分组,则函数会重新计算.

    实例

    我们想要去看顾客的购买明细及月购买总额,可以执行如下的sql

    select name,orderdate,cost,sum(cost) over(partition by month(orderdate))
    from t_window

    执行结果如下:

    name    orderdate   cost    sum_window_0
    jack    2015-01-01  10  205
    jack    2015-01-08  55  205
    tony    2015-01-07  50  205
    jack    2015-01-05  46  205
    tony    2015-01-04  29  205
    tony    2015-01-02  15  205
    jack    2015-02-03  23  23
    mart    2015-04-13  94  341
    jack    2015-04-06  42  341
    mart    2015-04-11  75  341
    mart    2015-04-09  68  341
    mart    2015-04-08  62  341
    neil    2015-05-10  12  12
    neil    2015-06-12  80  80

    可以看出数据已经按照月进行汇总了.

    order by子句

    上述的场景,假如我们想要将cost按照月进行累加.这时我们引入order by子句.

    order by子句会让输入的数据强制排序(文章前面提到过,窗口函数是SQL语句最后执行的函数,因此可以把SQL结果集想象成输入数据)。Order By子句对于诸如Row_Number(),Lead(),LAG()等函数是必须的,因为如果数据无序,这些函数的结果就没有任何意义。因此如果有了Order By子句,则Count(),Min()等计算出来的结果就没有任何意义。

    我们在上面的代码中加入order by

    select name,orderdate,cost,sum(cost) over(partition by month(orderdate) order by orderdate )
    from t_window

    得到的结果如下:(order by默认情况下聚合从起始行到当前行的数据)

    name    orderdate   cost    sum_window_0
    jack    2015-01-01  10  10
    tony    2015-01-02  15  25 //10+15
    tony    2015-01-04  29  54 //10+15+29
    jack    2015-01-05  46  100 //10+15+29+46
    tony    2015-01-07  50  150
    jack    2015-01-08  55  205
    jack    2015-02-03  23  23
    jack    2015-04-06  42  42
    mart    2015-04-08  62  104
    mart    2015-04-09  68  172
    mart    2015-04-11  75  247
    mart    2015-04-13  94  341
    neil    2015-05-10  12  12
    neil    2015-06-12  80  80

     

     

    window子句

    我们在上面已经通过使用partition by子句将数据进行了分组的处理.如果我们想要更细粒度的划分,我们就要引入window子句了.

    我们首先要理解两个概念: 
    - 如果只使用partition by子句,未指定order by的话,我们的聚合是分组内的聚合. 
    - 使用了order by子句,未使用window子句的情况下,默认从起点到当前行.

    当同一个select查询中存在多个窗口函数时,他们相互之间是没有影响的.每个窗口函数应用自己的规则.

    window子句: 
    - PRECEDING:往前 
    - FOLLOWING:往后 
    - CURRENT ROW:当前行 
    - UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED FOLLOWING:表示到后面的终点

    我们按照name进行分区,按照购物时间进行排序,做cost的累加. 
    如下我们结合使用window子句进行查询

    select name,orderdate,cost,
    sum(cost) over() as sample1,--所有行相加
    sum(cost) over(partition by name) as sample2,--按name分组,组内数据相加
    sum(cost) over(partition by name order by orderdate) as sample3,--按name分组,组内数据累加
    sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row )  as sample4 ,--和sample3一样,由起点到当前行的聚合
    sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING   and current row) as sample5, --当前行和前面一行做聚合
    sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING   AND 1 FOLLOWING  ) as sample6,--当前行和前边一行及后面一行
    sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
    from t_window;

    得到查询结果如下:

    name    orderdate   cost    sample1 sample2 sample3 sample4 sample5 sample6 sample7
    jack    2015-01-01  10  661 176 10  10  10  56  176
    jack    2015-01-05  46  661 176 56  56  56  111 166
    jack    2015-01-08  55  661 176 111 111 101 124 120
    jack    2015-02-03  23  661 176 134 134 78  120 65
    jack    2015-04-06  42  661 176 176 176 65  65  42
    mart    2015-04-08  62  661 299 62  62  62  130 299
    mart    2015-04-09  68  661 299 130 130 130 205 237
    mart    2015-04-11  75  661 299 205 205 143 237 169
    mart    2015-04-13  94  661 299 299 299 169 169 94
    neil    2015-05-10  12  661 92  12  12  12  92  92
    neil    2015-06-12  80  661 92  92  92  92  92  80
    tony    2015-01-02  15  661 94  15  15  15  44  94
    tony    2015-01-04  29  661 94  44  44  44  94  79
    tony    2015-01-07  50  661 94  94  94  79  79  50

    窗口函数中的序列函数

    主要序列函数是不支持window子句的.

    hive中常用的序列函数有下面几个:

    NTILE

    • NTILE(n),用于将分组数据按照顺序切分成n片,返回当前切片值

    • NTILE不支持ROWS BETWEEN, 
      比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

    • 如果切片不均匀,默认增加第一个切片的分布

    这个函数用什么应用场景呢?假如我们想要每位顾客购买金额前1/3的交易记录,我们便可以使用这个函数.

    select name,orderdate,cost,
           ntile(3) over() as sample1 , --全局数据切片
           ntile(3) over(partition by name), -- 按照name进行分组,在分组内将数据切成3份
           ntile(3) over(order by cost),--全局按照cost升序排列,数据切成3份
           ntile(3) over(partition by name order by cost ) --按照name分组,在分组内按照cost升序排列,数据切成3份
    from t_window

    得到的数据如下:

    name    orderdate   cost    sample1 sample2 sample3 sample4
    jack    2015-01-01  10  3   1   1   1
    jack    2015-02-03  23  3   1   1   1
    jack    2015-04-06  42  2   2   2   2
    jack    2015-01-05  46  2   2   2   2
    jack    2015-01-08  55  2   3   2   3
    mart    2015-04-08  62  2   1   2   1
    mart    2015-04-09  68  1   2   3   1
    mart    2015-04-11  75  1   3   3   2
    mart    2015-04-13  94  1   1   3   3
    neil    2015-05-10  12  1   2   1   1
    neil    2015-06-12  80  1   1   3   2
    tony    2015-01-02  15  3   2   1   1
    tony    2015-01-04  29  3   3   1   2
    tony    2015-01-07  50  2   1   2   3

    如上述数据,我们去sample4 = 1的那部分数据就是我们要的结果

    row_number

    rank

    dense_rank

    这三个窗口函数的使用场景非常多 
    - row_number()从1开始,按照顺序,生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列 
    - RANK() 生成数据项在分组中的排名,排名相等会在名次中留下空位 
    - DENSE_RANK() 生成数据项在分组中的排名,排名相等会在名次中不会留下空位

    **注意: 
    rank和dense_rank的区别在于排名相等时会不会留下空位.**

    举例如下:

    SELECT 
    cookieid,
    createtime,
    pv,
    RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
    DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
    ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 
    FROM lxw1234 
    WHERE cookieid = 'cookie1';
    
    cookieid day           pv       rn1     rn2     rn3 
    
    cookie1 2015-04-12      7       1       1       1
    cookie1 2015-04-11      5       2       2       2
    cookie1 2015-04-15      4       3       3       3
    cookie1 2015-04-16      4       3       3       4
    cookie1 2015-04-13      3       5       4       5
    cookie1 2015-04-14      2       6       5       6
    cookie1 2015-04-10      1       7       6       7
    rn1: 15号和16号并列第3, 13号排第5
    rn2: 15号和16号并列第3, 13号排第4
    rn3: 如果相等,则按记录值排序,生成唯一的次序,如果所有记录值都相等,或许会随机排吧。

    LAG和LEAD函数

    这两个函数为常用的窗口函数,可以返回上下数据行的数据. 
    以我们的订单表为例,假如我们想要查看顾客上次的购买时间可以这样去查询

    select name,orderdate,cost,
    lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1,
    lag(orderdate,2) over (partition by name order by orderdate) as time2
    from t_window;

    查询后的数据为:

    name    orderdate   cost    time1   time2
    jack    2015-01-01  10  1900-01-01  NULL
    jack    2015-01-05  46  2015-01-01  NULL
    jack    2015-01-08  55  2015-01-05  2015-01-01
    jack    2015-02-03  23  2015-01-08  2015-01-05
    jack    2015-04-06  42  2015-02-03  2015-01-08
    mart    2015-04-08  62  1900-01-01  NULL
    mart    2015-04-09  68  2015-04-08  NULL
    mart    2015-04-11  75  2015-04-09  2015-04-08
    mart    2015-04-13  94  2015-04-11  2015-04-09
    neil    2015-05-10  12  1900-01-01  NULL
    neil    2015-06-12  80  2015-05-10  NULL
    tony    2015-01-02  15  1900-01-01  NULL
    tony    2015-01-04  29  2015-01-02  NULL
    tony    2015-01-07  50  2015-01-04  2015-01-02

    time1取的为按照name进行分组,分组内升序排列,取上一行数据的值,见下图。

    time2取的为按照name进行分组,分组内升序排列,取上面2行的数据的值,注意当lag函数未设置行数值时,默认为1行.设定取不到时的默认值时,取null值.

    lead函数与lag函数方向相反,取向下的数据.

     

    first_value和last_value

    first_value取分组内排序后,截止到当前行,第一个值 
    last_value取分组内排序后,截止到当前行,最后一个值

    select name,orderdate,cost,
    first_value(orderdate) over(partition by name order by orderdate) as time1,
    last_value(orderdate) over(partition by name order by orderdate) as time2
    from t_window

    查询结果如下:

    name    orderdate   cost    time1   time2
    jack    2015-01-01  10  2015-01-01  2015-01-01
    jack    2015-01-05  46  2015-01-01  2015-01-05
    jack    2015-01-08  55  2015-01-01  2015-01-08
    jack    2015-02-03  23  2015-01-01  2015-02-03
    jack    2015-04-06  42  2015-01-01  2015-04-06
    mart    2015-04-08  62  2015-04-08  2015-04-08
    mart    2015-04-09  68  2015-04-08  2015-04-09
    mart    2015-04-11  75  2015-04-08  2015-04-11
    mart    2015-04-13  94  2015-04-08  2015-04-13
    neil    2015-05-10  12  2015-05-10  2015-05-10
    neil    2015-06-12  80  2015-05-10  2015-06-12
    tony    2015-01-02  15  2015-01-02  2015-01-02
    tony    2015-01-04  29  2015-01-02  2015-01-04
    tony    2015-01-07  50  2015-01-02  2015-01-07

    原文参考:https://blog.csdn.net/qq_26937525/article/details/54925827 

     

    扩展:

    row_number的用途非常广泛,排序最好用它,它会为查询出来的每一行记录生成一个序号,依次排序且不会重复,注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。

    rank函数用于返回结果集的分区内每行的排名,行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名,与row_number函数不同的是,rank函数考虑到了over子句中排序字段值相同的情况,如果使用rank函数来生成序号,over子句中排序字段值相同的序号是一样的,后面字段值不相同的序号将跳过相同的排名号排下一个,也就是相关行之前的排名数加一,可以理解为根据当前的记录数生成序号,后面的记录依此类推。

    dense_rank函数的功能与rank函数类似,dense_rank函数在生成序号时是连续的,而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时,将不跳过相同排名号,rank值紧接上一次的rank值。在各个分组内,rank()是跳跃排序,有两个第一名时接下来就是第四名,dense_rank()是连续排序,有两个第一名时仍然跟着第二名。

    借助实例能更直观地理解:

    假设现在有一张学生表student,学生表中有姓名、分数、课程编号。

    select * from student;

     现在需要按照课程对学生的成绩进行排序:

    --row_number() 顺序排序
    select name,course,row_number() over(partition by course order by score desc) rank from student;

    --rank() 跳跃排序,如果有两个第一级别时,接下来是第三级别
    select name,course,rank() over(partition by course order by score desc) rank from student;

    --dense_rank() 连续排序,如果有两个第一级别时,接下来是第二级别 
    select name,course,dense_rank() over(partition by course order by score desc) rank from student;

    取得每门课程的第一名:

    --每门课程第一名只取一个: 
    select * from (select name,course,row_number() over(partition by course order by score desc) rank from student) where rank=1;
    --每门课程第一名取所有: 
    select * from (select name,course,dense_rank() over(partition by course order by score desc) rank from student) where rank=1;
    --每门课程第一名取所有:
    select * from (select name,course,rank() over(partition by course order by score desc) rank from student) where rank=1;

      附:每门课程第一名取所有的其他方法(使用group by 而不是partition by):

    select s.* from student s
      inner join(select course,max(score) as score from student group by course) c
      on s.course=c.course and s.score=c.score; 
    --或者使用using关键字简化连接
    select * from student s
      inner join(select course,max(score) as score from student group by course) c
      using(course,score);

    关于Parttion by:

      Parttion by关键字是Oracle中分析性函数的一部分,用于给结果集进行分区。它和聚合函数Group by不同的地方在于它只是将原始数据进行名次排列,能够返回一个分组中的多条记录(记录数不变),而Group by是对原始数据进行聚合统计,一般只有一条反映统计值的结果(每组返回一条)。

      TIPS:

      使用rank over()的时候,空值是最大的,如果排序字段为null, 可能造成null字段排在最前面,影响排序结果。

      可以这样: rank over(partition by course order by score desc nulls last)

    总结:

      在使用排名函数的时候需要注意以下三点:

      1、排名函数必须有 OVER 子句。

      2、排名函数必须有包含 ORDER BY 的 OVER 子句。

      3、分组内从1开始排序。

    参考:https://www.cnblogs.com/qiuting/p/7880500.html

    展开全文
  • MySQL窗口函数简介

    万次阅读 2019-01-13 21:25:54
    原文地址:https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_last-value 译文: 12.21.1Window Function Descriptions ...大多数聚合函数也可以用作窗口函数,...
  • SQL——窗口函数汇总

    千次阅读 2021-02-25 16:27:30
    SQL——窗口函数
  • 窗口函数的基本使用方法

    千次阅读 多人点赞 2019-07-08 12:16:18
    窗口函数简介 窗口函数又名开窗函数,属于分析函数的一种。用于解决复杂报表统计需求的功能强大的函数。窗口函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组...
  • Flink SQL中的窗口函数

    千次阅读 2020-11-25 20:08:43
    Flink窗口函数是flink的重要特性,而Flink SQL API是Flink批流一体的封装,学习明白本节课,是对Flink学习的很大收益! 窗口函数 窗口函数Flink SQL支持基于无限大窗口的聚合(无需在SQL Query中,显式定义任何...
  • MySQL中的窗口函数

    万次阅读 多人点赞 2018-11-06 17:02:14
    从version 8.0开始,MySQL支持在查询中使用窗口函数。这篇文章是对一篇英文资料的不完全翻译,加上自己的一些理解。如果有兴趣可以去看看原文章。文中的示例用到的建表语句和插值语句如下: CREATE TABLE sales( ...
  • SQL_窗口函数

    千次阅读 2020-03-19 22:22:31
    1.窗口函数与分析函数 看到一篇写的很好的总结了,就把链接附上把,我们主要是结合一些题目看一下具体的用法(https://blog.csdn.net/scgaliguodong123_/article/details/60135385) 1.1 窗口函数 FIRST_VALUE:取...
  • postgresql系列之窗口函数

    千次阅读 2018-11-11 21:39:42
    一、 窗口函数 1.1 基本概念 窗口函数可以进行排序、生成序列号等一般的聚合函数无法实现的高级操作;聚合函数将结果集进行计算并且通常返回一行。窗口函数也是基于结果集的运算。与聚合函数不同的是,窗口函数并...
  • MySQL操作实战(二):窗口函数

    万次阅读 多人点赞 2019-02-21 16:25:43
    窗口函数:在满足某些条件的记录集合上执行的特殊函数,对于每条记录都要在此窗口内执行函数。有的函数随着记录的不同,窗口大小都是固定的,称为静态窗口;有的函数则相反,不同的记录对应着不同的窗口,称为滑动...
  • mysql实现窗口函数功能

    千次阅读 2019-04-22 14:19:41
    有时候我们想要得到每个分组的前几条记录,这个时候oracle中row_number函数使用非常方便,可惜MYSQL从8.0版本开始才支持窗口函数。本文介绍一些通过sql实现窗口函数效果的方法。 1.利用用户变量实现数据自增 表...
  • Hive窗口函数Over和排序函数Rank

    千次阅读 2018-08-13 20:33:39
    本文主要介绍hive中的窗口函数.hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于olap分析 概念 我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类...
  • Spark非常实用的窗口函数

    万次阅读 2018-11-11 21:30:19
    spark 累加历史主要用到了窗口函数,而进行全部统计,则需要用到rollup函数 1 应用场景: 1、我们需要统计用户的总使用时长(累加历史) 2、前台展现页面需要对多个维度进行查询,如:产品、地区等等 3、需要展现的...
  • SQL 窗口函数速查表

    千次阅读 多人点赞 2020-05-26 21:59:14
    SQL 窗口函数(分析函数)的速查表,包括窗口函数的语法、窗口函数列表(RANK、DENSE_RANK、ROW_NUMBER、LEAD、LAG、FIRST_VALUE、LAST_VALUE等)以及相关示例,内容适用于 MySQL、Oracle、SQL Server、PostgreSQL ...
  • 通俗易懂的学会:SQL窗口函数

    千次阅读 多人点赞 2019-11-14 21:00:00
    在日常工作中,经常会遇到需要在每组内排名,比如下面的业务需求:排名问题:每个部门按业绩来排名topN问题:找出每个部门排名前N的员工进行奖励面对这类需求,就需要使用sql的高级功能窗口函数了。二.什么是窗口...
  • lead窗口函数

    千次阅读 2019-04-11 20:38:48
    lead函数在Impala中可以配合over使用,lead函数有三个参数 lead(property,num,default) 第一个参数「property」标识想查询的列,「num」标识相对于当前行的第num行,第三个参数是默认值。 举例: -- 建表 ...
  • 窗口函数及OVER子句(2):窗口与分组的区别 若觉得本文写得还可以,请多多关注本人所作书籍《C++语法详解》电子工业出版社出版,作者 黄勇 本文为原创文章,转载请注明出处,或注明转载自“黄邦勇帅(原名:黄勇) 三、...
  • 二,窗口函数的两种写法 三,窗口表达式语法 1,PARTITION BY子句 2,ORDER BY子句 3,frame子句 四,窗口函数介绍 1,序号函数:row_number(),rank(),dense_rank() 2,分布函数:percent_rank() / cume_...
  • SparkStreaming窗口函数的原理

    千次阅读 2020-09-01 20:13:15
    窗口函数就是在原来定义的SparkStreaming计算批次大小的基础上再次进行封装,每次计算多个批次的数据,同时还需要传递一个滑动步长的参数,用来设置当次计算任务完成之后下一次从什么地方开始计算。 ...
  • hive窗口函数(over)详解

    千次阅读 2019-08-01 11:25:26
    hive窗口函数: 一.函数说明: OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化 CURRENT ROW:当前行 n PRECEDING:往前n行数据 n FOLLOWING:往后n行数据 UNBOUNDED:起点,UNBOUNDED ...
  • 如何理解窗口函数

    千次阅读 2017-08-22 14:06:31
    与聚集函数一样,窗口函数也针对定义的行集(组)执行聚集,但它不像聚集函数那样每组之返回一个值,窗口函数可以为每组返回多个值。实际上,DB2中称这种函数为联机分析处理OLAP函数,而Oracle把它们称为解析函数,...
  • sql:窗口函数

    万次阅读 多人点赞 2018-08-17 10:29:42
    窗口函数可以进行排序,生成序列号等一般的聚合函数无法实现的高级操作。 窗口函数也称为OLAP函数,意思是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。 窗口函数语法:其中[]中...
  • 窗口函数及OVER子句(4):聚合嵌套及窗口函数的其余问题 若觉得本文写得还可以,请多多关注本人所作书籍《C++语法详解》电子工业出版社出版,作者 黄勇 本文为原创文章,转载请注明出处,或注明转载自“黄邦勇帅(原名...
  • 在hive、Spark SQL中引入窗口函数

    千次阅读 2018-11-16 14:28:22
    一、Hive中的分析函数  分析函数主要用于实现分组内所有和连续累积的统计。 分析函数的语法结构一般是:  分析函数名(参数) OVER (PARTITION BY子句 ORDER BY子句 ROWS/RANGE子句)。  即由以下三部分组成:  ...
  • ntile 今天我们学习一个窗口函数ntile(tile 的意思是瓦片,动词的意思是并列显示),ntile(n)用于将分组数据按照顺序切均匀分成n片,返回每条数据当前所在的切片值,其实就是将数据分为n 组,然后告诉你这条数据属于那...
  • SQL窗口函数教程及练习笔记(sqlite)

    千次阅读 2019-12-13 10:27:07
    窗口函数 文章目录窗口函数教程数据说明sqlite 代码rank函数对比三种专业分组函数:rank, dense_rank, row_number聚合函数作为窗口函数使用场景排名问题TopN问题 教程 通俗易懂的学会:SQL窗口函数 by 猴子 数据说明...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 736,075
精华内容 294,430
关键字:

窗口函数