Hive 常用函数备忘

1.转换函数

cast (value as type) , 显式的将一个类型的数据转化为另一种类型的数据, 若不能进行转化则返回 null 值.

select cast(salary as char(1));

返回一个char类型的salary, 若不能进行转化则返回null值.

2.条件判断函数

case [...] when ... then ... else ... end

第一种: case when 条件 then 满足条件后的结果

case 
when tb1.os = 'android' then 'android' 
when tb1.os = 'ios' then 'iPhone' 
else 'PC' 
end as os

第二种: case 被筛选字段 when 筛选选项 then 满足条件后的结果

case tb1.os 
when 'android' then 'android'
when 'ios' then 'iPhone' 
else 'PC' 
end as os

3.非空查找函数

coalesce (T v1, T v2, …), 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL

select COALESCE(null,’aaa’,50) from table_A;

返回: aaa

4.指定精度取整函数

round(double a, int b), 返回指定精度 b 的 double 类型

select round(3.1415926,4) from table_A;

返回: 3.1416

5.替换特殊字符函数

regexp_repalce(initial_string, pattern, replacement) , 在初始字段中, 将 pattern 样式的内容, 替换为 replacement

name = '\n19013'
select regexp_replace(name,'\\\\n','') 

返回 name : 19013

6.Hive 中调用 java 函数

reflect( ) 函数可以支持在sql中调用java中的自带函数，秒杀一切udf函数。

引用java中的类,以及类型的方法,所以大小写一定要敏感!!!

hive 中建表 test_udf, 字段:column1(int),column2(int), 数据如下：
1,2
2,3
3,4

select reflect("java.lang.Math","max",column1,column2) from test_udf 
-- 使用java.lang.Math包下的 max 函数, 传入两个参数, 即 max(column1,column2)
返回结果：
2
3
4

拓展: Hive 中生成随机唯一标识id的方法 (注意大小写!!!)

regexp_replace(reflect("java.util.UUID", "randomUUID"), "-", "")

7.那年今日函数

add_months(日期，N) 函数可将日期往前、往后推N个月

select add_months('2018-02-28', 3); 

返回: 2018-05-31

8.空值转换函数

nvl(value, default), 如果value为null，则返回default的值，否则返回value.
适用于数字型、字符型和日期型，但是 value 和 default 的数据类型必须为相同类型.

从 emp 表中查询 comm,如果员工的 comm 为 NULL，则用领导编号 mgr 代替:
select comm,nvl(comm,mgr) from emp;

9.窗口函数

row_number() over (partition by col1 order by col2)

示例一:

row_number( ) 从1开始，为每一条分组记录返回一个数字

例如: row_number() over (order by xlh desc) 是先把xlh列降序，再为降序以后的没条xlh记录返回一个序号.
xlh      row_num
1700       1
1500       2
1085       3
示例二:

select *, row_number() over (partition by deptid order by salary desc) rank from employee

解释: 根据 deptid 分组，在分组内部根据 salary 排序, 而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

1.初始化数据
create table employee (empid int ,deptid int ,salary decimal(10,2))
insert into employee values(1,10,5500.00)
insert into employee values(2,10,4500.00)
insert into employee values(3,20,1900.00)
insert into employee values(4,20,4800.00)
insert into employee values(5,40,6500.00)
insert into employee values(6,40,14500.00)
insert into employee values(7,40,44500.00)
insert into employee values(8,50,6500.00)
insert into employee values(9,50,7500.00)

2.查看表数据
empid  deptid  salary
-----  ------  ------
1      10     5500.00
2      10     4500.00
3      20     1900.00
4      20     4800.00
5      40     6500.00
6      40     14500.00
7      40     44500.00
8      50     6500.00
9      50     7500.00

3.需求：根据部门分组，显示每个部门的工资等级
select *, row_number() over (partition by deptid order by salary desc) rank from employee;

返回: 
empid   deptid  salary   rank(就是row_number最后的计数)
------ ------ -------- ----------
1      10     5500.00        1
2      10     4500.00        2
4      20     4800.00        1
3      20     1900.00        2
7      40     44500.00       1
6      40     14500.00       2
5      40     6500.00        3
9      50     7500.00        1
8      50     6500.00        2

10.数字与日期截断函数

标准日期格式: yyyy-MM-dd HH:mm:ss

trunc(data[,fmt]), 函数为指定元素而截去的日期值

trunc(date, fmt) - Returns returns date with the time portion of the day truncated to the unit specified by the format model fmt.

翻译: trunc(date, fmt)——返回日期，将一天的时间截短到格式模型fmt指定的单元。

If you omit fmt, then date is truncated to the nearest day. It now only supports ‘MONTH’/‘MON’/‘MM’ and ‘YEAR’/‘YYYY’/‘YY’ as format.

翻译: 如果您省略fmt，则日期将被截断为最近的日期。现在只支持’MONTH’/‘MON’/‘MM’和’YEAR’/‘YYYY’/'YY’格式。

date is a string in the format ‘yyyy-MM-dd HH:mm:ss’ or ‘yyyy-MM-dd’. The time part of date is ignored.

