Hive最常用的函数大全_hive常用函数大全

一、时间处理类

1、把固定日期转换成时间戳

select unix_timestamp('2020-05-29','yyyy-MM-dd')  --返回结果 1590681600

select unix_timestamp('20200529','yyyyMMdd') --返回结果 1590681600

select unix_timestamp('2020-05-29T13:12:23Z', "yyyy-MM-dd'T'HH:mm:ss'Z'") --返回结果 1590729143

select unix_timestamp('2020-05-29 13:12:23', 'yyyy-MM-dd HH:mm:ss') --返回结果 1590729143
1
2
3
4
5
6
7

2、把 29/May/2020:11:30:03 +0800 转成正常格式（yyyy-MM-dd hh:mm:ss）

select from_unixtime(to_unix_timestamp('29/May/2020:11:30:03 +0800', 'dd/MMM/yyy:HH:mm:ss Z'))
--返回结果  2020-05-29 11:30:03
1
2

3、时间戳转换成固定日期

select from_timestamp(now(),'yyyyMM')   --返回结果  202309

select from_unixtime(1590681600,'yyyy-MM-dd') --返回结果 2020-05-29

select from_unixtime(1590681600,'yyyyMMdd') --返回结果 20200529

select from_unixtime(1590729143,'yyyy-MM-dd HH:mm:ss') --返回结果 2020-05-29 13:12:23

select from_unixtime(1590729143) --返回结果 --2020-05-29 13:12:23 不加默认 yyyy-MM-dd HH:mm:ss 格式

select from_unixtime(unix_timestamp('2020-05-29','yyyy-MM-dd'),'Y年') --返回结果 2020年

select from_unixtime(unix_timestamp('2020-05-29','yyyy-MM-dd'),'yyyy-MM') --返回结果 2020-05

select from_unixtime(unix_timestamp('20200529','yyyyMMdd'),'yyyy-MM-dd')  --返回结果 2020-05-29

select from_unixtime(unix_timestamp('2020-05-29','yyyy-MM-dd'),'EEEE') --返回结果 Friday

select from_unixtime(unix_timestamp('2020-05-29','yyyy-MM-dd'),'w') --返回第几周(22)

select from_unixtime(unix_timestamp('2020-05-29','yyyy-MM-dd'),'u') -- 返回本周的第几天

select from_unixtime(unix_timestamp('2020-05-29','yyyy-MM-dd'),'D') -- 本年的第几天(150)

select from_unixtime(unix_timestamp('2020-05-29','yyyy-MM-dd'),'d') -- 本月的第几天(29)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

4、对日期进行格式化转换

select date_format('2020-05-29','yyyyMMdd') --返回结果  20200529
1

5、计算得出星期几，date_code是日期参数

select pmod(datediff(date_code, '2012-01-01'), 7) = 0 --当等于0时，这天是'星期日'

select pmod(datediff(date_code, '2012-01-01'), 7) = 1 --当等于1时，这天是'星期一'

select pmod(datediff(date_code, '2012-01-01'), 7) = 2 --当等于2时，这天是'星期二'

select pmod(datediff(date_code, '2012-01-01'), 7) = 3 --当等于3时，这天是'星期三'

select pmod(datediff(date_code, '2012-01-01'), 7) = 4 --当等于4时，这天是'星期四' 

select pmod(datediff(date_code, '2012-01-01'), 7) = 5 --当等于5时，这天是'星期五'

select pmod(datediff(date_code, '2012-01-01'), 7) = 6 --当等于6时，这天是'星期六'
1
2
3
4
5
6
7
8
9
10
11
12
13

6、返回日期时间字段中的日期部分

