Hive 之函数 02-常用查询函数（二）

恋梦轩

已于 2023-02-13 16:46:31 修改

阅读量498

点赞数

分类专栏： Hive 文章标签： Hive 查询函数窗口函数排名函数 HQL

于 2020-04-09 17:14:04 首次发布

本文链接：https://blog.csdn.net/cybeyond_xuan/article/details/105414639

版权

Hive 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

本文详细介绍了Hive中的窗口函数和排名函数。内容包括窗口函数的函数说明、应用场景，如查询2017年4月购买过的顾客及总人数，逐个累加cost，查询顾客上次购买时间等。此外，还讲解了排名函数的用法，如DENSE_RANK、RANK和ROW_NUMBER等，并给出具体实现示例。

摘要由CSDN通过智能技术生成

欢迎大家扫码关注我的微信公众号：
数据之恋

六、窗口函数

6.1 函数说明

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化；如果括号内为空，表示对整个数据集开窗；

over()括号内可以写的参数：

CURRENT ROW：当前行；
n PRECEDING：往前 n 行数据；
n FOLLOWING：往后 n 行数据；
UNBOUNDED：起点， UNBOUNDED PRECEDING表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点；（如果想要计算某个时间段内整体的购买额，使用 distribute by 加 sort by又只能“累加”，此时就可使用“起点”到“终点”这个）

over()前面可以写的参数：

LAG(col,n)：往前第 n 行数据；可以接受第三个参数，当为 Null 时充当默认值；
LEAD(col,n)：往后第 n 行数据；可以接受第三个参数，当为 Null 时充当默认值；
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对于每一行， NTILE 返回此行所属的组的编号。注意： n 必须为 int 类型。主要用于求百分率内数据信息的情况。

【注】over() 放在聚合函数的后面，它的作用是开窗，范围仅仅是针对它前面的聚合函数有效；

6.2 需求

○ 查询在 2017 年 4 月份购买过的顾客及总人数
○ 查询顾客的购买明细及购买总额
○ 上述的场景, 要将 cost 按照日期进行逐个累加
○ 查询顾客上次的购买时间
○ 查询前 20% 时间的订单信息

6.3 实现

建表及导入数据：

hive (default)> create table business(
              > name string, 
              > orderdate string, 
              > cost int)
              > row format delimited fields terminated by ',';
OK
Time taken: 0.884 seconds

hive (default)> load data local inpath 
              > '/opt/module/data/business.txt'
              > into table business;
Loading data to table default.business
Table default.business stats: [numFiles=1, totalSize=171]
OK
Time taken: 0.419 seconds

查询全部数据：

hive (default)> select * from business;
OK
business.name	business.orderdate	business.cost
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
Time taken: 2.0 seconds, Fetched: 14 row(s)

6.3.1 查询在 2017 年 4 月份购买过的顾客及总人数

hive(default)> select name, count(1) over()
              > from business
              > where substring(orderdate, 1, 7) = '2017-04'
              > group by name;

... ...
OK
name	count_window_0
mart	2
jack	2
Time taken: 114.428 seconds, Fetched: 2 row(s)

【注】over()是开窗函数，针对【每一条】数据进行开窗，如果括号内没内容，就会将所有符合 where 子句及后续限定语句的数据作为开窗的数据给前面的聚合函数进行聚合计算；本例中， mart 购买过 4 次， jack 购买过 1 次，符合 where 子句的共有 5 条数据，加上 group by的限制，就变成了两条（因为相同的 name 就只输出了一条数据，总共两条），所以第二列的 count 函数就会输出 2。

如果本例中去掉 group by的语句，那么就会将符合 where 子句的所有数据进行开窗（5 条），那么结果的第一列会出现一个 jack 和四个 mart，而第二列就应该都是 5。

hive (default)> select name, count(*) over()
              > from business
              > where substring(orderdate, 1, 7) = '2017-04';

... ...
OK
name	count_window_0
mart	5
mart	5
mart	5
mart	5
jack	5
Time taken: 23.664 seconds, Fetched: 5 row(s)

如果不加开窗函数 over()，那么结果将是 2017 年 4 月份购买过的人，及购买过的次数：

hive (default)> select name, count(*)
              > from business
              > where substring(orderdate, 1, 7) = '2017-04'
              > group by name;

... ...
OK
name	_c1
jack	1
mart	4
Time taken: 19.944 seconds, Fetched: 2 row(s)

6.3.2 查询顾客的购买明细及购买总额

hive (default)> select name, orderdate, cost, sum(cost) over()
              > from business;

