Hive学习笔记05

目录

8.2.6 集合函数

8.2.7 案例

8.3 高级聚合函数

8.3.1 案例

8.4 炸裂函数

8.4.1 概述

8.4.2 案例

8.5 窗口函数（开窗函数）

8.5.1 概述

8.5.2 常用窗口函数

8.5.3 案例

---

8.2.6 集合函数

1）size：集合中元素的个数

hive> select size(friends) from test;  --2/2  每一行数据中的friends集合里的个数

2）map：创建map集合

语法：map (key1, value1, key2, value2, …)

说明：根据输入的key和value对构建map类型

案例实操：

hive> select map('xiaohai',1,'dahai',2);  

输出：

hive> {"xiaohai":1,"dahai":2}

3）map_keys： 返回map中的key

hive> select map_keys(map('xiaohai',1,'dahai',2));

输出：

hive>["xiaohai","dahai"]

4）map_values: 返回map中的value

hive> select map_values(map('xiaohai',1,'dahai',2));

输出：

hive>[1,2]

5）array 声明array集合

语法：array(val1, val2, …)

说明：根据输入的参数构建数组array类

案例实操：

hive> select array('1','2','3','4');

输出：

hive>["1","2","3","4"]

6）array_contains: 判断array中是否包含某个元素

hive> select array_contains(array('a','b','c','d'),'a');

输出：

hive> true

7）sort_array：将array中的元素排序

hive> select sort_array(array('a','d','c'));

输出：

hive> ["a","c","d"]

8）struct声明struct中的各属性

语法：struct(val1, val2, val3, …)

说明：根据输入的参数构建结构体struct类

案例实操：

hive> select struct('name','age','weight');

输出：

hive> {"col1":"name","col2":"age","col3":"weight"}

9）named_struct声明struct的属性和值

hive> select named_struct('name','xiaosong','age',18,'weight',80);

输出：

hive> {"name":"xiaosong","age":18,"weight":80}

8.2.7 案例

1. 数据准备

1）表结构

name	sex	birthday	hiredate	job	salary	bonus	friends	children
张无忌	男	1980/02/12	2022/08/09	销售	3000	12000	[阿朱，小昭]	{张小无:8,张小忌:9}
赵敏	女	1982/05/18	2022/09/10	行政	9000	2000	[阿三，阿四]	{赵小敏:8}
黄蓉	女	1982/04/13	2022/06/11	行政	12000	Null	[东邪，西毒]	{郭芙:5,郭襄:4}

2）建表语句

hive> 

create  table  employee(

    name string,  --姓名

    sex  string,  --性别

    birthday string, --出生年月

    hiredate string, --入职日期

    job string,   --岗位

    salary double, --薪资

    bonus double,  --奖金

    friends array<string>, --朋友

    children map<string,int> --孩子

)

3）插入数据

hive> insert into employee  

  values('张无忌','男','1980/02/12','2022/08/09','销售',3000,12000,array('阿朱','小昭'),map('张小无',8,'张小忌',9)),

        ('赵敏','女','1982/05/18','2022/09/10','行政',9000,2000,array('阿三','阿四'),map('赵小敏',8)),

        ('宋青书','男','1981/03/15','2022/04/09','研发',18000,1000,array('王五','赵六'),map('宋小青',7,'宋小书',5)),

        ('周芷若','女','1981/03/17','2022/04/10','研发',18000,1000,array('王五','赵六'),map('宋小青',7,'宋小书',5)),

        ('郭靖','男','1985/03/11','2022/07/19','销售',2000,13000,array('南帝','北丐'),map('郭芙',5,'郭襄',4)),

        ('黄蓉','女','1982/12/13','2022/06/11','行政',12000,null,array('东邪','西毒'),map('郭芙',5,'郭襄',4)),

        ('杨过','男','1988/01/30','2022/08/13','前台',5000,null,array('郭靖','黄蓉'),map('杨小过',2)),

        ('小龙女','女','1985/02/12','2022/09/24','前台',6000,null,array('张三','李四'),map('杨小过',2))

2. 需求

1）统计每个月的入职人数

（1）期望结果

month	cnt
4	2
6	1
7	1
8	2
9	2

（2）需求实现

select

  month(replace(hiredate,'/','-')) as month,

  count(*) as cn

from

  employee

group by

  month(replace(hiredate,'/','-'))

2）查询每个人的年龄（年 + 月）

（1）期望结果