select to_date('2020-05-29 13:12:23) -- 返回结果 2020-05-29
1

7、日期相减函数

datediff('2016-12-30','2016-12-29')   --1

datediff(now(),from_unixtime(unix_timestamp('20230501','yyyyMMdd')) )   --3

date_sub('2016-12-29',10)    -- 2016-12-19
1
2
3
4
5

二、null值处理

nvl(a,0)函数： 如果为空，值为0；如果不为空，值为a

三、数组

select array("a", "b", "c" )   --生成数组

select array(ename, job)  from  emp    --生成数组

split(category, ",")   --切分字符串生成数组

select collect_set(ename) from  emp group by deptno;   --行转列

select collect_list(ename) from  emp group by deptno;  --行转列

select  map("a",12,"b,3")  --创建一个map

map_keys()：  --遍历key

map_values()： --遍历valus
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

四、字符串拼接

concat("a"+"-"+"b") ： a-b

concat_ws("-", collect_list(ename))  ：   --第一个参数为拼接符，第二个参数为字符串  a-b
1
2
3

五、类型转换

cast ("100"  as  int)  --字符串转为int
1

六、判断

case when判断

case
    when  判断条件  then  值
    when  判断条件  then  值
else  值
as aaa
1
2
3
4
5

if 判断

sum(if(gender="男", 1, 0)) as sex;
1

七、最大值

greatest()   --求最大值 
1

八、行转列，列转行

行转列： 将多行数据集成到1行中

collect_list()  --可重复
collect_set()   --不可重复
1
2

列传行： 将1行中数据拆成多行

explode()   --将数据炸开

lateral view  -- 特殊的join
1
2
3

例如：

select  
   name, 
   cate
from
   tb_move2
lateral view explode(category) tmp  as cate
1
2
3
4
5
6

九、窗口函数
.
1、 函数名(列) over (选项) ：开窗口函数，控制聚合范围

例如：
sum(cost) over(partition by name)

+----------------+---------------------+----------------+---------------+
| tb_order.name  | tb_order.orderdate  | tb_order.cost  | sum_window_0  |
+----------------+---------------------+----------------+---------------+
| jack           | 2017-01-05          | 46.0           | 176.0         |
| jack           | 2017-01-08          | 55.0           | 176.0         |
| jack           | 2017-01-01          | 10.0           | 176.0         |
| jack           | 2017-04-06          | 42.0           | 176.0         |
| jack           | 2017-02-03          | 23.0           | 176.0         |
| mart           | 2017-04-13          | 94.0           | 299.0         |
| mart           | 2017-04-11          | 75.0           | 299.0         |
| mart           | 2017-04-09          | 68.0           | 299.0         |
| mart           | 2017-04-08          | 62.0           | 299.0         |
| neil           | 2017-05-10          | 12.0           | 92.0          |
| neil           | 2017-06-12          | 80.0           | 92.0          |
| tony           | 2017-01-04          | 29.0           | 94.0          |
| tony           | 2017-01-02          | 15.0           | 94.0          |
| tony           | 2017-01-07          | 50.0           | 94.0          |
+----------------+---------------------+----------------+-------------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

2、rows between 1 preceding and current row ：最近两行

 例如：
 partition by name order by orderdate rows  between 1 preceding  and  current row
 
| tb_order.name  | tb_order.orderdate  | tb_order.cost  | sum_window_0  |
+----------------+---------------------+----------------+---------------+
| jack           | 2017-01-01          | 10.0           | 10.0          |
| jack           | 2017-01-05          | 46.0           | 56.0          |
| jack           | 2017-01-08          | 55.0           | 101.0         |
| jack           | 2017-02-03          | 23.0           | 78.0          |
| jack           | 2017-04-06          | 42.0           | 65.0          |
| mart           | 2017-04-08          | 62.0           | 62.0          |
| mart           | 2017-04-09          | 68.0           | 130.0         |
| mart           | 2017-04-11          | 75.0           | 143.0         |
| mart           | 2017-04-13          | 94.0           | 169.0         |
| neil           | 2017-05-10          | 12.0           | 12.0          |
| neil           | 2017-06-12          | 80.0           | 92.0          |
| tony           | 2017-01-02          | 15.0           | 15.0          |
| tony           | 2017-01-04          | 29.0           | 44.0          |
| tony           | 2017-01-07          | 50.0           | 79.0     

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

3、 rows between unbounded preceding and current row ：当前行和累计行

例如：
 sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row)
等同于：sum(cost) over(partition by name order by orderdate)

+----------------+---------------------+----------------+---------------+
| tb_order.name  | tb_order.orderdate  | tb_order.cost  | sum_window_0  |
+----------------+---------------------+----------------+---------------+
| jack           | 2017-01-01          | 10.0           | 10.0          |
| jack           | 2017-01-05          | 46.0           | 56.0          |
| jack           | 2017-01-08          | 55.0           | 111.0         |
| jack           | 2017-02-03          | 23.0           | 134.0         |
| jack           | 2017-04-06          | 42.0           | 176.0         |
| mart           | 2017-04-08          | 62.0           | 62.0          |
| mart           | 2017-04-09          | 68.0           | 130.0         |
| mart           | 2017-04-11          | 75.0           | 205.0         |
| mart           | 2017-04-13          | 94.0           | 299.0         |
| neil           | 2017-05-10          | 12.0           | 12.0          |
| neil           | 2017-06-12          | 80.0           | 92.0          |
| tony           | 2017-01-02          | 15.0           | 15.0          |
| tony           | 2017-01-04          | 29.0           | 44.0          |
| tony           | 2017-01-07          | 50.0           | 94.0      
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

4、rows between 1 preceding and 1 following ：当前行，上一行，下一行

例如：
sum(cost) over(partition by name order by orderdate rows between 1 preceding  and 1 following )

