HQL 函数--系统内置函数

HQL 函数--系统内置函数

前言

更多往期文章请查看
大数据学习目录

查看系统自带函数

show functions;

显示自带函数的用法

desc function upper; 
desc function extended upper;

日期函数

-- 当前前日期 
select current_date;

--获得当前时间,带时间的
--建议使用current_timestamp，有没有括号都可以
select unix_timestamp();
select current_timestamp;

--时间戳转日期
select from_unixtime(1505456567); 
select from_unixtime(1505456567, 'yyyyMMdd'); 
select from_unixtime(1505456567, 'yyyy-MM-dd HH:mm:ss');

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

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

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

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

-- 当月第1天: 
select date_sub(current_date, dayofmonth(current_date)-1)

-- 下个月第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');

字符串函数

转小写。lower

select lower("HELLO WORLD");

转大写。upper

select upper("hello world");

求字符串长度。length

select length(ename), ename from mydb.emp;

字符串拼接。 concat / ||

select empno || " " ||ename idname from mydb.emp; 
select concat(empno, " " ,ename) idname from mydb.emp;

指定分隔符

SELECT concat_ws('.', 'www', array('lagou', 'com')); 
select concat_ws(" ", ename, job) from mydb.emp;

求子串

SELECT substr('www.lagou.com', 5); 
SELECT substr('www.lagou.com', -5); 
SELECT substr('www.lagou.com', 5, 5);

字符串切分

select split("www.lagou.com", "\\.");

注意:“.”需要转译

数学函数

四舍五入

select round(314.15926);
select round(314.15926, 2); 
select round(314.15926, -2);

向上取整

select ceil(3.1415926);

向下取整

select floor(3.1415926);

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

条件函数

方便案例使用,我们使用mydb.emp表

if

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

CASE WHEN

-- 将emp表的员工工资等级分类：0-1500、1500-3000、3000以上 
select sal, if (sal<=1500, 1, if (sal <= 3000, 2, 3)) from emp;

CASE WHEN a THEN b

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(b)

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)

相等为空，否则为x

SELECT nullif("b", "b"), nullif("b", "a");

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

常与 表生成函数explode结合使用

with t1 as ( select 'OK' cola, split('www.lagou.com', '\\.') colb )
select cola, colc from t1 lateral view explode(colb) t2 as colc;

案例1

--测试数据
1	1,2,3
2	2,3
3	1,2
--中间分隔式是tab
--编写sql,实现如下结果：
1 1 
1 2
1 3 
2 2 
2 3 
3 1
3 2

生产测试数据文件

cd /opt/data
vim tab1.dat
1	1,2,3
2	2,3
3	1,2

建表语句

create table tab1( id int, tags string)
row format delimited fields terminated by '\t'; 
load data local inpath '/opt/data/tab1.dat' into table tab1;

查询语句

select id,col
from tab1 lateral view explode(split(tags,',')) t1 as col;

案例2

-- 数据准备 /opt/data/score.dat
 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 '/opt/data/score.dat' overwrite into table studscore;

需求：找到每个学员的最好成绩
第一步，使用 explode 函数将map结构拆分为多行

select 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;

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

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 不能添加额外列的问题

下一篇,窗口函数