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

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

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


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


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


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


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;


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


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


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


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


select from_unixtime(pay_time,'HH'),
goods_category,
sum(pay_amount)
from