name	age
张无忌	42年8月
赵敏	40年5月
宋青书	41年7月
周芷若	41年7月
郭靖	37年7月
黄蓉	39年10月
杨过	34年9月
小龙女	37年8月

（2）需求实现

-- 转换日期

select

  name,

  replace(birthday,'/','-') birthday

from

  employee  t1

-- 求出年和月

select

  name,

  year(current_date())-year(t1.birthday) year,

  month(current_date())-month(t1.birthday) month

from

  (

    select

      name,

      replace(birthday,'/','-') birthday

    from

      employee

)t1 t2

-- 根据月份正负决定年龄

select

  name,

  concat(if(month>=0,year,year-1),'年',if(month>=0,month,12+month),'月') age

from

  (

    select

      name,

      year(current_date())-year(t1.birthday) year,

      month(current_date())-month(t1.birthday) month

    from

      (

        select

          name,

          replace(birthday,'/','-') birthday

        from

          employee

    )t1

)t2

3）按照薪资，奖金的和进行倒序排序，如果奖金为null，置位0

（1）期望结果

name	sal
周芷若	19000
宋青书	19000
郭靖	15000
张无忌	15000
黄蓉	12000
赵敏	11000
小龙女	6000
杨过	5000

（2）需求实现

select

  name,

  salary + nvl(bonus,0) sal

from

  employee

order by

   sal desc

4）查询每个人有多少个朋友

（1）期望结果

name	cnt
张无忌	2
赵敏	2
宋青书	2
周芷若	2
郭靖	2
黄蓉	2
杨过	2
小龙女	2

（2）需求实现

select

name,

size(friends) cnt

from

employee;

5）查询每个人的孩子的姓名

（1）期望结果

name	ch_name
张无忌	["张小无","张小忌"]
赵敏	["赵小敏"]
宋青书	["宋小青","宋小书"]
周芷若	["宋小青","宋小书"]
郭靖	["郭芙","郭襄"]
黄蓉	["郭芙","郭襄"]
杨过	["杨小过"]
小龙女	["杨小过"]

（2）需求实现

hive>

select

name,

map_keys(children) ch_name

from

employee;

6）查询每个岗位男女各多少人

（1）期望结果

job	male	female
前台	1	1
研发	1	1
行政	0	2
销售	2	0

（2）需求实现

select

  job,

  sum(if(sex='男',1,0)) male,

  sum(if(sex='女',1,0)) female

from

  employee

group by

  job

8.3 高级聚合函数

多进一出 （多行传入，一个行输出）。

1）普通聚合 count/sum.... 见第6章 6.2.4

2）collect_list 收集并形成list集合，结果不去重

hive>

select

  sex,

  collect_list(job)

from

  employee

group by

  sex

结果：

女 ["行政","研发","行政","前台"]

男 ["销售","研发","销售","前台"]

3）collect_set 收集并形成set集合，结果去重

hive>

select

  sex,

  collect_set(job)

from

  employee

group by

  sex

结果：

女 ["行政","研发","前台"]

男 ["销售","研发","前台"]

8.3.1 案例

1）每个月的入职人数以及姓名

hive> 

select

  month(replace(hiredate,'/','-')) as month,

  count(*) as cn,

  Collect_list(name) as name_list

from

  employee

group by

  month(replace(hiredate,'/','-'))

结果：

month  cn  name_list

4     2 ["宋青书","周芷若"]

6     1 ["黄蓉"]

7     1 ["郭靖"]

8     2 ["张无忌","杨过"]

9     2 ["赵敏","小龙女"]

8.4 炸裂函数

8.4.1 概述

8.4.2 案例

1.数据准备

1）表结构

movie	category
《疑犯追踪》	悬疑，动作，科幻，剧情
《Lie to me》	悬疑，警匪，动作，心理，剧情
《战狼2》	战争，动作，灾难

2）建表语句

hive (default)>

create table movie_info(

    movie string,     --电影名称

    category string   --电影分类

)

row format delimited fields terminated by "\t";

3）装载语句

insert overwrite table movie_info
values ("《疑犯追踪》", "悬疑,动作,科幻,剧情"),
       ("《Lie to me》", "悬疑,警匪,动作,心理,剧情"),
       ("《战狼2》", "战争,动作,灾难");

2. 需求

1）需求说明

根据上述电影信息表，统计各分类的电影数量，期望结果如下：

