当前位置:首页 > 程序 > 正文

SQL进阶 面试常见的4种sql算法考试题(一):多列转多行

2019-09-24 09:09 点击:6次 作者:biucz 我来投稿

SQL进阶 面试常见的4种sql算法考试题(一):多列转多行

近期在不同群里有小伙伴们提出了一些在面试和笔试中遇到的Hive SQL问题,Hive作为算法工程师的一项必备技能,在面试中也是极有可能被问到的,所以有备无患,本文将对这四道题进行详细的解析,还是有一定难度的,希望你看完本文能够有所收获。

多列转多行

第一道题目是这样的:

假设现有一张Hive表,
元数据格式为:

字段:
id    stirng
tim    string

数据格式如下:
a,b,c,d 2:00,3:00,4:00,5:00
f,b,c,d    1:10,2:20,3:30,4:40

需要变成:
a     2:00
b     3:00
c     4:00
d     5:00

这道题目是需要把多行转换成多行,有点类似python里面的zip操作。大伙应该都知道hive里有一个常用的一行转多行的函数叫explode,假设有如下的数据:

a,b,c,d 2:00,3:00,4:00,5:00
f,b,c,d    1:10,2:20,3:30,4:40

按照第二列explode的话,使用下面的SQL:

select
 id,tim,single_tim
from
 default.a1
 lateral view explode(split(tim,',')) t as single_timxk

效果如下:

SQL进阶 面试常见的4种sql算法考试题(一):多列转多行

但这道题目里,需要对两列同时进行explode,如果只进行简单的explode,效果如下:

SQL进阶 面试常见的4种sql算法考试题(一):多列转多行

这样一行变成了16行,而我们仅仅需要的是其中能够对齐下标的四行。所以在进行explode的时候,我们期望不仅仅能够能够获得数组里的每个值,还希望能够得到其对应的下标,这样在对两列同时进行explode的时候,保留数组下标相同的四行就可以了。这里我们会用到posexplode函数。

posexplode函数跟explode函数的使用方法类似,看下面的例子:

select
 id,tim,single_id_index,single_id
from
 default.a1
 lateral view posexplode(split(id,',')) t as single_id_index,single_id

返回的结果为:

SQL进阶 面试常见的4种sql算法考试题(一):多列转多行

应用到本题,只需要应用两次posexplode函数,再通过where留下两个index相等的行就可以了,按照这个思路,sql如下:

select
 id,tim,single_id,single_tim
from
 default.a1
 lateral view posexplode(split(id,',')) t as single_id_index,single_id
 lateral view posexplode(split(tim,',')) t as single_tim_index,single_tim
where
 single_id_index = single_tim_index

结果正是我们想要的:

SQL进阶 面试常见的4种sql算法考试题(一):多列转多行

推荐阅读:

SQL进阶 面试常见的4种sql算法考试题(一):多列转多行

SQL进阶 面试常见的4种sql算法考试题(二):排序后相邻两行均值

SQL进阶 面试常见的4种sql算法考试题(三):获取字符串索引列表

SQL进阶 面试常见的4种sql算法考试题(四):分块排序