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;

image-20240519222645496

题目二

蚂蚁森林低碳用户排名分析
问题:查询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));

image-20240519221650864

posted @ 2024-05-20 20:48  low-reed  阅读(23)  评论(0)    收藏  举报