很有意思的hive SQL面试题1

题目描述

数据源是有a,b两列,然后根据b列的连续相同值,加一列序号

先准备测试数据

CREATE TABLE IF NOT EXISTS `test1`(
    yy int, nn int
)  ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'; 

insert into table test1 values(2010,1);
insert into table test1 values(2011,1);
insert into table test1 values(2012,1);
insert into table test1 values(2013,0);
insert into table test1 values(2014,0);
insert into table test1 values(2015,1);
insert into table test1 values(2016,1);
insert into table test1 values(2017,0);
insert into table test1 values(2018,0);
insert into table test1 values(2019,1);
insert into table test1 values(2020,1);
insert into table test1 values(2021,1);

然后想思路…

Long long time later…

提需求的人是*****吧(是自己太菜了…)

绞尽脑汁,有了一个思路

思路有了,SQL就很简单了…

select 
    t1.yy,t1.nn,
    yy+1-min(yy) over(partition by t1.nn,t1.nn1) as rn2
from
    (
    select
        yy,nn,
        row_number() over(order by yy) - row_number() over(partition by nn order by yy) as nn1
    from 
        test1
    )t1
order by yy