HQL之种树题解

一、数据

user_low_carbon(user_id String,data_dt String,low_carbon int)
u_001 2017/1/1 10
plant_carbon(plant_id string,plant_name String,low_carbon int)

二、需求一

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

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

①求用户从2017年1月1日开始，到2017年10月1日一共收集了多少了碳

select  user_id,sum(low_carbon) sumCarbon
from user_low_carbon
where year(regexp_replace(data_dt,'/','-'))=2017 and month(regexp_replace(data_dt,'/','-'))  between 1 and 9
group by user_id  //t1

②求胡杨的单价

select low_carbon hycarbon from  plant_carbon where plant_id='p004'  //t2

③求沙柳的单价

select low_carbon slcarbon from  plant_carbon where plant_id='p002'  //t3

④求 (用户的碳总量-1颗胡杨的单价)/沙柳单价，之后向下取整，这是每个用户供领取多少课沙柳
按照沙柳的总量降序排序，取前11名的信息

select user_id,sumCarbon,floor((sumCarbon-hycarbon)/slcarbon) slcount
from 
t1 join t2 join t3
where sumCarbon>=hycarbon 
order by slcount desc
limit 11    //t4

⑤再从top11中取前10名的信息，再计算每一名比后一名多领了多少颗

select t4.*,slcount-lead(slcount,1,0) over(order by slcount desc) diffcount
from t4
limit 10

组合：

select t4.*,slcount-lead(slcount,1,0) over(order by slcount desc) diffcount
from
(select user_id,sumCarbon,floor((sumCarbon-hycarbon)/slcarbon) slcount
from 
(select  user_id,sum(low_carbon) sumCarbon
from user_low_carbon
where year(regexp_replace(data_dt,'/','-')