hive 分析函数缺陷

 
------  table(1)  数据全集
use databasename;
drop table if exists databasename.Tmp_CiiFcst_hotel_Cii_up_sample_1020;
create  table  databasename.Tmp_CiiFcst_hotel_Cii_up_sample_1020 as
select a.*,b.city
from
(select *
from databasename.CiiFcst_hotel_Cii_basetable
where d='2016-08-29') a
inner join 
(select distinct masterhotel, city  from databasename.CiiFrcst_Hotel_Info  ---有重复的数据
where d='2016-10-19') b on a.hotelid=b.masterhotel
where b.city in (375
,28
,258
,38
,660
,3996
,3997
,4004
,4029
,4125
,4130
,4131
,4137
,4139
,4144);


--- select hotelid, count(*)
--- from databasename.Tmp_CiiFcst_hotel_Cii_up_sample_1020
--- group by hotelid;
------   各组均为31
------ table (2)
---- distribute by, sort by
use databasename;
drop table if exists databasename.Tmp_CiiFcst_hotel_Cii_up_sample_1020_1;
create  table  databasename.Tmp_CiiFcst_hotel_Cii_up_sample_1020_1 as
select a.*, 
rank()over(partition by hotelid order by starttime asc) rank   ----- 用row_num()会导致重复，为何？ 
---,sum(notcancelcii)over(partition by hotelid) /sum(notcancelcii)over() r1, round(100000*notcancelcii/sum(notcancelcii)over()) cnt
from databasename.Tmp_CiiFcst_hotel_Cii_up_sample_1020 a;
 
 
 
Hive bug: rank()over() 与row_num()over() 与 sum()over()分析函数不能混用，会导致数据重复