Hive经典案例之蚂蚁森林(一)

最新推荐文章于 2021-02-14 22:13:15 发布
最新推荐文章于 2021-02-14 22:13:15 发布
阅读量2.8k 收藏 11
点赞数 1
文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/bigdataJ/article/details/108228207
版权

Hive经典案例之蚂蚁森林

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

user_id data_dt low_carbon
用户 日期 减少碳排放（g）

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

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

表数据：plant_carbon.txt

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

表数据 ：user_low_carbon.txt

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

问题一：
蚂蚁森林植物申领统计
问题：假设2017年1月1日开始记录低碳数据（user_low_carbon），
假设2017年10月1日之前满足申领条件的用户都申领了一颗p004-胡杨，
剩余的能量全部用来领取“p002-沙柳” 。
统计在10月1日累计申领“p002-沙柳” 排名前10
的用户信息；以及他比后一名多领了几颗沙柳。

1.时间转换，将2017/1/3 转换成 2017-01-03

SELECT
     user_id,
     DATE_FORMAT(regexp_replace(data_dt,'/','-'),'yyyy-MM-dd') data_dt,
     low_carbon
FROM
     user_low_carbon  t1

在这里插入图片描述
2.计算时间在2017.10.1号之前的能量总和

SELECT
    t1.user_id,
    SUM(low_carbon) sum_low_carbon
FROM
(
   SELECT
     user_id,
     DATE_FORMAT(regexp_replace(data_dt,'/','-'),'yyyy-MM-dd') data_dt,
     low_carbon
   FROM
     user_low_carbon
)
t1
WHERE t1.data_dt<"2017-10-01"
GROUP BY t1.user_id
ORDER BY sum_low_carbon DESC LIMIT 11   t2
+-------------+-----------------+
| t1.user_id  | sum_low_carbon  |
+-------------+-----------------+
| u_007       | 1470            |
| u_013       | 1430            |
| u_008       | 1240            |
| u_005       | 1100            |
| u_010       | 1080            |
| u_014       | 1060            |
| u_011       | 960             |
| u_009       | 930             |
| u_006       | 830             |
| u_002       | 659             |
| u_004       | 640             |
+-------------+-----------------+

3.求出每个人领取的沙柳的数量 (先减去胡杨的能量，在除以沙柳的能量)
注意：最后相除的结果要进行向下转型

SELECT
   t2.user_id,
   t2.sum_low_carbon,
   t2.sum_low_carbon-(SELECT low_carbon  FROM plant_carbon WHERE plant_id='p004') aa ,
   FLOOR((t2.sum_low_carbon-(SELECT low_carbon  FROM plant_carbon WHERE plant_id='p004'))/(SELECT low_carbon  FROM plant_carbon WHERE plant_id='p002'))  bb
FROM  t2

组合

SELECT
   t2.user_id,
   t2.sum_low_carbon,
   t2.sum_low_carbon-(SELECT low_carbon  FROM plant_carbon WHERE plant_id='p004') aa,
   FLOOR((t2.sum_low_carbon-(SELECT low_carbon  FROM plant_carbon WHERE plant_id='p004'))/(SELECT low_carbon  FROM plant_carbon WHERE plant_id='p002'))  bb

FROM (
SELECT
    t1.user_id,
    SUM(low_carbon) sum_low_carbon
FROM
(
   SELECT
     user_id,
     DATE_FORMAT(regexp_replace(data_dt,'/','-'),'yyyy-MM-dd') data_dt,
     low_carbon
   FROM
     user_low_carbon
)
t1
WHERE t1.data_dt<"2017-10-01"
GROUP BY t1.user_id
ORDER BY sum_low_carbon DESC LIMIT 11
) t2      t3
+-------------+--------------------+-------+-----+
| t2.user_id  | t2.sum_low_carbon  |  aa   | bb  |
+-------------+--------------------+-------+-----+
| u_007       | 1470               | 1255  | 66  |
| u_013       | 1430               | 1215  | 63  |
| u_008       | 1240               | 1025  | 53  |
| u_005       | 1100               | 885   | 46  |
| u_010       | 1080               | 865   | 45  |
| u_014       | 1060               | 845   | 44  |
| u_011       | 960                | 745   | 39  |
| u_009       | 930                | 715   | 37  |
| u_006       | 830                | 615   | 32  |
| u_002       | 659                | 444   | 23  |
| u_004       | 640                | 425   | 22  |
+-------------+--------------------+-------+-----+

