咳咳，今天來(lái)介紹一下幾個(gè)Hive函數(shù)吧，先放一張我登哥劃水的照片，希望大家也做一只自由的魚(yú)兒，在知識(shí)的海洋里游呀游，嘻嘻！

今天我們來(lái)介紹幾個(gè)Hive常用的函數(shù)吧！

首先我們產(chǎn)生我們的數(shù)據(jù),使用spark sql來(lái)產(chǎn)生吧：

valdata=Seq[(String,String)](("{"userid":"1","action":"0#222"}","20180131"),("{"userid":"1","action":"1#223"}","20180131"),("{"userid":"1","action":"2#224"}","20180131"),("{"userid":"1","action":"1#225"}","20180131"),("{"userid":"1","action":"2#225"}","20180131"),("{"userid":"1","action":"0#226"}","20180131"),("{"userid":"1","action":"1#227"}","20180131"),("{"userid":"1","action":"2#228"}","20180131"),("{"userid":"2","action":"0#223"}","20180131"),("{"userid":"2","action":"1#224"}","20180131"),("{"userid":"2","action":"1#225"}","20180131"),("{"userid":"2","action":"2#228"}","20180131")).toDF("info","dt").write.saveAsTable("test.sxw_testRowNumber")

為了模擬我們的hive函數(shù)，我們特地將info字段寫(xiě)成了一個(gè)json格式，info中有兩個(gè)鍵值對(duì)，一個(gè)是user_id,另一個(gè)是用戶的行為，行為中有兩個(gè)數(shù)據(jù)，用#隔開(kāi)，分別是動(dòng)作的類(lèi)型和動(dòng)作發(fā)生的時(shí)間。我們可以這樣認(rèn)為，0代表百度首頁(yè)，1代表進(jìn)行了一次搜索的搜索結(jié)果頁(yè)，2代表查看搜索結(jié)果中國(guó)年的某個(gè)詳情頁(yè)。從一次動(dòng)作0 到 下一次動(dòng)作0，我們可以認(rèn)為這是用戶和百度一次完整的交互，即一次session，從一次動(dòng)作1到下一次動(dòng)作1，可以認(rèn)為是一次完整的搜索操作。另一個(gè)字段是dt，即我們的分區(qū)字段。

我們用簡(jiǎn)單的查詢語(yǔ)句來(lái)看一下我們的數(shù)據(jù)效果：

select*fromtest.sxw_testRowNumberwheredt=20180131

結(jié)果如下：

get_json_object

我們使用get_json_object來(lái)解析json格式字符串里面的內(nèi)容，格式如下：

get_json_object(字段名,'$.key')

這里，我們來(lái)解析info中的userid和action：

selectget_json_object(info,'$.userid')asuser_id,get_json_object(info,'$.action')asactionfromtest.sxw_testRowNumberwheredt=20180131

結(jié)果如下：

字符串替換函數(shù)

字符串替換函數(shù)格式如下：

regexp_replace(字段名,被替換的內(nèi)容,替換為的內(nèi)容)

這里我們是可以寫(xiě)正則表達(dá)式來(lái)替換的，比如我們想把#和數(shù)字都替換成大寫(xiě)字母Y：

selectregexp_replace(info,'[\d#]','Y')asinfofromtest.sxw_testRowNumberwheredt=20180131

在上面的語(yǔ)句中，我們用了兩個(gè),因?yàn)檫@里需要進(jìn)行轉(zhuǎn)義。結(jié)果為：

字符串切分函數(shù)

字符串切分函數(shù)split，很像我們java、python中寫(xiě)的那樣，格式如下：

split(字段名,分割字符)

split分割后返回一個(gè)數(shù)組，我們可以用下標(biāo)取出每個(gè)元素。我們把a(bǔ)ction里面的動(dòng)作類(lèi)型和動(dòng)作時(shí)間使用split分割開(kāi)，語(yǔ)句如下：

selectget_json_object(info,'$.userid')asuser_id,split(get_json_object(info,'$.action'),'#')[0]asaction_type,split(get_json_object(info,'$.action'),'#')[1]asaction_tsfromtest.sxw_testRowNumberwheredt=20180131

結(jié)果如下：

取字串

取字串使用substring方法，格式如下：

substring(字段名,開(kāi)始位置,提取長(zhǎng)度)

這里，如果我們想吧info中前后的大括號(hào)去掉，可以使用substring，語(yǔ)句如下：

selectsubstring(info,2,length(info)-2)asinfofromtest.sxw_testRowNumberwheredt=20180131

你可能會(huì)問(wèn)，為什么開(kāi)始位置是從2開(kāi)始的而不是1，因?yàn)閔ive中字符串的索引是從1開(kāi)始的而不是0，同時(shí)，我們誰(shuí)用length方法來(lái)計(jì)算字符串的長(zhǎng)度，結(jié)果如下：

