(07)Hive——窗口函数详解

这篇具有很好参考价值的文章主要介绍了(07)Hive——窗口函数详解。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

一、 窗口函数知识点

1.1 窗户函数的定义

        窗口函数可以拆分为【窗口+函数】。窗口函数官网指路:

LanguageManual WindowingAndAnalytics - Apache Hive - Apache Software Foundationhttps://cwiki.apache.org/confluence/display/Hive/LanguageManual%20WindowingAndAnalytics

  • 窗口:over(),指明函数要处理的数据范围
  • 函数:指明函数计算逻辑

1.2 窗户函数的语法

<窗口函数>window_name  over ( [partition by 字段...]  [order by 字段...]  [窗口子句] )
  • window_name:给窗口指定一个别名。
  • over:用来指定函数执行的窗口范围,如果后面括号中什么都不写,即over() ,意味着窗口包含满足where 条件的所有行,窗口函数基于所有行进行计算。
  • 符号[] 代表:可选项;  | : 代表二选一
  •  partition by 子句: 窗口按照哪些字段进行分组,窗口函数在不同的分组上分别执行。分组间互相独立。
  • order by 子句:每个partition内部按照哪些字段进行排序,如果没有partition ,那就直接按照最大的窗口排序,且默认是按照升序(asc)排列。
  • 窗口子句:显示声明范围(不写窗口子句的话,会有默认值)。常用的窗口子句如下:

1.3 窗口子句范围大小的控制

rows 或 range子句往往来控制窗口的边界范围,其语法如下:

(07)Hive——窗口函数详解,# Hive,大数据,数据仓库

    rows between unbounded preceding and  unbounded following; -- 上无边界到下无边界(一般用于求 总和)
    rows between unbounded preceding and current row;  --上无边界到当前记录(累计值)
    rows between 1 preceding and current row; --从上一行到当前行
    rows between 1 preceding and 1 following; --从上一行到下一行
    rows between current row and 1 following; --从当前行到下一行

1.4 rows与range的区别

  • rows:rows是真实的行数,也就是我们实际中所说的1,2,3...连续的行数。
  • range:range是逻辑上的行数,需要通过计算才能知道是哪一行。

      ps: over()里面有order by子句,但没有窗口子句时 ,即: <窗口函数> over ( partition by 字段... order by 字段... ),此时窗口子句是有默认值的 -->  rows between unbounded preceding and current row (上无边界到当前行)。  此时窗口函数语法:

 <窗口函数> over ( partition by 字段... order by 字段... )  等价于  <窗口函数> over ( partition by 字段... order by 字段... rows between unbounded preceding and current row)
      需要注意有个特殊情况:当order by 后面跟的某个字段是有重复行的时候, <窗口函数> over ( partition by 字段... order by 字段... )  不写窗口子句的情况下,窗口子句的默认值是:range between unbounded preceding and current row(上无边界到当前相同行的最后一行)。

     因此,遇到order by 后面跟的某个字段出现重复行,且需要计算【上无边界到当前行】,那就需要手动指定窗口子句 rows between unbounded preceding and current row ,偷懒省略窗口子句会出问题~

    总结如下:

1、窗口子句不能单独出现,必须有order by子句时才能出现。
2、当省略窗口子句时:
 
   a) 如果存在order by则默认的窗口是unbounded preceding and current row  --当前组的第一行到当前行,即在当前组中,第一行到当前行
 
   b) 如果没有order by则默认的窗口是unbounded preceding and unbounded following  --整个组
 

 口诀:

  • 有partition by 且有order by,窗口范围:分组中第一行到当前行
  • 有partition by 无order by ,窗口范围:整个分组
  • 无partition by 且有order by 窗口范围:整个表中第一行到当前行
  • 无partition by 无order by,窗口范围:整个分组,即over()   

1.5 窗口函数执行顺序

       一般而言:sql 执行顺序

 from ->
 join ->
 on ->
 where ->
 group by->
 with (可以在分组后面加上 with rollup,在分组之后对每个组进行全局汇总) ->
 select 后面的普通字段,聚合函数-> 
 having(having中可以使用select 字段别名) -> 
 distinct -> 
 order by ->
 limit

 窗口函数的执行顺序窗口函数是作用于select后的结果集。即:select 的结果集作为窗口函数的输入窗口函数的执行结果只是在原有的列中单独添加一列,形成新的列,它不会对已有的行或列做修改。窗口函数简化版的执行顺序:

(07)Hive——窗口函数详解,# Hive,大数据,数据仓库

  窗口函数具体实现原理解析:

select channel, 
       month,
       sum(amount) as sum,
       dense_rank() over (partition by channel order by sum(amount) desc) as dr,
       row_number() over(partition by channel order by sum(amount) desc) as rn
from sales
group by channel,
          month;

  上述代码执行过程有两个阶段

   step1 : 计算除窗口函数以外的其他运算,如 from 、join 、where、group by、having等。上面的代码的第一阶段:

