【Hive】Hive入门解析（七）大数据Together-

我又。。。。。。。。。。。。。。来了！

我又给你们收集到了Hive练习题来了！！！

题目如下👇

背景说明：

以下表记录了用户每天的蚂蚁森林低碳生活领取的记录流水
table_name：user_low_carbon

建表语句👇 create table user_low_carbon(user_id String,data_dt String,low_carbon int)  row format delimited fields terminated by 't'; 

蚂蚁森林植物换购表，用于记录申领环保植物所需要减少的碳排放量
table_name: plant_carbon

建表语句👇 create table plant_carbon(plant_id string,plant_name String,low_carbon int)  row format delimited fields terminated by 't';  题目1.蚂蚁森林植物申领统计 问题：假设2017年1月1日开始记录低碳数据（user_low_carbon），假设2017年10月1日之前满足申领条件的用户都申领了一颗p004-胡杨， 剩余的能量全部用来领取“p002-沙柳” 。 统计在10月1日累计申领“p002-沙柳” 排名前10的用户信息；以及他比后一名多领了几颗沙柳 得到的统计结果如下表样式： user_id  plant_count less_count(比后一名多领了几颗沙柳) u_101    20         5 u_088    15         1 u_103    14         ...  题目2、蚂蚁森林低碳用户排名分析 问题：查询user_low_carbon表中每日流水记录，条件为： 用户在2017年，连续三天（或以上）的天数里， 每天减少碳排放（low_carbon）都超过100g的用户低碳流水。 需要查询返回满足以上条件的user_id 例如用户u_002符合条件的记录如下，因为2017/1/2~2017/1/5连续四天的碳排放量之和都大于等于100g： user_id u_002     u_005     u_008     u_009     u_010    u_011     u_013     u_014 备注：统计方法不限于sql、procedure、python,java等 

数据如下👇

plant_carbon表数据👇 p001 梭梭树 17 p002 沙柳 19 p003 樟子树 146 p004 胡杨 215  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 u_003 2017/1/1 20 u_003 2017/1/2 10 u_003 2017/1/2 150 u_003 2017/1/3 160 u_003 2017/1/4 20 u_003 2017/1/5 120 u_003 2017/1/6 20 u_003 2017/1/7 10 u_003 2017/1/7 110 u_004 2017/1/1 110 u_004 2017/1/2 20 u_004 2017/1/2 50 u_004 2017/1/3 120 u_004 2017/1/4 30 u_004 2017/1/5 60 u_004 2017/1/6 120 u_004 2017/1/7 10 u_004 2017/1/7 120 u_005 2017/1/1 80 u_005 2017/1/2 50 u_005 2017/1/2 80 u_005 2017/1/3 180 u_005 2017/1/4 180 u_005 2017/1/4 10 u_005 2017/1/5 80 u_005 2017/1/6 280 u_005 2017/1/7 80 u_005 2017/1/7 80 u_006 2017/1/1 40 u_006 2017/1/2 40 u_006 2017/1/2 140 u_006 2017/1/3 210 u_006 2017/1/3 10 u_006 2017/1/4 40 u_006 2017/1/5 40 u_006 2017/1/6 20 u_006 2017/1/7 50 u_006 2017/1/7 240 u_007 2017/1/1 130 u_007 2017/1/2 30 u_007 2017/1/2 330 u_007 2017/1/3 30 u_007 2017/1/4 530 u_007 2017/1/5 30 u_007 2017/1/6 230 u_007 2017/1/7 130 u_007 2017/1/7 30 u_008 2017/1/1 160 u_008 2017/1/2 60 u_008 2017/1/2 60 u_008 2017/1/3 60 u_008 2017/1/4 260 u_008 2017/1/5 360 u_008 2017/1/6 160 u_008 2017/1/7 60 u_008 2017/1/7 60 u_009 2017/1/1 70 u_009 2017/1/2 70 u_009 2017/1/2 70 u_009 2017/1/3 170 u_009 2017/1/4 270 u_009 2017/1/5 70 u_009 2017/1/6 70 u_009 2017/1/7 70 u_009 2017/1/7 70 u_010 2017/1/1 90 u_010 2017/1/2 90 u_010 2017/1/2 90 u_010 2017/1/3 90 u_010 2017/1/4 90 u_010 2017/1/4 80 u_010 2017/1/5 90 u_010 2017/1/5 90 u_010 2017/1/6 190 u_010 2017/1/7 90 u_010 2017/1/7 90 u_011 2017/1/1 110 u_011 2017/1/2 100 u_011 2017/1/2 100 u_011 2017/1/3 120 u_011 2017/1/4 100 u_011 2017/1/5 100 u_011 2017/1/6 100 u_011 2017/1/7 130 u_011 2017/1/7 100 u_012 2017/1/1 10 u_012 2017/1/2 120 u_012 2017/1/2 10 u_012 2017/1/3 10 u_012 2017/1/4 50 u_012 2017/1/5 10 u_012 2017/1/6 20 u_012 2017/1/7 10 u_012 2017/1/7 10 u_013 2017/1/1 50 u_013 2017/1/2 150 u_013 2017/1/2 50 u_013 2017/1/3 150 u_013 2017/1/4 550 u_013 2017/1/5 350 u_013 2017/1/6 50 u_013 2017/1/7 20 u_013 2017/1/7 60 u_014 2017/1/1 220 u_014 2017/1/2 120 u_014 2017/1/2 20 u_014 2017/1/3 20 u_014 2017/1/4 20 u_014 2017/1/5 250 u_014 2017/1/6 120 u_014 2017/1/7 270 u_014 2017/1/7 20 u_015 2017/1/1 10 u_015 2017/1/2 20 u_015 2017/1/2 10 u_015 2017/1/3 10 u_015 2017/1/4 20 u_015 2017/1/5 70 u_015 2017/1/6 10 u_015 2017/1/7 80 u_015 2017/1/7 60 

