Hive SQL 面试题及解析过程_以下表记录了用户每天的蚂蚁森林低碳

问题

问题：查询user_low_carbon表中每日流水记录，条件为：
用户在2017年，连续三天（或以上）的天数里，
每天减少碳排放（low_carbon）都超过100g的用户低碳流水。
需要查询返回满足以上条件的user_low_carbon表中的记录流水。
例如用户u_002符合条件的记录如下，因为2017/1/2~2017/1/5连续四天的碳排放量之和都大于等于100g：
seq（key） user_id data_dt low_carbon
xxxxx10 u_002 2017/1/2 150
xxxxx11 u_002 2017/1/2 70
xxxxx12 u_002 2017/1/3 30
xxxxx13 u_002 2017/1/3 80
xxxxx14 u_002 2017/1/4 150
xxxxx14 u_002 2017/1/5 10

表名

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

table_name：user_low_carbon

user_id ， data_dt ， low_carbon
用户 ， 日期 ， 减少碳排放（g）

数据样例

提供的数据说明：

user_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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

创建表

create table user_low_carbon
(user_id String,data_dt String,low_carbon int)
row format delimited fields terminated by '\t';
1
2
3

加载数据

load data local inpath “/opt/module/hive/datas/user_low_carbon.txt” into table user_low_carbon;

解析

a.格式化日期

SELECT user_id,DATE_FORMAT(regexp_replace(data_dt,'/','-'),'yyyy-MM-dd') AS data_dt,low_carbon
FROM user_low_carbon
1
2

==>t1

b.将每个人每天总能量超过100g的数据查出来。

SELECT t1.user_id,t1.data_dt,SUM(t1.low_carbon) day_sum_low_carbon
FROM 
(SELECT user_id,DATE_FORMAT(regexp_replace(data_dt,'/','-'),'yyyy-MM-dd') AS data_dt,low_carbon
FROM user_low_carbon)t1
WHERE YEAR(data_dt)='2017'
GROUP BY t1.user_id,t1.data_dt
HAVING day_sum_low_carbon>=100
1
2
3
4
5
6
7

===>t2

c.连续三天

– 1.对一条数据来说，如何计算连续三天的情况：

前天  昨天  今天
昨天  今天  明天
今天  明天  后天
1
2
3

– 2.真正满足连续：

今天-昨天=1  AND  今天-前天=2
OR
今天-昨天=1  AND  今天-明天=-1
OR
今天-明天=-1 AND 今天-后天=-2
1
2
3
4
5

d. 将前天, 昨天， 明天，后天的数据提取到当前行

 SELECT t2.user_id,t2.day_sum_low_carbon,t2.data_dt jt,
 lag(t2.data_dt,2,'1970-01-01') over(PARTITION BY t2.user_id ORDER BY t2.data_dt) qt,
  lag(t2.data_dt,1,'1970-01-01') over(PARTITION BY t2.user_id ORDER BY t2.data_dt) zt,
  lead(t2.data_dt,1,'1970-01-01') over(PARTITION BY t2.user_id ORDER BY t2.data_dt) mt,
 lead(t2.data_dt,2,'1970-01-01') over(PARTITION BY t2.user_id ORDER BY t2.data_dt) ht
 FROM 
 (SELECT t1.user_id,t1.data_dt,SUM(t1.low_carbon) day_sum_low_carbon
FROM 
(SELECT user_id,DATE_FORMAT(</
1
2
3
4
5
6
7
8