hive大数据——某电商平台数据分析(一)

hive数据库表结构

实现如下需求(由浅入深)

需求1：某次经营活动中，商家发起了"异性拼团购"，试着针对某个地区的用户进行推广，找出匹配用户。
分析：简单理解本需求，就是将某一个地区性别为男/女的所有用户找出。

select user_name
from 
user_info
where city='beijing' and sex='male';

需求2：某天，发现食物类的商品卖的很好，你能找出几个资深吃货吗？
分析：本题的需求就是将某段时间内购买食品这个品类的用户根据购买金额/购买量进行排序，这里使用的是购买金额。

select user_name,
 sum(pay_amount) amount
from
 user_trade 
where year(dt)=2019 and goods_category='food'
group by user_name 
order by amount desc limit 10;

需求3：试着对本公司2019年第一季度商品的热度与价值度进行分析。
分析：商品热度表示购买量，价值度表示购买金额。

select goods_category,
 sum(piece),
 sum(pay_amount)
from 
 user_trade
where dt between '2019-01-01' and '2019-03-31'
group by goods_category;

需求4：2019年4月，支付金额超过5万元的用户。给VIP用户赠送优惠劵。
分析：本题主要是考察group by …having…的用法

select user_name, 
 sum(pay_amount) 
from 
 user_trade
where dt between '2019-04-01' and '2019-04-30'
group by user_name
having sum(pay_amount)>50000;

需求5：对用户的年龄段进行分析，观察分布情况。
分析：先将年龄进行分段，再做聚合

select case when age<20 then 'less then 20'
when age>=20 and age<30 then '20-30'
when age>=30 and age<40 then '30-40'
else 'more then 40' end as age_type,
count(user_id)
from user_info
group by case when age<20 then 'less then 20'
when age>=20 and age<30 then '20-30'
when age>=30 and age<40 then '30-40'
else 'more then 40' end;

需求6：我们想要观察用户等级随性别的分布情况。
分析：将5级以上用户定义为高等级，5级以下用户定义为低等级。

select sex, if(level>5,'high','low'), count(user_id)
from user_info
group by if(level>5,'high','low'), sex;

需求7：分析每个月的拉新情况，可以倒推回运营效果。
分析：每个月的拉新情况即每个月的新注册人数。

select substr(firstactivetime,1,7),
 count(user_id)
from
 user_info
group by substr(firstactivetime,1,7);

需求8：找出不同手机品牌的用户分布情况。
分析：使用不同的手机品牌的不同性别的用户个数

select extra2['phonebrand'],
 sex,
 count(user_id)
from
 user_info
group by extra2['phonebrand'],sex;

需求9：激活天数距今超过300天的男女分布情况
分析：我们需要使用current_date（）函数获取今天的日期。

select sex,
 count(user_id)
from 
 user_info
where datediff(current_date(),to_date(firstactivetime))>300
group by sex;

需求10：2019年1月1日到2019年4月30日，每个时段的不同品类购买金额分布
分析：根据小时做聚合，统计不同时间段中不同商品类型的购买金额分布情况。

select from_unixtime(pay_time,'HH'),
 goods_category,
 sum(pay_amount)
from
 user_trade
where dt between '2019-01-01' and '2019-04-30'
group by from_unixtime(pay_time,'HH'),goods_category;

Hive大数据——某电商平台数据分析（二）
Hive大数据——电商平台数据分析（三）_特征分析与偏移分析