大数据私房菜--Hive实战案例
程序员文章站
2022-04-10 23:40:21
...
1 实战准备
1.1 创建homework数据库
-- create databases homework
create database if not exists homework;
2 创建hive表
创建以TEXTFILE格式存储的hive表作为中间表，将数据先导入到textfile格式中，再从表中用insert导入目标表（以ORC存储的hive表）
创建中间表：
-- create table homework.t1_ori stored as TEXTFILE
-- 创建homework.t1_ori存储为TEXTFILE
create table homework.t1_ori(
team string,
year int
)row format delimited fields terminated by ',';
-- create table homework.t2_ori stored as TEXTFILE
-- 创建homework.t2_ori存储为TEXTFILE
create table homework.t2_ori(
id string,
time string,
price double
)row format delimited fields terminated by ',';
-- create table homework.t3_ori stored as TEXTFILE
-- 创建homework.t3_ori存储为TEXTFILE
create table homework.t3_ori(
id string,
dt string,
browseid string
)row format delimited fields terminated by '\t';
创建ORC数据表
-- t1
-- 创建homework.t1存储为ORC
create table homework.t1(
team string,
year int
)stored as orc;
--t2
-- 创建homework.t2存储为ORC
create table homework.t2(
id string,
time string,
price double
)stored as orc;
--t3
-- 创建homework.t3存储为ORC
create table homework.t3(
id string,
dt string,
browseid string
)stored as orc;
show tables查看是否创建成功：
3 load数据
--加载数据到中间表
load data local inpath "/root/data/t1.dat" overwrite into table t1_ori;
load data local inpath "/root/data/t2.dat" overwrite into table t2_ori;
load data local inpath "/root/data/t3.dat" overwrite into table t3_ori;
-- 导入数据到目标表
insert into table t1 select * from t1_ori;
insert into table t2 select * from t2_ori;
insert into table t3 select * from t3_ori;
加载数据
导入orc表
验证数据
4 需求一
需求：找出全部夺得3连贯的队伍
team,year
活塞,1990
公牛,1991
公牛,1992
公牛,1993
火箭,1994
火箭,1995
公牛,1996
公牛,1997
公牛,1998
马刺,1999
湖人,2000
湖人,2001
湖人,2002
马刺,2003
活塞,2004
马刺,2005
热火,2006
马刺,2007
凯尔特人,2008
湖人,2009
湖人,2010
4.1 需求分析
需求一为连续值求解问题，求解出连续3次夺冠的队伍
--1、使用row_number在组内给数据排序获得row_number
--2、通过year - rownum = gid，根据gid作为分组条件
--3、根据求得的gid，作为分组条件，求最终结果
4.2 实现一
-- 1.获取基表
select team, year, ROW_NUMBER() over(partition by team order by year) rank from t1 t
team|year|rank
----|----|----
公牛 |1991| 1
公牛 |1992| 2
公牛 |1993| 3
公牛 |1996| 4
公牛 |1997| 5
公牛 |1998| 6
凯尔特人|2008| 1
活塞 |1990| 1
活塞 |2004| 2
湖人 |2000| 1
湖人 |2001| 2
湖人 |2002| 3
湖人 |2009| 4
湖人 |2010| 5
火箭 |1994| 1
火箭 |1995| 2
热火 |2006| 1
马刺 |1999| 1
马刺 |2003| 2
马刺 |2005| 3
马刺 |2007| 4
-- 2.通过将year-rank获得gid
select t.*, t.year - t.rank gid from(
select team, year, ROW_NUMBER() over(partition by team order by year) rank from t1
)t
team|year|rank|gid |
----|----|----|----|
公牛 |1991| 1|1990
公牛 |1992| 2|1990
公牛 |1993| 3|1990
公牛 |1996| 4|1992
公牛 |1997| 5|1992
公牛 |1998| 6|1992
凯尔特人|2008| 1|2007
活塞 |1990| 1|1989
活塞 |2004| 2|2002
湖人 |2000| 1|1999
湖人 |2001| 2|1999
湖人 |2002| 3|1999
湖人 |2009| 4|2005
湖人 |2010| 5|2005
火箭 |1994| 1|1993
火箭 |1995| 2|1993
热火 |2006| 1|2005
马刺 |1999| 1|1998
马刺 |2003| 2|2001
马刺 |2005| 3|2002
马刺 |2007| 4|2003|
-- 3.通过将gid和team分组计数，再通过having筛选 count >= 3 的队伍即可筛选出连续夺冠三年的队伍
select distinct g.team from(
select tb.team from(
select t.*, t.year - t.rank gid from(
select team, year,
ROW_NUMBER() over(partition by team order by year) rank from t1
)t
)tb
group by tb.team, tb.gid
having count(1) >=3
)g
数据验证如下：
4.3 实现二
-- 获取连续三年夺冠球队的夺冠日期
-- 通过row_number生成gid作为基表
-- 通过team，gid统计最小year为开始夺冠日期，最大year为最后夺冠日期并筛选count(1) >= 3
select g.team, min(year) first, max(year) last from (
select t.*, t.year - t.rank gid from(
select team, year,
ROW_NUMBER() over(partition by team order by year) rank from t1
)t) g
group by g.team, g.gid
having count(1) >=3
通过SQL实现获取连续夺冠的球队，数据验证如下：
5 需求二
需求：算出每个id在在一天之内所有的波峰与波谷值
最终结果与此类似：
id time price feature
sh66688 10:05 27.09 波峰
sh66688 10:15 26.11 波谷
sh66688 10:25 27.49 波峰
sh66688 10:30 26.7 波谷
sh66688 10:40 28.26 波峰
sh66688 10:55 26.48 波谷
sh66688 11:00 27.41 波峰
sh66688 11:05 26.7 波谷
5.1 需求分析
要计算每个id一天之内的price的波峰与波谷，只需要将id的信息按照时间进行排序之后，错峰排序，每一个时间段与之前的时间段进行比较即可判断出波峰与波谷。
5.2 实现一
通过将基础数据分组排序（升序）之后获取row_number，通过将基础表关联上基础表，关联条件为row_number=row_number - 1 实现错峰排序，然后判断price是涨高还是降低，即可判断出波峰与波谷。
SQL实现如下：
-- 通过将time时间字符串转换为时间戳，对时间戳time2进行分组排序获取rn
-- 通过rn将实现基础表自连接，获取当前值交易值、上一时间交易值、下一时间交易值
-- 通过当前交易值与上一时间和下一时间的交易值进行比较获取波峰和波谷的判断
-- 最好筛选出波峰与波谷的交易信息即可
with tb as (select tb.id, tb.time, tb.price,
ROW_NUMBER () OVER (partition by id order by time2) rn from (
select t.id, t.time , unix_timestamp(t.time , "hh:mm") time2 , t.price from t2 t
) tb)
select
a.id, a.time, a.price,
case when a.price>b.price and a.price>c.price then "波峰"
when a.price<b.price and a.price<c.price then "波谷"
else "other" end feature
from tb a left join tb b on a.rn=b.rn+1 and a.id=b.id
left join tb c on a.rn=c.rn-1 and a.id=c.id
where case when a.price>b.price and a.price>c.price then "波峰"
when a.price<b.price and a.price<c.price then "波谷"
else "other" end not like "other"
结果验证：
5.3 实现二
上一种方法是通过数据连接的方法实现获取当前交易的上一交易值和下一交易值，方法二我们通过序列函数：lag函数和lead函数获取数据行的上一行数据和下一行数，再通过波峰和波谷的逻辑进行判断，具体实现如下：
with tb as (
select tb.id, tb.time, tb.price,
lag(tb.price) over(partition by tb.id order by tb.time2) lagprice,
lead(tb.price) over(partition by tb.id order by tb.time2) leadprice
from (
select t.id, t.time , unix_timestamp(t.time , "hh:mm") time2 , t.price from t2 t
) tb)
select
a.id, a.time, a.price,
case when a.price>a.lagprice and a.price>a.leadprice then "波峰"
when a.price<a.lagprice and a.price<a.leadprice then "波谷"
else "other" end feature
from tb a
where case when a.price>a.lagprice and a.price>a.leadprice then "波峰"
when a.price<a.lagprice and a.price<a.leadprice then "波谷"
else "other" end not like "other";
数据验证：
6 需求三
**需求3-1：**每个id浏览时长、步长
6.1 需求3-1分析
t3表结构为：
create table homework.t3(
id string,
dt string,
browseid string
)stored as orc;
从建表语句上看，浏览时间dt为时间字符串，id为用户id。
每个id的浏览时长为每个id的最大浏览时间 - 每个id的最小浏览时间，利用max(dt) - min(dt),又由于是时间字符串，我们先把时间字符串转为时间戳，通过对时间戳进行相间等到时间间隔。
每个id的浏览步长为每个id的浏览页面的次数总和，利用count实现即可。
具体实现如下
with tb as (
select t.*,
unix_timestamp(t.dt, 'yyyy/MM/dd hh:mm') dt2
from t3 t
)
select id,
count(1) count,
(max(dt2) - min(dt2)) / 60 as timelen_min
from tb
group by id
数据验证：
6.2 需求3-2分析
**需求3-2：**如果两次浏览之间的间隔超过30分钟，认为是两个不同的浏览时间；再求每个id浏览时长、步长
需要根据数据本身进行分组，思路大致如下：
一、获取数据基础表
需要根据相邻两次浏览记录判断分组点，我们通过序列函数对数据进行错位，SQL如下：
select
t.id,
t.dt,
lag(t.dt) over(partition by t.id order by t.dt) dt2
from t3 t
二、获取切分点
根据基础表，将dt时间与上一个记录的时间dt2相减计算出时间间隔tl，通过case…when…语句判断是否为切分点，将切分点置一个mark为1
SQL如下：
with ta as (
select
t.id,
t.dt,
lag(t.dt) over(partition by t.id order by t.dt) dt2
from t3 t
)
select t.id,t.dt,t.dt2,
(unix_timestamp(t.dt , "YYYY/MM/dd hh:mm") - unix_timestamp(t.dt2 , "YYYY/MM/dd hh:mm"))/60 tl,
case when (unix_timestamp(t.dt , "YYYY/MM/dd hh:mm") - unix_timestamp(t.dt2 , "YYYY/MM/dd hh:mm"))/60 >= 30 then 1 else 0 end mark
from ta t
三、实现切分点分组
利用over函数并且在分区内排序之后使用窗口进行累加，累加窗口为第一行到当前行，这样就按照切分点进行分区，第一个分区的mark为0，第二分区的mark为1，第三个分区mark为3…如此类推
SQL实现如下：
with ta as (
select
t.id,
t.dt,
lag(t.dt) over(partition by t.id order by t.dt) dt2
from t3 t
),
tb as (
select t.id,t.dt,t.dt2,
(unix_timestamp(t.dt , "YYYY/MM/dd hh:mm") - unix_timestamp(t.dt2 , "YYYY/MM/dd hh:mm"))/60 tl,
case when (unix_timestamp(t.dt , "YYYY/MM/dd hh:mm") - unix_timestamp(t.dt2 , "YYYY/MM/dd hh:mm"))/60 >= 30 then 1 else 0 end mark
from ta t
)
select
t.id,
t.dt,
t.dt2,
t.tl,
sum(t.mark) over(partition by t.id order by t.dt rows BETWEEN unbounded preceding and current row) as mark
from tb t
四、汇总统计
在获取了每个分区的标识之后，我们对数据进行汇总，获取每个用户id每一段浏览记录（大于30分钟）的时间间隔和步长。
SQL整体实现：
with ta as (
select
t.id,
t.dt,
lag(t.dt) over(partition by t.id order by t.dt) dt2
from t3 t
),
tb as (
select t.id,t.dt,t.dt2,
(unix_timestamp(t.dt , "YYYY/MM/dd hh:mm") - unix_timestamp(t.dt2 , "YYYY/MM/dd hh:mm"))/60 tl,
case when (unix_timestamp(t.dt , "YYYY/MM/dd hh:mm") - unix_timestamp(t.dt2 , "YYYY/MM/dd hh:mm"))/60 >= 30 then 1 else 0 end mark
from ta t
),
tc as (select
t.id,
t.dt,
t.dt2,
t.tl,
sum(t.mark) over(partition by t.id order by t.dt rows BETWEEN unbounded preceding and current row) as mark
from tb t)
select t.id,
(unix_timestamp(max(t.dt) , "YYYY/MM/dd hh:mm") - unix_timestamp(min(t.dt), "YYYY/MM/dd hh:mm"))/60 tl,
count(1) count
from tc t
group by t.id,t.mark