hiveSQL面试题6__数据扩充与收缩

0- 描述

表名：t6
表字段及内容：

a
3
2
4

1- 问题一

描述：数据扩充
输出结果如下所示：

a     b
3   3、2、1
2   2、1
4   4、3、2、1

参考答案：

select  
  t.a,
  concat_ws('、',collect_set(cast(t.rn as string))) as b
from
(  
  select  
    t6.a,
    b.rn
  from t6
  left join
  ( 
   select
     row_number() over() as rn
   from  
   (select split(space(5), ' ') as x) t -- space(5)可根据t6表的最大值灵活调整
   lateral view
   explode(x) pe
  ) b
  on 1 = 1
  where t6.a >= b.rn
  order by t6.a, b.rn desc 
) t
group by  t.a;

2- 问题二

描述：数据扩充，排除偶数
输出结果如下所示：

a     b
3   3、1
2   1
4   3、1

参考答案：

select  
  t.a,
  concat_ws('、',collect_set(cast(t.rn as string))) as b
from
(  
  select  
    t6.a,
    b.rn
  from t6
  left join
  ( 
   select
     row_number() over() as rn
   from  
   (select split(space(5), ' ') as x) t
   lateral view
   explode(x) pe
  ) b
  on 1 = 1
  where t6.a >= b.rn and b.rn % 2 = 1
  order by t6.a, b.rn desc 
) t
group by  t.a;

3- 问题三

问题描述：将小于等于a字段的值聚合拼接起来

输出结果如下所示：

a     b
3     2、3
2     2
4     2、3、4

参考答案：

select  
  t.a,
  concat_ws('、',collect_set(cast(t.a1 as string))) as b
from
(   
  select  
    t6.a,
    b.a1
  from t6
  left join
  (   
   select  a as a1 
   from t6
  ) b
  on 1 = 1
  where t6.a >= b.a1
  order by t6.a, b.a1 
) t
group by  t.a;

4- 问题四

描述：如果a字段有重复，如何实现字符串累计拼接

输出结果如下所示：

a     b
2     2
3     2、3
3     2、3、3
4     2、3、3、4

参考答案：

select 
  a,
  b
from 
(
 select  
   t.a,
   t.rn,
   concat_ws('、',collect_list(cast(t.a1 as string))) as b
 from
  (   
    select  
     a.a,
     a.rn,
     b.a1
    from
    (
     select  
       a,
       row_number() over(order by a ) as rn 
     from t6
    ) a
    left join
    (   
     select  a as a1,
     row_number() over(order by a ) as rn  
     from t6
    ) b
    on 1 = 1
    where a.a >= b.a1 and a.rn >= b.rn 
    order by a.a, b.a1 
  ) t
  group by  t.a,t.rn
  order by t.a,t.rn
) tt; 

5- 问题五

问题描述：如何将字符串"1-5,16,11-13,9"扩展成"1,2,3,4,5,16,11,12,13,9"？注意顺序不变。

参考答案：

select  
  concat_ws(',',collect_list(cast(rn as string)))
from
(
  select  
   a.rn,
   b.num,
   b.pos
  from
   (
    select
     row_number() over() as rn
    from (select split(space(20), ' ') as x) t -- space(20)可灵活调整
    lateral view
    explode(x) pe
   ) a lateral view outer 
   posexplode(split('1-5,16,11-13,9', ',')) b as pos, num
   where a.rn between cast(split(num, '-')[0] as int) and cast(split(num, '-')[1] as int) or a.rn = num
   order by pos, rn 
) t;