Hive实战案例(一)：利用row_number()计算连续值

Hive实战案例(一)：利用row_number()计算连续值

题目一

题目描述

---

求出连续7天登录的用户

数据内容

---

1 2019-07-11 1
1 2019-07-12 1
1 2019-07-13 1
1 2019-07-14 1
1 2019-07-15 1
1 2019-07-16 1
1 2019-07-17 1
1 2019-07-18 1
2 2019-07-11 1
2 2019-07-12 1
2 2019-07-13 0
2 2019-07-14 1
2 2019-07-15 1
2 2019-07-16 0
2 2019-07-17 1
2 2019-07-18 0
3 2019-07-11 1
3 2019-07-12 1
3 2019-07-13 1
3 2019-07-14 0
3 2019-07-15 1
3 2019-07-16 1
3 2019-07-17 1
3 2019-07-18 1

准备工作

---

– 建表语句
create table ulogin(
uid int,
dt date,
status int
)
row format delimited fields terminated by ’ ';

– 加载数据
load data local inpath ‘/home/hadoop/data/ulogin.dat’ into
table ulogin;

解题思路

---

连续值的求解，面试中常见的问题。这也是同一类，基本都可按照以下思路进行：
1、使用 row_number 在组内给数据编号(rownum)
2、某个值 - rownum = gid，得到结果可以作为后面分组计算的依据
3、根据求得的gid，作为分组条件，求最终结果

要判断数据在某个字段上连续，则可以利用row_number()函数进行处理。因为该函数对每行数据打上连续的行坐标，此时利用待判断的字段值与行号进行相减，这样如果是连续值-连续值，则它们的差值是相等的。比如：

10 - 1 = 9
11 - 2 = 9
12 -3 = 9
....

此时，如果要计算题目中连续7天登陆的用户，即可以利用id与该差值字段共同作为分组的key，然后利用聚合函数 count(*) 来统计组内个数，如果等于7的话，就说明该用户连续登陆了七天。

代码示例

---

select uid, count(*) logincount
    from (select uid, dt,
                            date_sub(dt, row_number() over (partition by
uid order by dt)) gid
                    from ulogin
                where status=1) t1
group by uid, gid
having logincount>=7;

---

题目二

题目描述

---

找出下列数据中全部夺得3连贯的队伍

数据内容

team,year
活塞,1990
公牛,1991
公牛,1992
公牛,1993
火箭,1994
火箭,1995
公牛,1996
公牛,1997
公牛,1998
马刺,1999
湖人,2000
湖人,2001
湖人,2002
马刺,2003
活塞,2004
马刺,2005
热火,2006
马刺,2007
凯尔特人,2008
湖人,2009
湖人,2010

准备工作

---

-- 创建数据表
create table t1(
team string,
year int
)row format delimited fields terminated by ',';

-- 加载本地数据
load data local inpath "/root/data/t1.dat" into table t1;

解题思路

---

解题思路同上。由于要找到三连冠的队伍，即同样可以转换成对于连续值求解的问题。利用row_number() 函数对行标号，然后利用年份值与行号进行相减，这样，如果几条连续年份的记录相减后得到的值是相等的。因此就可以利用row_number()函数，其中利用over函数进行开窗， 按照team字段进行分区，按照year字段进行排序。然后将year列与row_number()得到的值相减，这样的话，如果在数据上时间连续，则相减后得到的差值应该是相等的。因此就可以对得到的结果，按照team字段和差值进行分组，并利用聚合函数count(*)计算组内数据的个数，如果个数为3，说明该队伍就是3连贯的队伍

代码示例

---

with tmp as (
    select team, year, year - row_number() over(partition by team order by year) num
    from t1
)

select team
from tmp
group by team, num
having count(*)=3