你可以分解数组，计算 row_number ，然后将重复的元素（行数大于1）聚合到集合中：

with initial_data as (
select 1 id   ,array("1","2","2","3","3") list union all
select 2   ,array("2","2","5","6") list union all    
select 3   ,array("2","4","5","6")  
)    
select s.id, s.list, collect_set(case when s.rn>1 then x end) duplicate_set 
from(
select s.id, s.list, l.x, row_number() over(partition by id, l.x) as rn
  from initial_data s
       lateral view explode(list) l as x --array element x
) s
group by s.id, s.list;

结果：

id      list                    duplicate_set
1       ["1","2","2","3","3"]   ["2","3"]
2       ["2","2","5","6"]       ["2"]
3       ["2","4","5","6"]       []