row_number排序

对指定数据排序后row_number
create table tmp.test_row_number as
select ‘115’ as task_id,‘3’ as operation_id,‘0’ as operation_order union all
select ‘115’ as task_id,‘1’ as operation_id,‘1’ as operation_order union all
select ‘115’ as task_id,‘2’ as operation_id,‘2’ as operation_order union all
select ‘115’ as task_id,‘4’ as operation_id,‘3’ as operation_order union all
select ‘115’ as task_id,‘5’ as operation_id,‘4’ as operation_order union all
select ‘115’ as task_id,‘9’ as operation_id,‘5’ as operation_order union all
select ‘115’ as task_id,‘6’ as operation_id,‘6’ as operation_order union all
select ‘115’ as task_id,‘7’ as operation_id,‘7’ as operation_order union all
select ‘115’ as task_id,‘11’ as operation_id,‘8’ as operation_order union all
select ‘115’ as task_id,‘12’ as operation_id,‘9’ as operation_order union all
select ‘115’ as task_id,‘13’ as operation_id,‘10’ as operation_order union all
select ‘115’ as task_id,‘14’ as operation_id,‘11’ as operation_order union all
select ‘115’ as task_id,‘15’ as operation_id,‘12’ as operation_order union all
select ‘115’ as task_id,‘18’ as operation_id,‘13’ as operation_order union all
select ‘115’ as task_id,‘16’ as operation_id,‘14’ as operation_order union all
select ‘115’ as task_id,‘8’ as operation_id,‘15’ as operation_order union all
select ‘115’ as task_id,‘10’ as operation_id,‘16’ as operation_order union all
select ‘115’ as task_id,‘17’ as operation_id,‘17’ as operation_order union all
select ‘115’ as task_id,‘19’ as operation_id,‘18’ as operation_order union all
select ‘115’ as task_id,‘20’ as operation_id,‘19’ as operation_order;

select t.task_id as task_id
,concat_ws(‘,’, collect_list(t.task_id_rank) over(partition by t.task_id order by t.rn)) as operation_id
,row_number() over(PARTITION BY t.task_id ORDER BY t.rn desc ) as rn
from
(
select task_id
,concat(task_id,‘:’,operation_id) as task_id_rank
,row_number() over(partition by task_id order by CAST(operation_order AS INT) asc) as rn
,operation_order as operation_order
from tmp.test_row_number
) t

select a.task_id as task_id
,a.operation_id as operation_id
from (
select t.task_id as task_id
,concat_ws(‘,’, collect_list(t.task_id_rank) over(partition by t.task_id order by t.rn)) as operation_id
,row_number() over(PARTITION BY t.task_id ORDER BY t.rn desc ) as rn
from
(
select task_id
,concat(task_id,‘:’,operation_id) as task_id_rank
,row_number() over(partition by task_id order by CAST(operation_order AS INT) asc) as rn
,operation_order as operation_order
from tmp.test_row_number
) t
) a where a.rn = 1;