hive场景练习题

xiaoxiao______

已于 2022-11-10 16:09:14 修改

阅读量195

点赞数 1

分类专栏： hive 文章标签： hive

于 2020-10-16 21:03:15 首次发布

本文链接：https://blog.csdn.net/xiaoxiao______/article/details/109124332

版权

hive 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

1、


以下数据的每一列分别表示：


sid  name  gender  age  academy  dt  chinese math english 



95001	李勇	男	20	CS	2017-08-31	56	28	62
95002	刘晨	女	19	IS	2017-08-31	84	22	38
95003	王敏	女	22	MA	2017-08-31	38	80	82
95004	张立	男	19	IS	2017-08-31	39	65	4
95005	刘刚	男	18	MA	2018-08-31	18	28	79
95006	孙庆	男	23	CS	2018-08-31	12	67	16
95007	易思玲	女	19	MA	2018-08-31	27	60	84
95008	李娜	女	18	CS	2018-08-31	12	12	82
95009	梦圆圆	女	18	MA	2018-08-31	62	35	94
95010	孔小涛	男	19	CS	2017-08-31	82	97	4
95011	包小柏	男	18	MA	2019-08-31	41	53	71
95012	孙花	女	20	CS	2017-08-31	90	51	79
95013	冯伟	男	21	CS	2019-08-31	20	69	86
95014	王小丽	女	19	CS	2017-08-31	83	64	60
95015	王君	男	18	MA	2019-08-31	39	48	29
95016	钱国	男	21	MA	2019-08-31	94	7	69
95017	王风娟	女	18	IS	2019-08-31	87	56	54
95018	王一啊	女	19	IS	2019-08-31	54	36	24
95019	邢小丽	女	19	IS	2018-08-31	78	9	82
95020	赵钱	男	21	IS	2019-08-31	4	48	87
95021	周二	男	17	MA	2018-08-31	84	61	16
95022	郑明	男	20	MA	2018-08-31	64	70	90
95023	李小勇	男	20	CS	2017-08-31	52	21	19
95024	刘小晨	女	19	IS	2017-08-31	1	16	76
95025	王小敏	女	22	MA	2017-08-31	13	89	98
95026	张小立	男	19	IS	2017-08-31	85	84	80
95027	刘小刚	男	18	MA	2018-08-31	7	17	48
95028	孙小庆	男	23	CS	2018-08-31	17	94	83
95029	易小思玲	女	19	MA	2018-08-31	69	84	23
95030	李小娜	女	18	CS	2018-08-31	16	70	70
95031	梦小圆	女	18	MA	2018-08-31	82	48	37
95032	孔涛	男	19	CS	2017-08-31	83	80	12
95033	包柏	男	18	MA	2019-08-31	18	16	71
95034	孙小花	女	20	CS	2017-08-31	36	95	93
95035	冯小伟	男	21	CS	2019-08-31	31	19	13
95036	王丽	女	19	CS	2017-08-31	16	42	9
95037	王小君	男	18	MA	2019-08-31	91	3	67
95038	钱小国	男	21	MA	2019-08-31	89	59	18
95039	王娟	女	18	IS	2019-08-31	7	17	67
95040	王小一	女	19	IS	2019-08-31	49	32	45
95041	邢丽	女	19	IS	2018-08-31	30	40	40
95042	赵小钱	男	21	IS	2019-08-31	71	69	57
95043	周小二	男	17	MA	2018-08-31	18	9	1
95044	郑一明	男	20	MA	2018-08-31	64	3	44
95045	张一勇	男	20	CS	2017-08-31	10	65	97
95046	刘一丽	女	19	IS	2017-08-31	56	8	45
95047	张一敏	女	22	MA	2017-08-31	19	8	98
95048	张一立	男	19	IS	2017-08-31	49	39	92
95049	刘一刚	男	18	MA	2018-08-31	4	79	91
95050	孙一庆	男	23	CS	2018-08-31	99	71	61
95051	易一玲	女	19	MA	2018-08-31	49	53	71
95052	李一	女	18	CS	2018-08-31	22	87	79
95053	小一	女	18	MA	2018-08-31	53	58	35
95054	孔一	男	19	CS	2017-08-31	30	62	5
95055	包一	男	18	MA	2019-08-31	73	68	28
95056	孙一庆	女	20	CS	2017-08-31	68	22	33
95057	冯一	男	21	CS	2019-08-31	91	26	46
95058	王一二	女	19	CS	2017-08-31	49	81	51
95059	王一三	男	18	MA	2019-08-31	56	27	49
95060	钱一	男	21	MA	2019-08-31	97	40	24
95061	王小娟	女	18	IS	2019-08-31	96	79	34
95062	王小一	女	19	IS	2019-08-31	87	6	55
95063	邢丽	女	19	IS	2018-08-31	56	19	33
95064	赵小钱	男	21	IS	2019-08-31	70	17	85
95065	周小二	男	17	MA	2018-08-31	66	19	14
95066	郑小明	男	20	MA	2018-08-31	99	95	14
95067	李勇	男	19	CS	2017-08-31	48	40	13
95068	刘晨	女	19	IS	2017-08-31	86	28	46
95069	王敏	女	21	MA	2017-08-31	91	59	0
95070	张立	男	17	IS	2017-08-31	99	39	64
95071	刘刚	男	20	MA	2018-08-31	73	2	64
95072	孙庆	男	19	CS	2018-08-31	90	71	5
95073	易思玲	女	19	MA	2018-08-31	72	87	25
95074	李娜	女	21	CS	2018-08-31	92	23	65
95075	梦圆圆	女	17	MA	2018-08-31	37	34	5
95076	孔小涛	男	20	CS	2017-08-31	15	37	40
95077	包小柏	男	19	MA	2019-08-31	29	82	30
95078	孙花	女	19	CS	2017-08-31	6	27	75
95079	冯伟	男	21	CS	2019-08-31	4	10	29
95080	王小丽	女	17	CS	2017-08-31	70	70	36
95081	王君	男	20	MA	2019-08-31	20	74	31
95082	钱国	男	19	MA	2019-08-31	59	85	39
95083	王风娟	女	19	IS	2019-08-31	6	98	95
95084	王一	女	21	IS	2019-08-31	16	26	27
95085	邢小丽	女	17	IS	2018-08-31	73	28	4
95086	赵钱	男	20	IS	2019-08-31	69	52	59
95087	周二	男	19	MA	2018-08-31	29	40	10
95088	郑明	男	18	MA	2018-08-31	77	13	91
95089	李勇	男	23	CS	2017-08-31	49	22	8
95090	刘晨	女	19	IS	2017-08-31	12	18	25
95091	王敏	女	18	MA	2017-08-31	44	94	47
95092	张立	男	18	IS	2017-08-31	71	45	17
95093	刘刚	男	19	MA	2018-08-31	27	90	50
95094	孙庆	男	18	CS	2018-08-31	92	34	75
95095	易思玲	女	20	MA	2018-08-31	38	63	24
95096	李娜	女	21	CS	2018-08-31	94	66	37
95097	梦圆圆	女	19	MA	2018-08-31	91	35	45
95098	孔小涛	男	18	CS	2017-08-31	9	61	0
95099	包小柏	男	19	MA	2019-08-31	93	66	25
95100	孙花	女	18	CS	2017-08-31	47	22	27
95101	冯伟	男	23	CS	2019-08-31	23	99	62
95102	王小丽	女	19	CS	2017-08-31	6	34	3
95103	王君	男	18	MA	2019-08-31	0	7	8
95104	钱国	男	18	MA	2019-08-31	93	2	35
95105	王风	女	19	IS	2019-08-31	12	84	50
95106	王一	女	18	IS	2019-08-31	29	60	86
95107	邢小	女	20	IS	2018-08-31	50	51	6
95108	赵钱	男	21	IS	2019-08-31	93	54	86
95109	周二	男	19	MA	2018-08-31	57	39	93
95110	郑明	男	18	MA	2018-08-31	30	78	62
95111	张勇	男	19	CS	2017-08-31	25	5	19
95112	刘丽	女	18	IS	2017-08-31	74	96	44
95113	张敏	女	23	MA	2017-08-31	64	17	82
95114	张小	男	19	IS	2017-08-31	14	57	69
95115	刘小	男	18	MA	2018-08-31	37	84	29
95116	孙小	男	18	CS	2018-08-31	7	99	26
95117	易小	女	19	MA	2018-08-31	60	97	26
95118	李小	女	18	CS	2018-08-31	57	8	88
95119	小小	女	20	MA	2018-08-31	7	97	64
95120	孔大	男	21	CS	2017-08-31	33	27	85
95121	包大	男	19	MA	2019-08-31	96	2	38
95122	孙小	女	18	CS	2017-08-31	54	94	23
95123	冯小伟	男	19	CS	2019-08-31	26	45	1
95124	王小丽	女	18	CS	2017-08-31	65	77	43
95125	王小君	男	23	MA	2019-08-31	24	22	34
95126	钱小国	男	19	MA	2019-08-31	98	98	2
95127	王小娟	女	18	IS	2019-08-31	36	66	30
95128	王小一	女	18	IS	2019-08-31	98	78	15
95129	邢丽	女	19	IS	2018-08-31	41	41	52
95130	赵小钱	男	18	IS	2019-08-31	87	65	93
95131	周小二	男	20	MA	2018-08-31	26	56	48
95132	郑小明	男	21	MA	2018-08-31	31	96	60
95133	李勇	男	19	CS	2017-08-31	26	53	78
95134	刘晨	女	18	IS	2017-08-31	60	27	11
95135	王敏	女	19	MA	2017-08-31	63	25	60
95136	张立	男	18	IS	2017-08-31	46	86	77
95137	刘刚	男	23	MA	2018-08-31	74	6	48
95138	孙庆	男	19	CS	2018-08-31	18	66	20
95139	易思玲	女	18	MA	2018-08-31	72	87	44
95140	李娜	女	18	CS	2018-08-31	12	68	55
95141	梦圆圆	女	19	MA	2018-08-31	63	70	29
95142	孔小涛	男	18	CS	2017-08-31	14	0	15
95143	包小柏	男	20	MA	2019-08-31	29	78	3
95144	孙花	女	21	CS	2017-08-31	1	21	86
95145	冯d伟	男	19	CS	2019-08-31	51	86	85
95146	王3丽	女	18	CS	2017-08-31	88	33	11
95147	王d君	男	23	MA	2019-08-31	61	98	29
95148	钱1国	男	19	MA	2019-08-31	88	65	12
95149	王2娟	女	18	IS	2019-08-31	30	64	41
95150	王B一	女	18	IS	2019-08-31	16	93	10
95151	邢A丽	女	19	IS	2018-08-31	58	20	24
95152	赵钱D	男	18	IS	2019-08-31	77	48	92
95153	周二C	男	20	MA	2018-08-31	89	7	60
95154	郑明B	男	21	MA	2018-08-31	18	76	59
95155	李勇A	男	19	CS	2017-08-31	53	35	49


