**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));

hive表名大小写 hive大小写转换_hive

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

hive表名大小写 hive大小写转换_hive表名大小写_02

第 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片,返回当前切片值

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