hive sql练习
蚂蚁森林植物申领统计
使用spark sql 完成以下作业
背景说明
下表记录了用户每天的蚂蚁森林低碳生活领取的记录流水。
table_name:user_low_carbon
字段名 | 字段描述 |
---|---|
user_id | 用户 |
data_dt | 日期 |
low_carbon | 减少碳排放(g) |
蚂蚁森林植物换购表,用于记录申领环保植物所需要减少的碳排放量
table_name: plant_carbon
字段名 | 字段描述 |
---|---|
plant_id | 植物编号 |
plant_name | 植物名 |
plant_carbon | 换购植物所需要的碳 |
数据样例**
user_low_carbon:
user_id | date_dt | 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 |
plant_carbon:
plant_id | plant_name | plant_carbon |
---|---|---|
p001 | 梭梭树 | 17 |
p002 | 沙柳 | 19 |
p003 | 樟子树 | 146 |
p004 | 胡杨 | 215 |
题目一
蚂蚁森林植物申领统计
问题:假设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 …
select user_id,su,su-lead(su,1) over ()
from (select user_id,`floor`((sum(low_carbon)-215)/19) as su
from user_low_carbon group by user_id order by su desc)t1;
题目二
蚂蚁森林低碳用户排名分析
问题:查询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 a.user_id,date_dt,low_carbon
from user_low_carbon a join (select user_id,min(da) as mintime,max(da) as maxtime,count(1)
from (select user_id,da,date_sub(da,rank() over (partition by user_id order by da)) as top
from (select user_id,replace(date_dt,"/","-") as da,sum(low_carbon)
from user_low_carbon group by user_id,date_dt having sum(low_carbon)>=100)t1)t2
group by user_id,top having count(1) >=3)b on
(a.user_id=b.user_id and (replace(date_dt,"/","-") between mintime and maxtime));