有條件計(jì)數(shù)

有條件計(jì)數(shù)使用count函數(shù)結(jié)合case when then語(yǔ)法來(lái)實(shí)現(xiàn)，比如我們要計(jì)算每個(gè)用戶有多少個(gè)session，語(yǔ)句如下：

selectget_json_object(info,'$.userid')asuser_id,count(casewhensplit(get_json_object(info,'$.action'),'#')[0]=='0'then1elsenullend)assession_countfromtest.sxw_testRowNumberwheredt=20180131groupbyget_json_object(info,'$.userid')

結(jié)果如下：

上面的幾個(gè)函數(shù)都只是簡(jiǎn)單的開(kāi)胃菜，接下來(lái)我們來(lái)介紹一下重頭戲，分組排序函數(shù)以及它的兩個(gè)衍生的函數(shù)，row_number() over的格式如下：

row_Number()OVER(partitionby分組字段ORDERBY排序字段排序方式asc/desc)

簡(jiǎn)單的說(shuō)，我們使用partition by后面的字段對(duì)數(shù)據(jù)進(jìn)行分組，在每個(gè)組內(nèi)，使用ORDER BY后面的字段進(jìn)行排序，并給每條記錄增加一個(gè)排序序號(hào)。比如，我們根據(jù)每個(gè)用戶每條記錄的發(fā)生時(shí)間對(duì)用戶的行為進(jìn)行排序，并添加一個(gè)序號(hào)：

select*row_number()over(partitionbyuser_idorderbyaction_tsasc)astnfrom(selectget_json_object(info,'$.userid')asuser_id,split(get_json_object(info,'$.action'),'#')[0]asaction_type,split(get_json_object(info,'$.action'),'#')[1]asaction_tsfromtest.sxw_testRowNumberwheredt=20180131)ast

執(zhí)行結(jié)果如下：

可以看到，我們已經(jīng)成功給用戶的行為添加了發(fā)生序號(hào)。

除了row_number以外，我們還有兩個(gè)函數(shù)，分別是：

lag(字段名,N)over(partitionby分組字段orderby排序字段排序方式)lead(字段名,N)over(partitionby分組字段orderby排序字段排序方式)lag(字段名,N) over(partition by 分組字段 order by 排序字段 排序方式) lead(字段名,N) over(partition by 分組字段 order by 排序字段 排序方式)

lag括號(hào)里理由兩個(gè)參數(shù)，第一個(gè)是字段名，第二個(gè)是數(shù)量N，這里的意思是，取分組排序之后比該條記錄序號(hào)小N的對(duì)應(yīng)記錄的指定字段的值，如果字段名為ts，N為1，就是取分組排序之后上一條記錄的ts值。

lead括號(hào)里理由兩個(gè)參數(shù)，第一個(gè)是字段名，第二個(gè)是數(shù)量N，這里的意思是，取分組排序之后比該條記錄序號(hào)大N的對(duì)應(yīng)記錄的對(duì)應(yīng)字段的值，如果字段名為ts，N為1，就是取分組排序之后下一條記錄的ts值。

比如，我們用lag和lead分別記錄用戶上一次行為和下一次行為的發(fā)生時(shí)間，語(yǔ)句如下：

