通过Hive的案例了解Hive在工作中的使用

通过Hive的案例了解Hive在工作中的使用

笔者学习了HIve有一阵子时间了，但是碍于缺乏项目的实战，所以HQL写的总是别别扭扭，所幸通过一个案例似乎打通了一些对于HiveSQL使用的一些疑惑，故通过这个案例做个小小的总结。

案例1：蚂蚁森林统计排名

背景说明：

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

table_name：user_low_carbon

user_id(用户)	data_dt(日期)	low_carbon(减少碳排放量g)
u_101	2020/01/02	117

user_low_carbon.txt

建表语句：

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

plant_id	plant_name	low_carbon(兑换所需碳排放量)
p001	梭梭树	17
p002	沙柳	19
p003	樟子树	146
p004	胡杨	215

建表语句

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    1000         100
u_088    900          400
u_103    500          …

需求1查询方案

在测试阶段，建议把Hive的本地模式开启，会大大降低HQL运行时长！！！

set hive.exec.mode.local.auto=true;

为了得到最终查询结果，通常我们使用子查询的方式，一步一步接近我们要的答案。

思路：先根据条件过滤掉一批数据，然后对剩下满足条件的数据进行分组、排序、聚合等操作

(1)统计在10月1日前每个用户减少碳排放量的总和（取前11名）

分析：先搞清楚条件是什么：

• 每个用户，所以要按用户进行分组然后求和(碳排放)

  • group by user_id
  • sum(low_carbon) sum_carbon

• 10月1日前，所以要格式化时间并与2017-10-1做比较

  • where datediff(regexp_replace(data_dt,"/","-"),"2017-10-1")<0

• 取前11名，所以要对数据根据每个用户的碳排放总量进行排序，并用limit关键字求前11

  • order by sum_carbon

  • limit 11

为什么要取11而不是10？看到后面就明白了

通过分析，就可以写出第一步的HQL了

select user_id,sum(low_carbon) sum_carbon
from user_low_carbon
where datediff(regexp_replace(data_dt,"/","-"),"2017-10-1")<0
group by user_id
order by sum_carbon desc
limit 11;

结果作为t1表(作为后面的子查询)，如下所示：

+----------+-------------+--+
| user_id  | sum_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         |
+----------+-------------+--+

(2)取出申领胡杨的条件

select low_carbon from plant_carbon where plant_id="p004";

很简单的SQL,将结果作为子查询表记为t2

+-------------+--+
| low_carbon  |
+-------------+--+
| 215         |
+-------------+--+

(3)取出申领沙柳的条件

select low_carbon from plant_carbon where plant_id="p002";

通过把结果记为t3

+-------------+--+
| low_carbon  |
+-------------+--+
| 19          |
+-------------+--+

(4)求出能申领沙柳的棵数

**分析：**根据前面3步我们得到了三张表，其中

• t1表包含了通过条件筛选后每个用户减少碳排放量的总和sum_carbon
• t2表的结果是申领胡杨所需的碳排放量
• t3表的结果是申领沙柳所需的碳排放量

因此，每个用户能够申领沙柳的棵数就等于(t1.sum_carbon-t2.low_carbon)/t3.low_carbon,注意应该是整除，所以我们通过floor对求出的棵数进行向下取整

select user_id,floor((t1.sum_carbon-t2.low_carbon)/t3.low_carbon) treeCount from t1,t2,t3;

将t1,t2,t3表的sql作为子查询，最终的HQL语句如下：

select 
user_id,
floor((t1.sum_carbon-t2.low_carbon)/t3.low_carbon) 
treeCount 
from (select user_id,sum(low_carbon) sum_carbon
from user_low_carbon
where datediff(regexp_replace(data_dt,"/","-"),"2017-10-1")<0
group by user_id
order by sum_carbon desc
limit 11)t1,
(select low_carbon from plant_carbon where plant_id="p004")t2,
(select low_carbon from plant_carbon where plant_id="p002")t3;t4

结果如下：

+----------+------------+--+
| user_id  | treecount  |
+----------+------------+--+
| u_007    | 66         |
| u_013    | 63         |
| u_008    | 53         |
| u_005    | 46         |
| u_010    | 45         |
| u_014    | 44         |
| u_011    | 39         |
| u_009    | 37         |
| u_006    | 32         |
| u_002    | 23         |
| u_004    | 22         |
+----------+------------+--+

(5)求出前一名比后一名多几棵

**分析：**这个需求的实现首先需要我们通过lead函数把t4表中的treeCount上移一位作为新的一列，

然后我们通过将两列数据相减就可以得到前一名比后一名多几棵树了。

这里就解释了为什么我们要取前11名的数据

示意图：

Hql如下：

select user_id,treeCount,treeCount-(lead(treeCount,1) over(order by treeCount desc))
from t4
limit 10;

将t4还原为子查询

select user_id,treeCount,treeCount-(lead(treeCount,1) over(order by treeCount desc))
from (select 
user_id,
floor((t1.sum_carbon-t2.low_carbon)/t3.low_carbon) 
treeCount 
from (select user_id,sum(low_carbon) sum_carbon
from user_low_carbon
where datediff(regexp_replace(data_dt,"/","-"),"2017-10-1")<0
group by user_id
order by sum_carbon desc
limit 11)t1,
(select low_carbon from plant_carbon where plant_id="p004")t2,
(select low_carbon from plant_carbon where plant_id="p002")t3)t4
limit 10;

结果：

+----------+------------+------+--+
| user_id  | treecount  | _c2  |
+----------+------------+------+--+
| u_007    | 66         | 3    |
| u_013    | 63         | 10   |
| u_008    | 53         | 7    |
| u_005    | 46         | 1    |
| u_010    | 45         | 1    |
| u_014    | 44         | 5    |
| u_011    | 39         | 2    |
| u_009    | 37         | 5    |
| u_006    | 32         | 9    |
| u_002    | 23         | 1    |
+----------+------------+------+--+

2. 蚂蚁森林低碳用户排名分析

问题：查询user_low_carbon表中每日流水记录，条件为：
用户在2017年，连续三天（或以上）的天数里，
每天减少碳排放（low_carbon）都超过100g的用户低碳流水。
需要查询返回满足以上条件的user_low_carbon表中的记录流水。
例如用户u_002符合条件的记录如下，因为2017/1/2~2017/1/5连续四