4.把下一行提取到前一行

    -- 把下一行提取到前面
SELECT 
    t3.user_id,
    t3.sum_low_carbon,
    t3.aa,
    t3.bb,
    lead(t3.bb,1) over(ORDER BY t3.bb DESC) next_sl
FROM t3
LIMIT 10

组合

SELECT 
    t3.user_id,
    t3.sum_low_carbon,
    t3.aa,
    t3.bb,
    lead(t3.bb,1) over(ORDER BY t3.bb DESC) next_sl
FROM 
(
SELECT
   t2.user_id,
   t2.sum_low_carbon,
   t2.sum_low_carbon-(SELECT low_carbon  FROM plant_carbon WHERE plant_id='p004') aa,
   FLOOR((t2.sum_low_carbon-(SELECT low_carbon  FROM plant_carbon WHERE plant_id='p004'))/(SELECT low_carbon  FROM plant_carbon WHERE plant_id='p002'))  bb

FROM (
SELECT
    t1.user_id,
    SUM(low_carbon) sum_low_carbon
FROM
(
   SELECT
     user_id,
     DATE_FORMAT(regexp_replace(data_dt,'/','-'),'yyyy-MM-dd') data_dt,
     low_carbon
   FROM
     user_low_carbon
)
t1
WHERE t1.data_dt<"2017-10-01"
GROUP BY t1.user_id
ORDER BY sum_low_carbon DESC LIMIT 11
) t2
)
t3
LIMIT 10
+-------------+--------------------+--------+--------+----------+
| t3.user_id  | t3.sum_low_carbon  | t3.aa  | t3.bb  | next_sl  |
+-------------+--------------------+--------+--------+----------+
| u_007       | 1470               | 1255   | 66     | 63       |
| u_013       | 1430               | 1215   | 63     | 53       |
| u_008       | 1240               | 1025   | 53     | 46       |
| u_005       | 1100               | 885    | 46     | 45       |
| u_010       | 1080               | 865    | 45     | 44       |
| u_014       | 1060               | 845    | 44     | 39       |
| u_011       | 960                | 745    | 39     | 37       |
| u_009       | 930                | 715    | 37     | 32       |
| u_006       | 830                | 615    | 32     | 23       |
| u_002       | 659                | 444    | 23     | 22       |
+-------------+--------------------+--------+--------+----------+
  1. 直接两行相减 即可得出
SELECT 
    t3.user_id,
    t3.sum_low_carbon,
    t3.aa,
    t3.bb,
    lead(t3.bb,1) over(ORDER BY t3.bb DESC) next_sl,
    t3.bb-lead(t3.bb,1) over(ORDER BY t3.bb DESC)  lastnum
