hive sql练习

蚂蚁森林植物申领统计

使用spark sql 完成以下作业

背景说明

下表记录了用户每天的蚂蚁森林低碳生活领取的记录流水。

table_name：user_low_carbon

字段名	字段描述
user_id	用户
data_dt	日期
low_carbon	减少碳排放（g）

蚂蚁森林植物换购表，用于记录申领环保植物所需要减少的碳排放量

table_name: plant_carbon

字段名	字段描述
plant_id	植物编号
plant_name	植物名
plant_carbon	换购植物所需要的碳

数据样例**

user_low_carbon：

user_id	date_dt	low_carbon
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

plant_carbon：

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

题目一

蚂蚁森林植物申领统计
问题：假设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          …

select user_id,su,su-lead(su,1) over ()
from (select user_id,`floor`((sum(low_carbon)-215)/19) as su
from user_low_carbon group by user_id order by su desc)t1;

题目二

蚂蚁森林低碳用户排名分析
问题：查询user_low_carbon表中每日流水记录，条件为：
用户在2017年，连续三天（或以上）的天数里，
每天减少碳排放（low_carbon）都超过100g的用户低碳流水。
需要查询返回满足以上条件的user_low_carbon表中的记录流水。
例如用户u_002符合条件的记录如下，因为2017/1/2~2017/1/5连续四天的碳排放量之和都大于等于100g：

 user_id data_dt  low_carbon
 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

select a.user_id,date_dt,low_carbon
from user_low_carbon a join (select user_id,min(da) as mintime,max(da) as maxtime,count(1)
from (select user_id,da,date_sub(da,rank() over (partition by user_id order by da)) as top
from (select user_id,replace(date_dt,"/","-") as da,sum(low_carbon)
from user_low_carbon group by user_id,date_dt having sum(low_carbon)>=100)t1)t2
group by user_id,top having count(1) >=3)b on
    (a.user_id=b.user_id and (replace(date_dt,"/","-") between mintime and maxtime));