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

    2020-09-11 12:53:43
    hive的窗口函数hive的窗口函数

    hive的窗口函数hive的窗口函数

    展开全文
  • Hive的窗口函数

    2021-01-07 08:57:56
    hive的窗口函数说的其实是over子句的用法而已,over可以实现逐条处理等效果,下面我列举一些实用的开窗函数 从第一条记录到当前行,逐行做sum SUM(xx) OVER(PARTITION BY xx ORDER BY xx) AS xx 从起点到当前行,...

    hive的窗口函数说的其实是over子句的用法而已,over可以实现逐条处理等效果,下面我列举一些实用的开窗函数

    从第一条记录到当前行,逐行做sum
    SUM(xx) OVER(PARTITION BY xx ORDER BY xx) AS xx
    
    从起点到当前行,结果同上,只是加了范围控制 ROWS BETWEEN是控制子句的关键字,UNBOUNDED原意是无边际这里做起始行 ,PRECEDING指定前几行时所用的关键字 ,AND 范围连接-至 ,CURRENT ROW 当前行
    SUM(xx) OVER(PARTITION BY xx ORDER BY xx ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS xx
    
    逐行做sum操作,但是每行只计算前三行到当前行
    SUM(xx) OVER(PARTITION BY xx ORDER BY xx ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS xx  
    
    逐行做sum操作,但是每行只计算前三行到当前行的后一行,FOLLOWING指定后几行时所用的关键字
    SUM(xx) OVER(PARTITION BY xx ORDER BY xx ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS xx
    
    逐行做sum操作,但是每行只计算当前行以及之后的每一行,UNBOUNDED原意无边界这里做末尾行
    SUM(xx) OVER(PARTITION BY xx ORDER BY xx ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS xx 
    
    

    上面的是over子句的用法,看似少,但大家只要掌握了over子句的用法,那么开窗函数就简单了,而相对的over字句跟随着那个函数其实就不是那么重要了,比如上面的例子中over跟随着sum函数,但是其实完全可以是其他的函数avg也好,rank也好,都是同样的道理

    但是在操作开窗函数的时候,任然有着一个应该引起注意的一点,就是对于over子句来说,其他的可以没有,不过partition by和order by是两个基石,虽然可以酌情灵活应用,但是最好能写上就写上,因为缺少这两个关键字之后的开窗函数可能会出问题,轻则可能达不到想要的效果,重则报错

    下面介绍一些其他的常用开窗函数,这里注意,我前面说了开窗函数就是over子句的应用,理论上任何函数只要符合逻辑都可以作为开窗函数,所以导致开窗函数其实相当多,多到可能有些都不会用到,因此我下面只列举常用的,而其他比较偏门的就需要大家用的时候自己试试了

    ROW_NUMBER() 分区内排序操作,从结果来说不会有同排序数据,因为凡是同排名的数据,该函数都会按照数据出现的先后继续排
    
    RANK() 分区内排序,从结果上讲,会有同排名出现,但重复的排名会占用本该有的排名,导致排名会变成1 1 3 3 5 5 5 8....的样子
    
    DENSE_RANK() 分区内排序,从结果上讲,也会有同排名出现,但重复的排名不会占用本该有的排名,结果会正常显示,如1 1 2 2 2 3 3 4 4 5 6.....
    
    NTILE(n) 用户对查询结果从新分为n片,并返回片值
    
    

    其他的还有很多但是不常用,大家有需要可以找找,我这里找到一个非常不错的博文,和大家分享一下https://blog.csdn.net/Abysscarry/article/details/81408265

    展开全文
  • HIVE的窗口函数

    2019-02-28 23:22:18
    https://www.jianshu.com/p/9fda829b1ef1?from=timeline HIVE的窗口函数 写的很棒
    展开全文
  • Hive 的窗口函数

    2020-01-31 16:54:30
    本文首发于:微信公众号【大数据每日哔哔,文章:Hive SQL 窗口函数 在 SQL 中有一类函数叫做聚合函数,例如 sum()、avg()、max()、min() 等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后行数是...

    本文首发于:微信公众号【大数据每日哔哔,文章:Hive SQL 窗口函数

    在 SQL 中有一类函数叫做聚合函数,例如 sum()、avg()、max()、min() 等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的。但是,有时候我们既要显示聚集前的数据,又要显示聚集后的数据,此时我们便引入了窗口函数。窗口函数主要用于 OLAP 数据分析。

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

    窗口函数 描述
    LAG() LAG()窗口函数返回分区中当前行之前行(可以指定第几行)的值。 如果没有行,则返回null。
    LEAD() LEAD()窗口函数返回分区中当前行后面行(可以指定第几行)的值。 如果没有行,则返回null。
    FIRST_VALUE FIRST_VALUE窗口函数返回相对于窗口中第一行的指定列的值。
    LAST_VALUE LAST_VALUE窗口函数返回相对于窗口中最后一行的指定列的值。

    LAG 和 LEAD 的用法:

    LAG | LEAD
    ( <col>, <line_num>, <DEFAULT> )
    OVER ( [ PARTITION BY ] [ ORDER BY ] )
    

    FIRST_VALUE 和 LAST_VALUE 的用法:

    FIRST_VALUE | LAST_VALUE
    ( <col>,<ignore nulls as boolean> ) OVER
    ( [ PARTITION BY ] [ ORDER BY ][ window_clause ] )
    

    下面举个例子,数据集如下:

    hive> select * from tmp_pv;
    OK
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2019-02-10	1
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2019-02-11	5
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2019-02-12	7
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2019-02-13	3
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2019-02-14	2
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2019-02-15	4
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2019-02-16	4
    993BD7AD-3B62-BA0C-15AE-A14B85921889	2019-02-10	2
    993BD7AD-3B62-BA0C-15AE-A14B85921889	2019-02-11	9
    993BD7AD-3B62-BA0C-15AE-A14B85921889	2019-02-12	3
    993BD7AD-3B62-BA0C-15AE-A14B85921889	2019-02-13	10
    993BD7AD-3B62-BA0C-15AE-A14B85921889	2019-02-14	1
    993BD7AD-3B62-BA0C-15AE-A14B85921889	2019-02-15	8
    993BD7AD-3B62-BA0C-15AE-A14B85921889	2019-02-16	2
    Time taken: 0.102 seconds, Fetched: 14 row(s)
    

    LAG(col,n,default)

    与 partitioned by 结合使用,返回当前分区中,当前行之前的第 n 行对应的值。如果没有则默认换回 null。第一个参数为列名,第二个参数为当前行之前第n行(可选,默认为1),第三个参数为缺失时默认值(当前行之前第n行为NULL没有时,返回该默认值,如不指定,则为NULL)。

    为了比较每个用户浏览次数与前一天的浏览次数进行比较,查询返回当前浏览次数以及前一天的浏览数量。由于在2019-02-10之前没有浏览行为,前一天的浏览次数设置为0(不设置默认为NULL)。

    hive> select gid, dt, pv, lag(pv, 1, 0) over (partition by gid order by dt) as pre_pv from tmp_pv;
     
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-10	1	0
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-11	5	1
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-12	7	5
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-13	3	7
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-14	2	3
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-15	4	2
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-16	4	4
    993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-10	2	0
    993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-11	9	2
    993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-12	3	9
    993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-13	10	3
    993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-14	1	10
    993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-15	8	1
    993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-16	2	8
    Time taken: 11.783 seconds, Fetched: 14 row(s)
    

    LEAD(col,n,default)

    与 LAG 函数相反。

    FIRST_VALUE(col,布尔值)

    第一个参数是需要第一个值的列,第二个(可选)参数必须是默认为false的布尔值。如果设置为true,则跳过空值。

    hive> select gid,dt,pv,first_value(pv,true) over(partition by gid order by dt) as first_value from temp_pv; 
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-10	1	1
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-11	5	1
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-12	7	1
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-13	3	1
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-14	2	1
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-15	4	1
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-16	4	1
    993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-10	2	2
    993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-11	9	2
    993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-12	3	2
    993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-13	10	2
    993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-14	1	2
    993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-15	8	2
    993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-16	2	2
    Time taken: 9.862 seconds, Fetched: 14 row(s)
    

    LAST_VALUE(col,布尔值)

    与 FIRST_VALUE() 函数相反,这里就不进行演示了。

    over子句

    官方 OVER子句 包括几个部分:

    • 聚合函数(count, sum, min, max, avg)
    • OVER 子句
    • PARTITION BY 子句
    • ORDER BY 子句
    • WINDOW 子句

    结合具体的业务场景,SQL 语句如下:

    ---1)201504月份的销售额
    select sum(amount) as total_amt
    from order_window 
    where substr(order_date,1,7)='2015-04'
    ;
    ---2)201504月份的订单明细与销售额
    select user_name, order_date, amount
          ,sum(amount) over() as total_amt
    from order_window
    where substr(order_date,1,7)='2015-04'
    ;
    ---3)客户的订单明细与月购买金额
    select user_name, order_date, amount
          ,sum(amount) over (partition by month(order_date)) month_amt
    from order_window
    ;
    ---4)客户的订单明细与累计购买金额
    select user_name, order_date, amount
          ,sum(amount) over (partition by month(order_date) order by order_date) month_add_amt
    from order_window
    ;
    ---5)不同窗口的销售额
    select 
         user_name
        ,order_date
        ,amount
        ,sum(amount) over() as sample1 --所有行相加
        ,sum(amount) over(partition by user_name) as sample2 --按name分组,组内数据相加
        ,sum(amount) over(partition by user_name order by order_date) as sample3 --按name分组,组内数据累加
        ,sum(amount) over(partition by user_name order by order_date rows between UNBOUNDED PRECEDING and current row) as sample4 --和sample3一样,由起点到当前行的聚合
        ,sum(amount) over(partition by user_name order by order_date rows between 1 PRECEDING and current row) as sample5 --当前行和前面一行做聚合
        ,sum(amount) over(partition by user_name order by order_date rows between 1 PRECEDING and 1 FOLLOWING) as sample6 --当前行和前边一行及后面一行
        ,sum(amount) over(partition by user_name order by order_date rows between current row and UNBOUNDED FOLLOWING) as sample7 --当前行及后面所有行
    from order_window
    ;
    

    windows子句

    带有窗口规范的OVER子句。窗口可以在WINDOW子句中单独定义。窗口规范支持如下格式:

    关键字 说明
    PRECEDING 表示当前行之前的行
    UNBOUNDED PRECEDING 表示当前行之前无边界行,即第一行
    num PRECEDING 表示当前行之前第num行
    CURRENT ROW 表示当前行
    FOLLOWING 表示当前行后面的行
    UNBOUNDED FOLLOWING 表示当前行后面无边界行,即最后一行
    num FOLLOWING 表示当前行后面第num行


    当缺少WINDOW子句并指定使用ORDER BY时,窗口规范默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,即从第一行到当前行。
    当缺少ORDER BY和WINDOW子句时,窗口规范默认为ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING,即第一行到最后一行。

    参考

    订阅

    扫码_搜索联合传播样式-标准色版.png

    展开全文
  • 本文首发于:微信公众号【大数据每日哔哔,文章:Hive SQL 窗口函数在 SQL 中有一类函数叫做聚合函数,例如 sum()、avg()、max()、min() 等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后行数是要...
  • 1.什么是窗口函数sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后行数是要少于聚集前行数。但是有时我们想要既显示聚集前数据,又要...
  • 关于Hive的窗口函数

    2020-05-08 11:28:32
    关于Hive的窗口函数 Hive是有一些窗口的大小限制 相关函数的说明: OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化 CURRENT ROW:当前行 n PRECEDING:往前n行数据 n FOLLOWING:...
  • Hive的窗口函数 准备测试数据 聚合函数+over() 用法描述 用法示例 未指定分组和排序 未指定排序 未指定分组 指定排序和分组 加上window子句 窗口函数 用法描述 LEAD函数和LAG函数 FIRST_VALUE函数和LAST_VALUE函数 ...
  • hive的窗口函数 本文主要介绍hive中的窗口函数.hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于olap分析(在线分析处理)。 概念 我们都知道在sql中有一类函数叫做聚合函数,例如...
  • hive的窗口函数详解

    2020-07-06 10:16:33
    1.1 hive窗口函数 窗口函数是什么鬼? 窗口函数指定了函数工作数据窗口大小(当前行上下多少行),这个数据窗口大小可能会随着行变化而变化。 窗口函数和聚合函数区别? 窗口函数对于每个组返回多行,组内每...
  • hive的窗口函数 概述 hive中一般求取TopN时就需要用到窗口函数 专业窗口函数一般有三个 rank() over dense rank() over row_number() over 实例 创建一个f_test表拿到以下数据+------------+--------------+--...
  • 本文详细介绍了hive窗口函数,即包括开窗函数和分析函数两部分。

空空如也

空空如也

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

hive的窗口函数