FROM 
(
SELECT
   t2.user_id,
   t2.sum_low_carbon,
   t2.sum_low_carbon-(SELECT low_carbon  FROM plant_carbon WHERE plant_id='p004') aa,
   FLOOR((t2.sum_low_carbon-(SELECT low_carbon  FROM plant_carbon WHERE plant_id='p004'))/(SELECT low_carbon  FROM plant_carbon WHERE plant_id='p002'))  bb

FROM (
SELECT
    t1.user_id,
    SUM(low_carbon) sum_low_carbon
FROM
(
   SELECT
     user_id,
     DATE_FORMAT(regexp_replace(data_dt,'/','-'),'yyyy-MM-dd') data_dt,
     low_carbon
   FROM
     user_low_carbon
)
t1
WHERE t1.data_dt<"2017-10-01"
GROUP BY t1.user_id
ORDER BY sum_low_carbon DESC LIMIT 11
) t2
)
t3
LIMIT 10
+-------------+--------------------+--------+--------+----------+----------+
| t3.user_id  | t3.sum_low_carbon  | t3.aa  | t3.bb  | next_sl  | lastnum  |
+-------------+--------------------+--------+--------+----------+----------+
| u_007       | 1470               | 1255   | 66     | 63       | 3        |
| u_013       | 1430               | 1215   | 63     | 53       | 10       |
| u_008       | 1240               | 1025   | 53     | 46       | 7        |
| u_005       | 1100               | 885    | 46     | 45       | 1        |
| u_010       | 1080               | 865    | 45     | 44       | 1        |
| u_014       | 1060               | 845    | 44     | 39       | 5        |
| u_011       | 960                | 745    | 39     | 37       | 2        |
| u_009       | 930                | 715    | 37     | 32       | 5        |
| u_006       | 830                | 615    | 32     | 23       | 9        |
| u_002       | 659                | 444    | 23     | 22       | 1        |
+-------------+--------------------+--------+--------+----------+----------+
苏世不醒
关注
  • 1
    点赞
  • 踩
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
hive之蚂蚁森林
qq_34264067的博客
11-20 630
介绍 蚂蚁森林是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练手04 蚂蚁森林2
weixin_40973190的博客
11-05 469
2、蚂蚁森林低碳用户排名分析 问题：查询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_001 20
Hive练习之蚂蚁森林
技术补完计划
06-09 2179
背景说明： 以下表记录了用户每天的蚂蚁森林低碳生活领取的记录流水。 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...
Hive实战 | 蚂蚁森林案例
蕾欧娜与26的博客
06-17 1809
前两天学习了 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 蚂蚁森林面试题训练
01-20
背景说明： 以下表记录了用户每天的蚂蚁森林低碳生活领取的记录流水。 table_name：user_carbon user_id data_dt low_carbon 用户 日期 减少碳排放（g） 蚂蚁森林植物换购表，用于记录申领环保植物所需要减少的碳排放量 table_name: plant_carbon plant_id plant_name low_carbon 植物编号 植物名 换购植物所需要的碳 —-题目 1.蚂蚁森林植物申领统计 问题：假设2017年1月1日开始记录低碳数据（
Hive（三、1）案例之蚂蚁森林
你宝爷的博客
09-29 827
Hive（三、1）案例之蚂蚁森林 一、前期准备 1.0、打开集群&服务&客户端 #1）注释掉配置文件内容，这样就不用开启metastore服务啦 vim $HIVE_HOME/conf/hive-site.xml #注释掉如下内容 <!-- <property> <name>hive.metastore.uris</name> <value>thrift://hadoop11:9
hive影评案例.zip
04-01
标题中的“hive影评案例.zip”表明这是一个关于使用Hive处理电影评论数据的示例。Hive是基于Hadoop的数据仓库工具，它允许用户通过SQL（HQL，Hive SQL）查询分布式存储的大数据集。这个压缩包可能包含了用于分析用户...
hive优化案例
01-14
Hive作业的规模决定着优化层级，一个Hive作业的优化和一万的Hive作业的优化截然不同。 拥有1万多个Hive作业的大电商如何进行Hive优化的？本系列课结合企业实战和场景从作业架构层面、Hql(Hive sql)语法层面、Hive...
Hive的案例详解.pdf
最新发布
06-28
### Hive的案例详解 #### 一、Hive概述 Hive是基于Hadoop的一个数据仓库工具，主要用于数据的提取、转换、加载（ETL），它能够将结构化的数据文件映射为数据库表，并提供SQL查询功能，从而将SQL语句转化为...
《Hive数据仓库案例教程》教学课件 第5章 Hive数据操作.pdf
06-22
《Hive数据仓库案例教程》教学课件 第5章 Hive数据操作.pdf《Hive数据仓库案例教程》教学课件 第5章 Hive数据操作.pdf《Hive数据仓库案例教程》教学课件 第5章 Hive数据操作.pdf《Hive数据仓库案例教程》教学课件 第...
史上最全的测试用例设计方法总结
08-13
等价类划分方法： 一.方法简介 1.定义 是把所有可能的输入数据,即程序的输入域划分成若干部分（子集）,然后从每一个子集中选取少数具有代表性的数据作为测试用例。该方法是一种重要的,常用的黑盒测试用例设计方法。 2.划分等价类： 等价类是指某个输入域的子集合。在该子集合中,各个输入数据对于揭露程序中的错误都是等效的，并合理地假定：测试某等价类的代表值就等于对这一类其它值的测试，因此,可以把全部输入数据合理划分为若干等价类,在每一个等价类中取一个数据作为测试的输入条件就可以用少量代表性的测试数据取得较好的测试结果。等价类划分可有两种不同的情况：有效等价类和无效等价类。
Hive经典案例之蚂蚁森林（二）
bigdataJ的博客
08-27 1479
需求 2、蚂蚁森林低碳用户排名分析 问题：查询user_low_carbon表中每日流水记录，条件为： 用户在2017年，连续三天（或以上）的天数里， 每天减少碳排放（low_carbon）都超过100g的用户低碳流水。 需要查询返回满足以上条件的user_low_carbon表中的记录流水。 例如用户u_002符合条件的记录如下，因为2017/1/2~2017/1/5连续四天的碳排放量之和都大于等于100g： 分析：首先根据用户和天数进行分组，过滤掉总能量小于100的用户。 接下来就是比较难理解的一点，就
Hive案例-蚂蚁森林
weixin_38255444的博客
11-28 613
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升级版教程（含配套资料）
08-29
Hive是基于Hadoop的一个数据仓库工具，将繁琐的MapReduce程序变成了简单方便的SQL语句实现，深受广大软件开发工程师喜爱。Hive同时也是进入互联网行业的大数据开发工程师必备技术之一。在本课程中，你将学习到，Hive架构原理、安装配置、hiveserver2、数据类型、数据定义、数据操作、查询、自定义UDF函数、窗口函数、压缩和存储、企业级调优、以及结合影音项目需求，把整个Hive的核心知识点贯穿起来。更新：课件升级、添加自定义UDTF函数、企业常用函数以及更多企业面试真题详细讲解
蚂蚁森林数据库造数
innovation的博客
08-21 1749
基于之前的数据库建模方法，我们设计好数据库之后还需要往数据库中填充数据以供程序测试使用 建表如下： energy friend steal user 我们需要往该空数据库中填入适当的测试数据，虽然各个表之间有主外键关系，但是我们建库时不需要考虑外键（很多大公司也是这么做），暂时只要给到合适的数据类型和数据范围就行了。 观察得到其中需要随机产生的数据类型大概有： 整形，可变长字符串，时间日...
sql练习一(蚂蚁森林)
qq_42506914的博客
03-17 1591
背景说明： 以下表记录了用户每天的蚂蚁森林低碳生活领取的记录流水。 table_name：user_low_carbon seq（key）user_iddata_dtlow_carbon 流水号用户日期减少碳排放（g） xxxxx01u_0012017/1/110 xxxxx02u_0012017/1/2150 xxx...
蚂蚁森林｜科技，是这个时代最大的公益
人人都是产品经理
02-09 6449
作者：浅忆全文共 4778 字 6 图，阅读需要 10 分钟———— / BEGIN / ————你以为我要写一篇情感类文章？不，这是一篇关于产品的文章，但从产品设计的角度来说它确实考验了人性的一些东西。蚂蚁森林里面的设定：每次浇水会给对方增加10g能量，同时会扣减自己10g能量。所以，看一个人愿不愿意付出，就看他/她愿不愿意帮你浇水咯……..毫无疑问，蚂蚁森林是一款成功的产品，就像当年偷菜类产品
30 行代码实现蚂蚁森林自动收能量(附送源码)
ITMuch的专栏
02-14 2306
点击上方IT牧场，选择置顶或者星标技术干货每日送达！blog.csdn.net/article/details/113734855推荐：https://www.xttblog.com...
蚂蚁森林(hive)查询练习
shyming's blog
08-28 631
蚂蚁森林背景说明： 以下表记录了用户每天的蚂蚁森林低碳生活领取的记录流水。 table_name：user_low_carbon user_id data_dt low_carbon 用户 日期 减少碳排放（g） 蚂蚁森林植物换购表，用于记录申领环保植物所需要减少的碳排放量 table_name: plant_carbon plant_id plant_name low_carbon 植物编号 植物名 换购植物所需要的碳 1.创建表 create table user_low_carb
hivesql经典案例
08-22
HiveSQL经典案例有很多，以下是其中的几个： 1. 电商数据分析：通过HiveSQL可以对电商平台的大数据进行分析，如用户购买行为、订单统计、商品销售排名等。可以利用Hive的内置函数和高级操作来进行数据清洗、聚合和筛选，从而得出有关用户行为和市场趋势的有用信息。 2. 日志分析：HiveSQL可以用于处理大规模的日志数据。例如，对于网站访问日志，可以使用HiveSQL来提取特定时间范围内的访问量、最常访问的页面、用户访问路径等信息。这对于了解用户行为和优化网站设计非常有帮助。 3. 社交网络分析：HiveSQL可以用于处理社交网络数据，如推特、微博等。通过HiveSQL可以提取用户发布的内容、关注的人、转发和评论等信息，并进行分析，如热门话题的挖掘、用户情感分析等。 4. 金融数据分析：HiveSQL可以用于处理金融领域的大数据，如股票交易数据、银行交易记录等。通过HiveSQL可以对交易数据进行汇总、统计和分析，例如计算平均交易金额、找出异常交易、分析不同证券的收益率等。 这些案例只是HiveSQL的一部分应用场景，实际上HiveSQL可以用于各种大数据处理和分析任务，根据具体的业务需求进行灵活应用。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值