咳咳，今天來介紹一下幾個Hive函數吧，先放一張我登哥劃水的照片，希望大家也做一只自由的魚兒，在知識的海洋里游呀游，嘻嘻！

今天我們來介紹幾個Hive常用的函數吧！

首先我們產生我們的數據,使用spark sql來產生吧：

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函數，我們特地將info字段寫成了一個json格式，info中有兩個鍵值對，一個是user_id,另一個是用戶的行為，行為中有兩個數據，用#隔開，分別是動作的類型和動作發生的時間。我們可以這樣認為，0代表百度首頁，1代表進行了一次搜索的搜索結果頁，2代表查看搜索結果中國年的某個詳情頁。從一次動作0 到 下一次動作0，我們可以認為這是用戶和百度一次完整的交互，即一次session，從一次動作1到下一次動作1，可以認為是一次完整的搜索操作。另一個字段是dt，即我們的分區字段。

我們用簡單的查詢語句來看一下我們的數據效果：

select*fromtest.sxw_testRowNumberwheredt=20180131

結果如下：

get_json_object

我們使用get_json_object來解析json格式字符串里面的內容，格式如下：

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

這里，我們來解析info中的userid和action：

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

結果如下：

字符串替換函數

字符串替換函數格式如下：

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

這里我們是可以寫正則表達式來替換的，比如我們想把#和數字都替換成大寫字母Y：

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

在上面的語句中，我們用了兩個,因為這里需要進行轉義。結果為：

字符串切分函數

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

split(字段名,分割字符)

split分割后返回一個數組，我們可以用下標取出每個元素。我們把action里面的動作類型和動作時間使用split分割開，語句如下：

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

結果如下：

取字串

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

substring(字段名,開始位置,提取長度)

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

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

你可能會問，為什么開始位置是從2開始的而不是1，因為hive中字符串的索引是從1開始的而不是0，同時，我們誰用length方法來計算字符串的長度，結果如下：

有條件計數

有條件計數使用count函數結合case when then語法來實現，比如我們要計算每個用戶有多少個session，語句如下：

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')

結果如下：

上面的幾個函數都只是簡單的開胃菜，接下來我們來介紹一下重頭戲，分組排序函數以及它的兩個衍生的函數，row_number() over的格式如下：

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

簡單的說，我們使用partition by后面的字段對數據進行分組，在每個組內，使用ORDER BY后面的字段進行排序，并給每條記錄增加一個排序序號。比如，我們根據每個用戶每條記錄的發生時間對用戶的行為進行排序，并添加一個序號：

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

執行結果如下：

可以看到，我們已經成功給用戶的行為添加了發生序號。

除了row_number以外，我們還有兩個函數，分別是：

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括號里理由兩個參數，第一個是字段名，第二個是數量N，這里的意思是，取分組排序之后比該條記錄序號小N的對應記錄的指定字段的值，如果字段名為ts，N為1，就是取分組排序之后上一條記錄的ts值。

lead括號里理由兩個參數，第一個是字段名，第二個是數量N，這里的意思是，取分組排序之后比該條記錄序號大N的對應記錄的對應字段的值，如果字段名為ts，N為1，就是取分組排序之后下一條記錄的ts值。

比如，我們用lag和lead分別記錄用戶上一次行為和下一次行為的發生時間，語句如下：

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

結果如下：

接下來，我們想實現下面的功能：給每條記錄添加一列，該列代表此次session的開始時間。

前面我們介紹過，我們這里認為一次session是從一個action_type為0開始，到下一次action_type為0結束，也就是說，我們這里的數據有三個session，前5條記錄是一個session，這五條記錄的新列的值應給為222，同理，中間三條記錄的新列的值應改為226，而最后四條記錄的值應為223，那么如何實現這個功能呢，這就需要我們的lag和lead函數啦。

語句如下：

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)

我們來一步步剖析一下該過程的實現，首先，我們在子查詢中實現了兩個表的內鏈接。第一個子查詢查詢出所有session開始的action_ts以及它對應的下一個session開始的action_ts，使用lead實現：

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

第二個子查詢，將簡單的進行一下解析：

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

隨后，我們根據兩個表的user_id進行內鏈接，但是內鏈接之后會多出很多數據，我們要從中取出滿足條件的,這里的條件有兩個，滿足其一即可，即記錄的ts在兩個session開始的ts之間，要么就沒有后一個session：

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

最終的結果如下：