HiveSQL基础+进阶(基本常识篇)

۩ﺴ往后余生ﺴ۩

已于 2022-06-05 08:43:27 修改

阅读量899

点赞数

分类专栏： SQL | HiveSQL 文章标签： hive 数据仓库 hadoop

于 2022-05-31 21:19:25 首次发布

本文链接：https://blog.csdn.net/weixin_44404350/article/details/125041951

版权

SQL | HiveSQL 专栏收录该内容

16 篇文章 4 订阅

订阅专栏

文章仅供学习参考

前言

Hive 是基于Hadoop 构建的一套数据仓库分析系统，它提供了丰富的SQL查询方式来分析存储在Hadoop 分布式文件系统中的数据，可以将结构化的数据文件映射为一张数据库表，并提供完整的SQL查询功能，可以将SQL语句转换为MapReduce任务进行运行，通过自己的SQL 去查询分析需要的内容，这套SQL 简称Hive SQL，使不熟悉mapreduce 的用户很方便的利用SQL 语言查询，汇总，分析数据。

一、HiveSQL基础

1.基础语法

SELECT A FROM B WHERE C

A列名
B表名
C筛选条件
在这里插入图片描述

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

在这里插入图片描述

在这里插入图片描述
该表有分区表，则WHERE条件中必须对分区字段dt进行限制，否则有限制的报错。

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

GROUP BY

作用：分类汇总
常用聚合函数：
count（）：计数 count(distinct XXX)去重计数
sum（）：求和
avg（）：平均值
max（）：最大值
min（）：最小值

--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-30'
GROUP BY goods_category;

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条件的结果
在这里插入图片描述

--2018年购买的商品品类在两个以上的用户数
SELECT count(a.user_name)
FROM
	(SELECT user_name,
			count(distinct goods_category) as category_num 
	 FROM user_trade
	 WHERE year(dt)='2018'
	 GROUP BY user_name 
	 HAVING count(distinct goods_category)>2)a;

ORDER BY

ASC:升序(默认)
DESC:降序
对多个字段按优先级顺序进行排序： ORDER BY A ASC , B DESC
注意：ORDER BY的执行顺序在SELECT之后，所以需使用重新定义的列名进行排序。
执行顺序：FROM→WHERE → GROUP BY → HAVING → SELECT → 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 limit 5;
--不能写ORDER BY sum（pay amount） DESC

在这里插入图片描述

2.常用函数

把时间截转化为日期

from_unixtime(bigint unixtime, string format)
format:
1.уууу-MM-dd hh:mm:ss
2.уууу-MM-dd hh
3.уууу-MM-dd hh:mm
4.ууууMMdd

拓展：把日期转化为时间戳——unix_timestamp
unix_timestamp(string date)

SELECT pay_time,
	   from_unixtime(pay_time, 'YYYY-MM-dd hh:mm:ss')
FROM user_trade
WHERE dt='2019-04-09';

在这里插入图片描述

计算日期间隔

datediff(string enddate,string startdate): 结束日期减去开始日期的天数

