种树问题

需求一
1.蚂蚁森林植物申领统计
问题：假设2017年1月1日开始记录低碳数据（user_low_carbon），
假设2017年10月1日之前满足申领条件的用户都申领了一颗p004-胡杨，
剩余的能量全部用来领取“p002-沙柳”。
统计在10月1日累计申领“p002-沙柳”排名前10的用户信息；以及他比后一名多领了几颗沙柳。

 

①统计用户在 2017-1-1 至 2017-10-1期间一共收集了多少碳量

select  user_id,sum(low_carbon) sumCarbon 
from  user_low_carbon 
where regexp_replace(data_dt,'/','-')  between '2017-1-1' and  '2017-10-1'  //替换后，在整个范围内
group by  user_id    //t1

②统计胡杨和沙柳单价

胡杨单价：
select low_carbon huyangCarbon from  plant_carbon where  plant_id='p004';  //t2

沙柳单价： 
select low_carbon shaliuCarbon from  plant_carbon where  plant_id='p002';  //t3

③计算每个用户领取了多少棵沙柳

select  user_id, floor((sumCarbon-huyangCarbon)/shaliuCarbon) shaliuCount  //向下取整
from  t1 join t2 join t3
order by shaliuCount desc
limit 11       //t4    取前11个

④统计前10用户，比后一名多多少

select   user_id,shaliuCount,rank() over(order by shaliuCount desc),  //排名函数
shaliuCount  -  lead(shaliuCount,1,0) over(order by shaliuCount desc)  //lead取当前之后，比下一个多多少
from  t4

题目二
问题：查询user_low_carbon表中每日流水记录，条件为：
用户在2017年，连续三天（或以上）的天数里，
每天减少碳排放（low_carbon）都超过100g的用户低碳流水。
需要查询返回满足以上条件的user_low_carbon表中的记录流水。

plant_carbon.plant_id  | plant_carbon.plant_name  | plant_carbon.low_carbon 
 user_low_carbon.user_id  | user_low_carbon.data_dt  | user_low_carbon.low_carbon 

①过滤2017年的数据，统计每个用户每天共收集了多少碳

select  user_id,regexp_replace(data_dt,'/','-') dt,sum(low_carbon) carbonPerDay
from  user_low_carbon
where year(regexp_replace(data_dt,'/','-'))=2017
group by  user_id,data_dt 
having  carbonPerDay >= 100  //t1

②过滤复合连续3天的数据
        如果判断当前记录复合连续三天的条件？
        a)如果当前日期位于连续三天中的第一天，使用当前日期减去 当前日期后一天的日期，差值一定为-1
                                                使用当前日期减去 当前日期后二天的日期，差值一定为-2
                                                
        b)如果当前日期位于连续三天中的第二天，使用当前日期减去 当前日期前一天的日期，差值一定为1
                                                使用当前日期减去 当前日期后一天的日期，差值一定为-1
                                                
        c)如果当前日期位于连续三天中的第三天，使用当前日期减去 当前日期前一天的日期，差值一定为1
                                                使用当前日期减去 当前日期前二天的日期，差值一定为2
                                                
        满足a,b,c其中之一，当前日期就复合要求

求当前日期和当前之前，前1，2天和后1，2天日期的差值

select  user_id,dt,carbonPerDay,
datediff(dt,lag(dt,1,'1970-1-1') over(partition by user_id order by dt)) pre1diff,
datediff(dt,lag(dt,2,'1970-1-1') over(partition by user_id order by dt)) pre2diff,
datediff(dt,lead(dt,1,'1970-1-1') over(partition by user_id order by dt)) after1diff,
datediff(dt,lead(dt,2,'1970-1-1') over(partition by user_id order by dt)) after2diff
from  t1    //t2