Hive——函数操作（一）

一，系统内置函数：

1.查看系统函数

-- 查看系统自带函数 
show functions; 

-- 显示自带函数的用法 
desc function upper;
desc function extended upper;

2，日期函数

--当前日期
select current_date;    2020-10-02

--当前日期时间戳
 select unix_timestamp();    1601646202

--当前时间
select current_timestamp();    2020-10-02 21:45:10.287

-- 时间戳转日期
select from_unixtime(1505456567);    2017-09-15 14:22:47
select from_unixtime(1505456567, 'yyyyMMdd');     20170915
select from_unixtime(1505456567, 'yyyy-MM-dd HH:mm:ss');     2017-09-15 14:22:47

-- 日期转时间戳 
select unix_timestamp('2019-09-15 14:23:00');    1568528580

-- 计算时间差 
select datediff('2020-04-18','2019-11-21');    149
select datediff('2019-11-21', '2020-04-18');    -149

-- 查询当月第几天
 select dayofmonth(current_date);

-- 计算月末: 
select last_day(current_date);

-- 当月第1天: 
select date_sub(current_date, dayofmonth(current_date)-1)
--hive>select date_sub('2016-12-29',10);    2016-12-19


-- 下个月第1天: 
select add_months(date_sub(current_date, dayofmonth(current_date)-1), 1)

---- 字符串转时间（字符串必须为：yyyy-MM-dd格式）
select to_date('2020-01-01'); 
select to_date('2020-01-01 12:12:12');

-- 日期、时间戳、字符串类型格式化输出标准时间格式 
select date_format(current_timestamp(), 'yyyy-MM-dd HH:mm:ss'); 
select date_format(current_date(), 'yyyyMMdd'); 
select date_format('2020-06-01', 'yyyy-MM-dd HH:mm:ss'); 

-- 计算emp表中，每个人的工龄 
select *, round(datediff(current_date, hiredate)/365,1) workingyears from emp;

3.字符串函数

-- 转小写。lower 

select lower("HELLO WORLD"); 

-- 转大写。upper 
select upper("aa") 

-- 求字符串长度。length 
select length("23sdfds");

-- 字符串拼接。 concat / ||
select concat("a","-","b");
select "a" || "*" || "c";

--指定分隔符。concat_ws(separator, [string | array(string)]+)
SELECT concat_ws('.', 'www', array('lagou', 'com'));  --www.lagou.com

--求子串。substr
SELECT substr('www.lagou.com', 5);     --lagou.com
SELECT substr('www.lagou.com', -5);     --u.com
SELECT substr('www.lagou.com', 5, 5);    --lagou

-- 字符串切分。split，注意 '.' 要转义 
select split("www.lagou.com", "\\.");

4.数学函数

-- 四舍五入。round 
select round(314.15926);     314
select round(314.15926, 2);     314.16
--，即四舍五入到十位，此时个位是5会进位
select round(255, -1)    260
select round(314.15926, -2);   300

select round(354.15926, -2);    400

-- 向上取整。ceil 
select ceil(3.1415926);    4

-- 向下取整。floor 
select floor(3.1415926);    3

-- 其他数学函数包括：绝对值、平方、开方、对数运算、三角运算等

5.条件函数

if else   case when 

-- if (boolean testCondition, T valueTrue, T valueFalseOrNull) 

select sal, if (sal<1500, 1, if (sal < 3000, 2, 3)) from emp; 

-- CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END 
-- 将emp表的员工工资等级分类：0-1500、1500-3000、3000以上 

select sal, if (sal<=1500, 1, if (sal <= 3000, 2, 3)) from emp; 

-- CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END 
-- 复杂条件用 case when 更直观 
select sal, case when sal<=1500 then 1 when sal<=3000 then 2 else 3 end sallevel from emp;

-- COALESCE(T v1, T v2, ...)。返回参数中的第一个非空值；如果所有值都为 NULL，那么返回NULL 
select sal, coalesce(comm, 0) from emp;


-- isnull(a) isnotnull(a) 
select * from emp where isnull(comm); 
select * from emp where isnotnull(comm);


-- nvl(T value, T default_value) 
select empno, ename, job, mgr, hiredate, deptno, sal + nvl(comm,0) sumsal from emp;


-- nullif(x, y) 相等为空，否则为a 
SELECT nullif("b", "b"), nullif("b", "a");

6.UDTF函数【重要】

UDTF : User Defined Table-Generating Functions。用户定义表生成函数，一行输入，多行输出。

-- explode，炸裂函数 
-- 就是将一行中复杂的 array 或者 map 结构拆分成多行 
select explode(array('A','B','C')) as col; 
select explode(map('a', 8, 'b', 88, 'c', 888));

-- lateral view 语法：

lateralView: LATERAL VIEW udtf(expression) tableAlias AS

columnAlias ( ',' columnAlias)*

fromClause: FROM baseTable (lateralView)*

 

 

-- 数据准备

lisi|Chinese:90,Math:80,English:70

wangwu|Chinese:88,Math:90,English:96

maliu|Chinese:99,Math:65,English:60

-- 创建表

create table studscore(

name string

,score map<String,string>)

row format delimited

fields terminated by '|'

collection items terminated by ','

map keys terminated by ':' ;

-- 加载数据

load data local inpath '/home/hadoop/data/score.dat' overwrite

into table studscore;

-- 需求：找到每个学员的最好成绩

-- 第一步，使用 explode 函数将 map 结构拆分为多行

select explode(score) as (subject, socre) from studscore;

-- 但是这里缺少了学员姓名，加上学员姓名后出错。下面的语句有是错的

select name, explode(score) as (subject, socre) from studscore;

-- 第二步： explode 常与 lateral view 函数联用，这两个函数结合在一起能关联其

他字段

select name, subject, score1 as score from studscore

lateral view explode(score) t1 as subject, score1;

-- 第三步：找到每个学员的最好成绩

select name, max(mark) maxscore

from ( select name, subject, mark

from studscore lateral view explode(score) t1 as

subject, mark) t1

group by name;

with tmp as (

select name, subject, mark

from studscore lateral view explode(score) t1 as subject, mark

)

select name, max(mark) maxscore

from tmp

group by name;

 

小结：

将一行数据转换成多行数据，可以用于array和map类型的数据；

lateral view 与 explode 联用，解决 UDTF 不能添加额外列的问题