剧情	2
动作	3
心理	1
悬疑	2
战争	1
灾难	1
科幻	1
警匪	1

2）答案

select
    cate,
    count(*)
from
(
    select
        movie,
        cate
    from
    (
        select
            movie,
            split(category,',') cates
        from movie_info
    )t1 lateral view explode(cates) tmp as cate
)t2
group by cate;

8.5 窗口函数（开窗函数）

8.5.1 概述

8.5.2 常用窗口函数

按照功能，常用窗口可划分为如下几类：聚合函数、跨行取值函数、排名函数。

1）聚合函数

max：最大值。

min：最小值。

sum：求和。

avg：平均值。

count：计数。

2）跨行取值函数

（1）lead和lag

1）聚合函数

max：最大值。

min：最小值。

sum：求和。

avg：平均值。

count：计数。

2）跨行取值函数

（1）lead和lag

注：lag和lead函数不支持自定义窗口。

（2）first_value和last_value

 

注：rank 、dense_rank、row_number不支持自定义窗口。

8.5.3 案例

1.数据准备

1）表结构

order_id	user_id	user_name	order_date	order_amount
1	1001	小元	2022-01-01	10
2	1002	小海	2022-01-02	15
3	1001	小元	2022-02-03	23
4	1002	小海	2022-01-04	29
5	1001	小元	2022-01-05	46

2）建表语句

create table order_info

(

    order_id     string, --订单id

    user_id      string, -- 用户id

    user_name    string, -- 用户姓名

    order_date   string, -- 下单日期

    order_amount int     -- 订单金额

);

3）装载语句

insert overwrite table order_info

values ('1', '1001', '小元', '2022-01-01', '10'),

       ('2', '1002', '小海', '2022-01-02', '15'),

       ('3', '1001', '小元', '2022-02-03', '23'),

       ('4', '1002', '小海', '2022-01-04', '29'),

       ('5', '1001', '小元', '2022-01-05', '46'),

       ('6', '1001', '小元', '2022-04-06', '42'),

       ('7', '1002', '小海', '2022-01-07', '50'),

       ('8', '1001', '小元', '2022-01-08', '50'),

       ('9', '1003', '小辉', '2022-04-08', '62'),

       ('10', '1003', '小辉', '2022-04-09', '62'),

       ('11', '1004', '小猛', '2022-05-10', '12'),

       ('12', '1003', '小辉', '2022-04-11', '75'),

       ('13', '1004', '小猛', '2022-06-12', '80'),

       ('14', '1003', '小辉', '2022-04-13', '94');

2. 需求

1）统计每个用户截至每次下单的累积下单总额

（1）期望结果

order_id	user_id	user_name	order_date	order_amount	sum_so_far
1	1001	小元	2022-01-01	10	10
5	1001	小元	2022-01-05	46	56
8	1001	小元	2022-01-08	50	106
3	1001	小元	2022-02-03	23	129
6	1001	小元	2022-04-06	42	171
2	1002	小海	2022-01-02	15	15
4	1002	小海	2022-01-04	29	44
7	1002	小海	2022-01-07	50	94
9	1003	小辉	2022-04-08	62	62
10	1003	小辉	2022-04-09	62	124
12	1003	小辉	2022-04-11	75	199
14	1003	小辉	2022-04-13	94	293
11	1004	小猛	2022-05-10	12	12
13	1004	小猛	2022-06-12	80	92

（2）需求实现

select

    order_id,

    user_id,

    user_name,

    order_date,

    order_amount,

    sum(order_amount) over(partition by user_id order by order_date rows between unbounded preceding and current row) sum_so_far

from order_info;

2）统计每个用户截至每次下单的当月累积下单总额

（1）期望结果

order_id	user_id	user_name	order_date	order_amount	sum_so_far
1	1001	小元	2022-01-01	10	10
5	1001	小元	2022-01-05	46	56
8	1001	小元	2022-01-08	50	106
3	1001	小元	2022-02-03	23	23
6	1001	小元	2022-04-06	42	42
2	1002	小海	2022-01-02	15	15
4	1002	小海	2022-01-04	29	44
7	1002	小海	2022-01-07	50	94
9	1003	小辉	2022-04-08	62	62
10	1003	小辉	2022-04-09	62	124
12	1003	小辉	2022-04-11	75	199
14	1003	小辉	2022-04-13	94	293
11	1004	小猛	2022-05-10	12	12
13	1004	小猛	2022-06-12	80	80

（2）需求实现

