hive sql常用技巧

1.多行合并

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

例如需要计算出交易额在0-100，100-200，200-300，大于300几个区级的笔数,

CREATE VIEW t_deal_tmp_view_1 AS
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,
    receiver
FROM t_transfer_info

SELECT
amount_range,
COUNT(receiver) AS cnt
FROM t_deal_tmp_view_1
GROUP 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_info
GROUP 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 expend
FROM
(
    SELECT 
    uin,
    income,
    expend
    FROM t_user_trans_inf_day
    WHERE statis_day=20180812
)t1
LEFT JOIN
(
    SELECT 
    uin,
    income,
    expend
    FROM t_user_trans_inf_day
    WHERE statis_day=20180811
)t2
ON(t1.uin=t2.uin)

3.列传行

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

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

方法一 使用union

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

SELECT uin, 1 AS type, of_amt
FROM t_user_trans
UNION ALL
SELECT uin, 2 AS type, lf_amt
FROM t_user_trans
UNION ALL
SELECT uin, 3 AS type, on_amt
FROM t_user_trans
UNION ALL
SELECT uin, 4 AS type, cr_amt
FROM 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 amount
FROM t_user_tans
LATERAL VIEW 
posexplode(
ARRAY(
of_amt,
lf_amt,
on_amt,
cr_amt
)) t as pos, value

4.计算连续天数

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

SELECT
uin,
COUNT(uin) AS continuity_days
FROM(
    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()函数。

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