既想聚合count(1) over(partition by a
2020-06-30 本文已影响0人
大道至简_6a43
select
concat(act_dep_city,'_',act_arr_city) as key,
concat(round(cast(city_line_num as float)/cast(city_all_num as float)*100,1),'%','-',rn) as value
from(
select
act_dep_city,
act_arr_city,
city_all_num,
count(1) as city_line_num,
row_number() over(partition by act_arr_city order by count(1) desc) as rn
from(
select
act_dep_city,
act_arr_city,
count(1) over(partition by act_arr_city) as city_all_num
from
f_wide.wide_order where dt='20200530'
)A group by act_dep_city,act_arr_city,city_all_num
)A
