Hive函数分类、CLI命令、简单函数、聚合函数、集合函数、特殊函数(分析函数、窗口函数、混合函数，UDTF)，常用函数Demo

1.1 Hive函数分类

1.2  Hive  CLI命令

显示当前会话有多少函数可用

show  functions;

显示函数的描述信息：

DESC  FUNCTION  concat;

显示函数的扩展描述信息：

1.3  简单函数

函数的计算粒度为单条记录：

关系运算

数学运算

逻辑运算

数值计算

日期函数

类型转换

条件函数

字符串函数

统计函数

 

1.4   聚合函数

函数处理的数据粒度为多条记录

sum()——求和

count()——求数据量

avg()——求平均值

distinct——求不同值数

min——求最小值

max——求最大值

1.5   集合函数

复合类型构建

复杂类型访问

复杂类型长度

1.6  特殊函数

窗口函数

应用场景

用于分区排序、动态Group By、Top N、累计计算、层次查询

Windowing  Functions：lead、lag、FIRST_VALUE、LAST_VALUE

分析函数

Analytivs  functions:RANK、ROW_NUMBER、DENSE_RANK、CUME_DIST、PERCENT_RANK、NTILE

混合函数

java_method(class,method[,arg1 [,arg2]]) reflect(class,method [, arg1 [,arg2..]])hash(a1[,a2...])

UDTF

lateralView: LATERAL  VIEW  udtf(expression) tableAlias AS columnAlias (',' columnAlias)*

fromClause: FROM  baseTable  (lateralView)*

lateral view用于和split，explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上

可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成

一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。

1.7  常用函数Demo：

create table employee(id string, money double,type string) row format delimited fields terminated by '\t' lines terminated by '\n' stored as textfile;

load   data  local  inpath  '/home/tuzq/software/hivedata/employee.txt' into table employee;

优先级依次为NOT  AND  OR

select id,money from employee where (id = '2' or id = '3' or id = '4' or id = '5') AND (money > 120 AND money < 250);

数据准备：在/home/tuzq/software/hivedata下创建employee.txt，

数据内容如下：

hive中的显示效果如下：

带有条件的查询：

cast类型转换：

select   cast (1.5  as int);

URL解析函数

parse_url(string urlString, string partToExtract [, string keyToExtract])
select parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1‘, ‘HOST‘) from
employee limit 1;

字符串连接函数： concat 
语法: concat(string A, stringB…) 
返回值: string 
说明：返回输入字符串连接后的结果，支持任意个输入字符串 
举例：

hive> select concat(‘abc‘,‘def’,‘gh‘) from lxw_dual;
abcdefgh

带分隔符字符串连接函数： concat_ws 
语法: concat_ws(string SEP,string A, string B…) 
返回值: string 
说明：返回输入字符串连接后的结果， SEP 表示各个字符串间的分隔符

concat_ws(string SEP, array<string>)
举例：
hive> select concat_ws(‘,‘,‘abc‘,‘def‘,‘gh‘) from lxw_dual;
abc,def,gh

再如案例：

列出该字段所有不重复的值，相当于去重
collect_set(id)  //返回的是数组
列出该字段所有的值，列出来不去重 
collect_list(id)   //返回的是数组
select collect_set(id) from taborder;
求和
sum(money)
统计列数
count(*)
select sum(num),count(*) from taborder;

窗口函数

first_value(第一行值)

first_value(money) over (partition by id order by money)
select ch,num,first_value(num) over (partition by ch order by num) from taborder;

rows between 1 preceding and 1 following (当前行以及当前行的前一行与后一行)
hive (liguodong)> select ch,num,first_value(num) over (partition by ch order by num ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) from taborder;

last_value 最后一行值
hive (liguodong)> select ch,num,last_value(num) over (partition by ch) from taborder;

 lead
 取当前行后面的第二行的值
 lead(money,2) over (order by money)
 
 lag 
 取当前行前面的第二行的值
 lag(money,2) over (order by money)
 ```
 
 ```
 select ch, num, lead(num,2) over (order by num) from taborder;
 select ch, num, lag(num,2) over (order by num) from taborder;

rank排名

rank() over(partition by id order by money)
select ch, num, rank() over(partition by ch order by num) as rank from taborder;
select ch, num, dense_rank() over(partition by ch order by num) as dense_rank from taborder;

cume_dist

cume_dist (相同值的最大行号/行数)
cume_dist() over (partition by id order by money)
 
percent_rank (相同值的最小行号-1)/(行数-1)
第一个总是从0开始
percent_rank() over (partition by id order by money)
 
 
 select ch,num,cume_dist() over (partition by ch order by num) as cume_dist,
 percent_rank() over (partition by ch order by num) as percent_rank
 from taborder;
 ntile分片 
 ntile(2) over (order by money desc)  分两份 
 
 select ch,num,ntile(2) over (order by num desc) from taborder;

混合函数

select id,java_method("java.lang,Math","sqrt",cast(id as double)) as sqrt from hiveTest;

UDTF

 select id,adid 
 from employee 
 lateral view explode(split(type,‘B‘)) tt as adid;
 explode      把一列转成多行
 
hive (liguodong)>  select id,adid
                >  from hiveDemo
                >  lateral view explode(split(str,‘,‘)) tt as adid;

正则表达式 
使用正则表达式的函数 
regexp_replace(string subject A,stringB,string C) 
regexp_extract(string subject,stringpattern,int index)

hive> select regexp_replace(‘foobar‘, ‘oo|ar‘, ‘‘) from lxw_dual;
fb
 
hive> select regexp_replace(‘979|7.10.80|8684‘, ‘.*\\|(.*)‘,1) from hiveDemo limit 1;
hive> select regexp_replace(‘979|7.10.80|8684‘, ‘(.*?)\\|(.*)‘,1) from hiveDemo limit 1;
 

转载：
原文：https://blog.csdn.net/tototuzuoquan/article/details/73028361