蚂蚁森林练习之低碳用户排名分析
问题:查询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_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 |
--查看表中数据情况
select * from user_low_carbon;
select * from plant_carbon;
--开启本地模式
set hive.exec.mode.local.auto = true;
开启本地模式,这样可以加快数据查询的速度
--1.格式化时间格式,转换成hive认识的格式,例如:2017/1/1转换成2017-01-01
select
user_id,
date_format(regexp_replace(data_dt,'/','-'),'yyyy-MM-dd') date_dt,
low_carbon
from user_low_carbon;
--2.过滤出2017年的数据
select
t1.user_id,
t1.date_dt,
sum(t1.low_carbon) user_day_carbon
from
(
select
user_id,
date_format(regexp_replace(data_dt,'/','-'),'yyyy-MM-dd') date_dt,
low_carbon
from user_low_carbon
) t1
where year(t1.date_dt) = 2017
group by t1.user_id,t1.date_dt
having user_day_carbon > 100;
--3.按照用户id分区,date_dt正序排序,编号
select
t2.user_id,
t2.date_dt,
t2.user_day_carbon,
rank() over (partition by t2.user_id order by t2.date_dt) rk
from
(
select
t1.user_id,
t1.date_dt,
sum(t1.low_carbon) user_day_carbon
from
(