select
    order_id,
    user_id,
    user_name,
    order_date,
    order_amount,
    sum(order_amount) over(partition by user_id,substring(order_date,1,7) order by order_date rows between unbounded preceding and current row) sum_so_far
from order_info;

3）统计每个用户每次下单距离上次下单相隔的天数（首次下单按0天算）

（1）期望结果

order_id	user_id	user_name	order_date	order_amount	diff
1	1001	小元	2022-01-01	10	0
5	1001	小元	2022-01-05	46	4
8	1001	小元	2022-01-08	50	3
3	1001	小元	2022-02-03	23	26
6	1001	小元	2022-04-06	42	62
2	1002	小海	2022-01-02	15	0
4	1002	小海	2022-01-04	29	2
7	1002	小海	2022-01-07	50	3
9	1003	小辉	2022-04-08	62	0
10	1003	小辉	2022-04-09	62	1
12	1003	小辉	2022-04-11	75	2
14	1003	小辉	2022-04-13	94	2
11	1004	小猛	2022-05-10	12	0
13	1004	小猛	2022-06-12	80	33

（2）需求实现

select

    order_id,

    user_id,

    user_name,

    order_date,

    order_amount,

    nvl(datediff(order_date,last_order_date),0) diff

from

(

    select

        order_id,

        user_id,

        user_name,

        order_date,

        order_amount,

        lag(order_date,1,null) over(partition by user_id order by order_date) last_order_date

    from order_info

)t1

4）查询所有下单记录以及每个用户的每个下单记录所在月份的首/末次下单日期

（1）期望结果

order_id	user_id	user_name	order_date	order_amount	first_date	last_date
1	1001	小元	2022-01-01	10	2022-01-01	2022-01-08
5	1001	小元	2022-01-05	46	2022-01-01	2022-01-08
8	1001	小元	2022-01-08	50	2022-01-01	2022-01-08
3	1001	小元	2022-02-03	23	2022-02-03	2022-02-03
6	1001	小元	2022-04-06	42	2022-04-06	2022-04-06
2	1002	小海	2022-01-02	15	2022-01-02	2022-01-07
4	1002	小海	2022-01-04	29	2022-01-02	2022-01-07
7	1002	小海	2022-01-07	50	2022-01-02	2022-01-07
9	1003	小辉	2022-04-08	62	2022-04-08	2022-04-13
10	1003	小辉	2022-04-09	62	2022-04-08	2022-04-13
12	1003	小辉	2022-04-11	75	2022-04-08	2022-04-13
14	1003	小辉	2022-04-13	94	2022-04-08	2022-04-13
11	1004	小猛	2022-05-10	12	2022-05-10	2022-05-10
13	1004	小猛	2022-06-12	80	2022-06-12	2022-06-12

（2）需求实现

select

    order_id,

    user_id,

    user_name,

    order_date,

    order_amount,

    first_value(order_date) over(partition by user_id,substring(order_date,1,7) order by order_date) first_date,

    last_value(order_date) over(partition by user_id,substring(order_date,1,7) order by order_date rows between unbounded preceding and unbounded following) last_date

from order_info;

5）为每个用户的所有下单记录按照订单金额进行排名

（1）期望结果

order_id	user_id	user_name	order_date	order_amount	rk	drk	rn
8	1001	小元	2022-01-08	50	1	1	1
5	1001	小元	2022-01-05	46	2	2	2
6	1001	小元	2022-04-06	42	3	3	3
3	1001	小元	2022-02-03	23	4	4	4
1	1001	小元	2022-01-01	10	5	5	5
7	1002	小海	2022-01-07	50	1	1	1
4	1002	小海	2022-01-04	29	2	2	2
2	1002	小海	2022-01-02	15	3	3	3
14	1003	小辉	2022-04-13	94	1	1	1
12	1003	小辉	2022-04-11	75	2	2	2
9	1003	小辉	2022-04-08	62	3	3	3
10	1003	小辉	2022-04-09	62	3	3	4
13	1004	小猛	2022-06-12	80	1	1	1
11	1004	小猛	2022-05-10	12	2	2	2

（2）需求实现

select
    order_id,
    user_id,
    user_name,
    order_date,
    order_amount,
    rank() over(partition by user_id order by order_amount desc) rk,
    dense_rank() over(partition by user_id order by order_amount desc) drk,
    row_number() over(partition by user_id order by order_amount desc) rn
from order_info;