[hive]collect_set排序问题

1、原始表

select zheng_shi_indicator,guo_biao_di_yu_dai_ma,areas,years from test.test_formal_edu

 2、需求

结果格式：

第一列：正式指标   

第二列：该指标下的所有地区名称,按照国标地域代码升序排序，按照";"进行分割，且该字段不为空时，末尾追加";"。

第三列：该指标下所有的年份,按字母顺序排序，按照";"进行分割，且该字段不为空时，末尾追加";"。

3、思路

处理年份很简单collect_set+sort_array就行了

处理地区，难在要按照地区代码的顺序将地区名称进行排序，可以使用下面的语句：

在over中按照地区代码进行排序，然后在collect_set中把排好顺序的数据收集起来。

collect_set(areas) 
over(partition by zheng_shi_indicator 
order by guo_biao_di_yu_dai_ma asc 
rows between unbounded preceding and current row) areas_sort

但是结果会是这样的：

  即：collect_set(a) over(partition by b order by c) 集合中的数据a会按照 c列的顺序 按行累加

如何处理这个问题呢？本身也是需要按照指标进行去重的，在这里我们选择最长的一条进行去重，就可以解决问题了，最长的那条涵盖了所有的数据。

4、实现

insert overwrite table test.test_formal_edu_res1
select
zheng_shi_indicator,
areas_str,
year_str
from
(
    select
    zheng_shi_indicator,
    areas_str,
    year_str,
    row_number() over (partition by zheng_shi_indicator order by length(areas_str) desc) rn
    from
    (
        select
        zheng_shi_indicator,
        if(areas_str!="",concat(areas_str,";"),areas_str)areas_str,
        if(year_str!="",concat(year_str,";"),year_str)year_str
        from
        (
            select
            zheng_shi_indicator,
            concat_ws(";",areas_sort)areas_str,
            concat_ws(";",year_sort)year_str
            from
            (
                select
                zheng_shi_indicator,
                areas_sort,
                sort_array(years_set) year_sort
                from
                (
                    select
                    zheng_shi_indicator,
                    collect_set(areas) over(partition by zheng_shi_indicator order by guo_biao_di_yu_dai_ma asc rows between unbounded preceding and current row) areas_sort,
                    collect_set(years) over(partition by zheng_shi_indicator) years_set
                    from
                    test.test_formal_edu
                    where nian_jian_zhong_wen_ming="中国教育统计年鉴"
                )t1
            )t2
        )t3
    )t4
)t5
where rn=1
;