row_number函数

select id, brand,firstactivetime,
row_number() over (partition by id order by firstactivetime desc) as rank
from profile.device_state_accumulator_all
where date = 20190731
and activetags &1 = 1
and finalcountry='中国' 
and imsi is not null
and imsi not like '%[,]%'
and imsi != ''
and brand !='第三方机型' 
and brand !='小米平板'
and brand !='POCO F1' 
and brand like '%小米%'
and rank=1

Invalid table alias or column reference ‘rank’:

	select id, brand,firstactivetime,
row_number() over (partition by id order by firstactivetime desc) as rank
from profile.device_state_accumulator_all
where date = 20190731
and activetags &1 = 1
and finalcountry='中国' 
and imsi is not null
and imsi not like '%[,]%'
and imsi != ''
and brand !='第三方机型' 
and brand !='小米平板'
and brand !='POCO F1' 
and brand like '%小米%'
and row_number() over (partition by id order by firstactivetime desc) = 1

Invalid column reference ‘firstactivetime’

select id, brand,firstactivetime from
(select id, brand,firstactivetime,
row_number() over (partition by id order by firstactivetime desc) as rank
from profile.device_state_accumulator_all
where date = 20190731
and activetags &1 = 1
and finalcountry='中国' 
and imsi is not null
and imsi not like '%[,]%'
and imsi != ''
and brand !='第三方机型' 
and brand !='小米平板'
and brand !='POCO F1' 
and brand like '%小米%') ttt
where rank = 1

成功～～～