hive0.12版本分析函数的测试

江苏,223
江苏,88
江苏,1555
河南,352
河南,352
河南,600
河北,2000
河北,3000
江西,82
江西,111
山东,910
山东,910
山东,910
山西,99
山西,111
山西,99

create table test1
(name string,
id  int
)  row format delimited
fields terminated by ','
lines terminated by '\n'
stored as textfile;

LOAD DATA LOCAL INPATH '/home/aspire/fzm/sss/123.txt' OVERWRITE INTO TABLE  test1

SELECT name, id , row_number( ) over (partition by name order by id desc ) as rn from test1

SELECT name, id ,  rank( ) over (partition by name order by id desc ) as rn from test1

SELECT name, id ,  dense_rank( ) over (partition by name order by id desc ) as rn from test1

SELECT  name,   id   , row_number( ) over  ( order by  id  desc )   as  rn  from  test1

SELECT  name,   id   , rank( ) over  ( order by  id  desc )   as  rn  from  test1

SELECT  name,   id   , dense_rank( ) over  ( order by  id  desc )   as  rn  from  test1

SELECT name, id , row_number( ) over (distribute by name sort by  id desc ) as rn from test1

SELECT name, id ,  rank( ) over (distribute by name sort by  id desc ) as rn from test1

SELECT name, id ,  dense_rank( ) over (distribute by name sort by  id desc ) as rn from test1