常用内置函数

一系统内置函数
1.查看系统自带函数
show functions;
2.显示自带的函数用法
desc function upper;
3.详细显示自带函数的用法

desc function extended upper;
二、常用内置函数
1.空字段赋值
NVL：给值为NULL的数据赋值，它的格式是NVL(value,default_value)。
它的功能是如果value为NULL，则NVL函数返回default_value的值，否则返回value的值，如果两个参数都为NULL ，则返回NULL。

hive (default)> select comm,nvl(comm, -1) from emp;
OK
comm _c1
NULL -1.0
300.0 300.0
500.0 500.0
NULL -1.0
1400.0 1400.0

2.CASE WHEN

select
dept_id,
sum(case sex when '男' then 1 else 0 end) male_count,
sum(case sex when '女' then 1 else 0 end) female_count
from
emp_sex
group by
dept_id;
3.行转列
1）相关函数说明
CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

select name,concat(orderdate,cost) from business;

jack,2017-01-0110
tony,2017-01-0215
jack,2017-02-0323
tony,2017-01-0429
jack,2017-01-0546

select name,concat_ws("sss",orderdate,'a')from business;
jack,2017-01-01sssa
tony,2017-01-02sssa
jack,2017-02-03sssa
tony,2017-01-04sssa
jack,2017-01-05sssa

select t1.base, concat_ws('|', collect_set(t1.name)) name
from
(select name, concat(constellation, ",", blood_type) base
from person_info) t1
group by t1.base;
4.列转行
函数说明
EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW
用法：

LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

select movie,t.a from
movie.txt
lateral view explode(split( category , ',' )) t as a;

5.开窗函数
over():指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化二变化。
current row :当前行
n preceding: 往前n行数据
n following: 往后n行数据
unbounded ：起点
unbounded preceding 表示从前面的起点 ，
unbounded following 表示到后面的终点
LAG(col,n,default_val): 往前n行数据
LEAD(col,n,default_val): 往后n行数据
NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

6.Rank
RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算

7.时间相关函数
current_date() 返回当前时间
data_add(current_date(),10) 今天开始10天以后日期
data_sub(current_date(),10) 今天开始10天以前日期
datediff(current_date(),'2020-05-12') 两个日期之间的差距