select channel,
       month, 
       sum(amount) as sum 
from sales 
group by channel, month;

step2:step1 输出作为 WindowingTableFunction窗口函数的输入,计算对应的窗口函数值。

(07)Hive——窗口函数详解,# Hive,大数据,数据仓库

1.6 条件判断语句嵌套window子句的执行顺序

HiveSQL——条件判断语句嵌套windows子句的应用-CSDN博客文章浏览阅读1.4k次,点赞42次,收藏21次。HiveSQL——条件判断语句嵌套windows子句的应用https://blog.csdn.net/SHWAITME/article/details/136079305?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522170763988016800180626588%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fblog.%2522%257D&request_id=170763988016800180626588&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~blog~first_rank_ecpm_v1~rank_v31_ecpm-1-136079305-null-null.nonecase&utm_term=%E6%9D%A1%E4%BB%B6&spm=1018.2226.3001.4450    结论:

  • case when(或 if)语句中嵌套窗口函数时,条件判断语句的执行顺序在窗口函数之后
  • 窗口函数partition by子句中是可以嵌套条件判断语句的 case when(或 if)

1.7 窗口函数中的partition by分组与group by的区别

  • group by 汇总后行数减少,partition by汇总后原表中的行数没变。

  • group by分组后,一组中只返回一个结果。窗口函数中partition by分组,每组每行中都会有一个分析结果。

(07)Hive——窗口函数详解,# Hive,大数据,数据仓库

(07)Hive——窗口函数详解,# Hive,大数据,数据仓库

  • group by分组后,select中的字段必须是group by的字段、sum()等聚合函数或常量;但是窗口函数中的partition by 分组就没有此限制,窗口函数分析的结果可以与表中其他字段并列,其相当于在原表每个分组中新增了一列。

举例:

CREATE TABLE t_order (
       oid int ,
       uid int ,
       otime string,
       oamount int
 )
ROW format delimited FIELDS TERMINATED BY ",";
load data local inpath "/opt/module/hive_data/t_order.txt" into table t_order;
with tmp as (
    select
        oid,
        uid,
        otime,
        oamount,
        date_format(otime, 'yyyy-MM') as dt,
        ---计算rk的目的是为了获取记录中的第一条
        row_number() over (partition by uid,date_format(otime, 'yyyy-MM') order by otime) rk
    from t_order
    order by uid
)
select
    uid,
    --每个用户一月份的订单数
    sum(if(dt = '2018-01', 1, 0)) as                 m1_count,
    --每个用户二月份的订单数
    sum(if(dt = '2018-02', 1, 0)) as                 m2_count,
    --每个用户三月份的订单数(当月订单金额超过10元的订单个数)
    sum(if(dt = '2018-03' and oamount > 10, 1, 0))   m3_count,
    --当月(3月份)首次下单的金额
    sum(if(dt = '2018-03' and rk = 1, oamount, 0))   m3_first_amount,
   -- 开窗函数
    row_number() over (partition by uid order by  sum(if(dt = '2018-01', 1, 0)))rk
from tmp
group by uid
having m1_count >0 and m2_count=0;

(07)Hive——窗口函数详解,# Hive,大数据,数据仓库

(07)Hive——窗口函数详解,# Hive,大数据,数据仓库

  • 根据HiveSQL的执行顺序得到,窗口函数的执行是在group by,having之后进行,是与select同级别的。如果SQL中既使用了group by又使用了partition by,那么此时partition by的分组是基于group by分组之后的结果集进行的再次分组,即窗口函数分析的数据范围也是基于group by后的数据。

(07)Hive——窗口函数详解,# Hive,大数据,数据仓库

  • 窗口中的partition by分组后,并没有去重功能,而group by具有去重功能

二、窗口函数运用案例

聚合窗口函数-——聚合开窗求累积汇总值

HiveSQL题——聚合函数(sum/count/max/min/avg)-CSDN博客文章浏览阅读1.1k次,点赞19次,收藏19次。HiveSQL题——聚合函数(sum/count/max/min/avg)https://blog.csdn.net/SHWAITME/article/details/135918264排序窗口函数——排序开窗求topN

HiveSQL题——排序函数(row_number/rank/dense_rank)-CSDN博客文章浏览阅读1.3k次,点赞20次,收藏16次。HiveSQL题——排序函数(row_number/rank/dense_rank)https://blog.csdn.net/SHWAITME/article/details/135909662前后窗口函数

HiveSQL题——前后函数(lag/lead)_sql hive lead-CSDN博客文章浏览阅读1.2k次,点赞23次,收藏21次。HiveSQL题——前后函数(lag/lead)_sql hive leadhttps://blog.csdn.net/SHWAITME/article/details/135902998注:参考文章:

