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
;

常见使用场景：

可参考「Hive窗口函数进阶指南」

参考链接：

标签：hive分析函数
Hive分析窗口函数(一) SUM,AVG,MIN,MAX
Hive分析窗口函数(二) NTILE,ROW_NUMBER,RANK,DENSE_RANK
HIVE中 ROW_NUMBER() OVER() 函数
Hive中row_number的使用
hive row_number分组排序top
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics
Hive row_number()函数用法详解及示例 #指导如何插入测试数据以进行后续功能的测试和验证
hive 中窗口函数row_number,rank,dense_ran,ntile分析函数的用法

=END=

1 5 月, 2019

Docker

KnowledgeBase, Programing

Hive, rank, row_number, SQL, 窗口函数

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

hi说道：

2019-05-01 21:04

Hive窗口函数进阶指南
https://mp.weixin.qq.com/s/JIJCtl63eGld5dhu3s-jZw

如何写好上千行的 SQL 存储过程（附代码规范）
https://mp.weixin.qq.com/s/IFkHd2p_zHrJ-Bg8zUTbwg

大数据不就是写SQL吗？
https://mp.weixin.qq.com/s/1ynozlnLV-EChiAFcAqrUQ

回复
hi说道：

2019-06-21 12:08

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

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

回复
hi说道：

2019-07-22 16:52

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

回复
hi说道：

2019-10-31 20:11

Hive SQL 中如何给查询结果添加序号？
How can I add row numbers for rows in PIG or HIVE?
https://stackoverflow.com/questions/9288578/how-can-i-add-row-numbers-for-rows-in-pig-or-hive
`
select str_id, row_number() over()
from
tabledata;
`

回复
abc说道：

2021-06-23 12:01

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/

回复
abc说道：

2021-08-26 15:10

# 借助 partition by 窗口函数实现
SQL Classic Problem: Identifying Gaps and Islands Across Overlapping Date Ranges
https://medium.com/analytics-vidhya/sql-classic-problem-identifying-gaps-and-islands-across-overlapping-date-ranges-5681b5fcdb8

https://www.oraylis.de/blog/2014/combining-multiple-tables-with-valid-from-to-date-ranges-into-a-single-dimension

回复
abc说道：

2022-07-07 17:09

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

回复
abc说道：

2022-07-07 17:09

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

回复
- hi说道：
  
  2024-10-10 20:13
  
  hive连续最大天数
  https://blog.51cto.com/u_16213658/7128503
  `
  解法1（通过与特定日期的日期差判定连续）
  
  解法2（left join进行笛卡尔积）：
  假设我们不需要知道用户最大的连续天数，只需要知道某个用户是否出现连续n天（假设n为3）登录的行为。那这里首先给出一种完全不考虑计算复杂度的解法，使用纯join关联去实现该问题。
  整体思路是去获得同一个用户的发单日期对，看每一个发单日期的n天内是否有n个发单日期。
  该方法容易理解，但其最大的弊端在于关联时造成的笛卡尔积大大增加了计算的复杂度。在较小的数据集上可以考虑该方法，但实际生产环境下意义并不大。
  
  解法3（lead或lag）：
  这里使用到了 HIVE 窗口函数 LAG/LEAD
  最后介绍一个最为直观，也是计算成本最小的方法。假设我们需要求连续登陆n天（假设n为7）及以上的用户，那么对于一个存在该行为的用户，他去重和排序后的发单日期信息中，必存在某一天，往前回溯（往后推）6条记录的日期，等于该日期减6（加6）。这么说可能不太好理解，但相信你看了以下代码便能很快明白我在说什么：
  
  总结：
  如我在介绍问题背景的时候所说，处理日期间的连续性就需要将行与行之间进行关联，而sql提供的解决方案是join和窗口函数。恰恰sql的优势便在于刻画这种行数据间的关系，该问题场景能够帮助我们更深入地理解SQL的这一特性。
  `
  Hive_HQL_复杂SQL_连续发单天数
  https://blog.csdn.net/u010003835/article/details/106739031
  
  Hive SQL复杂场景实现(1) —— 连续发单天数
  https://blog.csdn.net/Adrian_Wang/article/details/89791948
  
  回复
abc说道：

2022-11-30 16:38

新设备、首次访问域名等【首次】的判断逻辑在于——今天(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说道：

2024-10-10 20:17

mysql/hive求最大连续天数(间隔1天也算连续)
https://blog.csdn.net/weixin_42430074/article/details/121967436

回复
hi说道：

2024-10-10 20:17

Hive计算最大连续登陆天数
https://www.cnblogs.com/data-magnifier/p/15506609.html
`
一、背景
二、算法
1. 第一步：排序
2. 第二步：第二列与第三列做日期差值
3. 第三步:按第二列分组求和
4. 第四步：求最大次数
三、扩展（股票最大涨停天数）
`

Hive | 求最大连续登录天数的hql
https://blog.csdn.net/m0_37125796/article/details/131688664

1285. 找到连续区间的开始和结束数字（难度：中等）
https://sql.wang/sql-leetcode/lc1285/

1225. 报告系统状态的连续日期（难度：中等）
https://sql.wang/sql-leetcode/lc1225/

1709. 访问日期之间最大的空档期（难度：中等）
https://sql.wang/sql-leetcode/lc1709/

回复
hi说道：

2024-11-06 11:50

Hive 窗口函数
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics
`
— 对整体做sum，不做任何分区
SUM(b) OVER ()

— 基于c列分区然后做sum
SUM(b) OVER (PARTITION BY c)

— 前3行至当前行
SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM T;

— 前3行至后3行
SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)
FROM T;
`

回复
hi说道：

2024-11-06 11:50

How to Use SUM() with OVER(PARTITION BY) in SQL
https://learnsql.com/blog/sum-over-partition-by/
`
SUM(salary) OVER() AS total_salary

SUM(salary) OVER(PARTITION BY dept_id) AS dept_total_salary

SUM(salary) OVER(PARTITION BY job) AS job_total_salary

ROUND(100.0 * salary / SUM(salary) OVER(PARTITION BY dept_id), 2) AS percentage_of_dept_total_salary
`

Hive中使用sum over()实现累积求和和滑动求和
https://blog.csdn.net/xuehuagongzi000/article/details/121495870

Hive: Sum over a specified group (HiveQL)
https://stackoverflow.com/questions/25082057/hive-sum-over-a-specified-group-hiveql

回复

ASPIRE

Hive窗口函数简单学习

缘由：

正文：

参考解答：

一、窗口函数的定义

二、窗口函数的SQL语法

三、窗口函数的分类

常见用法1、分组排序取top

参考链接：

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

发表回复取消回复

Hive窗口函数简单学习

缘由：

正文：

参考解答：

一、窗口函数的定义

二、窗口函数的SQL语法

三、窗口函数的分类

常见用法1、分组排序取top

参考链接：

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

发表回复 取消回复

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

发表回复取消回复