**HQL操作之–函数
文章目录
- 前言
- HQL操作之--函数
- 第 1 节 系统内置函数
- 日期函数
- 字符串函数
- 数学函数
- 条件函数
- UDTF函数
- 第 2节 窗口函数(重点)
- over 关键字
- partition by子句
- order by 子句
- 排名函数
- 序列函数
前言
提示:本文章对于初学者准备,希望对大家有所帮助。如果有什么建议和疑问,请留言给我,我会不断完成完善。
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片,返回当前切片值
后续为练习题目,希望大家积极完成