select*,row_number()over(partitionbyuser_idorderbyaction_tsasc)astn,lag(action_ts,1)over(partitionbyuser_idorderbyaction_tsasc)asprev_ts,lead(action_ts,1)over(partitionbyuser_idorderbyaction_tsasc)asnext_tsfrom(selectget_json_object(info,'$.userid')asuser_id,split(get_json_object(info,'$.action'),'#')[0]asaction_type,split(get_json_object(info,'$.action'),'#')[1]asaction_tsfromtest.sxw_testRowNumberwheredt=20180131)ast

結(jié)果如下：

接下來(lái)，我們想實(shí)現(xiàn)下面的功能：給每條記錄添加一列，該列代表此次session的開(kāi)始時(shí)間。

前面我們介紹過(guò)，我們這里認(rèn)為一次session是從一個(gè)action_type為0開(kāi)始，到下一次action_type為0結(jié)束，也就是說(shuō)，我們這里的數(shù)據(jù)有三個(gè)session，前5條記錄是一個(gè)session，這五條記錄的新列的值應(yīng)給為222，同理，中間三條記錄的新列的值應(yīng)改為226，而最后四條記錄的值應(yīng)為223，那么如何實(shí)現(xiàn)這個(gè)功能呢，這就需要我們的lag和lead函數(shù)啦。

語(yǔ)句如下：

selectt2.user_id,t2.action_type,t2.action_ts,t1.action_tsassession_tsfrom(select*,lead(action_ts,1)over(partitionbyuser_idorderbyaction_tsasc)asnext_tsfrom(selectget_json_object(info,'$.userid')asuser_id,split(get_json_object(info,'$.action'),'#')[0]asaction_type,split(get_json_object(info,'$.action'),'#')[1]asaction_tsfromtest.sxw_testRowNumberwheredt=20180131andsplit(get_json_object(info,'$.action'),'#')[0]=='0')ast)t1innerjoin(selectget_json_object(info,'$.userid')asuser_id,split(get_json_object(info,'$.action'),'#')[0]asaction_type,split(get_json_object(info,'$.action'),'#')[1]asaction_tsfromtest.sxw_testRowNumberwheredt=20180131)t2ont1.user_id=t2.user_idwhere(t2.action_ts>=t1.action_tsandt2.action_ts=t1.action_tsandt1.next_tsisnull)

我們來(lái)一步步剖析一下該過(guò)程的實(shí)現(xiàn)，首先，我們?cè)谧硬樵冎袑?shí)現(xiàn)了兩個(gè)表的內(nèi)鏈接。第一個(gè)子查詢查詢出所有session開(kāi)始的action_ts以及它對(duì)應(yīng)的下一個(gè)session開(kāi)始的action_ts，使用lead實(shí)現(xiàn)：

select*,lead(action_ts,1)over(partitionbyuser_idorderbyaction_tsasc)asnext_tsfrom(selectget_json_object(info,'$.userid')asuser_id,split(get_json_object(info,'$.action'),'#')[0]asaction_type,split(get_json_object(info,'$.action'),'#')[1]asaction_tsfromtest.sxw_testRowNumberwheredt=20180131andsplit(get_json_object(info,'$.action'),'#')[0]=='0')ast

第二個(gè)子查詢，將簡(jiǎn)單的進(jìn)行一下解析：

selectget_json_object(info,'$.userid')asuser_id,split(get_json_object(info,'$.action'),'#')[0]asaction_type,split(get_json_object(info,'$.action'),'#')[1]asaction_tsfromtest.sxw_testRowNumberwheredt=20180131

隨后，我們根據(jù)兩個(gè)表的user_id進(jìn)行內(nèi)鏈接，但是內(nèi)鏈接之后會(huì)多出很多數(shù)據(jù)，我們要從中取出滿足條件的,這里的條件有兩個(gè)，滿足其一即可，即記錄的ts在兩個(gè)session開(kāi)始的ts之間，要么就沒(méi)有后一個(gè)session：

where(t2.action_ts>=t1.action_tsandt2.action_ts=t1.action_tsandt1.next_tsisnull)

最終的結(jié)果如下：