... ...
OK
name	orderdate	cost	sum_window_0
mart	2017-04-13	94	661
neil	2017-06-12	80	661
mart	2017-04-11	75	661
neil	2017-05-10	12	661
mart	2017-04-09	68	661
mart	2017-04-08	62	661
jack	2017-01-08	55	661
tony	2017-01-07	50	661
jack	2017-04-06	42	661
jack	2017-01-05	46	661
tony	2017-01-04	29	661
jack	2017-02-03	23	661
tony	2017-01-02	15	661
jack	2017-01-01	10	661
Time taken: 18.86 seconds, Fetched: 14 row(s)

6.3.3 上述的场景, 要将 cost 按照日期进行逐个累加

hive (default)> select orderdate, cost, sum(cost) over(order by orderdate)
              > from business;

... ...
OK
orderdate	cost	sum_window_0
2017-01-01	10	10
2017-01-02	15	25
2017-01-04	29	54
2017-01-05	46	100
2017-01-07	50	150
2017-01-08	55	205
2017-02-03	23	228
2017-04-06	42	270
2017-04-08	62	332
2017-04-09	68	400
2017-04-11	75	475
2017-04-13	94	569
2017-05-10	12	581
2017-06-12	80	661
Time taken: 22.812 seconds, Fetched: 14 row(s)

【注】在 over()函数中进行 order by的时候，第一次的数据只有日期最小的，结果就为 10，第二次，包含了最小和倒数第二小的日期的数据，所以结果就是 10+15=25，以此类推。

假如我现在想得到每个人的明细，及每个人各自的总消费额，则有：

hive (default)> select name, orderdate, cost, sum(cost) over(distribute by name)
              > from business;

... ...
OK
name	orderdate	cost	sum_window_0
jack	2017-01-05	46	176
jack	2017-01-08	55	176
jack	2017-01-01	10	176
jack	2017-04-06	42	176
jack	2017-02-03	23	176
mart	2017-04-13	94	299
mart	2017-04-11	75	299
mart	2017-04-09	68	299
mart	2017-04-08	62	299
neil	2017-05-10	12	92
neil	2017-06-12	80	92
tony	2017-01-04	29	94
tony	2017-01-02	15	94
tony	2017-01-07	50	94
Time taken: 19.979 seconds, Fetched: 14 row(s)

【注】窗口函数中的条件是按照 name 进行分区，那么结果就是计算每个分区后单独分区的 cost 总和了。不能在 over()函数中使用 group by，会报错！

假如现在想要每个人的购买明细，并且按照购买日期排序，同时还要把每个人的购买额逐一累加结果展示出来：

hive (default)> select name, orderdate, cost, sum(cost) over(distribute by name sort by orderdate)
              > from business;

... ...
OK
name	orderdate	cost	sum_window_0
jack	2017-01-01	10	10
jack	2017-01-05	46	56
jack	2017-01-08	55	111
jack	2017-02-03	23	134
jack	2017-04-06	42	176
mart	2017-04-08	62	62
mart	2017-04-09	68	130
mart	2017-04-11	75	205
mart	2017-04-13	94	299
neil	2017-05-10	12	12
neil	2017-06-12	80	92
tony	2017-01-02	15	15
tony	2017-01-04	29	44
tony	2017-01-07	50	94
Time taken: 30.149 seconds, Fetched: 14 row(s)

6.3.4 查询顾客上次的购买时间

hive (default)> select name, orderdate, cost, 
              > lag(orderdate, 1) over(distribute by name sort by orderdate)
              > from business;

... ...
OK
name	orderdate	cost	lag_window_0
jack	2017-01-01	10	NULL
jack	2017-01-05	46	2017-01-01
jack	2017-01-08	55	2017-01-05
jack	2017-02-03	23	2017-01-08
jack	2017-04-06	42	2017-02-03
mart	2017-04-08	62	NULL
mart	2017-04-09	68	2017-04-08
mart	2017-04-11	75	2017-04-09
mart	2017-04-13	94	2017-04-11
neil	2017-05-10	12	NULL
neil	2017-06-12	80	2017-05-10
tony	2017-01-02	15	NULL
tony	2017-01-04	29	2017-01-02
tony	2017-01-07	50	2017-01-04
Time taken: 14.933 seconds, Fetched: 14 row(s)

【注】要想不出现 Null，可以在 lag 函数中传第三个参数，可作为一个默认值，如果遇到 Null，则会以默认值替代；

如果需求变为 “查询顾客下次的购买时间” 就将 lag 函数换成 lead 即可；

6.3.5 查询前 20% 时间的订单信息

首先，将数据分成五 “组”，使用 ntile()函数即可：

hive (default)> select name, orderdate, cost, 
              > ntile(5) over(order by orderdate)
              > from business;

