文章目录
常用查询函数
1、空字段赋值（NVL）
给值为NULL的数据赋值，它的格式是NVL( value，default_value)。它的功能是如果value为NULL，则NVL函数返回default_value的值，否则返回value的值，如果两个参数都为NULL ，则返回NULL。
--NVL( value，default_value)
--将职员的奖金为NULL的置为-1
select comm,nvl(comm,-1)
from emp;
2、CASE WHEN（相当于java的switch case）
--举例：求出不同部门男女各多少人
--输入数据
name dept_id sex
悟空 A 男
大海 A 男
宋宋 B 男
凤姐 A 女
婷姐 B 女
婷婷 B 女
--需求数据
A 2 1
B 1 2
--1、创表
create table emp_sex(
name string,
dept_id string,
sex string)
row format delimited fields terminated by "\t";
--2、导入数据
load data local inpath '/usr/local/soft/hive-1.2.1/data/emp_sex.txt' into table emp_sex;
--3、按需求查询数据
select
dept_id,
sum(case sex when '男' then 1 else 0 end) male,
sum(case sex when '女' then 1 else 0 end) female
from
emp_sex
group by
dept_id;
3、行转列（聚合）
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。
--举例：把星座和血型一样的人归类到一起。
--输入数据
name constellation blood_type
孙悟空 白羊座 A
大 海 射手座 A
宋 宋 白羊座 B
猪八戒 白羊座 A
凤 姐 射手座 A
苍老师 白羊座 B
--需求数据
射手座,A 大 海|凤 姐
白羊座,A 孙悟空|猪八戒
白羊座,B 宋 宋|苍老师
--1、创表
create table person_info(
name string,
constellation string,
blood_type string)
row format delimited fields terminated by "\t";
--2、导入数据
load data local inpath '/usr/local/soft/hive-1.2.1/data/constellation.txt' into table person_info;
--3、按需求查询数据
select
concat(constellation,",",blood_type) con,
concat_ws("|",collect_list(name)) ren
from
person_info
group by
constellation,blood_type;
4、列转行
EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
--将电影分类中的数组数据展开
--输入数据
movie category
《疑犯追踪》 悬疑,动作,科幻,剧情
《Lie to me》 悬疑,警匪,动作,心理,剧情
《战狼2》 战争,动作,灾难
--需求数据
《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 心理
《Lie to me》 剧情
《战狼2》 战争
《战狼2》 动作
《战狼2》 灾难
--1、创表
create table movie_info(
movie string,
category array<string>)
row format delimited fields terminated by "\t";
--2、导入数据
load data local inpath '/usr/local/soft/hive-1.2.1/data/movie.txt' into table movie_info;
--3、按需求查询数据
select
movie,
category_name
from
movie_info
lateral view
explode(category) table_tmp
as category_name;
拓展：将上述原表按照电影类别划分电影，输出如下：
| 剧情 | ["《疑犯追踪》","《Lie to me》"] |
| 动作 | ["《疑犯追踪》","《Lie to me》","《战狼2》"] |
| 心理 | ["《Lie to me》"] |
| 悬疑 | ["《疑犯追踪》","《Lie to me》"] |
| 战争 | ["《战狼2》"] |
| 灾难 | ["《战狼2》"] |
| 科幻 | ["《疑犯追踪》"] |
| 警匪 | ["《Lie to me》"] |
--按需求查询数据
select
b.category_name,
collect_set(b.movie)
from
(select
movie,category_name
from
movie_info
lateral view
explode(category) ta as category_name) b
group by
b.category_name;
5、窗口函数（开窗函数）
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类型。
--输入数据
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
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94
--建表语句
create table business(
name string,
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
--导入数据
load data local inpath "/usr/local/soft/hive-1.2.1/data/business.txt" into table business;
--需求：
/*1、查询在2017年4月份购买过的顾客及总人数(substring切分字符串)*/
--1)初始
select
count(distinct name)
from
business
where
substring(orderdate,1,7)="2017-04";
--2)进阶（将姓名和统计人数都显示）
select
distinct name,
count(*) over()
from
business
where
substring(orderdate,1,7)="2017-04";
/*2、查询顾客的购买明细及月购买总额*/
select
name,
cost,
orderdate,
sum(cost) over(partition by substring(orderdate,1,7))
from
business;
/*3、上述的场景, 将每个顾客的cost按照日期进行累加*/
--窗口函数：按名字分区，区内按照时间排序，最后加范围（unbounded preceding每组第一行，current row当前行）的行
select
name,
cost,
orderdate,
sum(cost) over(partition by substring(orderdate,1,7)) mc,
sum(cost) over(partition by name order by orderdate asc rows between unbounded preceding and current row) lc
from
business;
--拓展：
select name,orderdate,cost,
sum(cost) over() as sample1,--所有行相加
sum(cost) over(partition by name) as sample2,--按name分组，组内数据相加
sum(cost) over(partition by name order by orderdate) as sample3,--按name分组，组内数据累加
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
from business;
--求明细和每个月有哪些顾客来过
select name,orderdate,cost,
concat_ws(",",collect_set(name) over(partition by substring(orderdate,1,7))) a
from business;
/*4、查询每个顾客上次的购买时间*/
--LAG(col,n,default_val)：往前第n行数据,这里可以往前一行的数据，通过窗口函数name分区orderdate排序,求得上次的购买时间
select name,orderdate,cost,
lag(orderdate,1,"1970-01-01") over(partition by name order by orderdate) last_order
from
business;
--拓展
--显示下一行数据
lead(orderdate,1) over(partition by name order by orderdate)
/*5、查询前20%时间的订单信息*/
select *
from
(select
name,orderdate,cost,
ntile(5) over(order by orderdate ASC) n
from
business) t1
where t1.n=1;
--拓展
--与上述题目同理
select
name,orderdate,cost,
percent_rank() over(order by orderdate) pr
from
business;
注意：rows必须跟在Order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量
6、Rank（排名）
RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算
输入数据
| name | subject | score |
|---|---|---|
| 孙悟空 | 语文 | 87 |
| 孙悟空 | 数学 | 95 |
| 孙悟空 | 英语 | 68 |
| 大海 | 语文 | 94 |
| 大海 | 数学 | 56 |
| 大海 | 英语 | 84 |
| 宋宋 | 语文 | 64 |
| 宋宋 | 数学 | 86 |
| 宋宋 | 英语 | 84 |
| 婷婷 | 语文 | 65 |
| 婷婷 | 数学 | 85 |
| 婷婷 | 英语 | 78 |
按需求查询数据--需求：计算每门学科成绩排名
--1、建表语句
create table score(
name string,
subject string,
score int)
row format delimited fields terminated by "\t";
--2、导入数据
load data local inpath '/usr/local/soft/hive-1.2.1/data/score.txt' into table score;
--3、按需求查询数据
select
*,
rank() over(partition by subject order by score) r
from
score;
--拓展
--RANK()、DENSE_RANK()、ROW_NUMBER()区别
select
*,
rank() over(partition by subject order by score desc) r1,
dense_rank() over(partition by subject order by score desc) r2,
row_number() over(partition by subject order by score desc) r3
from
score;
--比较结果
+-------------+----------------+--------------+-----+-----+-----+--+
| score.name | score.subject | score.score | r1 | r2 | r3 |
+-------------+----------------+--------------+-----+-----+-----+--+
| 孙悟空 | 数学 | 95 | 1 | 1 | 1 |
| 宋宋 | 数学 | 86 | 2 | 2 | 2 |
| 婷婷 | 数学 | 85 | 3 | 3 | 3 |
| 大海 | 数学 | 56 | 4 | 4 | 4 |
| 宋宋 | 英语 | 84 | 1 | 1 | 1 |
| 大海 | 英语 | 84 | 1 | 1 | 2 |
| 婷婷 | 英语 | 78 | 3 | 2 | 3 |
| 孙悟空 | 英语 | 68 | 4 | 3 | 4 |
| 大海 | 语文 | 94 | 1 | 1 | 1 |
| 孙悟空 | 语文 | 87 | 2 | 2 | 2 |
| 婷婷 | 语文 | 65 | 3 | 3 | 3 |
| 宋宋 | 语文 | 64 | 4 | 4 | 4 |
+-------------+----------------+--------------+-----+-----+-----+--+
7、日期函数（部分）
--current_date 返回当前日期
select current_date();
--日期的加减
--举例1：从今天开始90天以后的日期
select date_add(current_date(),90);
--举例2：从今天开始90天以以前的日期
select date_sub(current_date(),90);
--日期差额
--举例：今天和1996-06-04的差额天数
select datediff(current_date(),"1990-06-04");
--转换为日期格式
--举例：'2021年01月14日' -> '2021-01-14'
select from_unixtime(unix_timestamp('2021年01月14日','yyyy年MM月dd日'),'yyyy-MM-dd');
--获取当前时间戳
select unix_timestamp();
--时间戳转换为日期格式
--举例：1610611142 -> 'YYYY/MM/dd HH:mm:ss'
select from_unixtime(1610611142,'YYYY/MM/dd HH:mm:ss');
8、练习题
题目：有哪些顾客连续两天来过店里，数据是business表
--视频写法
--先排号、求日期相减、求连续两天来的
select
name,temp,
count(*) c
from
(select
*,
date_sub(orderdate,rn) temp
from
(select
*,
row_number() over(partition by name order by orderdate) rn
from
business
) t1
) t2
group by
name,temp
having
c>=2;
--自己写法
select
distinct name
from(
select name,
datediff(a.now_date,a.last_date) d
from
(select
name,
lag(orderdate,1) over(partition by name order by orderdate) last_date,
orderdate now_date
from business
) a
) b
where b.d<=1;
2228
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
