hive之函数篇

其他函数

常用函数参考：

https://mp.weixin.qq.com/s/pWf3XLtK5cxcGORaMHieJw https://mp.weixin.qq.com/s/9LRvrOmqZoahoOm8dFuryg 日期，集合，判断，字符串，数学函数

NVL判断是否为null，替换

NVL(E1, E2)的功能为：如果E1为NULL，则函数返回E2，否则返回E1本身。 NVL(isclick, 0) as isclick

列转行函数collect_set

collect_set去除重复元素；collect_list不去除重复元素；需要进行group by

select phone,collect_list(user_id) ,collect_set(user_id) from a group by phone

COALESCE返回第一个非空的值

Coalesce函数，并且发现它比ISNULL更加强大，返回第一个非空的值。 SELECT COALESCE(NULL, NULL, GETDATE()) 必须最少有一个非空的值，都是空会报错

row_number() 排序

语法： row_number() over (partition by col_list1 order by col_list2) rank() over(partition by col_list1 order by col_list2) 说明：首先根据col_list1分组，在分组内部根据col_list2排序，row_number()函数计算的值表示每组内部排序后的顺序编号，组内连续的唯一的;rank() over()函数则是跳跃排序,序号不唯一，即当有数据值值相同时，并列， 当遇到不同的数据，其序号为上一个数据的序号加上该序号的个数。如两个并列第一，序列号为（1，1，3…）。

withas

with as 优化SQL with as 也叫做子查询部分，首先定义一个sql片段，该sql片段会被整个sql语句所用到，为了让sql语句的可读性更高些，作为提供数据的部分，也常常用在union等集合操作中。 with t1 as (select * from carinfo) with t2 as (select * from car_blacklist) select * from t1,t2 --注意：这里必须要整体作为一条sql查询，即with as语句后不能加分号，不然会报错。

explode-lateral view explode

https://blog.csdn.net/SunnyYoona/article/details/62894761

explode（）接受数组（或映射）作为输入，并将数组（映射）的元素作为单独的行输出

对hive表中某一列进行split之后，想对其转换成1 to N的模式，即一行转多列

select game_id, user_id from table lateral view explode(split(userl_ids,'\\[\\[\\[')) snTable as user_id -- https://blog.csdn.net/guodong2k/article/details/79459282 select goods_id2,sale_info from explode_lateral_view LATERAL VIEW explode(split(goods_id,','))goods as goods_id2;

WITH ROLLUP

要看所有年份的总利润的话，可能还需要格外的SUM计算，其实不用那么麻烦，WITH ROLLUP 就可以帮你解决此问题

SELECT year, SUM(profit) AS profit FROM sales GROUP BY year WITH ROLLUP;

instr函数

为字符查找函数，其功能是查找一个字符串在另一个字符串中首次出现的位置

如果String2在String1中没有找到，instr函数返回0。 　示例： 　　SELECT instr('syranmo','s') FROM dual; -- 返回 1 　　SELECT instr('syranmo','ra') FROM dual; -- 返回 3 　　SELECT instr('syran mo','at',1,2) FROM dual; -- 返回 0

substr

Select substr(name,0,3) as subname from lhtstudent;