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;