一、总结hive难以记住的函数
1、聚合函数
avg(col) #返回改组的平均值
count(col) #返回检索的行数
max(col) #返回该组的最大值
min(col) #返回该组的最小值
collect_set(col) #返回一组消除重复元素的对象，可与concat_ws、explode连用，collect_set表示去重复，collect_set表示不去重复。**用于多行转单行**
collect_set的example（只接受基本的数据类型）：
select c1,collect_set(c2)[0]
from aa
group by c1
输出：
c1 c2
a ["1","2","3"]
b ["4","5","6"]
与concat_ws连用example：
select c1,concat_ws(",",collect_set(c2))
from aa
group by c1
输出类型：
c1 c2
a 1,2,3
b 4,5,6
2、explode函数
explode(array) #参数为数组类型，将数组每个数据作为1行返回
explode(map) #参数为map类型，将map中每个键值对，转化为两列返回新行
posexplode(array) #与explode(array)类似，但包含元素在数组中的位置(pos,value)
lateral view #LATERAL VIEW udtf表达式 tableAlias AS columnAlias。与udtf连用
数据示例：
表明aa
id col
1 [{"v1":"1","v2":"2"},{"v3":"3","v4":"4"}]
2 [{"v5":"5","v6":"6"},{"v7":"7","v8":"8"}]
3 null
explode的example：
select explode(col)
from aa
输出：
{"v1":"1","v2":"2"}
{"v3":"3","v4":"4"}
{"v5":"5","v6":"6"}
{"v7":"7","v8":"8"}
lateral view ecplode的example：
select id,col1
from aa
lateral view ecplode(col) col as col1
输出：
1 {"v1":"1","v2":"2"}
1 {"v1":"3","v2":"4"}
2 {"v1":"5","v2":"6"}
2 {"v1":"7","v2":"8"}
lateral view ecplode的example解析array并获取map中的值：
select id,col1['v1']
from aa
lateral view ecplode(col) col as col1
输出：
1 1
1 3
2 5
2 7
lateral view ecplode的example解析array并解析map中的值：
select id,ecplode(col1)
from aa
lateral view ecplode(col) col as col1
输出：
1 v1 1
1 v2 2
1 v1 3
1 v2 4
2 v1 5
2 v2 6
2 v1 7
2 v2 8
lateral VIEW outer explode 保留原始null值，将扩展列的null置为null
数据示例：
mhotel pois
90663029 [{"search": 5, "id": 15306667, "poi": "巫山县教委关心下一代工作委员会"}, {"search": 2, "id": 15381967, "poi": "文体广场"}, {"search": 1, "id": 15433199, "poi": "巫
山翠屏汽车站"}]
90808282 [{"search": 9, "id": 15451636, "poi": "巫山县教育委员会"}, {"search": 5, "id": 15306667, "poi": "巫山县教委关心下一代工作委员会"}, {"search": 1, "id": 15367082, "poi": "南峰小学"}]
sql例子：
SELECT
mhotel,
json_tuple(poi_json,'search','id','poi') AS (search,id,poi)
FROM
(SELECT
mhotel,
regexp_replace(regexp_replace(pois, '\\[|\\]', ''), '\\}, \\{', '\\}&\\{') pois
FROM iii) t
lateral VIEW explode(split(pois, '\\&')) pois AS poi_json
3、json字符串解析
get_josn_object #用来解析json的一个字段
json_tuple #用来解析json的多个字段
get_josn_object的example：
select get_josn_object(a,'$.type') as type,get_josn_object(a,'$.name') as name
from aa
json_tuple的example:
select a.id,b.name,b.type
from aa a
lateral view json_tuple(first,'name','type') b as name,type
4、时间函数
unix_timestamp(date) # 将日期转化成时间戳
from_unixtime(unixtime,format) #将时间戳转化成日期
from_timestamp(unix_timestamp(),’yyyy-MM-dd HH:mm:ss’)#获取当前日期
current_date #当前日期
current_timestamp() #当前日期精确到毫秒
date_add(DATE startdate,int days) #日期加
date_sub(DATE startdate,int days) #日期减
date_diff(DATE startdate,DATE enddate) #日期差，前面日期大返回正数
add_months(DATE|STRING|TIMESTAMP start_date, int num) #返回start_date的num天，若日期为2020-08-31，num为3则返回2020-11-30
year(stirng date) #返回日期的year部分
month(stirng date) #返回日期的month部分
day(stirng date) #返回日期的day部分
hour(stirng date) #返回日期的hour部分
dayofmonth(string date) #返回该月有多少天
last_day(string date) #返回所属月的最后一天
months_between(DATE|TIMESTAMP|STRING date1, DATE|TIMESTAMP|STRING date2)
#返回两个日期之间的月份，不是同一天则按照31计算天数，并按照小数点后8位4舍5入
next_day(STRING start_date, STRING day_of_week) #例如:next_day('2020-03-31','Monday') = 2020-04-06;及返回下周的的周一
quarter(DATE|TIMESTAMP|STRING a) #获取时间在一年的季度
weekofyear(STRING date) #获取日期在一年的周数
to_date(STRING timestamp) #返回时间的日期部分
5、数学函数
abs(double a) #a的绝对值
ceil(double a) #返回等于或大于a的最小的int值
floor(double a) #返回等于或小于a的最小的int值
rand(int seed) #返回一个从0到1均匀分布的随机数，指定种子数字确定
round(double a，int d) #返回四舍五入的BIGINT值a或a的四舍五入到d位小数
6、窗口函数
1、聚合型窗口函数 SUM(), MIN(),MAX(),AVG(),COUNT()这些常见的聚合函数配合窗口函数使用，rows between 3 preceding and current row可以定义窗口的范围，count(distinct xxx)在窗口函数里是不允许使用的，也可以用size(collect_set() over(partition by order by))来替代实现我们的需求。
例子：
select *, sum(result) over (partition by name order by time rows between 3 preceding and current row) as result
FROM aa;
2、分析性聚合函数 RANk(),ROW_NUMBER(),DENSE_RANK()等常见的排序用的窗口函数。区别：ow_number函数：生成连续的序号（相同元素序号相同）； rank函数：如两元素排序相同则序号相同，并且会跳过下一个序号； * rank函数：如两元素排序相同则序号相同，不会跳过下一个序号；还有 CUME_DIST函数 ：小于等于当前值的行在所有行中的占比 PERCENT_RANK() ：小于当前值的行在所有行中的占比 * NTILE() ：如果把数据按行数分为n份，那么该行所属的份数是第几份
3、取值型窗口函数 LAG是迟滞的意思，也就是对某一列进行往后错行；LEAD是LAG的反义词，也就是对某一列进行提前几行；FIRST_VALUE是对该列到目前为止的首个值，而LAST_VALUE是到目前行为止的最后一个值。 LAG()和LEAD() 可以带3个参数，第一个是返回的值，第二个是前置或者后置的行数，第三个是默认值。
7、条件函数
assert_true(boolean condition) #如果成立返回null，不成立抛出异常
coalesce(T v1, T v2, ...) #返回参数第一个非null表达式
if(boolean condation, T valueTrue, T valuefalse)#true返回valueTrue，false返回valuefalse
isnotnull(a) #不为null返回true，否则反之
isnull(a) #不为null返回false，否则反之
nullif(a, b) #a=b返回null，否则返回一个
nvl(T value, T default_value) #返回默认值，如果值为空，否则返回值