拓展：日期增加函数、减少函数——date_add、 date_sub
时间戳转时间的函数：from_unixtime(时间戳字段,‘yyyy-mm-dd hh:mm:ss’)
日期转时间戳的函数：unix_timestamp(string date)
时间转日期的函数：to_date()
时间转月份的函数：month()
时间转年份的函数：year()
时间转当前日期的函数：current_date()
日期增加函数：date_add(开始日期，增加天数)
日期减少函数：date_sub(开始日期，减少天数）

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

在这里插入图片描述

条件函数case when、if

case when函数：case when 条件 then 结果 end as ‘别名’
if函数：常用于只有是、否这种条件的情况

统计以下四个年龄段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,
	    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;

在这里插入图片描述
统计每个性别用户等级高低的分布情况（level大于5为高级）：

SELECT sex,
	   if(level>5, '高', '低'),
	   count(distinct user_id) user_num 
FROM user_info
GROUP BY sex,
		 if(level>5, '高', '低');

在这里插入图片描述
用户激活时间在2018年，年龄段在20—30岁和30—40岁的婚姻状况分布

SELECT a.age_type,
	   if(a.marriage_status=1,'已婚','未婚'),
	   count(distinct a.user_id)
FROM
	(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, 
			get_json_object(extral, '$.marriage_status') as marriage_status,
			user_id 
	FROM user_info
	WHERE to_date(firstactivetime) between '2018-01-01' and '2018-12-31') a
    WHERE a.age_type in ('20-30岁', '30-40岁') 
    GROUP BY a.age_type,
			 if(a.marriage_status=1,'已婚','未婚');

在这里插入图片描述

字符串函数

substr(string A,int start,int len) ：substr一般用来处理日期函数，若不写len，则默认到结尾；
get_json_object(string json_string,string path)：json_string需要解析的json字段，path用.key取出想要获取的value值

substr(string A, int start, int len)
备注：如果不指定截取长度，则从起始位一直截取到最后

每个月新激活的用户数

SELECT substr(firstactivetime, 1,7) as month,
	   count(distinct user_id) user_num
FROM user_info
GROUP BY substr(firstactivetime, 1,7);

在这里插入图片描述

解析的json字段函数get json_object()

get json_object(string json_string, string path)
json_string：需要解析的json字段
path：用.key取出想要获取的value

extra1(string):
(“systemtype”:“ios”,“education”:“master”, “marriage_status”:“1”, “phonebrand”:“iphone X”)

extra2(map<string,string>):
[“systemtype”:“ios”,“education”:“master”, “marriage_status”:“1”, “phonebrand”:“iphone X”)

在这里插入图片描述

不同手机品牌的用户数

--第一种情况
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 extra2['phonebrand'] as phone_brand,
	   count(distinct user_id) user_num 
FROM user_info
GROUP BY extra2['phonebrand'];

在这里插入图片描述

聚合统计函数

ELLA用户的2018年的平均支付金额，以及2018年最大的支付日期与最小的支付日期的间隔

SELECT avg(pay_amount) as avg_amount,
	   datediff(max(from_unixtime(pay_time, 'YYYY-MM-dd')),
	   min(from_unixtime(pay_time, 'YYYY-MM-dd')))
FROM user_trade
WHERE year(dt) =' 2018' and user_name='ELLA';
--max(from_unixtime(pay_time,'yyyy-MM-dd'))= from_unixtime(max(pay_time).'yyyy-MM-dd'))

注意：不许嵌套组合avg(count(*))

HiveSQL窗口函数

sum(),avg(),max(),count(),min()这五个聚合函数之间不可以互相嵌套。如不允许嵌套组合avg(count(*))；
在 Group by 子句中，Select 查询的列，要么需要是 Group by 中的列，要么得是用聚合函数（比如 sum、count 等）加工过的列。不支持直接引用非 Group by 的列。
这一点和 MySQL 有所区别。

sum（）、avg（）累计计算的窗口函数

sum(…) over(…)

partition by起到分组的作用
order by 按照什么顺序进行累加，升序ASC、降序DESC，默认升序

2018年每月的支付总额和当年累积支付总额

SELECT a.month, 
	   a.pay_amount,
	   sum(a.pay_amount) over(order by a.month) 
FROM
	(SELECT month(dt) month,
			sum(pay_amount) pay_amount 
	FROM user_trade
	WHERE year(dt)=2018 
	GROUP BY month(dt) ) a;

在这里插入图片描述
2017—2018年每月的支付总额和当年累积支付总额

SELECT a.year,
	   a.month,
	   a.pay_amount,
	   sum(a.pay_amount) over(partition by a.year order by a.month)
FROM
	(SELECT year(dt) year,
			month(dt) month,
			sum(pay_amount) pay_amount
	FROM user_trade
	WHERE year(dt) in(2017,2018)
	GROUP BY year(dt),month(dt) ) a;

在这里插入图片描述

avg(…) over(…)

2018年每个月的近三月移动平均支付金额

