1.4.3.8 Hive的函数操作（上）

总目录: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;