用Hive统计某个年阶段连接夺冠的NBA球队

用Hive统计某个年阶段连接夺冠的NBA球队

1.原始数据如下：`在这里插入
team,year

活塞,1990
公⽜,1991
公⽜,1992
公⽜,1993
⽕箭,1994
⽕箭,1995
公⽜,1996
公⽜,1997
公⽜,1998
⻢刺,1999
湖⼈,2000
湖⼈,2001
湖⼈,2002
⻢刺,2003
活塞,2004
⻢刺,2005
热⽕,2006
⻢刺,2007
凯尔特⼈,2008
湖⼈,2009
湖⼈,2010`

2. 保存到本地:
3. 创建表`在这里插

create table charm2(
team string, 
year string
)row format delimited fields terminated by ',';`

4.导入数据

load data local inpath "/opt/d1.dat" into table charm2

5. 最终的结果：

select team,t2.rank,t2.res from (SELECT team,year,lag(year) OVER (PARTITION BY team ORDER BY year) last_year,row_number() OVER (PARTITION BY team ORDER BY year) rank,year-lag(year) OVER (PARTITION BY team ORDER BY year) res from charm2) t2 where t2.rank >=3 and t2.res=1;

team	t2.rank	t2.res
公⽜	     3	      1.0
公⽜	     5	      1.0
公⽜	     6	      1.0
湖⼈	     3	      1.0
湖⼈	     5	      1.0

从结果上看，公牛和湖人连续三年得到冠军，可以扩展一下：连续三连冠的次数是哪个队伍呢？后续可以补充。