15-天亮大数据系列教程之一道阿里经典数据分析题精解

一个同学请帮忙做的一道阿里的线上代码评测题,很经典有价值,欢迎共同学习交流。

  • 按照hive sql的语法完成下面任务:

输入表:
曝光数据表:
create table if not exists expo_detail_table
(
pvid string comment’唯一标识一次请求’,
user_id string comment’用户id’,
item_id string comment’商品id’
)lifecycle 29;
点击数据表:
create table if not exists click_detail_table
(
pvid string comment’唯一标识一次请求’,
user_id string comment’用户id’,
item_id string comment’商品id’
)lifecycle 29;

输出表有两个(排名按照从大到小,标号从1开始):
create table if not exists result1_table
(
user_id string,
item_id string,
expo_cnt bigint comment’曝光数量’,
click_cnt bigint comment’点击数量’
)lifecycle 29;

create table if not exists result2_table
(
item_id string,
uv_ctr double comment’uv曝光点击率’,
uv_ctr_rank bigint comment’uv曝光点击率排名’,
pv_ctr double comment’pv曝光点击率’,
pv_ctr_rank bigint comment’pv曝光点击率排名’
)lifecycle 29;

  • 答案:

1、数据准备
–测试插入表数据
insert into table expo_detail_table values(‘p001’,’user001’,’item001’);
insert into table expo_detail_table values(‘p002’,’user001’,’item001’);
insert into table expo_detail_table values(‘p003’,’user001’,’item002’);
insert into table expo_detail_table values(‘p004’,’user002’,’item002’);
insert into table expo_detail_table values(‘p005’,’user002’,’item003’);
insert into table expo_detail_table values(‘p006’,’user003’,’item004’);

–测试插入表数据
insert into table click_detail_table values(‘c001’,’user001’,’item001’);
insert into table click_detail_table values(‘c002’,’user001’,’item001’);
insert into table click_detail_table values(‘c003’,’user002’,’item002’);

2、脚本代码
第一题:
insert overwrite table
result1_table
select
coalesce(expo.user_id, click.user_id) as user_id,
coalesce(expo.item_id, click.item_id) as item_id,
coalesce(expo.cnt, 0) as expo_cnt,
coalesce(click.cnt, 0) as click_cnt
from
(
select
user_id,
item_id,
count(1) as cnt
from
expo_detail_table
group by
user_id,
item_id
) expo full outer
join (
select
user_id,
item_id,
count(1) as cnt
from
click_detail_table
group by
user_id,
item_id
) click on expo.user_id = click.user_id
and expo.item_id = click.item_id

第二题:
insert overwrite table result2_table
select
item_id,
uv_ctr,
dense_rank() OVER(ORDER BY uv_ctr DESC) uv_ctr_rank,
pv_ctr,
dense_rank() OVER(ORDER BY pv_ctr DESC) pv_ctr_rank
from
(
select item_id,
sum(if(click_cnt>0,1,0))/sum(if(expo_cnt>0,1,0)) as uv_ctr,
sum(click_cnt)/sum(expo_cnt) as pv_ctr
from result1_table group by item_id
) middle

3、执行结果
第一题结果:(select * from result1_table)
user001 item001 2 2
user001 item002 1 0
user002 item002 1 1
user002 item003 1 0
user003 item004 1 0

第二题结果:(select * from result2_table)
item001 1.0 1 1.0 1
item002 0.5 2 0.5 2
item003 0.0 3 0.0 3
item004 0.0 3 0.0 3



天亮教育是一家从事大数据云计算、教育培训、产品开发、咨询服务、人才优选为一体的综合型互联网科技公司。
公司由一批BAT等一线互联网IT精英人士创建,
以”快乐工作,认真生活,打造高端职业技能教育的一面旗帜”为愿景,胸怀”让天下没有难找的工作”使命,
坚持”客户第一、诚信、激情、拥抱变化”的价值观,
全心全意为学员赋能提效,践行技术改变命运的初心。

更多学习讨论, 请加入
官方-天亮大数据交流-366784928
群二维码:
这里写图片描述
天亮教育公开课-从小白到大佬修成记-全系列视频地址:http://bbs.myhope365.com/forum.php?mod=viewthread&tid=1422&extra=page%3D1

欢迎关注天亮教育公众号,大数据技术资料与课程、招生就业动态、教育资讯动态、创业历程分享一站式分享,官方微信公众号二维码:
这里写图片描述

爬虫、nlp技术qq群320349384
天亮教育官方群318971238,
hadoop & spark & hive技术群297585251,
官网:myhope365.com
官方天亮论坛:http://bbs.myhope365.com/

阅读更多
上一篇14-天亮大数据系列教程之ssh内网穿透打通各子网的互连
下一篇16-天亮大数据系列教程之ambari集群运维之agent丢失心跳
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