hive常见函数

1.日期函数

1.1unix_timestmap

返回当前时刻的时间戳（单位m）
select unix_timestamp() as current_time ;
使用该函数传入时间格式字符串获取该时间的时间戳
select unix_timestamp(‘2019-03-11 09:00:00’)
select unix_timestamp(‘2017-04-07 12:01:06’,’yyyy-MM-dd HH:mm:ss’);

1.2from_unixtime

返回该时间戳对应的时间格式字符串
select from_unixtime(1613745667,’yyy-MM-dd’)

1.3current_date

返回当前时间的字符串格式
select current_date();

1.4current_timestamp

返回当前时间的日期+时间
select current_timestamp();

1.5to_date

select to_date(‘2017-01-01 12:12:12’);注意时间格式必须为yyyy-MM-dd格式）
相同函数还包括year month day hour minute second weekofyear dayofmonth

1.6时间运算

1. months_between： 两个日期间的月份，前-后
2. add_months：日期加减月
3. datediff：两个日期相差的天数，前-后
4. date_add：日期加天数
5. date_sub：日期减天数
6. last_day：日期的当月的最后一天

日期函数：https://blog.csdn.net/bitcarmanlee/article/details/51670879

2.常见字符串操作函数

upper： 转大写
lower： 转小写
length： 长度
trim： 前后去空格
lpad： 向左补齐，到指定长度
rpad： 向右补齐，到指定长度
regexp_replace： SELECT regexp_replace(‘100-200’, ‘(\d+)’, ‘num’)=’num-num
使用正则表达式匹配目标字符串，匹配成功后替换！

3.窗口函数

窗口函数仅支持以下几种函数 lead() 下面的记录 lag()上面的记录，first_value()第一次的值，last_value,排名函数和聚合函数，这些windows functions一般结合over()使用

LEAD("字段",offset,"找不到的默认值") 查询当前行几行以下的指定类字段值 同lag(),lag返回的是当前行之上
first_value(字段,没有值是否跳过默认false) 返回当前行的第一行的值如果参数为true代表加入第一个值为null，跳过空值，继续寻找 last_value 参数含义相同
row_number()  从1开始，按照顺序，生成分组内记录的序列
rank() 排名相等会在名次中留下空位
dense_rank() 排名相等会在名次中不会留下空位
percent_rank ：分组内当前行的RANK值-1/分组内总行数-1(应用常见可能较少)
cume_dist ：小于等于当前值的行数/分组内总行数
查询语句
！！！！！！
当指定 ORDER BY 缺少 WINDOW 子句时，WINDOW 规范默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
当缺少 ORDER BY 和 WINDOW 子句时，WINDOW 规范默认为ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

4集合操作

size： 集合（map和list）中元素的个数
map_keys： 返回map中的key
map_values: 返回map中的value
array_contains: 判断array中是否包含某个元素
sort_array： 将array中的元素排序
参考文档
英文窗口函数：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics
中文窗口函数https://www.docs4dev.com/docs/zh/apache-hive/3.1.1/reference/LanguageManual_WindowingAndAnalytics.html

5其他函数

5.1空字段复制

nvl(字段,默认值)
select t_id,nvl(t_name,”没有”) from teacher；

5.2 case when

select (case s_sex when “男” then 1 else 0 end) from student;

5.3行转列

collect_list | collect_set (数组) 结合分组使用
concat(数组)
concat_ws(分隔符,数组)

5.4列转行

explode(数组) 拆分数组
注意 from 后 lateral view explode(数组) tmp_tab as .

5.5窗口函数

聚合函数sum,avg..,lead(),lag(),first_value(),last_value() + over( partition by .. order by ..)
CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点
UNBOUNDED PRECEDING 表示从前面的起点，
UNBOUNDED FOLLOWING表示到后面的终点
LAG(col,n)：往前第n行数据
LEAD(col,n)：往后第n行数据
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

6排名函数

RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算
需要注意的是该函数需要配合窗口函数使用
hive函数大全.pdf
参考文档
窗口函数：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions