Hive练习之蚂蚁森林

最新推荐文章于 2020-09-29 20:17:18 发布
最新推荐文章于 2020-09-29 20:17:18 发布
阅读量2.1k 收藏 16
点赞数 10
分类专栏： 大数据 文章标签： Hive Hadoop 大数据
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-NC-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u013961139/article/details/91356798
版权

背景说明：

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

user_low_carbon

user_id data_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
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

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

plant_carbon

plant_id plant_name low_carbon

植物编号 植物名 换购植物所需要的碳

p001	梭梭树	17
p002	沙柳	19
p003	樟子树	146
p004	胡杨	215

题目

  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--->xxx…
  1. 蚂蚁森林低碳用户排名分析

问题：查询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

参考答案与解析

这里我们采用 Hive 的 HQL 来解决这两个问题。

准备工作

  1. 创建表
create table user_low_carbon(user_id String,data_dt String,low_carbon int) row format delimited fields terminated by '\t';
create table plant_carbon(plant_id string,plant_name String,low_carbon int) row format delimited fields terminated by '\t';
  1. 加载数据
load data local inpath “/opt/module/data/low_carbon.txt” into table user_low_carbon;
load data local inpath “/opt/module/data/plant_carbon.txt” into table plant_carbon;
  1. 设置本地模式
set hive.exec.mode.local.auto=true;

题目 1 详解

  1. 统计2017年1月1日至2017年10月1日之前记录低碳数据，并按照降序排列
SELECT user_id, SUM(low_carbon) sum_low_carbon
FROM user_low_carbon 
WHERE UNIX_TIMESTAMP(data_dt, 'yyyy/MM/dd') >= UNIX_TIMESTAMP('2017/01/01', 'yyyy/MM/dd') 
AND UNIX_TIMESTAMP(data_dt, 'yyyy/MM/dd') < UNIX_TIMESTAMP('2017/10/01', 'yyyy/MM/dd')
GROUP BY user_id
ORDER BY sum_low_carbon desc;
  1. 查询换购胡杨所需的碳
SELECT low_carbon FROM plant_carbon WHERE plant_id = 'p004';
  1. 查询换购沙柳所需的碳
SELECT low_carbon FROM plant_carbon WHERE plant_id = 'p002';
  1. 查询前11位用户减去领取一棵胡杨的碳量后，全部领取沙柳的数量。
SELECT user_id, sum_low_carbon, floor((sum_low_carbon - t2.low_carbon) / t3.low_carbon) plant_count
FROM
(SELECT user_id, SUM(low_carbon) sum_low_carbon
FROM user_low_carbon 
WHERE UNIX_TIMESTAMP(data_dt, 'yyyy/MM/dd') >= UNIX_TIMESTAMP('2017/01/01', 'yyyy/MM/dd') 
AND UNIX_TIMESTAMP(data_dt, 'yyyy/MM/dd') < UNIX_TIMESTAMP('2017/10/01', 'yyyy/MM/dd')
GROUP BY user_id
ORDER BY sum_low_carbon desc) t1,
(SELECT low_carbon FROM plant_carbon WHERE plant_id = 'p004') t2,
(SELECT low_carbon FROM plant_carbon WHERE plant_id = 'p002') t3
LIMIT 11;

有一点要注意，如果是临时表（子查询） 不用指定查询的字段属于哪张表，如果是 join（包括笛卡儿积）操作就必须指定查询字段属于哪张表。临时表一定要用（）包围并取一个别名。

对于前面 3 步查出来的数据，它们之间是没有关联字段，故只能采用笛卡儿积的形式将它们关联起来。上面的查询语句看起来十分复杂，其是就是下面这种结构：

SELECT * FROM t1, t2, t3;

至于为什么这里要查询11位的信息，这里先按下不表。嘿嘿嘿！

  1. 最后的查询。
SELECT user_id, plant_count ,plant_count - LEAD(plant_count, 1, 0) OVER(ORDER BY sum_low_carbon DESC) less_count
FROM
(SELECT user_id, sum_low_carbon, floor((sum_low_carbon - t2.low_carbon) / t3.low_carbon) plant_count
FR
最低0.47元/天 解锁文章
技术补完计划
关注
  • 10
    点赞
  • 踩
  • 16
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
hive之蚂蚁森林
qq_34264067的博客
11-20 642
介绍 蚂蚁森林是hive中的一个很有价值的项目 话不错说，步入正题 数据结构介绍 以下表记录了用户每天的蚂蚁森林低碳生活领取的记录流水。 user_low_carbon user_id data_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 ...
Hive实战 | 蚂蚁森林案例
蕾欧娜与26的博客
06-17 1812
前两天学习了 Hive，本篇博客将以案例的形式对之前学过的知识进行应用。 场景描述 本案例的场景是关于蚂蚁森林，没错，就是支付宝里的蚂蚁森林。首先这里有两张表。 一张记录了用户每天的蚂蚁森林低碳生活领取的记录流水。 table_name: user_low_carbon 字段如下： user_id data_dt low_carbon 用户 日期 减少碳排放(g) u_002 2017/1/2 150 u_002 2017/1/2
Hive经典案例之蚂蚁森林(一)
bigdataJ的博客
08-25 2806
Hive经典案例之蚂蚁森林 以下表记录了用户每天的蚂蚁森林低碳生活领取的记录流水。 table_name：user_low_carbon user_id data_dt low_carbon 用户 日期 减少碳排放（g） 蚂蚁森林植物换购表，用于记录申领环保植物所需要减少的碳排放量 table_name: plant_carbon plant_id plant_name low_carbon 植物编号 植物名 换购植物所需要的碳 表数据：
Hive案例-蚂蚁森林
weixin_38255444的博客
11-28 625
Hive案例-蚂蚁森林 一：蚂蚁森林 /* 背景说明： 以下表记录了用户每天的蚂蚁森林低碳生活领取的记录流水。 table_name：user_low_carbon user_id data_dt low_carbon 用户 日期 减少碳排放（g） */ drop table user_low_carbon; create table user_low_carbon(user_...
Hive 蚂蚁森林面试题训练
qq443967620的博客
02-19 600
背景说明： 以下表记录了用户每天的蚂蚁森林低碳生活领取的记录流水。 table_name：user_carbon user_id data_dt low_carbon 用户 日期 减少碳排放（g） 蚂蚁森林植物换购表，用于记录申领环保植物所需要减少的碳排放量 table_name: plant_carbon plant_i...
hive练习数据和练习题及答案
01-05
hive练习数据和hive练习题包含了hive练习数据，hive数据的建表ddl和hive练习题，非常适合做hive练习，新手培训，快速入门等； 包含以下练习： hive的group by 和集合函数 hive的Order By/Sort By/Distribute By Join...
Hive练习数据包.zip
12-11
这个“Hive练习数据包.zip”可能包含了一些示例数据和练习题目，帮助用户熟悉Hive的查询语句（HQL）以及数据处理流程。在学习Hive的过程中，理解其核心概念和操作是非常重要的。 首先，Hive构建在Hadoop之上，它将...
hivesql语句练习
12-06
5.安装hive和mysq完成后，将mysql的连接jar包拷贝到$HIVE_HOME/lib目录下 如果出现没有权限的问题，在mysql授权(在安装mysql的机器上执行) mysql -uroot -p #(执行下面的语句 *.*:所有库下的所有表 %：任何IP地址...
Hive经典案例之蚂蚁森林（二）
bigdataJ的博客
08-27 1496
需求 2、蚂蚁森林低碳用户排名分析 问题：查询user_low_carbon表中每日流水记录，条件为： 用户在2017年，连续三天（或以上）的天数里， 每天减少碳排放（low_carbon）都超过100g的用户低碳流水。 需要查询返回满足以上条件的user_low_carbon表中的记录流水。 例如用户u_002符合条件的记录如下，因为2017/1/2~2017/1/5连续四天的碳排放量之和都大于等于100g： 分析：首先根据用户和天数进行分组，过滤掉总能量小于100的用户。 接下来就是比较难理解的一点，就
js仿照 蚂蚁森林 效果
03-26
js仿照 蚂蚁森林 效果 https://blog.csdn.net/JAVA5G/article/details/99741511?spm=1001.2014.3001.5502
尚硅谷大数据之Hive视频
11-14
下载后，是一个文本，里面会有我百度网盘的分享链接，下载即可，绝对真实无儿戏。
仿蚂蚁森林能量特效
06-21
仿支付宝蚂蚁森林能量特效，以后可能用的到，记录一下
仿支付宝蚂蚁森林能量球效果
04-16
蚂蚁森林能量球效果模拟，主要是能量球上下浮动的效果。
蚂蚁森林自动收取能量的脚本
03-15
利用auto.js实现的蚂蚁森林收能量脚本。
sql练习一(蚂蚁森林)
qq_42506914的博客
03-17 1596
背景说明： 以下表记录了用户每天的蚂蚁森林低碳生活领取的记录流水。 table_name：user_low_carbon seq（key）user_iddata_dtlow_carbon 流水号用户日期减少碳排放（g） xxxxx01u_0012017/1/110 xxxxx02u_0012017/1/2150 xxx...
Hive（三、1）案例之蚂蚁森林
你宝爷的博客
09-29 839
Hive（三、1）案例之蚂蚁森林 一、前期准备 1.0、打开集群&服务&客户端 #1）注释掉配置文件内容，这样就不用开启metastore服务啦 vim $HIVE_HOME/conf/hive-site.xml #注释掉如下内容 <!-- <property> <name>hive.metastore.uris</name> <value>thrift://hadoop11:9
模仿蚂蚁森林水滴动效
weixin_34302561的博客
01-18 658
很久没写东西了，又来一下，可能看了这个东效你会觉得很简单，但是如果你看完，我相信你还是会发现简单里面还是有文章，也许并不是看起来那样简单，本文精华在于 性能 老规矩还是先上GIF 也许看到这个图，你就不想再继续看下去了，心想这个动画很简单啊，不就是创建循环创建view，再给每个view的动画，每个view的开始运动的方向随机，再给一个加速器就搞定了嘛，如果你也是这样想那就该把这个文章看完了 分析...
hive 蚂蚁森林二题
Bangka老船长
06-17 566
背景说明： 以下表记录了用户每天的蚂蚁森林低碳生活领取的记录流水。 蚂蚁森林植物换购表，用于记录申领环保植物所需要减少的碳排放量 题目 1.蚂蚁森林植物申领统计 问题：假设2017年1月1日开始记录低碳数据（user_low_carbon），假设2017年10月1日之前满足申领条件的用户都申领了一颗p004-胡杨， 剩余的能量全部用来领取“p002-沙柳” 。 统计在10月1日累计申领“p002-沙柳” 排名前10的用户信息；以及他比后一名多领了几颗沙柳。 得到的统计结果如下表样式： user_id
hive sql练习之影评案例
最新发布
05-26
好的，下面为你提供一个简单的影评案例，供你练习Hive SQL。 假设有一个电影影评平台，其中有两个表格： 1. `movies` 表：包含电影的基本信息，如电影ID、电影名称、电影类型等。 2. `reviews` 表：包含用户对电影的评分和评论，如用户ID、电影ID、评分、评论等。 现在我们要使用Hive SQL来回答以下问题： 1. 统计电影总数和不同类型电影的数量。 ```sql SELECT COUNT(*) AS total_movies, type, COUNT(*) AS num_movies FROM movies GROUP BY type ``` 2. 统计每个电影类型的平均评分和评论数量。 ```sql SELECT type, AVG(rating) AS avg_rating, COUNT(*) AS num_reviews FROM movies JOIN reviews ON movies.movie_id = reviews.movie_id GROUP BY type ``` 3. 找出评分最高的10部电影和评分最低的10部电影。 ```sql -- 评分最高的10部电影 SELECT movies.movie_name, AVG(rating) AS avg_rating FROM movies JOIN reviews ON movies.movie_id = reviews.movie_id GROUP BY movies.movie_name ORDER BY avg_rating DESC LIMIT 10 -- 评分最低的10部电影 SELECT movies.movie_name, AVG(rating) AS avg_rating FROM movies JOIN reviews ON movies.movie_id = reviews.movie_id GROUP BY movies.movie_name ORDER BY avg_rating ASC LIMIT 10 ``` 希望这个练习案例能够帮助你熟悉Hive SQL的使用。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

技术补完计划

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值