hive表合并字段_hivesql常⽤技巧
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,
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
sql中union多表合并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,
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,
row_number() OVER(PARTITION BY uin order by score asc) AS rn FROM
t_user_score
)
WHERE rn = 1

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。