蚂蚁金服试题hive/sql/hql

d

**

原理如图 SELECT username,time,SUM(carbon_g) c2,ROW_NUMBER()
OVER(PARTITION by username order by time ) rn FROM
test191128.user_low_carbon GROUP by username,time HAVING c2 >=100
//t1
------------------------- SELECT username,time,c2,date_sub(regexp_replace(time,’/’,’-’),rn) ds FROM
t1
--------------------------------------- SELECT username,time,c2,date_sub(regexp_replace(time,’/’,’-’),rn) ds FROM
(SELECT username,time,SUM(carbon_g) c2,ROW_NUMBER() OVER(PARTITION
by username order by time ) rn FROM test191128.user_low_carbon GROUP
by username,time HAVING c2 >=100)t1 //t2
----------------- SELECT username,time,COUNT(ds)OVER(PARTITION by username,ds ORDER by time rows BETWEEN UNBOUNDED PRECEDING and
UNBOUNDED FOLLOWING) cn FROM t2
----------------------------------- SELECT username,time,COUNT(ds)OVER(PARTITION by username,ds ORDER by time
rows BETWEEN UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) cn FROM
(SELECT username,time,c2,date_sub(regexp_replace(time,’/’,’-’),rn)
ds FROM (SELECT username,time,SUM(carbon_g) c2,ROW_NUMBER()
OVER(PARTITION by username order by time ) rn FROM
test191128.user_low_carbon GROUP by username,time HAVING c2 >=100)t1
)t2 //t4
------------------------------------- SELECT * FROM t4 WHERE cn >=3
------------------------ SELECT * FROM (SELECT username,time,COUNT(ds)OVER(PARTITION by username,ds ORDER by time
rows BETWEEN UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) cn FROM
(SELECT username,time,c2,date_sub(regexp_replace(time,’/’,’-’),rn)
ds FROM (SELECT username,time,SUM(carbon_g) c2,ROW_NUMBER()
OVER(PARTITION by username order by time ) rn FROM
test191128.user_low_carbon GROUP by username,time HAVING c2 >=100)t1
)t2 )t4 WHERE cn >=3 //t5
------------------------------ SELECT FROM user_low_carbon t6 JOIN t5 on t6.username=t5.username and t6.time=t5.time
--------------------------------- 最后的sql SELECT t6.* FROM test191128.user_low_carbon t6 JOIN (SELECT * FROM (SELECT
username,time,COUNT(ds)OVER(PARTITION by username,ds ORDER by time
rows BETWEEN UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) cn FROM
(SELECT username,time,c2,date_sub(regexp_replace(time,’/’,’-’),rn)
ds FROM (SELECT username,time,SUM(carbon_g) c2,ROW_NUMBER()
OVER(PARTITION by username order by time ) rn FROM
test191128.user_low_carbon GROUP by username,time HAVING c2 >=100)t1
)t2 )t4 WHERE cn >=3)t5 on t6.username=t5.username and
t6.time=t5.time 蚂蚁森林低碳用户排名分析 问题：查询user_low_carbon表中每日流水记录，条件为：
用户在2017年，连续三天（或以上）的天数里， 每天减少碳排放（low_carbon）都超过100g的用户低碳流水。
需要查询返回满足以上条件的user_low_carbon表中的记录流水。
例如用户u_002符合条件的记录如下，因为2017/1/2~2017/1/5连续四天的碳排放量之和都大于等于100g： 提供的数据说明：
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

**