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

78 阅读 0 评论 52 点赞

我是靠谱客的博主光亮白昼，这篇文章主要介绍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

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
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.将前两行数据以及后两行数据的日期放置当前行

复制代码

1
2
3
4
5
6
7
8
9
10
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.计算当前日期跟后两行时间的差值

复制代码

1
2
3
4
5
6
7
8
9
10
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的用户

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
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);