2、蚂蚁森林低碳用户排名分析
问题:查询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_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
解法1:
1.找出单日减少碳排放100的用户信息
select user_id,data_dt,
sum(low_carbon) userday_sum_carbon
from user_low_carbon
where substring(data_dt,1,4)=“2017”
group by user_id,data_dt
having userday_sum_carbon>100;t1
user_id data_dt low_carbon
u_001 2017/1/2 270
u_001 2017/1/6 135
u_002 2017/1/2 220
u_002 2017/1/3 110
u_002 2017/1/4 150
u_002 2017/1/5 101
u_003 2017/1/2 160
u_003 2017/1/3 160
u_003 2017/1/5 120
u_003 2017/1/7 120
2.前后各两行减少的碳排放量
原型:
select user_id,data_dt,
lag(data_dt,2,‘9999-09-09’) over(partition by user_id order by data_dt ),
lag(data_dt,1,‘9999-09-09’) over(partition by user_id order by data_dt ),
lead(data_dt,1,‘9999-09-09’) over(partition by user_id order by data_dt ),
lead(data_dt,2,‘9999-09-09’) over(partition by user_id order by data_dt )
from t1;t2
select user_id,data_dt,
lag(data