本文主要结合以下一个实际电商企业案例中的脱敏数据进行基本的Hive查询分析，主要的hive表结构，如Excel中所示，可以自行前往我的百度网盘链接中下载。具体数据在我搭建的虚拟机里，如果有兴趣可以在文末留言邮箱。

一、准备工作（仔细观察hive数据表结构）

一个Excel表格是要用到的主要的表格的结构；

因为一个字段的值可能会给不同的系统使用，所以会有两个字段结构，有的习惯从map拿值，有的习惯从string拿值【如下表中extra1和extra2】；extra1是在表中留一些拓展的字段；

而且这里面的都是string居多，如user_id，HQL里不用int；
Hive在创建表之后再增加字段嘛？一般用映射成外部表
启动hive的过程，前提把虚拟机打开了

使用kaikeba这个数据库

二、实际企业需求

关键难点：在于实际业务需求转化为可操作的业务需求，再写成HQL语句

①需求1：某次经营活动中，商家发起了"异性拼团购"，试着针对某个地区的用户进行推广，找出匹配用户。【拼团中，每个人也是不同的订单】

执行这个查询语句

②需求2：某天，发现食物类的商品卖的很好，你能找出几个资深吃货吗？

注：若该表是分区表，则WHERE条件中必须对分区字段进行限制。

③需求3：试着对本公司2019年第一季度商品的热度与价值度进行分析。

热度就是被买的数量多，价值度就是买的金额多

pv是没去重的量，uv是去重之后的量；如果是分区的表的话都是有dt，
(datetime)user_trade就是一个分区表，分区字段是日期，datatime；
between and是包括两个端点

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

可以查看的到分区的字段是dt：
不用pay_time是因为这个pay_time是每一秒都有一个记录

展示分区show partitions user_trade;
时间戳

五、需求5

去年的劳动节新用户推广活动价值分析。即拉新分析。

【就是从去年劳动节开始到现在激活了多少用户】【首次激活时间大于5.1】

一般都是使用时间戳存储的数据：因为有抢购和秒杀的活动

计算是后减前；

在hive中查看某个函数的用法：desc function extended XX函数名；

六、需求六：对用户的年龄段进行分析，观察分布情况。

写之前先判断使用哪个表，使用哪个字段，这样才可以开始去写；

case when … then….

注意记住上图的写法：这个里面用的是针对所有的分段使用了count(distinct user_id)；
group by 后面能不能使用age_type？不能，因为group by先执行了，这时还没有age_type这个字段，order by 可以，因为在select后面；

七、需求七：王思聪的微博抽奖活动引起争议，想要观察用户等级随性别分布情况。

八、需求八：分析每个月的拉新情况，可以倒推回运营效果。

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

结果如下：左边是月份，右边是人数；

九、需求九：找出不同手机品牌的用户分布情况。

就要用到拓展字段里的字段：

#第一种情况，【通过string和json这种类似调用对象的属性的方法】

SELECT get_json_object(extra1, '$.phonebrand') as
phone_brand,
count(distinct user_id) user_num
FROM user_info
GROUP BY get_json_object(extra1, '$.phonebrand');

##第二种情况【通过map的方式调用】

SELECT extra2['phonebrand'] as phone_brand,
count(distinct user_id) user_num
FROM user_info
GROUP BY extra2['phonebrand'];

练习2：不同性别、教育程度的分布情况(使用user_info)

##写法一：
select sex,
 get_json_object(extra1,'$.eduction'),
 count(distinct user_id)
from user_info
group by sex,
 get_json_object(extra1,'$.eduction')
写法二：
select sex,
extra2['education'] as nmsl,
count(distinct user_id) user_num
from user_info

map对应的Java的类型；
聚合统计函数

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

十、需求十：找出在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岁的用户，把他们的婚姻状况转义
成可理解的说明
第三步：聚合计算，针对年龄段、婚姻状况的聚合
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,'已婚','未婚');

十一、三个小练习

需求1-1：激活天数距今超过300天的男女分布情况
需求1-2：不同性别、教育程度的分布情况
需求1-3：2019年1月1日到2019年4月30日，每个时段的不同品类购买金额分布



需求1-1：激活天数距今超过300天的男女分布情况(使用user_info)
select sex,
 count(distinct user_id) --可不加distinct #注意是count()，而且是user_id这个字段；
from user_info
where datediff(current_date(),to_date(firstactivetime))>300 #记住这个函数datediff()，current_date()和to_date()函数
group by sex;

注：一个select语句也可以写成一个完整的函数，以便后续使用；

需求2-2：不同性别、教育程度的分布情况(使用user_info)
##写法一：
select sex,
       get_json_object(extra1, '$.education'),
       count(distinct user_id)  --可不加distinct(该表的user_id为唯一值)
from user_info
group by sex,
         get_json_object(extra1, '$.education');

##写法二：
select sex,
       extra2['education'],
       count(distinct user_id)  --可不加distinct(该表的user_id为唯一值)
from user_info
group by sex,
         extra2['education'];


需求2-3：2019年1月1日到2019年4月30日，每个时段的不同品类购买金额分布(使用user_trade)
注：时段最好转换成24小时的，方便一些；
select substr(from_unixtime(pay_time,'yyyy-MM-dd hh'),12), --12小时制
       goods_category,
       sum(pay_amount)
from user_trade
where dt between '2019-01-01' and '2019-04-30'
group by substr(from_unixtime(pay_time,'yyyy-MM-dd hh'),12),
         goods_category;


select substr(from_unixtime(pay_time,'yyyy-MM-dd HH'),12), --24小时制
       goods_category,
       sum(pay_amount)
from user_trade
where dt between '2019-01-01' and '2019-04-30'
group by substr(from_unixtime(pay_time,'yyyy-MM-dd HH'),12),
         goods_category;

其他写法：
select substr(from_unixtime(pay_time,'yyyy-MM-dd HH'),12),
goods_category,
sum(pay_amount)
from user_trade
where dt between '2019-01-01' and '2019-04-30'
group by substr(from_unixtime(pay_time, 'yyyy-MM-dd HH'),12),
goods_category;

ITPub博客

第六章：Hive基本查询分析