hive--如何保证concat

大概思路

dense_rank()先排序，得到序号把序号和字段拼接concat_wssort_array再concat_ws把数字替换掉

select  m.city_id                                                                                                                  as city_code
       ,m.class_id                                                                                                                 as ct_class_id  
       ,regexp_replace(concat_ws(',',sort_array(collect_set(concat_ws(':',cast(row_id as string),tea_id)))),'\d+:','')           as tea_ids
       ,regexp_replace(concat_ws(',',sort_array(collect_set(concat_ws(':',cast(row_id as string),tea_teacher_name)))),'\d+:','') as tea_teacher_names
from
(
    select  tct.city_id
           ,tct.class_id  
           ,tet.tea_id
           ,tet.tea_teacher_name
           ,tet.tea_emp_no
           ,tet.tea_teacher_code
           ,dense_rank() over (partition by tct.city_id,tct.class_id order by nvl(tet.tea_emp_no,tet.tea_teacher_code)*1) as row_id
    from class_teacher tct
    inner join teacher tet
        on tct.city_id=tet.city_id 
        and tct.teacher_id=tet.tea_id
) m
group by  m.city_id
         ,m.class_id 
;