Hive SQL基础

Hive执行顺序：

**FROM-->WHERE-->GROUP BY-->HAVING-->SELECT-->ORDER BY**

书写顺序：

**SELECT DISTINCT
FROM
JOIN
ON
WHERE
GROUP BY
WITH
HAVING
ORDER BY
LIMIT**

1.HIVE简介

1）HIVE是基于Hadoop的数据仓库

Hive SQL与传统SQL对比：

PS:块设备是i/o设备中的一类，是将信息存储在固定大小的块中，每个块都有自己的地址，还可以在设备的任意位置读取一定长度的数据，例如硬盘，U盘，SD卡等。

2）MapReduce简介

2.基础语法

1）SELECT…A…FROM…B…WHERE…C…

A:列名
B:表名
C:筛选条件

user_info列名	举例
user_id	10001,10002（唯一的）
user_name	Amy，Dennis（唯一的）
sex	[male,female]
age	[13,70]
city	beijing,shanghai
firstactivetime	2019-04-19 15:40:00
level	[1,10]
extra1	string类型:{“systemtype”:“ios”,“education”:“master”,“marriage_status”:“1”,“phonebrand”:“iphoneX”}
extra2	map<string.string>类型: {“systemtype”:“ios”,“education”:“master”,“marriage_status”:“1”,“phonebrand”:“iphoneX”}

--选出城市在北京，性别为女的10个用户名
select user_name
from user_info
where city='beijing' and sex='female'
limit 10;

user_trade列名	举例
user_name	Amy,Dennis
piece	购买数量
price	价格
pay_amount	支付金额
goods_category	food,clothes,book,computer,electronics,shoes
pay_time	2412521561,时间戳
dt	partition,‘yyyy-mm-dd’

注意：如果该表是一个分区表，则where条件中必须对分区字段进行限制。

--选出在2019年4月9日，购买的商品品类是food的用户名、购买数量、支付金额
select user_name,piece,pay_amount from user_trade
where dt="2019-04-09" and goods_category='food';

未对分区进行限制的报错：

select user_name,piece,pay_amount from user_trade
where goods_category='food';

注：分区表必须限制分区字段

2）GROUP BY 的作用：分类汇总

--2019年一月到四月，每个品类有多少人购买，累计金额是多少
select goods_category,
	count(distinct user_name) as user_num,
	sum(pay_amount) as total_amount
from user_trade
where dt between '2019-01-01' and '2019-04-03'
group by goods_category;

常用的聚合函数：
1.count():计数count(distinct…)去重计数；
2.sum():求和；
3.avg():平均值；
4.max():最大值；
5.min():最小值
GROUP BY …HAVING

--2019年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
having sum(pay_amount)>50000;

HAVING:对GROUP BY 的对象进行筛选，仅返回符合HAVING条件的结果

3）ORDER BY

--2019年4月，支付金额最多的top5用户
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 total_amount desc limin 5;

ASC:升序（默认）
DESC:降序
对多个字段进行排序：ORDER BY A ASC,B DESC
ORDER BY A DESC,B DESC
为什么order by 后面不直接写sum(pay_amount)而是用total_amount？
答：执行顺序，order by 的执行顺序在select之后，所以需要使用重新定义的列名进行排序。

4）执行顺序

FROM–>WHERE–>GROUP BY -->HAVING–>SELECT–>ORDER BY

3.常用函数

1）如何把时间戳转化为日期

select pay_time,from_unixtime(pay_time,
'yyyy-MM-dd hh:mm:ss')
from user_trade
where dt='2019-04-09';

from_unixtime(bigint unitime,string format)
format:
1.yyyy-MM-dd hh:mm:ss
2.yyyy-MM-dd hh
3.yyyy-MM-dd hh：mm
4.yyyyMMdd
把日期转化为时间戳–unix_timestamp
unix_timestamp(string date)

2）如何计算日期间隔

--用户的首次激活时间，与2019年5月1日的日期间隔
select user_name,
	datediff('2019-05-01',to_date(firstactivetime))
from user_info
limit 10;

datediff(string enddate,string startdate):结束如期减去开始日期的天数
拓展：日期增加函数、减少函数----date_add、date_sub
date_add(string startdate，int days)
date_sub(string startdate，int days)

3）条件函数

case when

--统计一下四个年龄段20岁一下、20-30岁、30-40岁、40岁以上的用户数
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 age_type,
		count(distinct user_id) 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 ;

if

--统计每个性别用户等级高低的分布情况（level 大于5为高级）
select sex,
	if(level>5,'高','低') as level_type,
	count(distinct user_id) user_num
from user_info
group by sex,
	if(level>5,'高','低');

4）字符串函数

--每个月激活的用户数
select substr(firstactivetime,1,7) as month,
	count(distinct user_id) user_num
from user_info
group by substr(firstactivetime,1,7);

substr(string A,int start,int len)
备注：如果不指定截取长度，则从起始位一直截取到最后
不同手机品牌的用户数

extra	类型
extra1	string类型:{“systemtype”:“ios”,“education”:“master”,“marriage_status”:“1”,“phonebrand”:“iphoneX”}
extra2	map<string.string>类型: {“systemtype”:“ios”,“education”:“master”,“marriage_status”:“1”,“phonebrand”:“iphoneX”}

-- 不同手机品牌的用户数
## 第一种情况
select get_json_object(extral,'$.phonebrand') as phone_brand,
		count(distinct user_id) user_num
from user_info
group by get_json_object(extral,'$.phonebrand');
## 第二种情况
select extral2['phonebrand'] as phone_brand,
		count(distinct user_id) user_num
from user_info
group by extra2['phonebrand'];

param1：需要解析的json字段；
param2：用key去除想要获取的value

5）聚合统计函数

– 如何取出在user_list_1表但不在user_list_2的用户？

select a.user_id,
	    a.user_name
from user_list_1 a left join user_list_2 b on a.user_id=b.user_id
where b.user_id is null;

--注：MySQL中的写法(子查询)
select user_id,
		user_name
from user_list_1
where user_id not in(select user_id from user_list_2)

--在2019年购买但是没有退款的用户
select a.user_name
from
(select distinct user_name 
from user_trade 
where year(dt)=2019)a
left join
(select distinct user_name
from user_refund 
where year(dt)=2019)b
on a.user_name=b.user_name
where b.user_name is null;

-- 在2017年、2018年、2019年都有交易的用户
-- 第一种写法
select distinct a.user_name
from trade_2017 a
join trade_2018 b on a.user_name=b.user_name
join trade_2019 c on b.user_name=c.user_name;

-- 第二种写法(在表的数据量很大时，推荐这种写法，hive中建议这种写法)
select a.user_name 
from
	(select distinct user_name 
	from trade_2017)a
join
	(select distinct user_name 
	from trade_2018)b on a.user_name=b.user_name
join
	(select distinct user_name 
	from trade_2019)c on b.user_name=c.user_name;