查询连续三天有销售记录的店铺!

需求:求连续N销售记录的店铺:

字段分别为:店铺名:订单时间:价格

a,2017-02-05,200
a,2017-02-06,300
a,2017-02-07,200
a,2017-02-08,400
a,2017-02-10,600
b,2017-02-05,200
b,2017-02-06,300
b,2017-02-08,200
b,2017-02-09,400
b,2017-02-10,600
c,2017-01-31,200
c,2017-02-01,300
c,2017-02-02,200
c,2017-02-03,400
c,2017-02-10,600
a,2017-03-01,200
a,2017-03-02,300
a,2017-03-03,200
a,2017-03-04,400
a,2017-03-05,600

在Hive中建立对应的表

create  table  tb_shop (
name string ,
dt string ,
money double 
)
row format delimited fields terminated by "," ;
load data local inpath "/hive/data/shop.txt"  into table tb_shop ;

建表之后全表检索:

 

第一步:

先按店铺名分组 再按照日期排序 

select* ,row_number() over(partition by name order by dt) nfromtb_shop