Hive 在不同数据处理场景下的强大功能和灵活性，包括行转列、多维度统计、访客分析和日期计算等方面。本文将介绍五个 Hive 数据处理问题的解决方案，并通过实际案例进行演示。
先在home文件夹下建一个hivedata文件夹，把我们所需的数据写成txt文件导入到/home/hivedata/文件夹下面。
一、成绩表行转列
问题描述：给定一个学生成绩表t_student2，包含学生 ID、姓名、课程和成绩，要求将课程列进行行转列操作，展示每个学生的各科成绩。
输出结果：
姓名 语文 数学 英语
张三 95 86 78
李四 80 96 88解决方案：
使用case when语句和聚合函数，按照学生姓名进行分组，对不同课程的成绩进行条件判断并求和，实现行转列的效果。
建表：
CREATE TABLE `t_student2` (`id` int NOT NULL COMMENT '主键 id',`name` string COMMENT '姓名',`course` string COMMENT '课程',`score` int COMMENT '成绩'
)INSERT INTO `t_student2` VALUES (1,'张三', '语文', 95),
(2,'李四', '语文', 99),(3,'王五', '语文', 80),(4,'张三', '数学', 86),(5,'李四', '数学', 96),
(6,'王五', '数学', 81),(7,'张三', '英语', 78),(8,'李四', '英语', 88),(9,'王五', '英语', 87);
代码如下：
selectname,sum(case when course ='语文' then score else 0 end) `语文`,sum(case when course ='数学' then score else 0 end) `数学`,sum(case when course ='英语' then score else 0 end) `英语`
from t_student2 group by name;
二、订单数、用户数、新客数统计
问题
1）给出2017年每个月的订单数、用户数、总成交金额。
2）给出2017年11月的新客数(指在11月才有第一笔订单)
数据
2017-01-01,10029001,1,33.57
2017-01-03,10029002,1,53.57
2017-01-11,10029003,2,63.57
2017-01-11,10029013,3,63.57
2017-02-02,10029004,1,73.57
2017-02-07,10029005,1,83.57
2017-02-15,10029006,1,93.57
2017-03-04,10029007,2,33.57
2017-04-01,10029008,1,53.57
2017-05-11,10029009,1,63.57
2017-05-21,10029010,2,73.57
2017-06-01,10029011,1,93.57
2017-06-07,10029012,1,13.57
2017-07-09,10029013,2,33.57
2017-07-20,10029014,1,23.57
2017-08-01,10029015,2,73.57
2017-09-09,10029016,2,93.57
2017-10-01,10029017,1,33.57
2017-11-11,10029018,1,36.57
2017-11-12,10029119,6,136.57
2017-11-21,10029019,1,37.57
2017-12-11,10029020,1,38.57
建表：
-- 建表
create table order_tab(dt string,order_id string,user_id string,amount decimal(10,2))
row format delimited fields terminated by ',';
-- 导入数据
load data local inpath '/home/hivedata/zy03.txt' into table order_tab;2017 年每个月的订单数、用户数、总成交金额：
使用group by按照月份对数据进行分组。
使用聚合函数分别统计每个月的订单数、用户数和总成交金额。
代码如下：
selectsubstr(dt, 1, 7) AS year_month,count(order_id) AS order_count,count(DISTINCT user_id) AS user_count,sum(amount) AS total_amount
fromorder_tab
wheresubstr(dt, 1, 4) = '2017'
group bysubstr(dt, 1, 7)
order byyear_month;2017 年 11 月的新客数：
首先找出在 2017 年 11 月之前没有订单记录的用户。
然后统计这些用户在 2017 年 11 月的订单数量，即为新客数。
代码如下：
selectcount(distinct user_id) as new_customers
fromorder_tab
wheremonth(dt) = 11 and year(dt) = 2017and not exists (select 1from order_tab as prev_orderswhereprev_orders.user_id = order_tab.user_idand month(prev_orders.dt) < 11and year(prev_orders.dt) = 2017);
三、京东店铺访问统计
访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop，请统计：
1）每个店铺的UV（访客数）
店铺名称 uv值
2）每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数
店铺名称 用户编号 访问次数
a u1 10
a u3 8
a u6 7
b xx xx
b xx xx
b xx xx
建表：
-- 建表
create table visit(user_id string,shop string
)row format delimited
fields terminated by '\t';
-- 导入数据
load data local inpath '/home/hivedata/jingdong.txt' into table visit;每个店铺的 UV（访客数）：
使用group by按照店铺名称进行分组。
使用count(distinct)统计每个店铺的不同访客数量。
代码如下：
select shop, count(distinct user_id) as uv
from visit
group by shop;每个店铺访问次数 top3 的访客信息：
使用窗口函数按照店铺名称和访客 ID 分组，统计每个访客的访问次数。
使用dense_rank()窗口函数为每个店铺内的访客按照访问次数进行排名。
筛选出排名前三的访客信息。
代码如下：
with t as (selectshop,user_id,count(*) AS visit_count,row_number() over (partition by shop order by count(*) desc ) as rankfrom visitgroup by shop, user_id
)
selectshop,user_id as `用户编号`,visit_count as `访问次数`
from t
where rank <= 3;四、店铺访问数据统计
问题描述：给定用户访问数据，包含用户 ID、访问日期和访问次数，要求统计每个用户的累计访问次数。
如下所示：
用户 月份 小计 累计
u01 2017-01 11 11
u01 2017-02 12 23
u02 2017-01 12 12
u03 2017-01 8 8
u04 2017-01 3 3
解决方案：
- 使用
substr函数将访问日期转换为月份格式。 - 使用窗口函数按照用户 ID 和月份进行分区，统计每个用户每个月的小计访问次数。
- 再次使用窗口函数按照用户 ID 进行分区，对小计访问次数进行累加得到累计访问次数。
数据：
userID visitDate visitCount
u01 2017-01-21 5
u02 2017-01-23 6
u03 2017-01-22 8
u04 2017-01-20 3
u01 2017-01-23 6
u01 2017-02-21 8
u02 2017-01-23 6
u01 2017-02-22 4
建表：
-- 建表
create table shopping(userID string,visitDate string,visitCount int
)row format delimited
fields terminated by ' '
tblproperties("skip.header.line.count"="1");
-- 导入数据
load data local inpath '/home/hivedata/shops.txt' into table shopping;代码如下：
with t as (select userID,substr(visitDate, 1, 6) as visitMonth,sum(visitCount) as monthCountfrom shoppinggroup by userID, substr(visitDate, 1, 6)
)
select userID, visitMonth as `月份`, monthCount as `小计`,sum(monthCount) over (partition by userID order by visitMonth) as `累计`
from t order by userID, visitMonth;
五、计算连续用户完成订单间隔最大的天数和最小的天数
问题描述：有一个订单表t15，包含用户 ID、订单日期和订单金额，要求计算每个用户完成订单间隔的最大天数和最小天数。
uid_1 max_day min_day
uid_1 4 1
解决方案：
- 使用窗口函数
lead获取每个用户下一个订单的日期。 - 使用
datediff函数计算当前订单日期与下一个订单日期的天数差。 - 对天数差进行处理，减去 1 得到实际的间隔天数。
- 按用户 ID 分组，使用聚合函数
max和min分别计算最大和最小间隔天数。
数据：
user_id dt amt
uid_1 20200501 10
uid_1 20200503 160
uid_1 20200508 201
uid_1 20200511 103
uid_1 20200516 119建表：
-- 建表
create table t15(user_id string,dt string,amt int
)row format delimited
fields terminated by ' '
tblproperties("skip.header.line.count"="1");
-- 导入数据
load data local inpath '/home/hivedata/homework0830/t15.txt' into table t15;代码如下：
with t as (select user_id, dt,lag(dt) over (partition by user_id order by unix_timestamp(dt, 'yyyyMMdd') asc) as prev_dt,row_number() over (partition by user_id order by unix_timestamp(dt, 'yyyyMMdd') asc) as rnFROM t15
),t2 as (select user_id, dt,case when prev_dt is null then null else`floor`((unix_timestamp(dt, 'yyyyMMdd')- unix_timestamp(prev_dt, 'yyyyMMdd') - 1) / 86400)end as days_difffrom t where rn > 1
)
select user_id, max(days_diff) AS max_day, min(days_diff) AS min_day
from t2 group by user_id;通过以上五个问题的解决，展示了 Hive 在不同数据处理场景下的强大功能和灵活性，包括行转列、多维度统计、访客分析和日期计算等方面。