SELECT a.month,
	   a.pay_amount,
	   avg(a.pay_amount) over(
	   order by a.month 
	   rows between 2 preceding and current row)
FROM
	(SELECT month(dt) month,
			sum(pay_amount) pay_amount 
	FROM user_trade
	WHERE year(dt) =2018 
	GROUP BY month(dt)) a;

在这里插入图片描述
用rows between 2 preceding and current row来限制计算移动平均的范围
本语句含义是包含本行及前两行，这个就是我们题目中要求的近三月的写法。

小结：
sum( A ) over(partition by B order by C rows between D1 and D2 )
avg( A ) over(partition by B order by C rows between D1 and D2 )
A：需要被加工的字段名称
B：分组的字段名称
C：排序的字段名称
D：计算的行数范围

–包括本行和之前所有的行
rows between unbounded preceding and current row
–包括本行和之后所有的行
rows between current row and unbounded following
–包括本行以内和前三行
rows between 3 preceding and current row
–从前三行到下一行(5行)
rows between 3 preceding and 1 following

同理：
max(…) over(partition by … order by … rows between … and …)
min(…) over(partition by … order by … rows between … and …)

row_number（）、rank（）、dense_rank（）分区排序窗口函数

2019年1月，用户购买商品品类数量的排名

SELECT user_name,
	   count(distinct goods_category),
	   row_number() over(order by count(distinct goods_category)), 
	   rank() over(order by count(distinct goods_category)),
	   dense_rank() over(order by count(distinct goods_category)) 
FROM user_trade
WHERE substr(dt,1,7) ='2019-01' 
GROUP BY user_name;

在这里插入图片描述
选出2019年支付金额排名在第10、20、30名的用户

SELECT a.user_name,
	   a.pay_amount, 
	   a.rank
FROM
	(SELECT user_name,
			sum(pay_amount) pay_amount,
			rank() over(order by sum(pay_amount) desc) rank 
	FROM user_trade
	WHERE year(dt) =2019
	GROUP BY user_name) a 
WHERE a.rank in(10,20,30);

在这里插入图片描述
每个城市，不同性别，2018年支付金额最高的TOP3用户

SELECT c.user_name,
	   c.city,
	   c.sex,
	   c.pay_amount, 
	   c.rank
FROM
	(SELECT a.user_name,
			b.city,
			b.sex,
			a.pay_amount, 
			row_number() over (
			partition by b.city,b.sex order by a.pay_amount desc) rank
	FROM 
		(SELECT user_name, 
				sum(pay_amount) pay_amount 
		FROM user_trade 
		WHERE year (dt) =2018 
		GROUP BY user_name) a 
		LEFT JOIN user_info b on a.user_name=b.user_name) c 
WHERE c.rank<=3;

在这里插入图片描述
小结：
row_number() over(…).rank() over(…) dense_rank() over(…)

这三个函数的作用都是返回相应规则的排序序号
row_number() over(partition by A order by B )
rank() over(partition by A order by B )
dense_rank() over(partition by A order by B )

A：分组的字段名称
B：排序的字段名称

注意：row_number（）的这个括号内是不加任何字段名称的
rank（）和dense_rank（）同理。

row_number：它会为查询出来的每一行记录生成一个序号，依次排序且不会重复。

rank&dense_rank：如果使用rank函数来生成序号， over子句中排序字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名号排下一个，也就是相关行之前的排名数加一。dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时，将不跳过相同排名号，rank值紧接上一次的rank值。在各个分组内， rank（）是跳跃排序，有两个第一名时接下来就是第三名， dense_rank（）是连续排序，有两个第一名时仍然跟着第二名。

ntile(n) over(…)分组排序窗口函数

将2019年1月的支付用户，按照支付金额分成5组

SELECT user_name,
	   sum(pay_amount) pay_amount,
	   ntile(5) over(order by sum(pay_amount) desc) level 
FROM user_trade 
WHERE substr(dt, 1,7) ='2019-01' 
GROUP BY user_name;

在这里插入图片描述
选出2019年退款金额排名前10%的用户

