Hive刷题——explode关于数据扩充与收缩

标签：Hive explode num rn desc array select 刷题

有以下数据

select explode(array(2, 3, 4)) num;
结果
num
2
3
4

需求1、数据扩充：

输出结果如下所示：

4    1,4,3,2
3    1,3,2
2    1,2

参考实现

select t.num,concat_ws(',',collect_set(cast(t1.rn as string))) lists
from (select explode(array(2, 3, 4, 5)) num) t
         join
     (select row_number() over () rn
      from (select split(space(4), '') x) t lateral view explode(x) t as pe) t1
     on 1 > 0
where t.num >= t1.rn
group by t.num
order by 1 desc;

需求2、数据扩充时剔除偶数：

期望输出结果

5    4,2
4    4,2
3    2
2    2

参考实现

select num, concat_ws(',', collect_set(cast(rn as string))) list_nmum
from (select *
      from (select explode(array(2, 3, 4,5)) num) t
               join
           (select row_number() over () as rn
            from (select split(space(4), '') as x) t
                     lateral view explode(x) t as pe) b
           on 1 > 0
      where t.num >= b.rn
        and b.rn % 2 = 0
      order by t.num, b.rn desc) t
group by num
order by 1 desc;

标签：Hive,explode,num,rn,desc,array,select,刷题
From： https://www.cnblogs.com/wdh01/p/17073576.html