+----------------+---------------------+----------------+---------------+
| tb_order.name  | tb_order.orderdate  | tb_order.cost  | sum_window_0  |
+----------------+---------------------+----------------+---------------+
| jack           | 2017-01-01          | 10.0           | 56.0          |
| jack           | 2017-01-05          | 46.0           | 111.0          |
| jack           | 2017-01-08          | 55.0           | 124.0         |
| jack           | 2017-02-03          | 23.0           | 120.0         |
| jack           | 2017-04-06          | 42.0           | 65.0         |
| mart           | 2017-04-08          | 62.0           | 130.0          |
| mart           | 2017-04-09          | 68.0           | 205.0         |
| mart           | 2017-04-11          | 75.0           | 237.0         |
| mart           | 2017-04-13          | 94.0           | 169.0         |
| neil           | 2017-05-10          | 12.0           | 92.0          |
| neil           | 2017-06-12          | 80.0           | 92.0          |
| tony           | 2017-01-02          | 15.0           | 44.0          |
| tony           | 2017-01-04          | 29.0           | 94.0          |
| tony           | 2017-01-07          | 50.0           | 79.0         
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

5、lag(col,n,default) ：用于统计窗口内往上第n行值

例如：
lag(orderdate ,1,'第一次购买') over(partition by name  order by orderdate)
1
2

6、ntile（ ) : 将数据分成几份

例如:  
select
     *
from
  (select 
	   * ,
	    ntile(5) over(order by orderdate) as f
   from
	   tb_order) t
 where f = 1
1
2
3
4
5
6
7
8
9
10

十、编号函数

1、rank() 排序相同时会重复，总数不会变

例如：
rank() over(partition by subject order by score  desc

+--------------+-----------------+---------------+----------------+
| tb_sub.name  | tb_sub.subject  | tb_sub.score  | rank_window_0  |
+--------------+-----------------+---------------+----------------+
| 孙悟空          | 数学              | 95.0          | 1         |
| 宋宋           | 数学              | 86.0          | 2          |
| 婷婷           | 数学              | 85.0          | 3          |
| 娜娜           | 数学              | 56.0          | 4          |
| 宋宋           | 英语              | 84.0          | 1          |
| 娜娜           | 英语              | 84.0          | 1          |
| 婷婷           | 英语              | 78.0          | 3          |
| 孙悟空          | 英语              | 68.0          | 4         |
| 娜娜           | 语文              | 94.0          | 1          |
| 孙悟空          | 语文              | 87.0          | 2         |
| 婷婷           | 语文              | 65.0          | 3          |
| 宋宋           | 语文              | 64.0          | 4          
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2、dense_rank() 排序相同时会重复，总数会减少

例如：
dense_rank() over(partition by subject order by score  desc)

+--------------+-----------------+---------------+----------------------+
| tb_sub.name  | tb_sub.subject  | tb_sub.score  | dense_rank_window_0  |
+--------------+-----------------+---------------+----------------------+
| 孙悟空          | 数学              | 95.0          | 1                    |
| 宋宋           | 数学              | 86.0          | 2                    |
| 婷婷           | 数学              | 85.0          | 3                    |
| 娜娜           | 数学              | 56.0          | 4                    |
| 宋宋           | 英语              | 84.0          | 1                    |
| 娜娜           | 英语              | 84.0          | 1                    |
| 婷婷           | 英语              | 78.0          | 2                    |
| 孙悟空          | 英语              | 68.0          | 3                    |
| 娜娜           | 语文              | 94.0          | 1                    |
| 孙悟空          | 语文              | 87.0          | 2                    |
| 婷婷           | 语文              | 65.0          | 3                    |
| 宋宋           | 语文              | 64.0          | 4            
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

3、row_number() 会根据顺序计算

例如：
row_number() over(partition by subject order by score desc) 

+--------------+-----------------+---------------+----------------------+
| tb_sub.name  | tb_sub.subject  | tb_sub.score  | row_number_window_0  |
+--------------+-----------------+---------------+----------------------+
| 孙悟空          | 数学              | 95.0          | 1                    |
| 宋宋           | 数学              | 86.0          | 2                    |
| 婷婷           | 数学              | 85.0          | 3                    |
| 娜娜           | 数学              | 56.0          | 4                    |
| 宋宋           | 英语              | 84.0          | 1                    |
| 娜娜           | 英语              | 84.0          | 2                    |
| 婷婷           | 英语              | 78.0          | 3                    |
| 孙悟空          | 英语              | 68.0          | 4                    |
| 娜娜           | 语文              | 94.0          | 1                    |
| 孙悟空          | 语文              | 87.0          | 2                    |
| 婷婷           | 语文              | 65.0          | 3                    |
| 宋宋           | 语文              | 64.0          | 4             
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

十一、字符串正则表达式解析函数

语法：regexp_extract(string subject, string pattern, int index)

说明： 将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。

第一参数： 要处理的字段
第二参数： 需要匹配的正则表达式
第三个参数:
    0 是显示与之匹配的整个字符串
    1 是显示第一个括号里面的
    2 是显示第二个括号里面的字段...
1
2
3
4
5
6

十二、自定义函数

1、UDF(一进一出)
2、UDAF（多进一出，类似于count、max、min）
3、UDTF（一进多出，类似于explore、split）

备注： 也可以使用reflect实现自定义函数功能

1 编写任意的类 任意的方法
2 打包 
3 上传到linux 
4 add jar demo2.jar
5 调用  select reflect("类名" , "方法名" , "参数") ;
1
2
3
4
5