SELECT a.user_name,
	   a.refund_amount, 
	   a.level
FROM
	(SELECT user_name,
		sum(refund_amount) refund_amount,
		ntile(10) over(order by sum(refund_amount) desc) level 
	FROM user_refund
	WHERE year(dt) =2019 
	GROUP BY user_name) a 
WHERE a.level=1;

在这里插入图片描述
每个手机品牌退款金额前25%的用户

SELECT *
FROM
		(SELECT a.user_name,
				extra2['phonebrand' ] as phonebrand, 
				a.refund_amount,
				ntile(4) over(
				partition by extra2['phonebrand'] order by a.refund_amount desc) level
	FROM
		(		SELECT user_name, 
					   sum(refund_amount) refund_amount 
				FROM user_refund 
				WHERE dt>'O' 
				GROUP BY user_name) a 
				LEFT JOIN user_info b on a.user_name=b.user_name) c 
WHERE c.level=1;

在这里插入图片描述

小结：
ntile(n) over(partition by A order by B )

n：切分的片数
A：分组的字段名称
B：排序的字段名称

ntile(n)，用于将分组数据按照顺序切分成n片，返回当前切片值.
ntile不支持ROWS BETWEEN,比如：
NTILE(2) OVER(PARTITION BY … ORDER BY … ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
如果切片不均匀，默认增加第一个切片的分布

lag(…) over(…)、lead(…) over(…)偏移分析窗口函数

Alice和Alexander的各种时间偏移

SELECT user_name,
	   dt,
	   lag(dt, 1,dt) over(partition by user_name order by dt), 
	   lag(dt) over (partition by user_name order by dt), 
	   lag(dt, 2,dt) over(partition by user_name order by dt),
	   lag(dt, 2) over(partition by user_name order by dt) 
	   FROM user_trade
WHERE dt>'0' 
and user_name in('Alice', 'Alexander');

在这里插入图片描述
支付时间间隔超过100天的用户数

SELECT count(distinct user_name) 
FROM
	(SELECT user_name,
			dt,
			lead(dt) over(partition by user_name order by dt) lead_dt 
			FROM user_trade
			WHERE dt>'0' )  a
WHERE a.lead_dt is not null 
	  and datediff(a.lead_dt,a.dt)>100;

小结：
Lag和Lead分析函数可以在同一次查询中取出同一字段的**前N行的数据（Lag）和后N行的数据（Lead）**作为独立的列。

在实际应用当中，若要用到取今天和昨天的某字段差值时，Lag和Lead函数的应用就显得尤为重要。当然，这种操作可以用表的自连接实现，但是LAG和LEAD与left join、right join等自连接相比，效率更高， SQL更简洁。

lag(exp_str, offset,defval) over(partion by …order by …)
lead(exp_str,offset,defval) over(partion by …order by …)

exp_str是字段名称。

offset是偏移量，即是上1个或上N个的值，假设当前行在表中排在第5行，则offset 为3，则表示我们所要找的数据行就是表中的第2行（即5—3=2）。offset默认值为1。

defval默认值，当两个函数取上N/下N个值，当在表中从当前行位置向前数N行已经超出了表的范围时， lag（）函数将defval这个参数值作为函数的返回值，若没有指定默认值，则返回NULL，那么在数学运算中，总要给一个默认值才不会出错。

3.表连接(inner join、left join | right join、full join、union all)

在这里插入图片描述

inner join

inner可省略不写，效果一样
在这里插入图片描述
既在user_list_1也在user_list_2的用户

SELECT *
FROM user_list_1 a 
JOIN user_list_2 b ON a.user_id=b.user_id;

在这里插入图片描述
在2019年购买后又退款的用户

SELECT a.user_name
FROM
	(SELECT distinct user_name 
	FROM user_trade
	WHERE year(dt) =2019) a 
	JOIN
	(SELECT distinct user_name 
	FROM user_refund
	WHERE year(dt) =2019) b on a.user_name=b.user_name;

在2017年和2018年都购买的用户

SELECT a.user_name
FROM
	(SELECT distinct user_name 
	FROM user_trade
	WHERE year(dt) =2017) a 
	JOIN
	(SELECT distinct user_name 
	FROM user_trade
	WHERE year(dt)=2018) b on a.user_name=b.user_name;

在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;
##在表的数据量级很大时，推荐第二种写法
SELECT a.user_name
	   --b.user_name     --三选一
	   --c.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;

left join | right join

left join 中可以使用max、count等聚合函数

SELECT *
FROM user_list_1 a 
LEFT JOIN user_list_2 b ON a.user_id=b.user_id;

在这里插入图片描述
left join进行左连接后，以左边的表1为全集，返回能够匹配上的右边表2的匹配结果，没有匹配上的则显示NULL。
同理，right join：以右边的表为全集，返回能够匹配上的左表的匹配结果，没有匹配上的则显示NULL但其完全可以由left join改写出同样的结果，所以较少使用

取出在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;

在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;

在2019年购买用户的学历分布

SELECT b.education,
	   count(a.user_name)
FROM
	(SELECT distinct user_name 
	FROM user_trade
	WHERE year(dt) =2019) a 
	LEFT JOIN
	(SELECT user_name, 
	get_json_object(extral, '$.education') as education
	--extra2['education'] as education  --或者这种写法
	FROM user_info) b on a.user_name=b.user_name
GROUP BY b.education;

在这里插入图片描述
在2017和2018年都购买，但是没有在2019年购买的用户

--推荐写法
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 
	LEFT JOIN
	(SELECT distinct user_name 
	FROM trade_2019) c on b.user_name=c.user_name 
WHERE c.user_name is null;

--不推荐写法  如果表比较小的时候，这样写影响不大。但是有分区的大表，这样写执行速度很慢。
SELECT distinct a.user_name 
	FROM trade_2017 a 
	JOIN trade_2018 b on a.user_name=b.user_name
	LEFT JOIN trade_2019 c on b.user_name=c.user_name
	WHERE c.user_name is null;

注意：
表连接时，必须进行重命名
on后面使用的连接条件必须起到唯一键值的作用
子查询记得写别名，如：a.user_name
表连接最好都先去重，再连接（提高查询效率）

full join

在这里插入图片描述

SELECT *
FROM user_list_1 a 
FULL JOIN user_list_2 b ON a.user_id=b.user_id;

在这里插入图片描述
user_list_1和user_list_2的所有用户

SELECT coalesce(a.user_name, b.user_name)
FROM user_list_1 a FULL JOIN user_list_2 b on a.user_id=b.user_id;

在这里插入图片描述
coalesce是一个函数,（expression_1， expression_2，…，expression_n）依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。

union all

注意：字段名称必须一致、字段顺序必须一致、没有连接条件

1.Sql支持Union 和 UnionAll，其中Union可以达到去重的效果，但Hive只支持Union ALL，所以这里有去重需求需要使用Group By

2.Union All的时候每个语句返回的列的数量和名字必须一样，这也是为什么union all时在select语句外又重复了一条select user语句

3.Union All的时候每个表使用别名，否则容易报 missing EOF

4.Union all的每张表不支持limit、order by等函数，但是可以添加很多where过滤条件

5.子查询中使用union all时，在子查询里不能使用count、sum 等聚合函数,但是两表直接进行union all 可以使用count、sum 等聚合函数。

6.Union方法可以确定拿到每个字段，支持插入到对应表。

将user_list_1和user_list_3合并在一起

SELECT user_id,
	   user_name 
FROM user_list_1 
UNION ALL 
SELECT user_id,
	   user_name 
FROM user_list_3;

在这里插入图片描述
2017—2019年有交易的所有用户数

##写法一  hive
SELECT count(distinct a.user_name),
	   count(a.user_name)
FROM
	(
	   SELECT user_ name
	   FROM trade_2017
	   UNION ALL
	   SELECT user_name
	   FROM trade_2018
	   UNION ALL
	   SELECT user_ name
	   FROM trade_2019) a;
	    
##写法二    hive 不支持union 
SELECT count(distinct a.user_name), 
	   count(a.user_name) 
FROM 
	  (
		SELECT user_name 
		FROM trade_2017 
		UNION 
		SELECT user_name 
		FROM trade_2018 
		UNION
		SELECT user_name 
		FROM trade_2019) a;

UNION ALL和UNION的区别(hive 不支持union)

对比	UNION ALL	UNION
对重复结果的处理	不会去除重复记录	在进行表连接后会筛选掉重复的记录
对排序的处理	只是简单的将两个结果合并后就返	将会按照字段的顺序进行排序
效率	更快	更慢
总述	不去重不排序	去重且排序

注意：如果表很大时推荐先去重，再进行union all。

2019年每个用户的支付和退款金额汇总

SELECT coalesce(a.user_name, b.user_name),
	   if(a.pay_amount is null, 0, a.pay_amount),
	   if(b.refund_amount is null, 0,b.refund_amount)
FROM
	(SELECT user_name,
		    sum(pay_amount) as pay_amount 
	FROM user_trade
	WHERE year(dt) = 2019 
	GROUP BY user_name) a 
	FULL JOIN
	(SELECT user_name,
			sum(refund_amount) as refund_amount 
	FROM user_refund
	WHERE year(dt)=2019
	GROUP BY user_name) b on a.user_name=b.user_name;

同理，2019年每个用户的支付和退款金额

SELECT a.user_name,
	   a.pay_amount, 
	   b.refund_amount
FROM
	(SELECT user_name,
			sum(pay_amount) as pay_amount 
	FROM user_trade
	WHERE year(dt)=2019 
	GROUP BY user_name) a 
	LEFT JOIN
	(SELECT user_name,
			sum(refund_amount) as refund_amount 
	FROM user_refund
	WHERE year(dt) =2019
	GROUP BY user_name) b on a.user_name=b.user_name;

首次激活时间在2017年，但是一直没有支付的用户年龄段分布

SELECT a.age_level,
	   count(a.user_name)
FROM
	(SELECT user_name,
			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_level 
	FROM user_info
	WHERE year(firstactivetime) =2019) a 
	LEFT JOIN 
	(SELECT distinct user_name 
	FROM user_trade
	WHERE dt>O)b on a.user_name=b.user_name
WHERE b.user_name is null
GROUP BY a.age_level;

在这里插入图片描述
2018、2019年交易的用户，其激活时间段分布

SELECT hour(firstactivetime),
	   count(a.user_name)
FROM
	(
	SELECT user_name 
	FROM trade_2018 
	UNION ALL
	SELECT user_name 
	FROM trade_2019) a 
	LEFT JOIN user_info b on a.user_name=b.user_name
GROUP BY hour(firstactivetime);

4.常见错误及解决办法

1.标点符号错误，使用全角符号
2.没有对子查询的表和字段进行重命名
3.使用错误的字段名
4.丢了逗号分隔符

细节概念：
1.LEFT OUTER JOIN 和 LEFT JOIN ，FULL JOIN 和 FULL OUTER JOIN效果都相同。

2.FULL OUTER JOIN 其实就是把 LEFT OUTER JOIN 和 RIGHT OUTER JOIN的结果结合在一起。

总结

1.利用GROUP BY做聚合计算
2. 利用ORDER BY做排序
3.牢记SQL执行顺序
4.常用函数组合使用
5.避免常见错误
6.实际业务场景中，熟练选择JOIN、LEFT JOIN来解决具体问题
7.区分好FULL JOIN和UNION ALL的使用场景(Full Join，适合两个表；Union All，推荐👍 适合3个及以上表)
8.union会重复记录(去重),union all和full join不删除重复值
9.在hive中若有子查询必须指定子查询的表别名
10.对sum()、avg()这类累计计算的窗口函数的行数限制
11.不要混清row_number()、rank()、dense_rank()三种函数
12.会使用ntile()进行分组查询
13.lag()：前N行、lead()：后N行