窗口函数应用之移动范围计算【详细剖析窗口函数】(HiveSql面试题4详解)-CSDN博客文章浏览阅读3.5k次,点赞17次,收藏53次。本文通过案例来引出对窗口函数的认识,总结了窗口函数的用法及使用规律,该案例主要是对窗口函数在移动计算中的应用,类似于滑动窗口,所谓的滑动窗口也就是指每一行对应对应的数据窗口都不同,通过窗口子句类实现移动计算时数据的范围,也就是窗口每次按行滑动时长度大小,但窗口中每一次对应的数据总是在变化。通过本文你可以获得如下知识: (1)窗口函数的使用规则及用法 (2)窗口子句的使用规则 (3)窗口函数的意义 (4)窗口函数在移动计算中的应用_窗口函数应用之移动范围计算【详细剖析窗口函数】https://blog.csdn.net/godlovedaniel/article/details/106542519文章来源地址https://www.toymoban.com/news/detail-827682.html

到了这里,关于(07)Hive——窗口函数详解的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 【大数据技术】hive 窗口函数sum range between的详细介绍

    【大数据技术】hive 窗口函数sum range between的详细介绍 数据准备 建表 插入数据 查看数据 对比两段sql的不同之处 及其运行结果 结果如下: 第一段 第二段 重点注意: 第一段和第二段sql的rows between与range between的不同 第一段与第二段sql的order by字段有不同 第二段sql最后使用了

    2024年02月13日
    浏览(34)
  • 详解数据仓库之拉链表(原理、设计以及在Hive中的实现)

    最近发现一本好书,读完感觉讲的非常好,首先安利给大家,国内第一本系统讲解数据血缘的书!点赞!近几天也会安排朋友圈点赞赠书活动(ง•̀_•́)ง 0x00 前言 本文将会谈一谈在数据仓库中拉链表相关的内容,包括它的原理、设计、以及在我们大数据场景下的实现方式

    2024年02月05日
    浏览(51)
  • 【大数据Hive3.x数仓开发】窗口函数案例:连续N次登录的用户;级联累加求和;分组TopN

    对窗口函数的讲解part见:【大数据Hive3.x数仓开发】函数–窗口函数 自连接过滤实现 连续两天登陆的用户ID: 窗口函数lead()实现 功能:用于从当前数据中基于当前行的数据向后偏移取值 语法: lead(colName,N,defautValue) colName:取哪一列的值 N:向后偏移N行 defaultValue:如果取不到返回

    2023年04月17日
    浏览(32)
  • Hive窗口函数-lead/lag函数

    前面我们学习的first_value和last_value 取的是排序后的数据截止当前行的第一行数据和最后一行数据 Lag和Lead分析函数可以在一次查询中取出当前行后N行和前N行的数据,虽然可以不用排序,但是往往只有在排序的场景下取前面或者后面N 行数据才有意义 这种操作可以代替表的自

    2024年02月16日
    浏览(59)
  • Hive窗口函数全解

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

    2024年02月03日
    浏览(49)
  • Hive 窗口函数大全

    目录 窗口函数概述 窗口序列函数 row_number dense_rank 窗口边界 滑动窗口 lag 获取上一行数据 lead 获取下一行数据 窗口专用计算函数 sum累加函数 max最大值 min最小值 avg平均值 count累计次数 first_value首行值 last_value末行值 cume_dist分布统计 percent_rank 秩分析函数 nitle数据切片函数

    2024年02月15日
    浏览(37)
  • Hive窗口函数整理

    Hive 中的窗口函数允许你在结果集的一个特定“窗口”内对行进行计算。这些窗口可以是物理的(基于行在数据中的实际位置)或逻辑的(基于行的一些排序标准)。窗口函数在处理排名、计算累计和或计算移动平均值等问题时特别有用。 以下是一些 Hive 中的常见窗口函数:

    2024年01月18日
    浏览(44)
  • hive窗口函数计算累加值

    rows是物理窗口,是哪一行就是哪一行,与当前行的值(order by key的key的值)无关,只与排序后的行号相关,就是我们常规理解的那样。 range是逻辑窗口,与当前行的值有关(order by key的key的值),在key上操作range范围。 简要:如果当前行的值有重复的,range会默认把重复的值加

    2024年02月11日
    浏览(31)
  • Hive 窗口函数超详细教程

    在 SQL 开发中,有时我们可以使用聚合函数将多行数据按照规则聚集在一行,但是我们又想同时得到聚合前的数据,单纯的聚合函数是做不到的,怎么办呢?这时我们的窗口函数就闪亮登场了。窗口函数兼具分组和排序功能,又叫分析函数! 语法如下:

    2024年02月04日
    浏览(38)
  • Hive学习---4、函数(单行函数、高级聚合函数、炸裂函数、窗口函数)

    1.1 函数简介 Hive会将常用的逻辑封装成函数给用户进行使用,类似java中的函数。 好处:避免用户反复写逻辑,可以直接拿来使用 重点:用户需要知道函数叫什么,能做什么 Hive提供了大量的内置函数,按照其特点大致可分为如下几类:单行函数、聚合函数、炸裂函数、窗口

    2024年02月08日
    浏览(60)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包