Hive---＞函数的使用

hive中函数的简单简绍

• hive中的函数大致分为三种：UDF、UDTF、UDAF
• UDF：是一进一出的
• UDAF：多进一出
• UDTF：一进多出
• 注意：这个一指的是行数

系统内置函数

1. 查看系统内置函数:show functions;
2. 显示自带函数的使用方法:desc function upper;
3. 详细显示系统自带函数的使用方法:desc function ectended upper'

空字段赋值函数（NVL）

• 如果员工的奖金为null，就赋值为-1

select comm,nvl(comm,-1) from emp;

CASE WHEN THEN ELSE ENG

数据准备

姓名	部门	性别
张三	A	男
李四	B	女
王五	A	男
赵六	A	女

需求

• 求出不同部门的男女各多少人

select
   dpt,
   sum(case gender when '男' then 1 else 0 end) male,
   sum(case gender when '女' then 1 else 0 end) female
from
   emp
group by
    dpt;
//或者如下也是可以的
select
    dpt,
    sum(if(sex='男',1,0)) male,
    sum(if(sex='女',1,0)) female
from
   emp
group by
   dpt; 

行转列（concat、concat_ws、collect_set、collect_list）

concat

select concat('hello','-','world');

• 拼接列

select concat(id,'-',name) from emp;

concat_ws

• 当拼接符号一样的时候可以使用concat_ws，但是他只可以拼接string类型的
• 这个还可以放数组，将数组里的元素使用第一个符号拼接

select concat_ws('-','hello','world','china');
select concat_ws('-',id,name) from emp;

collect_set

• collect_set函数只接受基本数据类型，他是将字段的值进行去重,返回的是一个数组
• collect_list函数显示出所有的元素,返回的是一个数组

select collect_set(col) from emp;

练习

需求

• 将星座和血性一样的人归纳到一起
• 数据:

name	coll	blood
孙悟空	白羊	A
大海	射手	A
松松	白羊	B
八戒	白羊	A
沙僧	射手	A

select
    type,concat_ws('|',collect_set(name)) 
    from (select concat(coll,',',blood) as type,name from nametype) a
    group by type;

列转行函数(explode)

功能简绍

• explode(col):将hive一列中复杂的Array或者Map结构拆分成多行
• lateral view:他能将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合

实例

• 要求：将数据展开
• 数据准备

电影名	类型
《疑犯追踪》	[“悬疑”,“科幻”,“动作”,“剧情”]
《LIE TO ME》	[“悬疑”,“警匪”,“动作”,“心理”,“剧情”]
《战狼2》	[“战争”,“动作”,“灾难”]

select name,type_name from filetype lateral view explode (type) table_tmp as type_name;

• 结果集

name	type_name
《疑犯追踪》	悬疑
《疑犯追踪》	科幻
《疑犯追踪》	动作
《疑犯追踪》	剧情
《LIE TO ME》	悬疑
《LIE TO ME》	警匪
《LIE TO ME》	动作
《LIE TO ME》	心理
《LIE TO ME》	剧情
《战狼2》	战争
《战狼2》	动作
《战狼2》	灾难

窗口函数（开窗函数）

相关函数说明

• over():指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变
• CURRENT ROW:当前行
• n PRECEDING:往前n行数据
• n FOLLOWING:往后n行数据
• UNBOUNDED:起点，UNBOUNDED PRECEDING 表示从前面的起点 UNBOUNDED FOLLOWING表示到后面终点
• LAG(col,n) 往前第n行数据
• LEAD(col,n) 往后第n行
• NTILE 把有序分区中的行分发到指定数据的组中，每个组有编号，编号从1开始

练习

• 数据准备

Jack 2017-01-01 10
Tony 2017-01-02 15
Jack 2017-02-03 23
Tony 2017-01-04 29
Jack 2017-01-05 46
Tony 2017-04-06 42
Jack 2017-01-07 50
Mark 2017-01-08 55
Mark 2017-04-09 62
Neil 2017-04-10 68
Mark 2017-05-11 12
Neil 2017-06-12 75

需求

• 查询在4月购买过的顾客和总人数

select name,count(*) over() from goods where substring(orderdate,6,2)='04';

• 查询顾客购买的明细和月购买总额

select *,sum(cost) over(rows between unbounded preceding and current row) from goods;

• 上述的场景，将每个顾客的cost按照日期进行累加

select *, sum(cost) over(partition by name order by orderdate) from goods;

• 查询每个顾客上次购买的时间

select name,orderdate,cost,lag(orderdate,1) over(distribute by name sort by orderdate) from goods;

• 查询前百分之20的订单信息

select name,orderdate,cost from 
(select name,orderdate,cost,ntile(5) over(order by orderdate) ntile_5  from goods)t 
where ntile_5=1;

Rank

函数说明

• rank():相同的在一个名次，但是总数不变
• dense_rank():这个相同的在同一个名次,还是连续
• row_number():相同的不在一个每次

其他常用函数

时间函数

//返回当前或者指定时间的时间戳
select unix_timestamp();
select unix_timestamp('2020-11-30','yyyy-MM-dd');
//将时间戳转换为日期格式
select from_unixtime(1606694400);
//将时间戳转换为指定的日期格式
select from_unixtime(1606694400,'yyyy-MM');
//当前日期(最小单位号)
select current_date;
//当前日期(最小单位到秒)
select current_timestamp;
//抽取日期部分,获取到号
select to_date('2020-02-01 12:12:12');
//获取年(月、日、时、分、秒一样)（month、day、hour、mintue、second）
select year('2020-02-01 12:12:12');
//当前时间时一年中的第几周
select weekofyear('2020-11-30');
//当前时间时一月中的第几周
select weekofmonth('2020-11-30');
//两个日期间的月份(前面减后面)
select months_between('2020-04-01','2020-10-01');
//日期加减月
select add_months('2020-11-30',3);
select add_months('2020-11-30',-3);
//日期间相差的天数(前减后)
select datediff('2020-04-01','2020-10-01');
//日期加天数
select date_add('2020-11-30',4);
select date_add('2020-11-30',-4);
//日期所在月份的最后一天
select last_day('2020-02-28');
//格式化日期
select date_format('2020-11-30 12:00:01','yyyy/MM/dd HH mm ss');

取整函数

//四舍五入
select round(3.24);
//向上取整
select ceil(3.14);
//向下取整
select floor(3.14);

字符串函数

//转大写
select upper('low');
//转小写
select lower('LOW');
//字符串长度
select length('123');
//前后去空格
select trim('   aa ');
//替换
select regexp_replace('2020/01/01','/','-');

集合函数

//返回a集合中的元素个数
select size(a);
//返回mao中的key
select map_keys(a);
//返回map中的values
select map_values(a);
//判断集合a中是否包含某个元素
select array_contains(a,'a');
//将array中的元素排序
select sort_array(a);