算法人必懂的进阶SQL知识，4道面试常考题
算法人必懂的进阶SQL知识，4道面试常考题
近期在不同群里有小伙伴们提出了一些在面试和笔试中遇到的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
效果如下：
但这道题目里，需要对两列同时进行explode，如果只进行简单的explode，效果如下：
这样一行变成了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
返回的结果为：
应用到本题，只需要应用两次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
结果正是我们想要的：
2、排序后相邻两行均值
第二题的原始数据如下：
要求如下：
分组排序想必大家都知道使用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判断，结果符合预期：
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
输出结果符合预期：
接下来，一步步解析下上面的过程：
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
这里的结果如下：
四个分块的开始分别是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
结果如下：
4）把分块最小值作为分组键，进行分组排序
好了，这四道题就解析完毕了，抓紧时间去练习一下吧～～