数据仓库工具 hive的入门（五）HQL操作之--函数

**HQL操作之–函数

提示：本文章内容取自来源： 拉勾教育大数据高薪训练营

---

---

前言

提示：本文章对于初学者准备，希望对大家有所帮助。如果有什么建议和疑问，请留言给我，我会不断完成完善。

---

HQL操作之–函数

第 1 节 系统内置函数

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

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

日期函数

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

字符串函数

-- 转小写。lower
select lower("HELLO WORLD");

-- 转大写。upper
select lower(ename), ename from emp;

-- 求字符串长度。length
select length(ename), ename from emp;

-- 字符串拼接。 concat / ||
select concat(empno, " " ,ename) idname from emp;

-- 指定分隔符。concat_ws()
select concat_ws(" ", ename, job) from emp;

数学函数

-- 四舍五入。round
select round(314.15926);
select round(314.15926, 2);
select round(314.15926, -2);

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

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

条件函数

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

-- case when a thenb [WHEN c THEN d]* [ELSE e] END
( 将emp表的员工工资等级分类：0-1500、1500-3000、3000以上)
-- 复杂条件用 case when 更直观
select sal, case when sal<=1500 then 1
        		case  when sal<=3000 then 2
        		else 3 
        		end sallevel
 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

UDTF函数

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

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

第 2节 窗口函数(重点)

窗口函数又名开窗函数，属于分析函数的一种。用于解决复杂报表统计需求的功能强大的函数，很多场景都需要用到。

over 关键字

使用窗口函数之前一般要要通过over()进行开窗

使用窗口函数，查询员工姓名、薪水、薪水总和
select ename, sal, sum(sal) over() salsum,
   concat(round(sal / sum(sal) over()*100, 1) || '%')
ratiosal
 from emp;

注意：窗口函数是针对每一行数据的；如果over中没有参数，默认的是全部结果
集；

partition by子句

在over窗口中进行分区，对某一列进行分区统计，窗口的大小就是分区的大小

-- 查询员工姓名、薪水、部门薪水总和
select ename, sal, sum(sal) over(partition by deptno) salsum
 from emp;

order by 子句

-- 增加了order by子句；sum：从分组的第一行到当前行求和
select ename, sal, deptno, sum(sal) over(partition by deptno
order by sal) salsum
 from emp;

排名函数

都是从1开始，生成数据项在分组中的排名。
row_number()。排名顺序增加不会重复；如1、2、3、4、… …
RANK()。 排名相等会在名次中留下空位；如1、2、2、4、5、… …
DENSE_RANK()。 排名相等会在名次中不会留下空位 ；如1、2、2、3、4、… …

序列函数

lag():返回当前数据行的上一行数据
lead():返回当前数据行的下一行数据
first_value():取分组内排序后，截止到当前行，第一个值
last_value():分组内排序后，截止到当前行，最后一个值
ntile():将分组的数据按照顺序切分成n片，返回当前切片值

后续为练习题目，希望大家积极完成