... ...
OK
name	orderdate	cost	ntile_window_0
jack	2017-01-01	10	1
tony	2017-01-02	15	1
tony	2017-01-04	29	1
jack	2017-01-05	46	2
tony	2017-01-07	50	2
jack	2017-01-08	55	2
jack	2017-02-03	23	3
jack	2017-04-06	42	3
mart	2017-04-08	62	3
mart	2017-04-09	68	4
mart	2017-04-11	75	4
mart	2017-04-13	94	4
neil	2017-05-10	12	5
neil	2017-06-12	80	5
Time taken: 54.086 seconds, Fetched: 14 row(s)

从五组里面取出第一组的数据，即是 “前 20%” 时间的数据：

hive (default)> select name, orderdate, cost
              > from 
              > (
              > select name, orderdate, cost, 
              > ntile(5) over(order by orderdate) ntile_5
              > from business
              > ) t1
              > where t1.ntile_5 = 1;

... ...
OK
name	orderdate	cost
jack	2017-01-01	10
tony	2017-01-02	15
tony	2017-01-04	29
Time taken: 24.67 seconds, Fetched: 3 row(s)

6.3.6 关于几个时间参数的使用示例

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;

6.3.7 SQL 语句的书写及执行顺序

书写顺序：（gohl）

select
from 
join on
where
group by 
order by
having
limit

执行顺序：（gshol）

from
join on
where
group by
select
having
order by
limit

七、排名函数

首先要说明的是下面介绍的排名函数也是窗口函数中的，只不过为了凸显它们的排名功能，所以给单列出来了。

7.1 函数说明

RANK()：排序相同时会重复，总数不会变；（有并列第一时是 1， 1， 3， 4， … …）

DENSE_RANK()：排序相同时会重复，总数会减少；（有并列第一时是 1， 1， 2， 3， … …）

ROW_NUMBER()：会根据顺序计算；（有并列第一时也是 1， 2， 3， 4， … …）

7.2 需求

计算每门学科成绩排名。

7.3 实现

建表并导入数据：

hive (default)> create table score
              > (
              > name string, 
              > subject string, 
              > score int
              > )
              > row format delimited fields terminated by '\t';
OK
Time taken: 1.336 seconds

hive (default)> load data local inpath 
              > '/opt/module/data/subject.txt'
              > into table score;
Loading data to table default.score
Table default.score stats: [numFiles=1, totalSize=213]
OK
Time taken: 0.52 seconds

查询数据：

hive (default)> select * from score;
OK
score.name	score.subject	score.score
孙悟空	语文	87
孙悟空	数学	95
孙悟空	英语	68
大海	语文	94
大海	数学	56
大海	英语	84
宋宋	语文	64
宋宋	数学	86
宋宋	英语	84
婷婷	语文	65
婷婷	数学	85
婷婷	英语	78
Time taken: 0.094 seconds, Fetched: 12 row(s)

按需求查询数据：

hive (default)> select name, subject, score, 
              > rank() over(partition by subject order by score desc) rank_f, 
              > dense_rank() over(distribute by subject sort by score desc) dense_rank_f, 
              > row_number() over(partition by subject order by score desc) row_number_f
              > from score;

... ...
OK
name	subject	score	rank_f	dense_rank_f	row_number_f
孙悟空	数学	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
Time taken: 30.053 seconds, Fetched: 12 row(s)

八、两个小题

8.1 统计用户累计访问次数

建表及导入数据：

hive (default)> create table action
              > (
              > userid string, 
              > visitdate string,
              > visitcount int
              > )
              > row format delimited fields terminated by '\t';
OK
Time taken: 0.079 seconds
hive (default)> load data local inpath 
              > '/opt/module/data/visit.txt'
              > into table action;
Loading data to table default.action
Table default.action stats: [numFiles=1, totalSize=128]
OK
Time taken: 0.206 seconds

查询数据：

hive (default)> select * from action;
OK
action.userid	action.visitdate	action.visitcount
u01	2017/1/21	5
u02	2017/1/23	6
u03	2017/1/22	8
u04	2017/1/20	3
u01	2017/1/23	6
u01	2017/2/21	8
u02	2017/1/23	6
u01	2017/2/22	4
Time taken: 0.054 seconds, Fetched: 8 row(s)

需求：统计出每个用户、每个月的累计访问次数：

首先，将日期修改为最终需要的格式：

hive (default)> select userid, 
              > date_format(regexp_replace(visitdate, '/', '-'), 'yyyy-MM') mn, 
              > visitcount
              > from action;
OK
userid	mn	visitcount
u01	2017-01	5
u02	2017-01	6
u03	2017-01	8
u04	2017-01	3
u01	2017-01	6
u01	2017-02	8
u02	2017-01	6
u01	2017-02	4
Time taken: 0.065 seconds, Fetched: 8 row(s)

其次，求出第三列：

hive (default)> select userid, mn, sum(visitcount)
              > from 
              > (
              > select userid, 
              > date_format(regexp_replace(visitdate, '/', '-'),'yyyy-MM') mn,
              > visitcount
              > from action
              > ) t1
              > group by userid, mn;

