hive-蚂蚁金服笔试题（1）

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

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

user_low_carbon表

u_004	2017/1/4	30
u_004	2017/1/5	60
u_004	2017/1/6	120
u_004	2017/1/7	10
u_004	2017/1/7	120
u_005	2017/1/1	80
u_005	2017/1/2	50
u_005	2017/1/2	80
u_005	2017/1/3	180
u_005	2017/1/4	180
u_005	2017/1/4	10
u_005	2017/1/5	80
u_005	2017/1/6	280
u_005	2017/1/7	80
u_005	2017/1/7	80
u_006	2017/1/1	40
u_006	2017/1/2	40
u_006	2017/1/2	140
u_006	2017/1/3	210
u_006	2017/1/3	10
u_006	2017/1/4	40
u_006	2017/1/5	40
u_006	2017/1/6	20
u_006	2017/1/7	50
u_006	2017/1/7	240
u_007	2017/1/1	130
u_007	2017/1/2	30
u_007	2017/1/2	330
u_007	2017/1/3	30
u_007	2017/1/4	530
u_007	2017/1/5	30
u_007	2017/1/6	230
u_007	2017/1/7	130
u_007	2017/1/7	30
u_008	2017/1/1	160
u_008	2017/1/2	60
u_008	2017/1/2	60
u_008	2017/1/3	60
u_008	2017/1/4	260
u_008	2017/1/5	360
u_008	2017/1/6	160
u_008	2017/1/7	60
u_008	2017/1/7	60
u_009	2017/1/1	70
u_009	2017/1/2	70
u_009	2017/1/2	70
u_009	2017/1/3	170
u_009	2017/1/4	270
u_009	2017/1/5	70
u_009	2017/1/6	70
u_009	2017/1/7	70
u_009	2017/1/7	70
u_010	2017/1/1	90
u_010	2017/1/2	90
u_010	2017/1/2	90
u_010	2017/1/3	90
u_010	2017/1/4	90
u_010	2017/1/4	80
u_010	2017/1/5	90
u_010	2017/1/5	90
u_010	2017/1/6	190
u_010	2017/1/7	90
u_010	2017/1/7	90
u_011	2017/1/1	110
u_011	2017/1/2	100
u_011	2017/1/2	100
u_011	2017/1/3	120
u_011	2017/1/4	100
u_011	2017/1/5	100
u_011	2017/1/6	100
u_011	2017/1/7	130
u_011	2017/1/7	100
u_012	2017/1/1	10
u_012	2017/1/2	120
u_012	2017/1/2	10
u_012	2017/1/3	10
u_012	2017/1/4	50
u_012	2017/1/5	10
u_012	2017/1/6	20
u_012	2017/1/7	10
u_012	2017/1/7	10
u_013	2017/1/1	50
u_013	2017/1/2	150
u_013	2017/1/2	50
u_013	2017/1/3	150
u_013	2017/1/4	550
u_013	2017/1/5	350
u_013	2017/1/6	50
u_013	2017/1/7	20
u_013	2017/1/7	60
u_014	2017/1/1	220
u_014	2017/1/2	120
u_014	2017/1/2	20
u_014	2017/1/3	20
u_014	2017/1/4	20
u_014	2017/1/5	250
u_014	2017/1/6	120
u_014	2017/1/7	270
u_014	2017/1/7	20
u_015	2017/1/1	10
u_015	2017/1/2	20
u_015	2017/1/2	10
u_015	2017/1/3	10
u_015	2017/1/4	20
u_015	2017/1/5	70
u_015	2017/1/6	10
u_015	2017/1/7	80
u_015	2017/1/7	60

plant_carbon表

p001	梭梭树	17
p002	沙柳	19
p003	樟子树	146
p004	胡杨	215

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 …
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';

2.加载数据

load data local inpath "/export/data/low_carbon.txt" into table user_low_carbon;
load data local inpath "/export/data/plant_carbon.txt" into table plant_carbon;

3.设置本地模式
set hive.exec.mode.local.auto=true;
1.同意每个用户截止到2017/10/1日期总低碳

select
    user_id,
    sum(low_carbon) sum_low_carbon
 from
    user_low_carbon
 where 
   date_format(regexp_replace(data_dt,'/','-'),'yyyy-MM')<'2017-10'
 group by user_id;t1

2.取出胡杨的能量

select
     low_carbon
 from 
     plant_carbon
 where plant_id = 'p004';t2

3.取出沙柳的能量

select
     low_carbon
 from 
     plant_carbon
 where plant_id = 'p002';t3

4.计算每个人申领沙柳的棵树

select
     user_id,
     floor((sum_low_carbon-t2.low_carbon)/t3.low_carbon) plant_count
from
     t1,t2,t3;

5.按照申领沙柳棵树进行排序

select
     user_id,
     plant_count
from
      t4
order by
     plant_count desc
limit 11;t5

6.将下一行数据中的plant_count放置当前行

select
    user_id,
    plant_count,
    lead(plant_count,1,'2020-05-29') over(order by plant_count desc) lead_plant_count
from
    t4
limit 10;t5

7.求差值

select 
      user_id,
      plant_count,
      (plant_count-lead_plant_count) plant_count_diff
 from
     t5;

最终结果

select 
      user_id,
      plant_count,
      (plant_count-lead_plant_count) plant_count_diff
 from
     (select
    user_id,
    plant_count,
    lead(plant_count,1,'2020-05-29') over(order by plant_count desc) lead_plant_count
from
    (select
     user_id,
     floor((sum_low_carbon-t2.low_carbon)/t3.low_carbon) plant_count
from
     (select
    user_id,
    sum(low_carbon) sum_low_carbon
 from
    user_low_carbon
 where 
   date_format(regexp_replace(data_dt,'/','-'),'yyyy-MM')<'2017-10'
 group by user_id)t1,
     (select
     low_carbon
 from 
     plant_carbon
 where plant_id = 'p004')t2,
     (select
     low_carbon
 from 
     plant_carbon
 where plant_id = 'p002')t3)t4
limit 10)t5;