算法工程师面试-Hive面试及笔试常见试题解析

Hive是基于Hadoop的一个数据仓库工具,可以将结构化的数据文件映射为一张数据库表,并提供类SQL查询功能。

Hive作为算法工程师的一项必备技能,在面试和笔试中会遇到的Hive SQL问题,本文就Hive在面试中常常会被问到的四个问题进行详细的解析。

1、多列转多行

第一道题目是这样的:

假设现有一张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_tim

效果如下:

算法工程师面试-Hive面试及笔试常见试题解析-我爱自学

 

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

算法工程师面试-Hive面试及笔试常见试题解析-我爱自学

 

这样一行变成了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

返回的结果为:

算法工程师面试-Hive面试及笔试常见试题解析-我爱自学

 

应用到本题,只需要应用两次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

结果正是我们想要的:

算法工程师面试-Hive面试及笔试常见试题解析-我爱自学

2、排序后相邻两行均值

第二题的原始数据如下:

算法工程师面试-Hive面试及笔试常见试题解析-我爱自学

 

要求如下:

算法工程师面试-Hive面试及笔试常见试题解析-我爱自学

 

分组排序想必大家都知道使用row_number()函数,但要找到同组前一行的值,可能有许多同学不太了解,这里是用的是lead/lag函数,两个函数用法如下:

lag(字段名,N) over(partition by 分组字段 order by 排序字段 排序方式)

lead(字段名,N) over(partition by 分组字段 order by 排序字段 排序方式)

简单解释一下:

lag括号里理由两个参数,第一个是字段名,第二个是数量N,这里的意思是,取分组排序之后比该条记录序号小N的对应记录的指定字段的值,如果字段名为ts,N为1,就是取分组排序之后上一条记录的ts值。

lead括号里理由两个参数,第一个是字段名,第二个是数量N,这里的意思是,取分组排序之后比该条记录序号大N的对应记录的对应字段的值,如果字段名为ts,N为1,就是取分组排序之后下一条记录的ts值。

如果没有前一行或者后一行,对应的字段值为null。

所以,这里我们应该使用的是lag函数,来获取同组排序后前一行数据对应字段的值,SQL如下:

select
 year,chr,if(pre_val is null,val,(val + pre_val) / 2.0) as avg_val
from
(
select
 year,chr,val,
 lag(val,1) over(partition by year order by chr asc) as pre_val
from
 default.a2
) a

注意这里的一个小细节,如果分组后数据排在第一位,它是没有前一个数的,此时数仍保持原样,所以这里加了一个if判断,结果符合预期:

算法工程师面试-Hive面试及笔试常见试题解析-我爱自学

3、获取字符串索引列表

第三题的题目要求如下:

1011
0101
=> 取到每一行中1所对应的索引列表,索引从1开始
0101    2,4
1011    1,3,4

这一行其实也是对posexplode方法的应用,直接上代码:

select
    id,stri,concat_ws(',',collect_list(index)) as indices
from
(
    select
        id,stri,chr,cast(index + 1 as string) as index
    from
        default.abcg
        lateral view posexplode(split(stri,'')) ids as index,chr
    where
        chr = '1'
) a
group by
    id,stri

4、分块排序

最后一题感觉是比较有难度的一道题目:

2014,1
2015,1
2017,0
2018,0
2019,1
2020,1
2021,1
2022,0
2023,0
=>
2014,1,1
2015,1,2
2017,0,1
2018,0,2
2019,1,1
2020,1,2
2021,1,3
2022,0,1
2023,0,2

简单描述下题目,col1是有序的,然后按照col2分块计数,每当col2发生变化,就重新开始计数,计数的结果当作col3返回。

这道题我想到的方法可能比较笨,先上代码,然后咱们一步步解析:

select year,
       num,
       row_number() over(partition by min_year order by year asc) as new_rank
    from
    (
        select year,
               base.num as num,
               min_year,
               row_number() over(partition by base.year order by min_year desc) as rank
          from (
                select *
                  from default.a3
               ) base
         inner join (
                select min_year,
                       num,
                       pre_num
                  from (
                        select year as min_year,
                               num,
                               lag(num,1) over(order by year) as pre_num
                          from default.a3
                       ) a
                 where num!=pre_num
                    or pre_num is null
               ) min_year
            on base.num = min_year.num
         where base.year >= min_year.min_year
       ) cc
 where rank = 1
 order by year

输出结果符合预期:

算法工程师面试-Hive面试及笔试常见试题解析-我爱自学

 

接下来,一步步解析下上面的过程:

1)使用lag函数,得到其前面一个数:

select year as min_year,
      num,
      lag(num,1) over(order by year) as pre_num
    from default.a3

2)判断当前数和前面一个数的关系,得到分块最小值
如果两个数不相等,说明在此处数发生了变化,是一个新的分块的开始,除此之外,如果没有前一个数,说明当前行是第一行,同样作为一个分块的开始。这样,我们可以得到每个分块的开始:

select min_year,
       num,
       pre_num
from (
     select year as min_year,
            num,
            lag(num,1) over(order by year) as pre_num
     from default.a3
     ) a
where num!=pre_num
    or pre_num is null

这里的结果如下:

算法工程师面试-Hive面试及笔试常见试题解析-我爱自学

四个分块的开始分别是2014、2017、2019、2022。

3)判断每一行属于哪个分块

我们需要拿第二步得到的结果与原结果使用第二列进行join,然后判断每一行属于哪个分块。决定每一行的所属分块有两个条件,首先该行第一列的值要大于或等于分块的最小值;其次,在所有满足条件的分块最小值中,选择最大的一个,便是该行所在分块的最小值。

所以这里我们首先进行join操作,然后使用row_number()得到了每一行所在的分块:

select year,
       num,
       min_year
    from
    (
        select year,
               base.num as num,
               min_year,
               row_number() over(partition by base.year order by min_year desc) as rank
          from (
                select *
                  from default.a3
               ) base
         inner join (
                select min_year,
                       num,
                       pre_num
                  from (
                        select year as min_year,
                               num,
                               lag(num,1) over(order by year) as pre_num
                          from default.a3
                       ) a
                 where num!=pre_num
                    or pre_num is null
               ) min_year
            on base.num = min_year.num
         where base.year >= min_year.min_year
       ) cc
 where rank = 1
 order by year

结果如下:

算法工程师面试-Hive面试及笔试常见试题解析-我爱自学

4)把分块最小值作为分组键,进行分组排序

好了,这四道题就解析完毕了,抓紧时间去练习一下吧~~

转载请注明出:我爱自学 » 算法工程师面试-Hive面试及笔试常见试题解析

赞 (0)