从小案例学习Hive——基础查询分析、连接查询分析

一、基础查询分析

基础语法

需求1：某次经营活动中，商家发起了"异性拼团购"，试着针对某个地区的用户
进行推广，找出匹配用户。
参考实现：选出城市在北京，性别为女的10个用户名

SELECT user_name
FROM user_info
WHERE city='beijing' and sex='female'
limit 10;

需求2：某天，发现食物类的商品卖的很好，你能找出几个资深吃货吗？
参考实现：选出在2019年6月18日，购买的商品品类是food的用户名、购买数
量、支付金额

SELECT user_name,
piece,
pay_amount
FROM user_trade
WHERE dt='2019-06-18' and goods_category='food' ;

需求3：试着对本公司2019年第一季度商品的热度与价值度进行分析。
参考实现：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-03-31'
GROUP BY goods_category;

需求4-1：2019年4月，支付金额超过5万元的用户。给VIP用户赠送优惠劵。

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;

需求4-2：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;

常用函数

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 unixtime, string format)：将时间戳转化为
指定格式的日期
format：

1. yyyy-MM-dd hh:mm:ss
2. yyyy-MM-dd hh
3. yyyy-MM-dd hh:mm
4. yyyyMMdd
   2、如何计算日期间隔？
   需求5：去年的劳动节新用户推广活动价值分析。即拉新分析。
   参考实现：用户的首次激活时间，与2019年5月1日的日期间隔

SELECT user_name,
datediff('2019-05-01',to_date(firstactivetime))
FROM user_info
limit 10;

datediff(string enddate, string startdate)：结束日期减去开始日
期的天数
3、条件函数
case when
需求6：对用户的年龄段进行分析，观察分布情况。
参考实现：统计以下四个年龄段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
需求7：去年王思聪的微博抽奖活动引起争议，我们想要观察用户等级随性别的
分布情况。
参考实现：统计每个性别用户等级高低的分布情况(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、字符串函数
需求8：分析每个月的拉新情况，可以倒推回运营效果。

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)
备注：如果不指定截取长度，则从起始位一直截取到最后。
get_json_object(string json_string, string path)
param1：需要解析的json字段
param2：用$.key取出想要获取的value
5、聚合统计函数
随堂练习1：ELLA用户的2018年的平均每次支付金额，以及2018年最大的支付
日期与最小的支付日期的间隔

SELECT avg(pay_amount) as avg_amount,
datediff(max(from_unixtime(pay_time,'yyyy-MMdd')),
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’))
datediff(max(pay_time),min(pay_time))
注意：不许嵌套组合avg(count(*))

重点练习
找出在2018年具有VIP潜质的用户，发送VIP试用劵。
三步走：
第一步：先求出每个人购买的商品品类数
第二步：筛选出购买商品品类数大于2的用户
第三步：统计符合条件的用户有多少个
随堂练习1：ELLA用户的2018年的平均每次支付金额，以及2018年最大的支付
日期与最小的支付日期的间隔

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

需求10：找出在2018年具有VIP潜质的用户，发送VIP试用劵。
参考实现：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;

2、用户激活时间在2018年，年龄段在20-30岁和30-40岁的婚姻状况分布
审题——拆解
第一步：先选出激活时间在2018年的用户，并把他们所在的年龄段计算
好，并提取出婚姻状况
第二步：取出年龄段在20-30岁和30-40岁的用户，把他们的婚姻状况转义
成可理解的说明
第三步：聚合计算，针对年龄段、婚姻状况的聚合
随堂练习2：用户激活时间在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(extra1, '$.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,'已婚','未婚');

二、连接查询分析

表连接【inner join / left join / full join /union join】

需求1：某年度对用户满意度进行调研分析，找出目标人群
参考实现：在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;

！！注意：一定要先去重，再做表连接，养成良好习惯
PS：虽然可以先连接后再去重，但是这么做的话，执行效率会低。
需求2：用户忠诚度类项目的调研分析，找出目标人群
参考实现：在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;

需求3：高忠诚度用户的匹配分析，用以生成心路历程类报表推送给用户，
找出目标人群
参考实现：在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
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;

在表的数据量级很大时，推荐第二种写法
为什么写a.user_name呢？b.user_name或者c.user_name可以吗？
——当然可以。
因为JOIN是取表的交集，所以不管取哪个表的这个字段都是一样的结
果。
【left join】

right join：以右边的表为全集，返回能够匹配上的右表的匹配结果，没有
匹配上的则显示NULL
但其完全可以由left join改写出同样的结果，所以较少使用

需求4：无退款服务类用户的定位分析，用以发送服务判断类用户调研
参考实现：在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;

需求5：对重点客户的学历进行调研分析，观察其分布情况
参考实现：在2019年购买用户的学历分布

SELECT b.education,
count(distinct a.user_name)
FROM
(SELECT distinct user_name
FROM user_trade
WHERE year(dt)=2019)a
LEFT JOIN
(SELECT user_name,
get_json_object(extra1, '$.education') as
education
FROM user_info)b
on a.user_name=b.user_name
GROUP BY b.education;

需求6：老客户召回计划，匹配到目标人群
参考实现：在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;

【full join】

拓展：coalesce是一个函数， (expression_1, expression_2,
…,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该
值。如果所有的表达式都是空值，最终将返回一个空值。
【union all】：联合所有
注意：

• 字段名称必须一致
• 字段顺序必须一致
• 没有连接条件
  需求7：对近几年的交易进行分析，评估平台的价值
  参考实现：2017-2019年有交易的所有用户数

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;

需求8-1：对某年度的客户交易价值进行分析
参考实现：2019年每个用户的支付和退款金额汇总

SELECT a.user_name,
sum(a.pay_amount),
sum(a.refund_amount)
FROM
(
SELECT user_name,
sum(pay_amount) as pay_amount,
0 as refund_amount
FROM user_trade
WHERE year(dt)=2019
GROUP BY user_name
UNION ALL
SELECT user_name,
0 as pay_amount,
sum(refund_amount) as refund_amount
FROM user_refund
WHERE year(dt)=2019
GROUP BY user_name
)a
GROUP BY a.user_name;

需求8-2：对某年度的重点客户交易价值进行分析
参考实现：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;

需求9-1：对沉默用户的年龄段进行分析，用以部署活动来刺激其消费
参考实现：首次激活时间在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)=2017)a
LEFT JOIN
(SELECT distinct user_name
FROM user_trade
WHERE dt>'0')b
on a.user_name=b.user_name
WHERE b.user_name is null
GROUP BY a.age_level;