翻译: 日期是一个格式为’yyyy-MM-dd HH:mm:ss’或’yyyy-MM-dd’的字符串。日期的时间部分被忽略。

SELECT trunc('2009-02-12', 'MM');
返回: '2009-02-01'

SELECT trunc('2015-10-27', 'YEAR');
返回: '2015-01-01'

拓展: 区分 Oracle 中的 trunc 函数:按照指定精度截取一个数,小数点左面以0来补位;
SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) TRUNC2 from dual;
TRUNC1     TRUNC2
------   ----------
100        124.16

11.拼接函数

concat(str1,str2,...): 将多个字符串拼接成一个字符串, 若其中有一个参数为null ,则返回结果为null ;

+----+--------+
| id | name   |     
+----+--------+      -- 表中原始数据
| 1  | BioCyc |
+----+--------+

SELECT CONCAT(id, ‘，’, name) AS con FROM table LIMIT 1;
+----------+
| con      |
+----------+         -- 返回结果为拼接后字符串
| 1,BioCyc |
+----------+ 

SELECT CONCAT(‘My’, NULL, ‘QL’);     
+--------------------------+
| CONCAT('My', NULL, 'QL') |
+--------------------------+      -- 若需要拼接的字符串中包含 null, 则返回的结果为 null;
| NULL                     |
+--------------------------+

12.指定参数间分隔符函数

concat_ws() 代表 concat with separator ，是concat()的特殊形式

concat_ws(separator,str1,str2,…), 参数一为分隔符,参数二三为需要拼接字符串

  1. 若分隔符为 NULL，则结果为 NULL
  2. 忽略任何分隔符参数后的 NULL 值( 但是CONCAT_WS()不会忽略任何空字符串 )
+----+--------+
| id | name   |     
+----+--------+      -- 表中原始数据
| 1  | BioCyc |
+----+--------+

SELECT CONCAT_WS('_',id,name) AS con_ws FROM info LIMIT 1;
+----------+
| con_ws   |
+----------+         -- 两个字符串中间分割符为 ' _ '
| 1_BioCyc |
+----------+

SELECT CONCAT_WS(',','First name',NULL,'Last Name');
+----------------------------------------------+
| CONCAT_WS(',','First name',NULL,'Last Name') |
+----------------------------------------------+       -- 忽略指定分隔符后面的所有null值
| First name,Last Name                         |
+----------------------------------------------+

SELECT CONCAT_WS(NULL,'First name','Last Name');
+----------------------------------------------+
| CONCAT_WS(NULL,'First name','Last Name')     |
+----------------------------------------------+       -- 若分隔符为null则返回结果为null
| NULL                                         |
+----------------------------------------------+

13.列转行函数

collect_set/collect_list(col), 将某一列汇总产生 Array 类型的字段.

  1. 函数只接受基本数据类型,只接受一列参数
  2. set 去重, list 不去重, 两者区别仅此而已
  3. 通常配合 group by 使用, 直接汇总分组数据

详细见 Hive 笔记本中: Hive collect_list/_set（列转行)

14.日期时间转日期

to_data(string timestamp), 返回日期时间字段中的日期部分

hive> select to_date('2011-12-08 10:03:01');
返回: 2011-12-08

hive> select to_date('2011-12-08');
返回: 2011-12-08

hive> select to_date('2011-12');
返回: NULL

注意: 与 cast(xxx as date) 进行区分!!

15.字符串分割函数

split(str,regex), 将 str 字符串按照 regex 格式进行分割,]

a.基本用法：

split('a,b,c,d',',')
返回: ["a","b","c","d"]

b.截取字符串中的某个值：

当然，我们也可以指定取结果数组中的某一项

split('a,b,c,d',',')[0]
返回: a

c.特殊字符的处理：

特殊分割符号
regex 为字符串匹配的参数，所以遇到特殊字符的时候需要做特殊的处理
split('192.168.0.1','.')
返回: []    

正确的写法：对特殊字符形式进行分割时,需要进行转义
split('192.168.0.1','\\.')
返回: ["192","168","0","1"]

16.转化为日期函数

from_unixtime(str, format), 按照 format 格式, 将时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）转化为当前时区时间

select from_unixtime(1356768454, 'yyyy-MM-dd');

输出结果：2012-12-29

17.转化为 unix 时间戳函数

unix_timestamp(str, format), 将格式为 format 的日期转化为 unix 时间戳, 如果不添加任何参数 unix_timestamp(), 则返回当前时区的 unix 时间戳;

select unix_timestamp('2012-12-29 13:01:03','yyyy-MM-dd HH:mm:ss');

输出结果：1356757263

注意: 如果转化失败, 返回为0 ;

18.with name as (…) 子查询部分

with name as (...) 需要定义一个sql片段,会将这个片段产生的结果集保存在内存中,后续的sql均可以访问这个结果集和,可以提高查询性能,作用与视图或临时表类似.

限制:

  1. with name as …必须和其他sql一起使用
  2. with name as …是一次性的

优点:

  1. 提高代码可读性(结构清晰)
  2. 简化sql,优化执行速度(with子句只需要执行一次)