一道经典hive面试题输入 1,2,4,5,6,8,9 输出能连续上的搞在一起用hive解决 1,2 4,5,6 8,9

本文链接：https://blog.youkuaiyun.com/zwmonk/article/details/106603054

这是一篇关于如何使用Hive处理经典面试题的博客，题目要求将连续的数字合并。通过创建表、插入数据，然后利用窗口函数和集合函数解决此问题。在MySQL中，使用类似的方法，结合GROUP_CONCAT和变量来实现相同的功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

经典hive面试题
输入
1,2,4,5,6,8,9
输出就是能连续上的搞在一起用hive解决
1,2
4,5,6
8,9
直接进入主题：
建表CREATE TABLE people(
number varchar(256),
id int,
pt_d int)
ROW FORMAT SERDE
‘org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe’
STORED AS INPUTFORMAT
‘org.apache.hadoop.mapred.TextInputFormat’
OUTPUTFORMAT
‘org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat’
LOCATION
‘hdfs://centos01:9000/user/hive/warehouse/people’
插入数据：insert into people values(‘1,2,4,5,6,8,9’,1,20200607)；
查看是否插入成功：
在这里插入图片描述
执行如下语句
SELECT
collect_set(CONCAT_WS(’,’,ranknum)) AS ranknum
FROM
(SELECT
id
,ranknum
,ranknum - next_ranknum AS dif
FROM
(
SELECT
id
,ranknum
,ROW_NUMBER() OVER(PARTITION BY id ORDER BY ranknum) AS next_ranknum
FROM
(
SELECT
id
,ranknum
FROM people LATERAL VIEW explode(split(number,’,’))rank AS ranknum
) t
) tt
) ttt
group by dif

在这里插入图片描述

同样的在mysql中如何实现呢？
SELECT
group_concat(val) as val
FROM
(
select
val
,rank-val AS dif
FROM
(select
val
,@num:= @num + 1 AS rank
from
(select substring_index(substring_index(a.address,’,’,b.help_topic_id),’,’,-1) AS val
from
people a
join
mysql.help_topic b
on b.help_topic_id <= (length(a.address) - length(replace(a.address,’,’,’’))+1)
) c
,(select @num:=0)d
order by rank
) e WHERE val is NOT NULL AND val!=’’
) f
group by dif
order by dif desc
结果如图：
在这里插入图片描述