hive-蚂蚁金服笔试题（2）

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

select
     user_id,
     date_format(regexp_replace(data_dt,'/','-'),'yyyy-MM-dd') data_dt
from
    user_low_carbon
where 
     substring(data_dt,1,4)='2017'
group by
    user_id,data_dt
having 
     sum(low_carbon)>=100;

2.将前两行数据以及后两行数据的日期放置当前行

select
     user_id,
     data_dt,
     lag(data_dt,2,'1970-01-01') over(partition by user_id order by data_dt) lag2,
     lag(data_dt,1,'1970-01-01') over(partition by user_id order by data_dt) lag1,
     lead(data_dt,1,'1970-01-01') over(partition by user_id order by data_dt) lead1,
     lead(data_dt,2,'1970-01-01') over(partition by user_id order by data_dt) lead2
from
     t1;t2

3.计算当前日期跟后两行时间的差值

select
    user_id,
    data_dt,
    datediff(data_dt,la2) la2_diff,
    datediff(data_dt,la1) la1_diff,
    datediff(data_dt,lead1) lead1_diff,
    datediff(data_dt,lead2) lead2_diff
from
    t2;t3

4.过滤出连续三天超过100g的用户

select
    user_id,
    data_dt
from
   t3
where 
  (lag2_diff=2 and lag1_diff=1) 
  or 
  (lag1_diff =1 and lead1_diff =-1) 
  or 
  (lead1_diff =-1 and lead2_diff =-2);

5.关联原表

最后

以上就是光亮白昼为你收集整理的hive-蚂蚁金服笔试题（2）的全部内容，希望文章能够帮你解决hive-蚂蚁金服笔试题（2）所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。