... ...
OK
userid	mn	_c2
u01	2017-01	11
u01	2017-02	12
u02	2017-01	12
u03	2017-01	8
u04	2017-01	3
Time taken: 17.183 seconds, Fetched: 5 row(s)

最后，求出第四列：

hive (default)> select userid, mn, sum_c, sum(sum_c) over(distribute by userid sort by mn)
              > from 
              > (
              > select userid, mn, sum(visitcount) sum_c
              > from (
              > select userid,
              > date_format(regexp_replace(visitdate, '/', '-'),'yyyy-MM') mn,
              > visitcount
              > from action) t1
              > group by userid, mn
              > ) t2;

... ...
OK
userid	mn	sum_c	sum_window_0
u01	2017-01	11	11
u01	2017-02	12	23
u02	2017-01	12	12
u03	2017-01	8	8
u04	2017-01	3	3
Time taken: 15.256 seconds, Fetched: 5 row(s)

8.2 店铺访客数等

有 50W个店铺，每个顾客访问任何一个店铺的任何一个商品时都会产生一条访问日志，访问日志存储的表名为 jd，访客的用户 id 为 user_id，被访问的店铺名称为 shop，请统计：

每个店铺的UV（访客数）
每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

建表及导入数据：

hive (default)> create table jd(
              > usr_id string, 
              > shop string
              > )
              > row format delimited fields terminated by '\t';
OK
Time taken: 0.679 seconds

hive (default)> load data local inpath
              > '/opt/module/data/jd.txt'
              > into table jd;
Loading data to table default.jd
Table default.jd stats: [numFiles=1, totalSize=95]
OK
Time taken: 0.34 seconds

查询数据：

hive (default)> select * from jd;
OK
jd.usr_id	jd.shop
u1	a
u2	b
u1	b
u1	a
u3	c
u4	b
u1	a
u2	c
u5	b
u4	b
u6	c
u2	c
u1	b
u2	a
u2	a
u3	a
u5	a
u5	a
u5	a
Time taken: 0.079 seconds, Fetched: 19 row(s)

需求一：每个店铺的UV（访客数）

先按照用户、商铺去重：

hive (default)> select usr_id, shop 
              > from jd
              > group by usr_id, shop;

... ...
OK
usr_id	shop
u1	a
u1	b
u2	a
u2	b
u2	c
u3	a
u3	c
u4	b
u5	a
u5	b
u6	c
Time taken: 15.159 seconds, Fetched: 11 row(s)

然后计数：

hive (default)> select shop, count(1) uv from 
              > (
              > select usr_id, shop 
              > from jd
              > group by usr_id, shop
              > ) t1
              > group by shop;

... ...
OK
shop	uv
a	4
b	4
c	3
Time taken: 64.018 seconds, Fetched: 3 row(s)

需求二：每个店铺访问次数 top3 的访客信息。输出店铺名称、访客id、访问次数

先计算每个商铺中每个账户的访问次数：

hive (default)> select shop, usr_id, count(*)
              > from jd
              > group by shop, usr_id;

... ...
OK
shop	usr_id	_c2
a	u1	3
a	u2	2
a	u3	1
a	u5	3
b	u1	2
b	u2	1
b	u4	2
b	u5	1
c	u2	2
c	u3	1
c	u6	1
Time taken: 44.766 seconds, Fetched: 11 row(s)

针对同一店铺，对访问次数进行逆序排序，并新增一个 rank 排序列：

hive (default)> select shop, usr_id, uv, row_number() over(distribute by shop sort by uv desc)
              > from (
              > select shop, usr_id, count(*) uv
              > from jd
              > group by shop, usr_id
              > ) t1;

... ...
OK
shop	usr_id	uv	row_number_window_0
a	u5	3	1
a	u1	3	2
a	u2	2	3
a	u3	1	4
b	u4	2	1
b	u1	2	2
b	u5	1	3
b	u2	1	4
c	u2	2	1
c	u6	1	2
c	u3	1	3
Time taken: 47.539 seconds, Fetched: 11 row(s)

从上一步的数据中，拿出 top3：

hive (default)> select shop, usr_id, uv
              > from 
              > (
              > select shop, usr_id, uv, row_number() over(distribute by shop sort by uv desc) rk
              > from (
              > select shop, usr_id, count(*) uv
              > from jd
              > group by shop, usr_id) t1
              > ) t2
              > where rk < 4;

... ...
OK
shop	usr_id	uv
a	u5	3
a	u1	3
a	u2	2
b	u4	2
b	u1	2
b	u5	1
c	u2	2
c	u6	1
c	u3	1
Time taken: 73.195 seconds, Fetched: 9 row(s)