Hive窗口函数简单学习


=Start=

缘由:

简单记录一下前段时间学习到的Hive窗口函数的使用方法,方便后期使用参考。

正文:

参考解答:
一、窗口函数的定义

窗口函数也称为OLAP(Online Analytical Processing)函数,是对一组值进行操作,不需要使用Group by子句对数据进行分组,还能在同一行返回原来行的列和使用聚合函数得到的聚合列

那为什么叫窗口函数呢?因为窗口函数将表以窗口为单位进行分割,并在其中进行各种分析操作,为了让大家快速形成直观印象,才起了这样一个容易理解的名称

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

窗口函数的语法分为四个部分

  • 函数子句:指明具体操作,如sum-求和,first_value-取第一个值;
  • partition by子句:指明分区字段,如果没有,则将所有数据作为一个分区;
  • order by子句:指明了每个分区排序的字段和方式,也是可选的,没有就是按照表中的顺序;
  • 窗口子句:指明相对当前记录的计算范围,可以向上(preceding),可以向下(following),也可以使用between指明,上下边界的值,没有的话默认为当前分区。
三、窗口函数的分类

窗口函数的功能分为:计算、取值、排序、序列四种,前三种的使用场景比较常见,容易理解,最后一种(序列)的使用场景比较少。

  • 计算
    • count、sum、avg、max/min
  • 取值
    • first_value/last_value、lag/lead
  • 排序
    • rank、dense_rank、row_number、ntitle
  • 序列
    • cume_dist、percent_rank
常见用法1、分组排序取top
/*
	统计Web访问日志中:
	每个域名访问量最大的日期是哪几天?

	在 table 中没有一个直接的字段可用于排序时,可以借助 count+[group by] 先生成一个字段,再借助 row_number() 进行分区排序。
*/
select xx.*
from
(select dt, domain, cnt, row_number() over(partition by domain order by cnt desc) rank
from
(select dt, domain, count(*) as cnt from log.access_log where dt between '20190401' and '20190403' group by dt,domain
having cnt between 1000 and 100000
)x
)xx
where rank <= 3
;

 

常见使用场景:

 

参考链接:

=END=


《“Hive窗口函数简单学习”》 有 9 条评论

  1. Hive分析窗口函数(一) SUM,AVG,MIN,MAX
    http://lxw1234.com/archives/2015/04/176.htm
    `
    如果不指定 ROWS BETWEEN 默认为从起点到当前行;
    如果不指定 ORDER BY 则将分组内所有值累加;

    根据我的实际经验来看,大部分场合是不需要在 PARTITION BY 后面添加 ORDER BY 限制的,因为这可能会对你造成困惑。
    `

  2. Hive中如何在对某一列排序之后添加一个专门表示序号/排名的列?
    Row number functionality in Hive
    https://stackoverflow.com/questions/37476952/row-number-functionality-in-hive
    `
    — 下面的order by语句中记得加上desc表示逆序,否则可能和预期不太一样
    select uid, 3d_cnt, 6d_cnt, total_uid_cnt, ROW_NUMBER() OVER (ORDER BY total_uid_cnt desc) AS row_num
    from
    (select uid,
    count(distinct if(dt>’20190714′,dt,”)) as 3d_cnt,
    count(distinct dt) as 6d_cnt,
    sum(cnt) as total_uid_cnt
    from log.access_log
    where
    dt between 20190712 and 20190717
    group by uid
    )x
    ;
    `
    https://stackoverflow.com/questions/50767960/how-do-i-use-row-number-in-hive-query-to-get-the-latest-user-login

  3. Hive SQL 中如何对每天的近7天的数据做累加统计?
    hive sql sum last 7 days’ value
    `
    select
    t.*
    ,sum(sales) over (partition by itemid order by dt rows between 6 preceding and current row) as sales_7
    ,sum(sales) over (order by dt rows between 6 preceding and current row) as sales_7x
    from
    t
    ;
    `
    https://stackoverflow.com/questions/51403870/sum-last-7-days-of-sales-in-new-column
    https://dba.stackexchange.com/questions/130949/calculate-rolling-sum-over-7-consecutive-day-period-on-postgresql
    https://chartio.com/learn/postgresql/how-to-create-a-rolling-period-running-total/

  4. Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE
    http://lxw1234.com/archives/2015/04/190.htm
    `
    LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
    第一个参数为列名,第二个参数为往上第n行(可选,默认为1),第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)

    LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
    第一个参数为列名,第二个参数为往下第n行(可选,默认为1),第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL)

    lag与lead函数是跟偏移量相关的两个分析函数
    通过这两个函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而更方便地进行进行数据过滤,该操作可代替表的自联接,且效率更高

    需求:查询顾客上次的购买时间?

    select
    name, orderdate, cost
    ,lag(orderdate,1,’1900-01-01′) over(partition by name order by orderdate) as last_orderdate –基于name对orderdate进行排序,同时取当前orderdate往上1行的值,即为上次购买的orderdate
    from
    order_info
    ;
    `
    hive笔记之窗口函数lead()/lag()
    https://zhuanlan.zhihu.com/p/349291325

  5. HIVE_HIVE函数_窗口函数_LAG()/LEAD() 详解
    https://blog.csdn.net/u010003835/article/details/106739353
    `
    Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。

    在实际应用当中,若要用到取今天和昨天的某字段差值时,Lag和Lead函数的应用就显得尤为重要。当然,这种操作可以用表的自连接实现,但是 LAG 和 LEAD 与 left join、right join 等自连接相比,效率更高,SQL更简洁。
    `

    Hive_HQL_复杂SQL_连续发单天数
    https://blog.csdn.net/u010003835/article/details/106739031
    https://blog.csdn.net/Adrian_Wang/article/details/89791948

  6. 新设备、首次访问域名等【首次】的判断逻辑在于——今天(dt)使用的设备ID、访问域名,没有在昨天及以前的n天(dt-n,dt-1)中出现过。可以通过:
    1. 将今天使用的设备、访问的域名做一个 group by 去重
    2. 用前段时间的数据用基于 partition by 的 collect_set 生成一个历史设备列表、访问域名列表
    3. 用 array_contains 判断今天使用的设备、访问的域名在不在上一步生成的列表里面
    4. 如果不在的话,说明设备是新设备、域名是近期首次访问的域名,可以发出提醒,然后进行运营确认。
    “`
    select
    x1.*, x2.*
    from
    (select
    user, ip, device, domain
    ,count(1) as cnt
    from
    accesslog
    where
    dt = ‘today’
    group by user, ip, device, domain
    )x1
    join
    (select
    user
    ,collect_set(device) over (partition by user) device_list
    ,collect_set(domain) over (partition by user) domain_list
    ,count(1) as cnt
    from
    accesslog
    where
    dt between ‘today-30’ and ‘today-1’
    group by user
    )x2
    on x1.user = x2.user
    where
    ! array_contains(x2.device_list, x1.device)
    ;
    “`

回复 hi 取消回复

您的电子邮箱地址不会被公开。 必填项已用*标注