HiveQL学习笔记（五）：Hive练习题_hive sql练习题目

本系列是本人对Hive的学习进行一个整理，主要包括以下内容：
1.HiveQL学习笔记（一）：Hive安装及Hadoop,Hive原理简介
2.HiveQL学习笔记（二）：Hive基础语法与常用函数
3.HiveQL学习笔记（三）：Hive表连接
4.HiveQL学习笔记（四）：Hive窗口函数
5.HiveQL学习笔记（五）：Hive练习题
接下来对第五个内容进行介绍。

HiveQL学习笔记（二）对应的练习题

HiveQL学习笔记（二）：Hive基础语法与常用函数
这里没有原版的数据，只有字段名，所以只能靠脑补……主要是锻炼思考问题的方法，也可以上网找一些MySQL的题，然后用HiveSQL去完成。

select user_name
from user_info
where city='beijing'
and sex='female'
limit 10;
1
2
3
4
5

---

select user_name,piece,price
from user_trade
where dt='2019-04-09'
and goods_category='food';
1
2
3
4

---

select goods_category,
		sum(distinct user_name) as user_sum,
		sum(pay_amount) as total_amount 
from user_trade
where dt BETWEEN '2019-01-01' AND '2019-04-30'
group by goods_category;
1
2
3
4
5
6

select user_name,sum(pay_amount) as total_amount
from user_trade
where dt BETWEEN '2019-04-01' AND '2019-04-30'
group by user_name
having sum(pay_amount) > 50000;
1
2
3
4
5

---

select user_name,sum(pay_amount) as total_amount
from user_trade
where dt BETWEEN '2019-04-01' AND '2019-04-30'
group by user_name
order by sum(pay_amount) desc
limit 5;
1
2
3
4
5
6

---

将user_trade中的时间戳转为以下时间格式

select pay_time,
from_unixtime(pay_time,'yyyy-MM-dd hh:mm:ss')
from user_trade
where dt='2019-04-09';
****************************
select pay_time,
from_unixtime(pay_time,'yyyy-MM-dd hh')
from user_trade
where dt='2019-04-09';
****************************
select pay_time,
from_unixtime(pay_time,'yyyy-MM-dd hh:mm')
from user_trade
where dt='2019-04-09';
****************************
select pay_time,
from_unixtime(pay_time,'yyyyMMdd')
from user_trade
where dt='2019-04-09';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

select user_name,
datediff('2019-05-01',to_date(firstactivetime))
from user_info
limit 10;
1
2
3
4

---

select case when age<20 then '20岁以下'
			when age>=20 and age<30 then'20-30岁'
			when age>=30 and age<40 then'30-40岁'
			else '40岁以上'
			end as '年龄分组',
	   count(distinct user_id) as user_num
from user_info
group by case when age<20 then '20岁以下'
			  when age>=20 and age<30 then'20-30岁'
			  when age>=30 and age<40 then'30-40岁'
			  else '40岁以上'
			  end;
1
2
3
4
5
6
7
8
9
10
11
12

select sex,
if(level>5,'高级','低级'),
count(distinct user_id) as user_num
from user_info
group by sex,if(level>5,'高级','低级');
1
2
3
4
5

---

select substr(firstactivetime,1,