写在前面:最近在苦兮兮得用Hive搬砖,想起来还有这么一个“年更”的号。嘿,那就再搬一篇吧。
本农民工习惯使用的是pandas,平常会使用到mysql最基本的用法,最多的时候就是“select * from table1”,万事大吉。无奈供应商只提供了Hive的查询平台,还有数据导出的条数限制。emmmmm,,,,好的吧,我学还不行吗。
Hive版本:未知(没有“hive -version”这种用法),目测不是最新。
1、自增序列 & top N
2、【窗口函数】前一次/后一次
3、not in (子查询)
4、一列变多行
一、自增序列 & top N
需求:增加一列自增序列,作为行的index。
hive 支持的索引机制和mysql不同,比较复杂,太难不看。
1.1 自增一列
工具:row_number()
-----order by 1 ,表示按照表单顺序-----
1.2 排序后,显示排名
涉及排序的问题,我们需要考虑到遇到“相同值“的情况,如何显示排名
例如,遇到并列第1的情况
工具:
row_number(),不管是否相同,顺序排序。
例如输出名次: 1,2,3,4
rank(),相同的排序是一样的,但下一个不同值是跳着排序的。
例如输出名次,1,1,3,4
dense_rank(),相同的排序是一样的,且名次是连续的
例如输出名次,1,1,2,3
语句都类似,
select
1.3 分组后,保留组内topN
工具:rank() + partition by
举例,提取每月gmv top5 的城市
select
其中,
row_number
表示,将原数据按照months月度分组后,每组order by gmv 倒序排列,最后row number()给出一个排名。
二、窗口函数
2.1 lead()
lead函数是跟偏移量相关的分析函数,它可以在一次查询中取出同一字段的后N行的数据作为独立的列,从而更方便地进行进行数据过滤。
例如,获取当前记录的id,以及下一条记录的id (引用参考资料1)
select
lead(字段,N,默认)
用lead函数指定要偏移的【字段】,并指定向后偏移【N】行
若没有符合条件的数据,则输出【默认】值。
结合上 partition by,我们可以解决更复杂的问题。
举例:计算不同category 消费者的复购间隔。
问题解析:难点在于如何锁定同一个消费者相邻的两个订单。
下面给出一个实例,计算不同类目下,消费者复购的时间间隔。
select
2.2 lag ()
有向后偏移,自然就有向前偏移。这时就可以用 lag函数,二者语法都是相同的。
相似的用法还有:(引自资料一)
count() over(partition by ... order by ...):求分组后的总数。
max() over(partition by ... order by ...):求分组后的最大值。
min() over(partition by ... order by ...):求分组后的最小值。
avg() over(partition by ... order by ...):求分组后的平均值。
lag() over(partition by ... order by ...):取出前n行数据。
lead() over(partition by ... order by ...):取出后n行数据。
ratio_to_report() over(partition by ... order by ...):Ratio_to_report() 括号中就是分子,over() 括号中就是分母。
percent_rank() over(partition by ... order by ...):
参考资料:
Hive lag()与lead() 函数blog.csdn.net三、not in (子查询)
hive 中可以使用
select
但是不支持子查询,例如
select * from table1 where users not in (select users from table2)
因此我们就要用到 left join 工具啦~~
select
当然还有其他的方法,例如 not exist
关于hive中not in不支持子查询的解决方法blog.csdn.net四、一行变多行
工具:lateral view explode()
举例,原表格中每一个产品有一个功效keyword字段,是字符串的形式。
每一种产品对应了多种功效,每个功效之间用逗号连接,例如 ”美白,补水,抗衰老“
由于我们需要对功效进行统计,因此需要对keyword字段拆分,拆分到多行。
select col1,effect from table1 a
lateral view explode(split(a.keyword,',')) t as effect where 1=1
其中,
explode(split(a.keyword,',')) ,将keyword字段,按照逗号切分
lateral view explode(split(a.keyword,',')) t as effect 切分后的列取一个别名effect
具体原理(过于难,不解释了):
lateral view首先为原始表的每行调用UDTF,UDTF会把一行拆分成一行或者多行,lateral view在把结果组合,产生一个支持别名表的虚拟表。
今天就学习到这里~~~希望对你有^^