hive sql常用技巧-白红宇

概述

介绍一些常见的数据分析场景中hive sql的一些写法，涉及区间分析，数据按条件转换，数据列转行，计算连续天数，分组排序取top N等场景。

1.多行合并

多行合并常用于做区间统计，通过定义一定的金额区级，将上亿的记录降维为不同区间内总数。概括来说就是多映射到一。典型场景：基于用户交易天流水，计算每天不同金额段的金额笔数。

如用户的天交易流水表结构如上，需要计算出交易额在0-100，100-200，200-300，大于300几个区级的笔数,

CREATE VIEW t_deal_tmp_view_1 ASSELECT    CASE        WHEN rcv_amount <= 100 THEN 1        WHEN rcv_amount <= 200 THEN 2        WHEN rcv_amount <= 300 THEN 3         ELSE 4 END AS amount_range,    receiverFROM t_transfer_infoSELECTamount_range,COUNT(receiver) AS cntFROM t_deal_tmp_view_1GROUP BY amount_range   DROP VIEW t_deal_tmp_view_1 复制代码

为什么不使用下面这种写法

SELECT    CASE        WHEN rcv_amount <= 100 THEN 1        WHEN rcv_amount <= 200 THEN 2        WHEN rcv_amount <= 300 THEN 3         ELSE 4 END AS amount_range,    COUNT(receiver)FROM t_transfer_infoGROUP BY     CASE        WHEN rcv_amount <= 100 THEN 1        WHEN rcv_amount <= 200 THEN 2        WHEN rcv_amount <= 300 THEN 3        ELSE NULL END复制代码

这种写法会报Expressio Not In Group By Key 的错误，在hive中，使用Group By时，非Group By的字段必须使用聚合函数，只有Group By的字段才能原值取出。

主要原因是上面在Group By后面使用Case When没方法命名新字段。因此需要使用临时view进行处理。

2.使用条件语句将NULL转为0

在hive的表中，有些记录可能是NULL，这时如果我们直接对这条记录做运算或逻辑判断是得不到我们期望的结果的，这里可以将NULL转换为0再做处理。当然NULL转0可以使用hive现成的函数nvl，这里使用CASE WHEN是想介绍在hive sql里条件语句的用法。

如上表记录用户每天的收入以及支出，每天的收入和支出可能为空，需要计算用户连续两天的总收入以及总支出。使用join将两天的表链接进行计算，对于NULL使用替换为0，sql如下：

SELECT t1.uin,t1.income + CASE WHEN t2.income IS NULL THEN 0 ELSE t2.income END AS income, t1.expend + CASE WHEN t2.expend IS NULL THEN 0 ELSE t2.expend END AS expendFROM(    SELECT     uin,    income,    expend    FROM t_user_trans_inf_day    WHERE statis_day=20180812)t1LEFT JOIN(    SELECT     uin,    income,    expend    FROM t_user_trans_inf_day    WHERE statis_day=20180811)t2ON(t1.uin=t2.uin)复制代码

3.列传行

如有一个表A，如上，记录了用户的消费记录，每类消费一列，现在需要将该表的列转化为行，如表B，原来的多列转化为多行。如下

这里有两种方式可以实现，分布是使用union以及posexplode。

方法一使用union

union实现方式就是分布取出单列，然后进行对结果进行合并，sql如下。

SELECT uin, 1 AS type, of_amtFROM t_user_transUNION ALLSELECT uin, 2 AS type, lf_amtFROM t_user_transUNION ALLSELECT uin, 3 AS type, on_amtFROM t_user_transUNION ALLSELECT uin, 4 AS type, cr_amtFROM t_user_trans复制代码

方法二，使用posexplode

explode是内建函数，支持两种用法分别是：

explode(ARRAY) 列表中的每个元素生成一行。

explode(MAP) map中每个key-value对，生成一行，key为一列，value为一列。

使用explode(ARRAY)没有type列，因此无法将转换后的行对应到之前的列，这里可以使用posexplode来代替，posexplode(ARRAY)转换后，可以获得列名在数组中的位置，这样将位置对应一列进行输出即可。

SELECT uin t.pos+1 AS type, t.value AS amountFROM t_user_tansLATERAL VIEW posexplode(ARRAY(of_amt,lf_amt,on_amt,cr_amt)) t as pos, value复制代码

4.计算连续天数

有一张用户登陆流水表，需要计算用户的连续登陆天数，这里可以使用分组编号，Group By uin+时间减分组编号，这样连续的天数就被聚合在一起了，可以通过聚合函数计算最终结果。

SELECTuin,COUNT(uin) AS continuity_daysFROM(	SELECT	uin,	statis_day,	row_number() OVER(PARTITION BY uin order by statis_day asc) AS rn	FROM	(	    SELECT 	    uin,	    statis_day  	    FROM t_user_login_log 	    WHERE statis_day>= 20170101    	    AND statis_day <= 20180809	)	)GROUP BY uin, date_sub(statis_day,CAST(rn AS INT))复制代码

5.分组排序取topN

如有t_user_score记录了学生所有的科目成绩，需要取出每个学生分数最高的一门学科。这里主要用到row_number()函数。

SELECTuinFROM(	SELECT 	uin, 	course, 	row_number() OVER(PARTITION BY uin order by score asc) AS rn	FROM	t_user_score)WHERE rn = 1复制代码

概述