hive--如何保证concat_ws内部有序

• 大概思路
  • dense_rank()先排序，得到序号
  • 把序号和字段拼接concat_ws
  • sort_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 
;

• 有朋友问为什么*1，随手写了个临时表，大家一眼就能看出来为什么了～

drop table if exists bi_temp.hzy_20220825;
create table bi_temp.hzy_20220825 as 
select '1' as a 
union all
select '2' as a
union all
select '3' as a
union all
select '11' as a
union all
select '22' as a
union all
select '33' as a
;

select *
	,dense_rank() over (order by a) as rank
from  bi_temp.hzy_20220825 
;
a	rank	
1	1
11	2
2	3
22	4
3	5
33	6


select *
	,dense_rank() over (order by a*1) as rank
from  bi_temp.hzy_20220825 
;
a	rank	
1	1
2	2
3	3
11	4
22	5
33	6