------ 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()分析函数不能混用,会导致数据重复