Hive练习之蚂蚁金服面试题

Hive练习题之蚂蚁森林

一、题目概述

1、表格信息

以下表记录了用户每天的蚂蚁森林低碳生活领取的记录流水。
table_name：user_low_carbon
user_id    data_dt     low_carbon
用户        日期         减少碳排放（g）

蚂蚁森林植物换购表，用于记录申领环保植物所需要减少的碳排放量
table_name:  plant_carbon
plant_id     plant_name     low_carbon
植物编号	植物名	    换购植物所需要的碳

2、表格数据

具体表格数据，详见本文末尾

3、题目

（1）蚂蚁森林植物申领统计

问题：假设2017年1月1日开始记录低碳数据（user_low_carbon），假设2017年10月1日之前满足申领条件的用户都申领了一颗p004-胡杨，
剩余的能量全部用来领取“p002-沙柳” 。
统计在10月1日累计申领“p002-沙柳” 排名前10的用户信息；以及他比后一名多领了几颗沙柳。
得到的统计结果如下表样式：
user_id  plant_count less_count(比后一名多领了几颗沙柳)
u_101    1000         100
u_088    900          400
u_103    500          …

（2）蚂蚁森林低碳用户排名分析

查询user_low_carbon表中每日流水记录，条件为：
用户在2017年，连续三天（或以上）的天数里，
每天减少碳排放（low_carbon）都超过100g的用户低碳流水。
需要查询返回满足以上条件的user_low_carbon表中的记录流水。
例如用户u_002符合条件的记录如下，因为2017/1/2~2017/1/5连续四天的碳排放量之和都大于等于100g：
seq（key） user_id data_dt  low_carbon
xxxxx10    u_002  2017/1/2  150
xxxxx11    u_002  2017/1/2  70
xxxxx12    u_002  2017/1/3  30
xxxxx13    u_002  2017/1/3  80
xxxxx14    u_002  2017/1/4  150
xxxxx14    u_002  2017/1/5  101

二、练习详解

1、蚂蚁森林植物申领统计

--首先创建表格，并导入数据。
create table user_low_carbon(user_id String,data_dt String,low_carbon int) row format delimited fields terminated by '\t';
create table plant_carbon(plant_id string,plant_name String,low_carbon int) row format delimited fields terminated by '\t';

load data local inpath "/opt/module/hive-3.1.2/datas/user_low_carbon.txt" into table user_low_carbon;
load data local inpath "/opt/module/hive-3.1.2/datas/plant_carbon.txt" into table plant_carbon;

--第一题、蚂蚁森林植物申领统计
	--第1步：筛选2017年10月1日前的用户，并修改日期格式
select
user_id ,
regexp_replace(data_dt,'/','-'),
low_carbon 
from user_low_carbon 
where regexp_replace(data_dt,'/','-')<'2017-10-01';t1
	--第2步：求出每个人的蚂蚁能量和
SELECT 
t1.user_id,
sum(t1.low_carbon) s_l_c
from (select
user_id ,
regexp_replace(data_dt,'/','-'),
low_carbon 
from user_low_carbon 
where regexp_replace(data_dt,'/','-')<'2017-10-01')t1
group by t1.user_id;t2
	--第3步：将每个人去掉一颗胡杨的能量，注意思考是否先过滤掉连胡杨都买不起的人
SELECT 
t2.user_id,
t2.s_l_c- (SELECT low_carbon from plant_carbon where plant_name = '胡杨') slc_hy
from (SELECT 
t1.user_id,
sum(t1.low_carbon) s_l_c
from (select
user_id ,
regexp_replace(data_dt,'/','-'),
low_carbon 
from user_low_carbon 
where regexp_replace(data_dt,'/','-')<'2017-10-01')t1
group by t1.user_id)t2;t3

	--第4步：将每个人买完一颗胡杨后，剩下的能量，计算能买多少颗沙柳，即剩余能量对沙柳单价求商。
	 -------并对每个人买完一颗胡杨，剩下能量买沙柳的棵树，进行倒序排序求前10名
SELECT 
t3.user_id,
floor(t3.slc_hy/(SELECT low_carbon from plant_carbon where plant_name = '沙柳')) lc_sl
from (SELECT 
t2.user_id,
t2.s_l_c- (SELECT low_carbon from plant_carbon where plant_name = '胡杨') slc_hy
from (SELECT 
t1.user_id,
sum(t1.low_carbon) s_l_c
from (select
user_id ,
regexp_replace(data_dt,'/','-'),
low_carbon 
from user_low_carbon 
where regexp_replace(data_dt,'/','-')<'2017-10-01')t1
group by t1.user_id)t2)t3
order by slc_hy DESC 
limit 11;t4

	--第5步：然后要获得这前10名的用户信息，以及他比后一名多领了几颗沙柳（这一步可以先完成）。
SELECT 
t4.user_id,
t4.lc_sl,
(t4.lc_sl-lead(t4.lc_sl,1,0)over(order by t4.lc_sl desc)) lc_sl_next
from (SELECT 
t3.user_id,
floor(t3.slc_hy/(SELECT low_carbon from plant_carbon where plant_name = '沙柳')) lc_sl
from (SELECT 
t2.user_id,
t2.s_l_c- (SELECT low_carbon from plant_carbon where plant_name = '胡杨') slc_hy
from (SELECT 
t1.user_id,
sum(t1.low_carbon) s_l_c
from (select
user_id ,
regexp_replace(data_dt,'/','-'),
low_carbon 
from user_low_carbon 
where regexp_replace(data_dt,'/','-')<'2017-10-01')t1
group by t1.user_id)t2)t3
order by slc_hy DESC 
limit 11)t4;t5

2、蚂蚁森林低碳用户排名分析

解法一：
--第1步：筛选出2017年,因为每天可能有多条能量，所以肯定会对每天进行聚合，流水肯定要等后面来处理。
SELECT 
user_id ,
regexp_replace(data_dt,'/','-') dd,
sum(low_carbon) slc
from user_low_carbon 
where year(regexp_replace(data_dt,'/','-'))='2017'
group by user_id,data_dt ;t1

	--第2步：筛选出每天能量超过100g的用户
SELECT 
t1.user_id,
t1.dd,
t1.slc
from (SELECT 
user_id ,
regexp_replace(data_dt,'/','-') dd,
sum(low_carbon) slc
from user_low_carbon 
where year(regexp_replace(data_dt,'/','-'))='2017'
group by user_id,data_dt )t1
where t1.slc>=