1.4.3.8 Hive的函数操作（上）
2020-04-12 本文已影响0人
寒暄_HX
总目录:https://www.jianshu.com/p/e406a9bc93a9
Hadoop - 子目录：https://www.jianshu.com/p/9428e443b7fd
这一节说一下HIVE自带的函数。
缺失值处理
nvl
- 用途：给值为 NULL 的数据赋值。
- 语法：NVL( string1, replace_with)
- 功能：它的功能是如果string1 为 NULL，则 NVL 函数返回 replace_with 的值，否则返回 string1 的值，如果两个参
数都为 NULL ，则返回 NULL。 - 例子：如果员工的 comm 为 NULL，则用-1 代替
select nvl(comm,-1) from emp;
时间处理
date_format
- 用途：格式化时间
- 语法：data_format( string1, replace_with)
- 功能：它的功能是如果string1 为 日期格式的字符串（2000-01-01），replace_with是相符合的日期格式（YYYY-MM-DD），则将string1转换为日期类型。
- 例子：
select date_format('2019-06-29','yyyy-MM-dd');
date_add
- 用途：时间跟天数相加
- 语法：date_add( string1, int1)
- 功能：string1加上int1，结果转换为日期类型。
- 例子1：
select date_add('2019-06-29',5);
- 例子2：
select date_add('2019-06-29',-5);
date_sub
-
用途：时间跟天数相减
-
语法：date_sub( string1, int1)
-
功能：string1减去int1，结果转换为日期类型。
-
例子1：
select date_sub('2019-06-29',5);
-
例子2：
select date_sub('2019-06-29',-5);
datediff
-
用途：两个时间相减
-
语法：datediff( string1,string2)
-
功能：string1减去string2，结果转换为整数类型。
-
例子1：
select datediff('2019-06-29','2019-06-24');
-
例子2：
select datediff('2019-06-24','2019-06-29');
CASE WHEN
在hive中表示的是实时计算。
首先建个表，导入数据。
create table emp_sex(
name string,
dept_id string,
sex string)
row format delimited fields terminated by "\t";
load data local inpath '/usr/hive_data/emp_sex.txt' into table
emp_sex;
- 需求：求出不同部门男女各多少人。
select
dept_id,
sum(case sex when '男' then 1 else 0 end) male_count,
sum(case sex when '女' then 1 else 0 end) female_count
from
emp_sex
group by
dept_id;
行转列
相关函数
- CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
- CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
- COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生 array 类型字段。
建表，导入数据
create table person_info(
name string,
constellation string,
blood_type string)
row format delimited fields terminated by "\t";
load data local inpath "/usr/hive_data/person_info.txt" into table person_info;
- 需求：把星座和血型一样的人归类到一起。
select
t1.base,
concat_ws('|', collect_set(t1.name)) name
from
(select
name,
concat(constellation, ",", blood_type) base
from
person_info) t1
group by
t1.base;
列转行
create table movie_info(
movie string,
category array<string>)
row format delimited fields terminated by "\t"
collection items terminated by ",";
load data local inpath "/usr/hive_data/movie.txt" into table movie_info;
- 需求：将电影分类中的数组数据展开。
select
movie,
category_name
from
movie_info lateral view explode(category) table_tmp as category_name;
窗口函数
什么是窗口函数
我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的.但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数.
窗口函数用于数据分析的工作，一般用于LOAP处理。
OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化；
CURRENT ROW：当前行； n PRECEDING：往前 n 行数据； n FOLLOWING：往后 n 行数据；
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点，UNBOUNDED
FOLLOWING 表示到后面的终点；
LAG(col,n)：往前第 n 行数据；
LEAD(col,n)：往后第 n 行数据；
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从 1 开始，
对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。
准备数据
create table business(
name string,
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
load data local inpath "/usr/hive_data/business.txt" into table business;
- 需求:查询在 2015 年 4 月份购买过的顾客及购买次数
select name,count(*) over()
from business
where substring(orderdate,1,7) = '2015-04'
group by name;
- 需求:查询顾客的购买明细及购买总额
select name,orderdate,cost,sum(cost) over(partition by month(orderdate))
from business;
- 需求:查询顾客上次的购买时间
select name,orderdate,cost, lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1,lag(orderdate,2) over (partition by name order by orderdate) as time2
from business;
- 需求:查询前 20%时间的订单信息
select * from (
select name,orderdate,cost, ntile(5) over(order by orderdate)
sorted
from business
) t
where sorted = 1;
RANK
排名函数。
RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算
准备数据
create table score(
name string,
subject string,
score int)
row format delimited fields terminated by "\t";
load data local inpath '/usr/hive_data/score.txt' into table score;
- 需求：计算每门学科成绩排名。
select name,subject,score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from score;
