hive 判断子字符串_Hive 学习笔记（一）topN,lead()……

写在前面：最近在苦兮兮得用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在把结果组合，产生一个支持别名表的虚拟表。

今天就学习到这里~~~希望对你有^^