hive笔记（七）：函数-内置函数/空字段赋值/行转列/列转行/窗口函数/Rank

目录

函数

系统内置函数

常用内置函数

空字段赋值

CASE WHEN THEN ELSE END

行转列

列转行

窗口函数

Rank

---

函数

系统内置函数

（1）查看系统自带函数

show functions;

（2）显示自带函数的用法

desc function if;

（3）详细显示自带函数的用法

desc function extended if;

常用内置函数

空字段赋值

（1）说明

赋值，它的格式是NVL(value，default_value)。功能是如果value为NULL，则NVL函数返回default_value的值，否则返回value的值，如果都为NULL ，则返回NULL。

（2）准数据备

查看前面内容！

（3）查询

NVL(value，default_value)

1)comm为null，default_value为-1

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

 

 2）comm为null，default_value为领导id

select comm,nvl(comm,mgr) from emp;

CASE WHEN THEN ELSE END

1）准备数据

emp_sex.txt

悟空 A 男
大海 A 男
宋宋 B 男
凤姐 A 女
婷姐 B 女
婷婷 B 女

建表

create table emp_sex(
name string,
dept_id string,
sex string)
row format delimited fields terminated by " ";

 导入数据

load data local inpath '/root/datas/emp_sex.txt' into table emp_sex;

2）查询：求出不同部门男女各多少人

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;

 

行转列

（1）相关函数说明

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入的字符串;

CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的CONCAT()，除第一个参数外剩余参 数间的分隔符，分隔符可以是与剩余参数一样的字符串；如果分隔符是NULL返回值也将为NULL。这个函数会跳过分隔符参数后的任何NULL和空字符串，分隔符将被加到被连接的字符串之间；

注意: CONCAT_WS must be "string or array<string>

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生Array类型字段。

（2）数据准备

/root/datas/person.txt

孙悟空 白羊座 A
猪八戒 白羊座 A
牛魔王 射手座 A
白眉大王 白羊座 B
金角大王 射手座 A
哪吒 白羊座 B

（3）创建hive表

create table person(
name string,
constellation string,
biood string)
row format delimited fields terminated by " ";

load data local inpath "/root/datas/person.txt" into table person;

（4）把星座和血型一样的人归类

SELECT
t1.c_b,
CONCAT_WS("|",collect_set(t1.name))
FROM (
SELECT
NAME,
CONCAT_WS(',',constellation,blood) c_b
FROM person
)t1
GROUP BY t1.c_b；

 

列转行

（1）函数说明

EXPLODE(col)：将hive一列中复杂的Array或者Map结构拆分成多行。

LATERAL VIEW：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

（2）数据准备

movie.txt

《杀死那个男人》 悬疑,动作,科幻,剧情
《分手那天》 悬疑,警匪,动作,心理,剧情
《笨蛋的战争》 战争,动作,灾难

（3）创建hive表

create table movie(
name string,
category string)
row format delimited fields terminated by " ";

load data local inpath "/root/datas/movie.txt" into table movie;

（4）把电影分类中的数组展开

select name,category from movie lateral VIEW explode(split(category,",")) movie_tmp AS category_name;

没有拆分成功，我也不知道为什么

 

窗口函数

（1）函数说明

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。

CURRENT ROW：当前行；

n PRECEDING：往前n行数据；

n FOLLOWING：往后n行数据；

UNBOUNDED：起点；

UNBOUNDED PRECEDING：表示从前面的起点；

UNBOUNDED FOLLOWING：表示到后面的终点；

LAG(col,n,default_val)：往前第n行数据；

LEAD(col,n, default_val)：往后第n行数据；

NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

（2）窗口函数

business.txt

jack,2022-08-01,10
tony,2022-08-02,15
jack,2022-08-03,23
tony,2022-08-04,29
jack,2022-08-05,46
jack,2022-09-06,42
tony,2022-09-07,50
jack,2022-08-08,55
mart,2022-08-08,62
mart,2022-08-09,68
neil,2022-09-10,12
mart,2022-09-11,75
neil,2022-09-12,80
mart,2022-09-13,94

（3）创建hive数据表

create table business(
name string,
cost_date string,
cost string)
row format delimited fields terminated by ",";

load data local inpath "/root/datas/business.txt" into table business;

（4）查询在2022年8月购买的顾客及人数

select name,count(*) over() from business 
where substring(cost_date,1,7)='2022-08' 
group by name;

 

（5）查询顾客购买明细及月购买总额

 select name,cost_date,cost,sum(cost) 
over(partition by month(cost_date)) from business;

 

 

（6）将每一个顾客的cost按照日期进行累加

select name,cost_date,cost,sum(cost) over() as sample1,
sum(cost) over(partition by name) as sample2,
sum(cost) over(partition by name order by cost_date) as sample3,
sum(cost) over(partition by name order by cost_date rows between UNBOUNDED PRECEDING and current row) as sample4 
from business;

 sample1：所有行相加；

sample2：按照name进行分组，组内数据进行相加；

sample3：按照name进行分组，组内数据累加；

sample4：按照name进行分组，组内数据累加（另一个方法）；

 

select name,cost_date,cost,sum(cost) over(partition by name order by cost_date rows between 1 PRECEDING and current row) as sample5,
sum(cost) over(partition by name order by cost_date rows between 1 PRECEDING and 1 FOLLOWING) as sample6,
sum(cost) over(partition by name order by cost_date rows between current row and UNBOUNDED FOLLOWING) as sample7 from business;

sample5：当前行和前面行做聚合；

sample6：当前行与前面一行和后面一行做聚合；

sample7：当前行和后面所有行做聚合； 

 

 

（7）查看上次购买时间

select name,cost_date,cost,
lag(cost_date,1,'2022-07-30') over(partition by name order by cost_date) as time1,
lag(cost_date,2) over(partition by name order by cost_date) as time2 
from business;

 

（8）查看前20%时间的订单信息

select * from (select name,cost_date,cost,ntile(5) 
over(order by cost_date)sorted from business) t where sorted = 1;

 

（9） 查看前50%时间的订单信息

select * from (select name,cost_date,cost,ntile(2) 
over(order by cost_date)sorted from business) t where sorted = 1;

 

Rank

（1）函数说明

RANK() ：排序相同时会重复，总数不会变；

DENSE_RANK()： 排序相同时会重复，总数会减少；

ROW_NUMBER() ：会根据顺序计算；

（2）数据准备

score.txt

孙悟空 语文 87
孙悟空 数学 95
孙悟空 英语 68
猪八戒 语文 94
猪八戒 数学 56
猪八戒 英语 84
沙和尚 语文 64
沙和尚 数学 86
沙和尚 英语 84
唐僧 语文 65
唐僧 数学 85
唐僧 英语 78

（3）创建hive数据表

create table score(
name string,
subject string,
score string)
row format delimited fields terminated by " ";


load data local inpath "/root/datas/score.txt" into table score;

（4）计算每门学科成绩排名

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;

本文为学习笔记！！！