有关蚂蚁森林植物申领统计题目(Hive 实例)

蚂蚁森林植物申领统计

创建两个表
user_low_carbon：记录了用户每天的蚂蚁森林低碳生活领取的记录流水
plant_carbon：蚂蚁森林植物换购表，用于记录申领环保植物所需要减少的碳排放量

表结构

表一
table_name：user_low_carbon
user_id data_dt low_carbon
用户 日期 领取碳排放（g）
表二
table_name: plant_carbon
plant_id plant_name low_carbon
植物编号 植物名 换购植物所需要的碳

表一

create table if not exists user_low_carbon(user_id string,date_dt string,low_carbon int)
row format delimited fields terminated by ' '

表二

create table if not exists user_low_carbon(user_id string,date_dt string,low_carbon int)
row format delimited fields terminated by ' '

导入数据
//user_low_carbon.txt

u_001 2017/1/1 10
u_001 2017/1/2 150
u_001 2017/1/2 110
u_001 2017/1/2 10
u_001 2017/1/4 50
u_001 2017/1/4 10
u_001 2017/1/6 45
u_001 2017/1/6 90
u_002 2017/1/1 10
u_002 2017/1/2 150
u_002 2017/1/2 70
u_002 2017/1/3 30
u_002 2017/1/3 80
u_002 2017/1/4 150
u_002 2017/1/5 101
u_002 2017/1/6 68
......

load data local inpath '/home/dt/user_low_carbon.txt' into table user_low_carbon

//plant_carbon.txt

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

LOAD DATA LOCAL INPATH '/home/dt/plant_carbon.txt' INTO TABLE plant_carbon

题目一

问题：假设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          …

sql语句

from(
select t3.user_id user_id,floor(t3.residue/t4.low_carbon) as shaliunum
from (
select t1.user_id,t1.sumres-t2.low_carbon as residue
from (SELECT a.user_id,sum(a.low_carbon) as sumres
-- OVER(PARTITION BY a.user_id) 
from user_low_carbon a
group by a.user_id) as t1,(select low_carbon
from plant_ca