本文主要结合以下一个实际电商企业案例中的脱敏数据进行基本的Hive查询分析，主要的hive表结构，如Excel中所示，可以自行前往我的百度网盘链接中下载。具体数据在我搭建的虚拟机里，如果有兴趣可以在文末留言邮箱。
hive表结构汇总.xlsx
一、准备工作（仔细观察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;
