Hive函数

         常用的内置函数

 

空字段赋值

     NVL：给值为NULL的数据赋值，格式：NVL（value，default_value）。

  （如果value为NULL，则NVL函数返回default_value的值，否则返回value的值，如果两个参数都为NULL，则返回NULL。）

 

 

 

select comm, nvl(comm, -1) from emp;
# comm的数据为空的 用-1填充

comm    _c1

NULL    -1.0

300.0   300.0

500.0   500.0

NULL    -1.0

1400.0  1400.0

NULL    -1.0

select comm, nvl(comm, mgr) from emp;

# comm的数据为空的 用mgr数据填充

comm    _c1

NULL    7902.0

300.0   300.0

500.0   500.0

NULL    7839.0

1400.0  1400.0

NULL    7839.0

 

 

 

行转列

      CONCAT(string a/col, string b/col……）：返回输入字符串连接后的结果，支持任意个输入字符串

      CONCAT_WS（separator，st1，st2，…）：一个特殊形式的CONCAT（）。separator其他参数之间的分隔符

 (分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;）

 

     COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

 

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;

 

 

 

列转行

  

EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。

LATERAL VIEW

(LATERAL VIEW udtf(expression) tableAlias AS columnAlias)

（用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。）

 

select m.movie,  tbl.cate
from  movie_info m
lateral view
explode(split(category, ",")) tbl as cate;

 

 

 

开窗函数：

1. OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
2. CURRENT ROW：当前行
3. n PRECEDING：往前n行数据
4. n FOLLOWING：往后n行数据
5. UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
6. LAG(col,n,default_val)：往前第n行数据
7. LEAD(col,n, default_val)：往后第n行数据
8. NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

#查询在2017年4月份购买过的顾客及总人数
select name,count(*) over () 
from business 
where substring(orderdate,1,7) = '2017-04' 
group by name;

#查询顾客的购买明细及月购买总额
select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from
 business;

#上述的场景, 将每个顾客的cost按照日期进行累加
select name,orderdate,cost, 
sum(cost) over() as sample1,--所有行相加 
sum(cost) over(partition by name) as sample2,--按name分组，组内数据相加 
sum(cost) over(partition by name order by orderdate) as sample3,--按name分组，组内数据累加 
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合 
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合 
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行 
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行 
from business;

#查看顾客上次的购买时间
select name,orderdate,cost, 
lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1, lag(orderdate,2) over (partition by name order by orderdate) as time2 
from business;

#查询前20%时间的订单信息
select * from (
    select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
    from business) t

where sorted = 1;

 

 

排序函数: 

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

 

select name,subject,score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from score;

 

 

日期相关函数：

 

current_date返回当前日期

date_add, date_sub 日期的加减

 

--date_add, date_sub 日期的加减
--今天开始90天以后的日期
select date_add(current_date(), 90);
--今天开始90天以前的日期
select date_sub(current_date(), 90);
--两个日期之间的日期差
--今天和1990年6月4日的天数差
SELECT datediff(CURRENT_DATE(), "1990-06-04");

 

 

 

自定义函数

     

1.      Hive 自带了一些函数，比如：max/min等，但是数量有限，自己可以通过自定义UDF来方便的扩展。
2.   当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。
3. 根据用户自定义函数类别分为以下三种：           

（1）UDF（User-Defined-Function）一进一出

（2）UDAF（User-Defined Aggregation Function）聚集函数，多进一出【类似于：count/max/min】

（3）UDTF（User-Defined Table-Generating Functions）一进多出  【如lateral view explore()】