HIVE常用函数总结

HIVE常用函数总结

---

一：explode（列转行）

lateral view explode()a

explode只能炸array和ma，不能炸struct，UDTF explode 只支持一个字段**
hive explode函数可以将一个array或者map展开，其中explode(array)使得结果中将array列表里的每个元素生成一行；explode(map)使得结果中将map里的每一对元素作为一行，key为一列，value为一列，一般情况下，直接使用即可，但是遇到以下情况时需要结合lateral view 使用。

**LATERAL VIEW的使用：**
侧视图的意义是配合explode（或者其他的UDTF），一个语句生成把单行数据拆解成多行后的数据结果集。

二：case when（行转列）

//两种方法结果等效
方法一：
case 
when tb1.os = 'android' then 'android'
when tb1.os = 'ios' then 'iPhone'
else 'PC'
end as os,

方法二：
case tb1.os
when 'android' then 'android'
when 'ios' then 'iPhone'
else 'PC'
end as os,

三：rows beetwen

current row:当前行
unbounded preceding:首行
unbounded following：尾行
n preceding：往前n行数据   					//preceding
n following：往后n行数据

四： count(),sum(),avg(),max(),min()

1. count()返回匹配制定条件的行数。

   count(*)返回在给定的选择中被选的行数。

2.sum()返回组中所有值的和。sum只能用于数字列,空值会被忽略！

3.avg()返回组中值的平均值，空值回避忽略（默认值为16位）！

4.round (avg(列名)，2)返回组中的平均值，可以选择保留一位或者两位。

5.max()返回组中值的最大值。

6.min()返回组中值的最小值。

7.过滤掉最低分小于60的学生，并平均分保留两位数值
select snum,sum(score)  ,round (avg(score)，2),max(score) from Tbl_Grade
Group by snum having min(score)>=60;

8.round  四舍五入

五：排名函数/排序函数

1.排名函数

//排名函数是窗口函数，后边必须开窗over（）

1.1 row_number() over()

//不考虑相等 {1.2.3.4.5}

1.2 dense_rank() over()

//不留空位  {1.2.3.3.4}

1.3 rank() over()

//留空位  {1.2.3.3.5}

2.排序函数

2.1 order by

只有一个reduce  实现全局排序

2.2 sort by

只有一个reduce时功能跟order by一样实现全局排序，reduce有多个时实现每个reduce局部排序

2.3 distribute by

distribute by和sort by结合使用实现分组局部排序

2.4 cluster by

cluster by的功能就是distribute by和sort by相结合

六：get_json_object

get_json_object(param1,"$.param2")

param1：需要解析的json字段

param2：遇到数组就用 [0,1,2...] 0,1,2是数组对应的元素,遇到jsonObject直接用 ".key"取出想要获取的value

比如：
对于jsonArray（json数组），如person表的xjson字段有数据：
[{
  "name":"王二狗","sex":"男","age":"25"},{
  "name":"李狗嗨","sex":"男","age":"47"}]
取出第一个json对象，那么hive sql为：
SELECT get_json_object(xjson,"$.[0]") FROM person;
结果是：
{
  "