参考答案如下👇

题目1 （前提：前十名不考虑棵树一样，只要10条数据） 步骤1：求出2017年10月1日之前每个人的low_carbon总量，并获取low_carbon总量前11名 select  user_id,  sum(low_carbon) sum_low_carbon from  user_low_carbon where     date_format(regexp_replace(data_dt,"/","-"),"yyyy-MM")<"2017-10" group by user_id order by sum_low_carbon desc limit 11;t1   步骤2：求出申领一颗胡杨所需要的low_carbon select  low_carbon from  plant_carbon where plant_name='胡杨';t2    步骤3：求出申领一颗沙柳所需要的low_carbon select  low_carbon from  plant_carbon where plant_name='沙柳';t3  步骤4：求出换取一颗胡杨之后剩余可换取的沙柳个数（取整） select  user_id,  floor((t1.sum_low_carbon-t2.low_carbon)/t3.low_carbon) low_carbon_count from (select  user_id,  sum(low_carbon) sum_low_carbon from  user_low_carbon where     date_format(regexp_replace(data_dt,"/","-"),"yyyy-MM")<"2017-10" group by user_id order by sum_low_carbon desc limit 11)t1, (select  low_carbon from  plant_carbon where plant_name='胡杨')t2, (select  low_carbon from  plant_carbon where plant_name='沙柳')t3;t4  步骤5：求出用户后一名的沙柳个数 select  user_id,  low_carbon_count,  lead(low_carbon_count,1) over(order by low_carbon_count desc) next_low_carbon_count from  (select  user_id,  floor((t1.sum_low_carbon-t2.low_carbon)/t3.low_carbon) low_carbon_count from (select  user_id,  sum(low_carbon) sum_low_carbon from  user_low_carbon where     date_format(regexp_replace(data_dt,"/","-"),"yyyy-MM")<"2017-10" group by user_id order by sum_low_carbon desc limit 11)t1, (select  low_carbon from  plant_carbon where plant_name='胡杨')t2, (select  low_carbon from  plant_carbon where plant_name='沙柳')t3)t4;  步骤6：求出用户比后一名多领了几颗沙柳 select   user_id,  low_carbon_count,  (low_carbon_count-next_low_carbon_count) low_carbon_diff from  (select  user_id,  low_carbon_count,  lead(low_carbon_count,1) over(order by low_carbon_count desc) next_low_carbon_count from  (select  user_id,  floor((t1.sum_low_carbon-t2.low_carbon)/t3.low_carbon) low_carbon_count from (select  user_id,  sum(low_carbon) sum_low_carbon from  user_low_carbon where     date_format(regexp_replace(data_dt,"/","-"),"yyyy-MM")<"2017-10" group by user_id order by sum_low_carbon desc limit 11)t1, (select  low_carbon from  plant_carbon where plant_name='胡杨')t2, (select  low_carbon from  plant_carbon where plant_name='沙柳')t3)t4)t5  limit 10; 

题目2  步骤1：查询在2017年low_carbon超过100g的用户低碳流水的日期 select  user_id,  date_format(regexp_replace(data_dt,"/","-"),"yyyy-MM-dd") date from  user_low_carbon where substring(data_dt,1,4)='2017' group by user_id,data_dt having sum(low_carbon)>100;t1  步骤2：针对所有用户设置日期的rank排名（使用数学上的等差进行解决问题） select  user_id,  date,  rank() over(partition by user_id order by date) rk from  (select  user_id,  date_format(regexp_replace(data_dt,"/","-"),"yyyy-MM-dd") date from  user_low_carbon where substring(data_dt,1,4)='2017' group by user_id,data_dt having sum(low_carbon)>100)t1;t2  步骤3：日期减去排名 select  user_id,  date,  date_sub(date,rk) datediff from (select  user_id,  date,  rank() over(partition by user_id order by date) rk from  (select  user_id,  date_format(regexp_replace(data_dt,"/","-"),"yyyy-MM-dd") date from  user_low_carbon where substring(data_dt,1,4)='2017' group by user_id,data_dt having sum(low_carbon)>100)t1)t2;t3   步骤4：根据用户和datediff进行分组，判断个数是否大于3 select  user_id from (select  user_id,  date,  date_sub(date,rk) datediff from (select  user_id,  date,  rank() over(partition by user_id order by date) rk from  (select  user_id,  date_format(regexp_replace(data_dt,"/","-"),"yyyy-MM-dd") date from  user_low_carbon where substring(data_dt,1,4)='2017' group by user_id,data_dt having sum(low_carbon)>100)t1)t2 )t3 group by user_id,datediff having count(*) >= 3