【hive】字符串操作，截取想要的字符串

你好呀！这里是小易同学的博客，一名大二在校生。
写博客是为了记录自己的学习过程，同时也希望能帮助到需要帮助的人。
如果我的博客可以帮助到你，不妨给我一个关注🥰

前言

对字符串截取，这里主要有两种方法：

一是用split截取字符串

二是用substr截取字符串

提示：以下是本篇文章正文内容

一、目标

将hive中movie表的年份数字取出来，并统计每一年有多少部电影

二、截取字符串，取出年份数字

1.split

注：在分割时加“\”是因为（）是特殊字符，需要加\才能让hive识别

（1）因为数据太多，先用一条数据来测试，也就是movieId=1这条数据

select split(title,"\(") from yiqianbin_movie where movieId=1;

select split(title,"\(")[1] from yiqianbin_movie where movieId=1;

select split(split(title,"\(")[1],"\)")[0] from yiqianbin_movie where movieId=1;

（2）取出了1995，那么就可以直接去掉后面where的条件，查出全部的年份

select split(split(title,"\(")[1],"\)")[0] from yiqianbin_movie；

2.substr

注：这里加 where title<>"title" 是为了去掉表头“title”

三、统计每个数字出现了多少次

实例：创建临时表，一步完成计数

select year,count(1) from
(select regexp_extract(title, "(\d{4})",1)as year from yiqianbin_movie where title regexp '\d{4}') temp
group by year;

补充

杂七杂八

select * from yiqianbin_movie where title not regexp '\d{4}';

create table movie_year as select *,substr(title,-5,4) as year from yiqianbin_movie;

create table movies_year as select *,regexp_extract(title, "(\d{4})",1)as year from yiqianbin_movie where title regexp '\d{4}';

select substr(title,-5,4)from yiqianbin_movie where title regexp '\d{4}';

select year,count(1) from movies_year group by year;

查出括号中的四个数字

select regexp_extract(title,"\((\d{4})\)") from yiqianbin_movie;

查出哪一年出的电影最多，即年份出现最多的数字对应的电影名
select year,count(1) as year1 from movies_year group by year order by year1 desc limit 1;

将多个电影类型分开
先分开
select explode(split(genres,'\|')) as type from yiqianbin_movie;
之后将分开的导入一个新表
create table movies_type as select explode(split(genres,'\|')) as type from yiqianbin_movie;
将类型的多少按降序排下来
select type,count(1) as count from movies_type group by type order by count desc;
临时表（一步完成）
select type,count(1) as count from (select explode(split(genres,'\|')) as type from yiqianbin_movie)temp group by type order by count desc;

查出评分次数最高的电影
select movieId,count(1) as count from yiqianbin_ratings group by movieId order by count desc limit 1;
select * from(select movieId,count(1) as count from yiqianbin_ratings group by movieId order by count desc limit 1)t left join yiqianbin_movie m on t.movieId = m.movieId;

什么类型的电影评分最高 第四行是一个视图查询
1.先连接查询，每部电影的评分+分类
2.分类 行专列
3.按类型分组统计
select type,avg(rating) as avgrating from
(select t1.rating,type.col as type from(select r.rating,m.genres from yiqianbin_ratings r
left join yiqianbin_movie m on r.movieId = m.movieId)t1
lateral view explode(split(t1.genres,'\|')) type)t2
group by type order by avgrating desc
limit 10;

按年份统计电影数量
create table movies_year_result(year string,num string);
insert into movies_year_result
select year,count(1) from
(select substr(title,-5,4) as year from yiqianbin_movie where title regexp '\d{4}')t group by year;

什么类型电影最多
create table genres_top10_result(type string,num string);
insert into genres_top10_result
select type,count(1) as total from
(select explode(split(genres,'\|')) as type from yiqianbin_movie)t group by type
order by total desc limit 10;

评分人气最高电影top10
create table hot_top10_result(title string,rating string);
insert into hot_top10_result
select m.title,t.total from (
select movieId,count(1) as total
from yiqianbin_ratings group by movieId
order by total desc limit 10)t
left join yiqianbin_movie m on t.movieId = m.movieId;

高频词top10
create table tag_top10_result(tag string,num string);
insert into tag_top10_result
select tag,count(1) as total from
(select explode(split(tag,'\s')) as tag from yiqianbin_tags)t
group by tag
order by total desc limit 10;

评分最高电影top10
create table rating_top10_result(title string,rating string);
insert into rating_top10_result
select m.title,t.avgrating from(
select movieId,avg(rating) as avgrating
from yiqianbin_ratings
group by movieId
order by avgrating desc limit 10)t
left join yiqianbin_movie m on t.movieId = m.movieId;