将上面的数据存储到一个分区分桶表中。要求：分区按照学院和入学日期分区，分四个桶。

1、建表语句

-- 创建临时表
drop table if exists student_temp;
create table if not exists student_temp(
sid int,
name string,
gender char(1),
age int,
academy string, 
dt date,
chinese int, 
math int,
english int
)
row format delimited
fields terminated by '\t';

-- 加载数据
load data local inpath './data/student_temp.txt' into table student_temp;


create table student_info(
sid int,
name string,
gender string,
age int ,
chinese int,
math  int,
english int
)
partitioned by (academy string,dt date)
clustered by (sid) sorted by (age) into 4 buckets
row format delimited
fields terminated by '\t'
;



2、动态导入语句

```sql
insert into student_info partition(academy,dt) select sid,
name,gender,age,chinese,math,english,academy,dt from temp_student_info 
distribute by (sid) sort by (age desc);



3、统计每个学院每年入学的总人数以及前一年的总人数。


select 
academy,aa,`总人数`,lag(`总人数`,1) over(distribute by academy sort by aa) `去年总人数`
from 
(
select 
academy,year(dt) aa,count(1) `总人数`
from student_info
group by academy,year(dt)
) a
;



4、统计每个学院的男女人数，在一行上显示，并按照学院名称升序。


查询效果如下：
学院    男     女
cs     xxx    xxx
MA     xxxx   xxx



select academy,
sum(case when gender='男' then 1 else 0 end) `男`,
sum(case when gender='女' then 1 else 0 end) `女`
from student_info 
group by academy
;


5、查询每个学院的每年入学总成绩排名的前五名的学生信息：连续，重复效果

select
*
from 
(
select 
*,dense_rank() over(distribute by academy,year(dt) sort by `总成绩`) aa
from 
(
select 
*,chinese+math+english `总成绩`
from student_info
) a
) b 
where b.aa<6
;

2、

a_test、b_test、c_test三个hive表 每个表中都只有一列int类型且列名相同，求三个表中互不重复的数
a.txt	b.txt	c.txt
1		2		1
2		3		2
3		11		3
4		12		11
5		14		5
6		15		6
7		16		7
8		18		8
9		35		20
		6		30
		7		40
		8
    
create table a_test(
id int
);
load data local inpath './data/a.txt' into table a_test;

create table b_test(
id int
);

create table c_test(
id int
);

答案：
select 
id
from
(
select id from a
union ALL
select id from  b 
union ALL
select id from c
)t1
group by id
HAVING count(1)=1

3、

数据： 
content_test表： 
uid contents
1 i|love|china
2 china|is|good|i|i|like

需求：词频统计。统计结果如下,如果出现次数一样，则按照content名称排序： 
content cnt
i 3
china 2
good 1
like 1
love 1
is 1

create table content_test(
uid int,
contents string
)
row format delimited fields terminated by ' ';
load data local inpath './data/content_test' into table content_test;

答案：

select b.a `content`,count(1) `cnt`
from 
(
select explode(split(contents,'\\|')) a
from content_test
) b
group by b.a
;

4、

需求：求出连续七天登陆的总人数

数据: login_test表
uid  dt  login_status(1登录成功,0异常)
1	2019-07-11	1
1	2019-07-12	1
1	2019-07-13	1
1	2019-07-14	1
1	2019-07-15	1
1	2019-07-16	1
1	2019-07-17	1
1	2019-07-18	1
2	2019-07-11	1
2	2019-07-12	1
2	2019-07-13	0
2	2019-07-14	1
2	2019-07-15	1
2	2019-07-16	0
2	2019-07-17	1
2	2019-07-18	0
3	2019-07-11	1
3	2019-07-12	1
3	2019-07-13	1
3	2019-07-14	1
3	2019-07-15	1
3	2019-07-16	1
3	2019-07-17	1
3	2019-07-18	1

create table login_test(
uid int,
dt string,
login_status int
) 
row format delimited fields terminated by '\t';
load data local inpath './data/login_test' into table login_test;

答案：
select count(distinct b.uid)
from 
(
select 
*, datediff(dt,lag(dt,6) over(distribute by uid sort by dt)) a
from login_test
where login_status=1
) b 
where b.a=6
;

5、

2014010114
2014010216
2014010317
2014010410
2014010506
2012010609
2012010732
2012010812
2012010919
2012011023
2001010116
2001010212
2001010310
2001010411
2001010529
2013010619
2013010722
2013010812
2013010929
2013011023
2008010105
2008010216
2008010337
2008010414
2008010516
2007010619
2007010712
2007010812
2007010999
2007011023
2010010114
2010010216
2010010317
2010010410
2010010506
2015010649
2015010722
2015010812
2015010999
2015011023

create table if not exists temperature(
data string
)
row format delimited fields terminated by '\t'
lines terminated by '\n';

load data local inpath './data/temperature' into table temperature;

数据解析：比如：2010012325表示在2010年01月23日的气温为25度。

需求：现在要求使用hive，计算每一年的最大气温的日期+温度。效果如下：
20010105        29
20070109        99
20080103        37
20100103        17
20120107        32
20130109        29
20140103        17
20150109        99

答案
select 
b.dt,b.temp
from 
(
select
a.year,a.dt,a.temp,row_number() over(distribute by a.year sort by a.temp desc) rn
from 
(
select 
substr(data,0,4) year,substr(data,0,8) dt,substr(data,9,2) temp 
from temperature
) a 
) b 
where b.rn=1
;

6、

login_user_test表 
uid,udate
1,2019-08-01
1,2019-08-02
1,2019-08-03
2,2019-08-01
2,2019-08-02
3,2019-08-01
3,2019-08-03
4,2019-07-28
4,2019-07-29
4,2019-08-01
4,2019-08-02
4,2019-08-03

create table login_user_test(
uid int,
udate date
)
row format delimited
fields terminated by ','
;

load data local inpath './data/login_user_test.txt' into table login_user_test;

需求：查询每个用户连续登陆的最大天数，结果如下: 
uid cnt_days 
1 3 
2 2 
3 1 
4 3

答案

select 
uid,max(days)
from 
(
select
uid,dt,count(1) days
from 
(
select 
uid,date_sub(udate,row_number() over(distribute by uid sort by udate)) dt    --妙啊
from login_user_test
) a
group by uid,dt
) b
group by uid
;

7、

有如下通话记录：
呼叫		被叫	  时长
goudan haoge 01:01:01
goudan mazi 00:11:21
goudan laowang 00:19:01
goudan Jingba 00:21:01
goudan weige 01:31:17
haoge mazi 00:51:01
haoge mazi 01:11:19
haoge laowang 00:00:21
haoge laowang 00:23:01
laowang mazi 01:18:01
laowang weige 00:18:00
Jingba haoge 00:01:01
Jingba haoge 00:00:06
Jingba haoge 00:02:04
Jingba haoge 00:02:54
haoge laowang 01:00:13
haoge laowang 00:01:01
haoge goudan 00:01:01



create table call_test(
huname string comment '主动呼叫用户名',
huiname string comment '被呼叫用户名',
times string comment '呼叫时间'
) 
row format delimited 
fields terminated by ' '
;
load data local inpath './data/call_test' into table call_test;

需求：使用hql统计两个人的通话总时长（用户之间互相通话的时长）?

答案：

select B.huname,B.huiname,from_unixtime(B.length+nvl(A.length,0)-28800,'HH:mm:ss')  from 
(
select huname,huiname,sum(unix_timestamp(times,'HH:mm:ss')+28800) length  from call_test
group by huname,huiname
) 
B
left join 
(
select huname,huiname,sum(unix_timestamp(times,'HH:mm:ss')+28800) length from call_test
 group by huname,huiname
) 
A on B.huname = A.huiname and B.huiname = A.huname;

8、

user_hobbys表,数据如下、数据是制表符隔开的：

john	男	打乒乓球,游泳,看电影 
jack	男	打乒乓球,看电影
lucy	女	看书,看电影
rose	女	看电影,大乒乓球
lili	男	足球,看电影,大乒乓球
smith	男	游泳,篮球,大乒乓球
Asia	女	看书,看电影
linus	女	看书

表名user_hobbys，字段名 name	sex	hobby 

create table if not exists user_hobbys(
name string,
sex string,
hobby array<string>
)
row format delimited
fields terminated by '\t'
collection items terminated by ',';

load data local inpath './data/user_hobbys.txt' into table user_hobbys;

需求：查询最被喜欢的爱好的前三名使用hql实现结果如下:

答案：
select B.hobb from 
(select A.hobb,A.num,dense_rank() over(order by A.num desc) rk
from(
select hobb,count(*) num from user_hobbys lateral view explode(hobby) t as hobb group by hobb order by num desc
) A ) B where B.rk<4;

9、


lili	女	chinese:90,math:87,english:63,nature:76
lucy	女	chinese:60,math:30,english:78,nature:0
jack	男	chinese:89,math:25,english:81,nature:9
john	男	chinese:45,math:90,english:100,nature:80
mack	男	chinese:67,math:20,english:90,nature:45
tick	男	chinese:89,math:56,english:82,nature:34
rose	女	chinese:30,math:65,english:56,nature:76
hong	女	chinese:54,math:60,english:90,nature:56
xiao	女	chinese:66,math:70,english:12,nature:99


完成建表语句：表名:students_test,字段   name，gender,scores，制表符分隔符


create table students_test(
name string,
gender string,
scores map<string,int>
)
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':';

load data local inpath './data/students_test.txt' into table students_test;


需求1：查询男生第二名和女生第二名的姓名、性别、总成绩、以及平均分（并列连续效果）


select B.name,B.gender,B.totalscore,B.avgscore
from (
select A.name,A.gender,A.totalscore,A.avgscore,dense_rank() over(partition by gender order by totalscore desc) rk
from (
select name,gender,sum(score) totalscore,avg(score) avgscore 
from students_test lateral view explode(scores) t as subject,score 
group by name,gender) A )B where B.rk=2;



select * from t_user where uname like '%u%' and sal > 2000

10、

准备数据：
name	constellation	blood_type
小明	小熊座	R
小红	猎户座	XR
小白	猎户座	R
小蓝	小熊座	R
小绿	小熊座	R

建表，导入数据
create table white(
name string,
constellation string,
blood_type string
)
row format delimited 
fields terminated by '\t'
;
load data local inpath '/root/hivedata/white' into table white;

需求：
1、将星座和血型相同的人显示（行转列：多行）
select
t.base,concat_ws('|',collect_set(t.name)) name
from 
(
select 
*,concat(constellation,',',blood_type) base
from white
) t
group by t.base
;

11、

编写sql答案
数据：
userid,month,visits
A,2015-01,5
A,2015-01,15
B,2015-01,5
A,2015-01,8
B,2015-01,25
A,2015-01,5
A,2015-02,4
A,2015-02,6
B,2015-02,10
B,2015-02,5
A,2015-03,16
A,2015-03,22
B,2015-03,23
B,2015-03,10
B,2015-03,1

每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数，结果数据格式如下:
uid|month  |current_month_sum|截止到每月为止的最大单月访问次数|累计到本月的总访问次数|
---|-------|-----------------|----------------|-----------|
A  |2015-01|               33|              33|         33|
A  |2015-02|               10|              33|         43|
A  |2015-03|               38|              38|         81|
B  |2015-01|               30|              30|         30|
B  |2015-02|               15|              30|         45|
B  |2015-03|               34|              34|         79|


答案：
select uid,month,current_month_sum,
max(current_month_sum) over(distribute by uid sort by month) `截止到每月为止的最大单月访问次数`,
sum(current_month_sum) over(distribute by uid sort by month) `累计到本月的总访问次数`
from 
(
select 
uid,month,sum(visits) current_month_sum
from maxvisits 
group by uid,month 
) a
;

12、

数据： t4表： 

id tag flag 
a b 2
a b 1
a b 3
c d 6
c d 8
c d 8


编写sql实现如下结果： 

id tag flag 
a b 1|2|3
c d 6|8



create table t4( 
id string, 
tag string, 
flag int 
)
row format delimited 
fields terminated by ' ' 
;

load data local inpath '/root/hivedata/t4.txt' overwrite into table t4;


select 
id,tag,concat_ws("|",collect_set(cast(flag as string))) flag
from t4
group by id,tag
;

connect_set()函数，必须是string类型或者array类型才能使用，所以应该将要收集的字段修改为string类型，否则会报以下错误
Argument type mismatch 'flag': Argument 2 of function CONCAT_WS must be "string or array<string>

13、

数据： T2表: 

Tags 
1,2,3
1,2
2,3


T3表: 

id lab 
1 A
2 B
3 C


根据T2和T3表的数据，编写sql实现如下结果： 


+--------+--------+--+
|  tags  |  labs  |
+--------+--------+--+
| 1,2    | A,B    |
| 1,2,3  | A,B,C  |
| 2,3    | B,C    |
+--------+--------+--+



create table t2( 
tags string 
);
load data local inpath '/root/hivedata/t2.txt' overwrite into table t2;


create table t3( 
id int, 
lab string 
)
row format delimited 
fields terminated by ' ' 
;

load data local inpath '/root/hivedata/t3.txt' overwrite into table t3; 

select 
tags,
concat_ws(",",collect_set(lab)) labs
from 
(
select
*
from t3 
join 
(
select 
tags,tag
from t2 lateral view explode(split(tags,","))t as tag
) a 
on a.tag=t3.id
) b 
group by b.tags
;

14、

数据： t5表 


uid name tags 
1	goudan	chihuo,huaci
2	mazi	sleep
3	laotie	paly


编写sql实现如下结果： 


uid name tag 
1 goudan chihuo
1 goudan huaci
2 mazi sleep
3 laotie paly

create table t5( 
uid string, 
name string, 
tags string 
)
row format delimited 
fields terminated by '\t' ;

load data local inpath '/root/hivedata/t5.txt' overwrite into table t5;


select 
uid,name,tag
from t5 lateral view explode(split(tags,","))t as tag
;

15、

数据： content表： 


uid contents 
1	i|love|china
2	china|is|good|i|i|like


统计结果如下,如果出现次数一样，则按照content名称排序： 


+----------+------+--+
| content  | num  |
+----------+------+--+
| i        | 3    |
| china    | 2    |
| good     | 1    |
| is       | 1    |
| like     | 1    |
| love     | 1    |
+----------+------+--+

drop table content;
create table content_test( 
uid int, 
contents string 
)
row format delimited 
fields terminated by '\t' 
;
load data local inpath '/root/hivedata/content_test' overwrite into table content; 


select 
content,count(1) as num 
from 
(
select 
*
from content_test lateral view explode(split(contents,"\\|")) t as content
) a 
group by content
;

注意：|符号要记得转义！！！

explode()函数只支持展开map类型和array类型

explode() takes an array or a map as a parameter

split()函数是用于切分数据，也就是将一串字符串切割成了一个数组，标准格式为split(str,regex),支持正则切分不过
有点奇怪的是，对于 “.”,"|“这样的特殊字符，不加”\“的时候是特殊字符，加了以后才是普通字符，而对于”\d"的字符，需要
加"\“后才是特殊字符，就是是说”\\d"才是匹配数字。

16、

数据： course1表 

id course 
1,a
1,b
1,c
1,e
2,a
2,c
2,d
2,f
3,a
3,b
3,c
3,e


根据编写sql，得到结果如下(表中的1表示选修，表中的0表示未选修)： 


+-----+----+----+----+----+----+----+--+
| id  | a  | b  | c  | d  | e  | f  |
+-----+----+----+----+----+----+----+--+
| 1   | 1  | 1  | 1  | 0  | 1  | 0  |
| 2   | 1  | 0  | 1  | 1  | 0  | 1  |
| 3   | 1  | 1  | 1  | 0  | 1  | 0  |
+-----+----+----+----+----+----+----+--+



create table course( 
id int, 
course string 
)
row format delimited 
fields terminated by ',' 
;

load data local inpath '/root/hivedata/course.txt' overwrite into table course; 

select 
id,
sum(if(course='a',1,0)) as a,
sum(if(course='b',1,0)) as b,
sum(if(course='c',1,0)) as c,
sum(if(course='d',1,0)) as d,
sum(if(course='e',1,0)) as e,
sum(if(course='f',1,0)) as f
from course 
group by id
;

要加sum（）函数，否则后面的别名对应的是一列数据而不是一个，所以会报错

17、

时间戳函数：unix_timestamp，from_unixtime :

获取当前时间戳： 

unix_timestamp()

获取"2019-07-31 11:57:25"对应的时间戳: 

unix_timestamp("2019-07-31 11:57:25")

获取"2019-07-31 11:57"对应的时间戳： 

unix_timestamp("2019-07-31 11:57")

获取时间戳:1564545445所对应的日期和时分秒： 

from_unix(1564545445,"yyyy-MM-dd")

语法： from_unixtime(bigint unixtime[, string format])

获取时间戳:1564545446所对应的日期和小时(yyyy/MM/dd HH): 
from_unixtime(1564545446,"yyyy/MM/dd HH")

18、

编写sql实现行列互换。数据如下： 


id sid subject int
1,001,语文,90
2,001,数学,92
3,001,英语,80
4,002,语文,88
5,002,数学,90
6,002,英语,75.5
7,003,语文,70
8,003,数学,85
9,003,英语,90
10,003,政治,82


编写sql实现
drop table score1;
create table score1( 
id int, 
sid string, 
subject string, 
score double
)
row format delimited 
fields terminated by ',' 
;

load data local inpath '/root/hivedata/score.txt' into table score1; 

select 
sid,
sum(case when subject='语文' then score else 0 end) as `语文`,
sum(case when subject='数学' then score else 0 end) as `数学`,
sum(case when subject='英语' then score else 0 end) as `英语`,
sum(case when subject='政治' then score else 0 end) as `政治`,
sum(score) `total`
from score1
group by sid
union 
select
"total",sum(`语文`) ,sum(`数学`) ,sum(`英语`) ,sum(`政治`),sum(`total`) 
from 
(
select 
sid,
sum(case when subject='语文' then score else 0 end) as `语文`,
sum(case when subject='数学' then score else 0 end) as `数学`,
sum(case when subject='英语' then score else 0 end) as `英语`,
sum(case when subject='政治' then score else 0 end) as `政治`,
sum(score) `total`
from score1
group by sid
) t
;