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

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

• 有朋友说要 用 lapd 左补足
  • 具体还是要看你的业务场景，左补足也行
    • lpad(row_number() over (partition by tct.city_id,tct.class_id order by nvl(tet.tea_emp_no,tet.tea_teacher_code)*1) ,10,‘0’) as row_id
  • 函数不熟悉的，可以参考这篇hive函数