（圖片付費下載自視覺中國）
作者 | 石曉文
來源｜小小挖掘機（ID：wAlsjwj）
近期在不同群裡有小夥伴們提出了一些在面試和筆試中遇到的Hive SQL問題，Hive作為算法工程師的一項必備技能，在面試中也是極有可能被問到的，所以有備無患，本文將對這四道題進行詳細的解析，還是有一定難度的，希望你看完本文能夠有所收穫。
1、多列轉多行
第一道題目是這樣的：
假設現有一張Hive表，元數據格式為：欄位：id stirngtim string數據格式如下：a,b,c,d 2:00,3:00,4:00,5:00f,b,c,d 1:10,2:20,3:30,4:40需要變成：a 2:00b 3:00c 4:00d 5:00
這道題目是需要把多行轉換成多行，有點類似python裡面的zip操作。大夥應該都知道hive裡有一個常用的一行轉多行的函數叫explode，假設有如下的數據：
a,b,c,d 2:00,3:00,4:00,5:00f,b,c,d 1:10,2:20,3:30,4:40
按照第二列explode的話，使用下面的SQL：
selectid,tim,single_timfromdefault.a1lateral view explode(split(tim,',')) t as single_tim
效果如下：
但這道題目裡，需要對兩列同時進行explode，如果只進行簡單的explode，效果如下：
這樣一行變成了16行，而我們僅僅需要的是其中能夠對齊下標的四行。所以在進行explode的時候，我們期望不僅僅能夠能夠獲得數組裡的每個值，還希望能夠得到其對應的下標，這樣在對兩列同時進行explode的時候，保留數組下標相同的四行就可以了。這裡我們會用到posexplode函數。
posexplode函數跟explode函數的使用方法類似，看下面的例子：
selectid,tim,single_id_index,single_idfromdefault.a1lateral view posexplode(split(id,',')) t as single_id_index,single_id
返回的結果為：
應用到本題，只需要應用兩次posexplode函數，再通過where留下兩個index相等的行就可以了，按照這個思路，sql如下：
selectid,tim,single_id,single_timfromdefault.a1lateral view posexplode(split(id,',')) t as single_id_index,single_idlateral view posexplode(split(tim,',')) t as single_tim_index,single_timwheresingle_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值。
如果沒有前一行或者後一行，對應的欄位值為。
所以，這裡我們應該使用的是lag函數，來獲取同組排序後前一行數據對應欄位的值，SQL如下：
selectyear,chr,if(pre_val is ,val,(val + pre_val) / 2.0) as avg_valfrom(selectyear,chr,val,lag(val,1) over(partition by year order by chr asc) as pre_valfromdefault.a2) a
注意這裡的一個小細節，如果分組後數據排在第一位，它是沒有前一個數的，此時數仍保持原樣，所以這裡加了一個if判斷，結果符合預期：
3、獲取字符串索引列表
第三題的題目要求如下：
10110101=> 取到每一行中1所對應的索引列表，索引從1開始0101 2,41011 1,3,4
這一行其實也是對posexplode方法的應用，直接上代碼：
selectid,stri,concat_ws(',',collect_list(index)) as indicesfrom(selectid,stri,chr,cast(index + 1 as string) as indexfromdefault.abcglateral view posexplode(split(stri,'')) ids as index,chrwherechr = '1') agroup byid,stri
4、分塊排序
最後一題感覺是比較有難度的一道題目：
2014,12015,12017,02018,02019,12020,12021,12022,02023,0=>2014,1,12015,1,22017,0,12018,0,22019,1,12020,1,22021,1,32022,0,12023,0,2
簡單描述下題目，col1是有序的，然後按照col2分塊計數，每當col2發生變化，就重新開始計數，計數的結果當作col3返回。
這道題我想到的方法可能比較笨，先上代碼，然後咱們一步步解析：
select year,num,row_number over(partition by min_year order by year asc) as new_rankfrom(select year,base.num as num,min_year,row_number over(partition by base.year order by min_year desc) as rankfrom (select *from default.a3) baseinner join (select min_year,num,pre_numfrom (select year as min_year,num,lag(num,1) over(order by year) as pre_numfrom default.a3) awhere num!=pre_numor pre_num is) min_yearon base.num = min_year.numwhere base.year >= min_year.min_year) ccwhere rank = 1order by year
輸出結果符合預期：
接下來，一步步解析下上面的過程：
1）使用lag函數，得到其前面一個數：
select year as min_year,num,lag(num,1) over(order by year) as pre_numfrom default.a3
2）判斷當前數和前面一個數的關係，得到分塊最小值。
如果兩個數不相等，說明在此處數發生了變化，是一個新的分塊的開始，除此之外，如果沒有前一個數，說明當前行是第一行，同樣作為一個分塊的開始。這樣，我們可以得到每個分塊的開始:
select min_year,num,pre_numfrom (select year as min_year,num,lag(num,1) over(order by year) as pre_numfrom default.a3) awhere num!=pre_numor pre_num is
這裡的結果如下：
四個分塊的開始分別是2014、2017、2019、2022。
3）判斷每一行屬於哪個分塊
我們需要拿第二步得到的結果與原結果使用第二列進行join，然後判斷每一行屬於哪個分塊。決定每一行的所屬分塊有兩個條件，首先該行第一列的值要大於或等於分塊的最小值；其次，在所有滿足條件的分塊最小值中，選擇最大的一個，便是該行所在分塊的最小值。
所以這裡我們首先進行join操作，然後使用row_number得到了每一行所在的分塊：
select year,num,min_yearfrom(select year,base.num as num,min_year,row_number over(partition by base.year order by min_year desc) as rankfrom (select *from default.a3) baseinner join (select min_year,num,pre_numfrom (select year as min_year,num,lag(num,1) over(order by year) as pre_numfrom default.a3) awhere num!=pre_numor pre_num is) min_yearon base.num = min_year.numwhere base.year >= min_year.min_year) ccwhere rank = 1order by year
結果如下：
4）把分塊最小值作為分組鍵，進行分組排序
好了，這四道題就解析完畢了，抓緊時間去練習一下吧～～
（*本文為 AI大本營轉載文章，轉載請聯繫原作者。）
◆
◆