Hive案例-蚂蚁森林
一：蚂蚁森林
/*
背景说明：
以下表记录了用户每天的蚂蚁森林低碳生活领取的记录流水。
table_name：user_low_carbon
user_id data_dt low_carbon
用户 日期 减少碳排放（g）
*/
drop table user_low_carbon;
create table user_low_carbon(user_id string,data_dt string,low_carbon int)
row format delimited fields terminated by '\t';
load data local inpath '/opt/module/datas/user_low_carbon.txt' into table user_low_carbon;
select * from user_low_carbon;
/*
蚂蚁森林植物换购表，用于记录申领环保植物所需要减少的碳排放量
table_name: plant_carbon
plant_id plant_name low_carbon
植物编号 植物名 换购植物所需要的碳
*/
create table plant_carbon(plant_id string,plant_name string,low_carbon int)
row format delimited fields terminated by '\t';
load data local inpath '/opt/module/datas/plant_carbon.txt' into table plant_carbon;
select * from plant_carbon;
/*
----题目
1.蚂蚁森林植物申领统计
问题：假设2017年1月1日开始记录低碳数据（user_low_carbon），假设2017年10月1日之前满足申领条件的用户都申领了一颗p004-胡杨，
剩余的能量全部用来领取“p002-沙柳” 。
统计在10月1日累计申领“p002-沙柳” 排名前10的用户信息；以及他比后一名多领了几颗沙柳。
得到的统计结果如下表样式：
user_id plant_count less_count(比后一名多领了几颗沙柳)
u_101 1000 100
u_088 900 400
u_103 500 …
*/
题目分析：
-
观察结果数据样式，发现，原表中日期数据与其不一致，所以再进行对日期的筛选之时需要进行格式化处理，首先联想到的函数数dateformat，通过show functions like ‘date’，找到date-format函数，使用desc function extended date_format;进行查看。
- SELECT date_format(‘2015-04-08’, ‘y’); ----- ‘2015’ 能将其分割或者按照yyyy-MM-dd的格式进行日期解析，但原表中的日期类型yyyy/MM/dd，HQL语句解析不了。
- 所以，继续分析，找到select regexp_replace(‘2017/1/2’,’/’,’-’); regexp_replace函数能将字符串‘/’替换为‘-’。
- 接着进行日期的筛选，发现有datediff（date_dt,‘2017-10-01’）-----日期进行相减，结果<0则表示当前日期再10-01之前。
- 筛选出来所需要的时间日期后，进行分组排序，对用户的user_id进行分组，因为需要统计出每个用户到10-01日期为止的总低碳数，对总低碳数进行排序，因为结果要求排出前十名的用户信息，所以我们这里直接再筛选数据的时候进行分组排序筛选。
- 结果要求比后一名多，所以选取11行
select user_id ,sum(low_carbon) carbonSum from user_low_carbon where datediff(regexp_replace(data_dt,'/','-'),'2017-10-01')<0 group by user_id order by carbonSum desc limit 11; -- t1 -
查询p002-沙柳的低碳值
select low_carbon from plant_carbon where plant_id='p004';--t2 -
查询p004-胡杨的低碳值
select low_carbon from plant_carbon where plant_id='p002';--t3 -
三表笛卡尔乘积，得到一个总表，然后进行计算使用总的低碳数—胡杨的低碳数/沙柳的低碳数，得到能够种植沙柳的数量，（注意，使用floor进行向下取整）。
select user_id, floor((carbonSum-t2.low_carbon)/t3.low_carbon) treeCount from (select user_id ,sum(low_carbon) carbonSum from user_low_carbon where datediff(regexp_replace(data_dt,'/','-'),'2017-10-01')<0 group by user_id order by carbonSum desc limit 11) t1, (select low_carbon from plant_carbon where plant_id='p004') t2, (select low_carbon from plant_carbon where plant_id='p002') t3;--t45.计算比后一名用户多几颗沙柳树
select user_id,treeCount,treeCount-(lead(treeCount,1) over (order by treeCount desc)) moreTreeCount from t4 limit 10; --t5
6. 整合上述查询语句
select user_id,treeCount,treeCount-(lead(treeCount,1) over (order by treeCount desc)) moreTreeCount
from
( select user_id, floor((carbonSum-t2.low_carbon)/t3.low_carbon) treeCount
from
(select user_id ,sum(low_carbon) carbonSum from user_low_carbon where datediff(regexp_replace(data_dt,'/','-'),'2017-10-01')<0
group by user_id order by carbonSum desc limit 11) t1,
(select low_carbon from plant_carbon where plant_id='p004') t2,
(select low_carbon from plant_carbon where plant_id='p002') t3)t4 limit 10;
2、蚂蚁森林低碳用户排名分析
问题：查询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 101
备注：统计方法不限于sql、procedure、python,java等
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
—题目分析
-
分析，连续三天，如何判断连续三天？最能想到的方法是，两两进行对日期的比较，这样三个时间日期就会产生六种比较情况，如果出现连续100天，则此方式不适用，故需要用到小技巧。
-
技巧：判断连续三天，新增一个字段，为其添加一个连续的数字，使用日期与数字相减，会得到一个固定的日期，如果出现不同日期，则表示不连续反之，相减得到的结果恒定，表示日期也连续
dt value1 value2
2017/1/2 1 2017/1/1
2017/1/3 2 2017/1/1
2017/1/4 3 2017/1/1
2017/1/6 4 2017/1/2
2017/1/7 5 2017/1/2
2017/1/8 6 2017/1/2 -
筛选出每天减少排放的低碳量超过100g的用户,对用户id和日期进行分组，筛选出该用户下每个月的低碳数。
select user_id,data_dt,sum(low_carbon) sumcarbon from user_low_carbon group by user_id,data_dt having sumcarbon>100; --t1 -
对筛选出来的用户进行根据每个人进行分区，然后按照日期排序，显示出每个用户在每个月的时间中当日的低碳量。
-- select user_id,data_dt,sumcarbon,rank() over (partition by user_id order by data_dt ) rn from t1; --t2 select user_id,data_dt,sumcarbon,rank() over (partition by user_id order by data_dt ) rn from (select user_id,data_dt,sum(low_carbon) sumcarbon from user_low_carbon group by user_id,data_dt having sumcarbon>100)t1; -- t2 -
日期计算，原数据中的日期需要转换格式才能进行与数字的相减，检索date函数，发现date_sub函数：SELECT date_sub(‘2009-07-30’, 1) FROM src LIMIT 1; ---- ‘2009-07-29’
此函数能够进行对日期的减法计算。依旧使用regexp_replace函数对日期格式进行转换。
-- select user_id,data_dt,date_sub(regexp_replace(data_dt,'/','-'),rn) datesub from t2; select user_id,data_dt,date_sub(regexp_replace(data_dt,'/','-'),rn) datesub from ( select user_id,data_dt,sumcarbon,rank() over (partition by user_id order by data_dt ) rn from (select user_id,data_dt,sum(low_carbon) sumcarbon from user_low_carbon group by user_id,data_dt having sumcarbon>100)t1 ) t2; -- t3 -
进行分区统计
-- select user_id,data_dt,count(datesub) over (partition by user_id,datesub) from t3 ; select user_id,data_dt,count(datesub) over (partition by user_id,datesub) dayCount from (select user_id,data_dt,date_sub(regexp_replace(data_dt,'/','-'),rn) datesub from ( select user_id,data_dt,sumcarbon,rank() over (partition by user_id order by data_dt ) rn from (select user_id,data_dt,sum(low_carbon) sumcarbon from user_low_carbon group by user_id,data_dt having sumcarbon>100)t1 ) t2)t3 ; -- t4 -
检索出前三天的 低碳量
-- select user_id,data_dt from t4 where dayCount>=3; select user_id,data_dt from (select user_id,data_dt,count(datesub) over (partition by user_id,datesub) dayCount from (select user_id,data_dt,date_sub(regexp_replace(data_dt,'/','-'),rn) datesub from ( select user_id,data_dt,sumcarbon,rank() over (partition by user_id order by data_dt ) rn from (select user_id,data_dt,sum(low_carbon) sumcarbon from user_low_carbon group by user_id,data_dt having sumcarbon>100)t1 ) t2)t3)t4 where dayCount>=3; --t5 -
多表连接，查询需要的字段
-- select u.* from user_low_carbon u join t5 on u.user_id=t5.user_id and u.data_dt=t5.data_dt; select u.* from user_low_carbon u join (select user_id,data_dt from (select user_id,data_dt,count(datesub) over (partition by user_id,datesub) dayCount from (select user_id,data_dt,date_sub(regexp_replace(data_dt,'/','-'),rn) datesub from ( select user_id,data_dt,sumcarbon,rank() over (partition by user_id order by data_dt ) rn from (select user_id,data_dt,sum(low_carbon) sumcarbon from user_low_carbon group by user_id,data_dt having sumcarbon>100)t1 ) t2)t3)t4 where dayCount>=3)t5 on u.user_id=t5.user_id and u.data_dt=t5.data_dt;
824
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
