一些常用的hive sql函数
1. with...as语句
with 语句，允许hive定义一个sql片段，供整个sql使用，会将这个片段产生的结果集保存在内存中，后续的sql均可以访问这个结果集，作用与视图或临时表类似；
with 语句，相当于建立了一张临时虚拟表，但是不会被物理创建，用完即销毁；
with 语句，可以将业务接耦，每一个with语句，单独成为一个子模块，最后使用基础表将它们串联起来；
这里必须要整体作为一条sql查询，即with as语句后不能加分号，不然会报错；
语法限制：
1）with...as...必须和其他sql一起使用（可以定义一个with但在后续语句中不使用他），如果定义了with子句，但其后没有跟select查询，则会报错；
2）with...as...是一次性的；
with temp_table as ( select * from xxx ) select * from temp;
---------------------------
with temp1 as ( select * from xxx) , temp2 as ( select * from xxx) select * from temp1,temp2;
2. lateral view（行转列）、explode
lateral view用于和split、explode等UDTF一起使用的，能将一行数据拆分成多行数据，在此基础上可以对拆分的数据进行聚合，lateral view首先为原始表的每行调用UDTF（操作作用于单个数据行，并且产生多个数据行，返回拆分值，一对多），UDTF会把一行拆分成一行或者多行，lateral view在把结果组合，产生一个支持别名表的虚拟表。
split(str, regex) ：按规则截取字符串，返回数组
explode函数（行转列）：
1）explode函数以array类型数据输入，将数组中的元素拆分成多行显示；
2）array函数是将一列输入转换成一个数组输出；
3）hive的表生成函数只是生成了一种数据的展示方式，而无法在表中产生一个其他的列；
4）因此这块需要使用LATERAL VIEW功能来进行处理，LATERAL VIEW将explode生成的结果当做一个视图来处理；
5）a表示表示UDTF函数转换的虚拟表的名称，browse_product表示虚拟表的虚拟字段名称
select * from jindi_data.ods_es_search_copyright_reg_index_df lateral view explode(split(gids, ';')) a as browse_product where pt = 20210510
--多个lateral view
col1 col2
[1, 2] ['a', 'b', 'c']
[3, 4] ['d', 'e', 'f']
select aa,bb from temptable lateral view explode(col1) a as aa lateral view explode(col2) b as bb;
3. coalesce函数
将控制替换成其他值，返回第一个非空值
coalesce (expression_1, expression_2, ...,expression_n)，依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。
select coalesce(success_cnt, 1) from table1
--当success_cnt 为null值的时候，将返回1，否则将返回success_cnt的真实值
select coalesce(success_cnt,period,1) from table2
--当success_cnt不为null，那么无论period是否为null，都将返回success_cnt的真实值（因为success_cnt是第一个参数）
--当success_cnt为null，而period不为null的时候，返回period的真实值。只有当success_cnt和period均为null的时候，将返回1
4. collect_list、collect_set函数（列转行）
都是将分组中的某列转为一个数组返回，不同的是collect_list不去重而collect_set去重。
还可以利用collect突破group by的限制，Hive中在group by查询的时候要求出现在select后面的列都必须是出现在group by后面的，即select列必须是作为分组依据的列，但是有的时候想根据A进行分组然后随便取出每个分组中的一个B。
select username, collect_list(video)[0] from visit_video group by username
5. get_json_object（提取json格式中的数据）
get_json_object(string json_string，stringpath)
说明：第一个参数为json对象变量，第二个参数使用$表示json变量标识，然后用.或[]读取对象或数组，如果json无效，返回null，每次只能返回一个数据项
举例：
data = {
"store": {
"fruit":[{"size":8,"name":"apple"}, {"size":9,"name":"pear"}],
"bicycle":{"price":20,"color":"yellow"}
},
"email":"[email protected]",
"owner":"amy"
}
--获取单层json
select get_json_object(data,"$.owner") from table
--获取多层json
select get_json_object(data,"$.store.fruit") from table
--获取数组值[ ]
select get_json_object(data,"$.store.fruit[0]") from table
--获取所有name值
select get_json_object('[[{"gid":"1289809956","name":"湛江市公安局"}],[{"gid":"1289809956","name":"湛江市公安局"}],[{"gid":"1289809956","name":"湛江市公安局"}]]', '$.[*].[*].name');
6. regexp_extract（字符串正则表达式解析函数）
语法： regexp_extract(string subject, string pattern, int index)
index：0是显示与之匹配的整个字符串，1 是显示第一个括号里面的，2 是显示第二个括号里面的字段
在有些情况下要使用转义字符（双斜杠'\'）
7. array_contains（判断array是否含有某个元素）
select array_contains(applicant_other_2,'ABC') as flag from table
8. 日期函数
date_add(string startdate, int days) 日期加天数
date_sub(string startdate, int days) 日期减天数
datediff(string enddate, string startdate) 返回两个日期差，int类型
unix_timestamp(string date) 返回值: bigint，转换格式为"yyyy-MM-dd HH:mm:ss"的日期到unix时间戳，如果转化失败，则返回0
9. concat_ws（带分隔符字符串连接函数）
concat_ws(string SEP, string A, string B…)
返回值: string
说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符