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

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

1、數(shù)據(jù)介紹

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

 val data = 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字段寫成了一個(gè)json格式畸肆，info中有兩個(gè)鍵值對(duì)，一個(gè)是user_id,另一個(gè)是用戶的行為野来，行為中有兩個(gè)數(shù)據(jù)恼除，用#隔開(kāi)，分別是動(dòng)作的類型和動(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 * from test.sxw_testRowNumber where dt=20180131

結(jié)果如下：

2顽冶、常用的Hive函數(shù)

2.1 get_json_object

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

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

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

select 
    get_json_object(info,'$.userid') as user_id,
    get_json_object(info,'$.action') as action
  from test.sxw_testRowNumber
 where dt=20180131

結(jié)果如下：

2.2 字符串替換函數(shù)

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

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

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

select 
    regexp_replace(info,'[\\d#]','Y') as info
  from test.sxw_testRowNumber
 where dt=20180131

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

2.3 字符串切分函數(shù)

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

split(字段名,分割字符)

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

select 
    get_json_object(info,'$.userid') as user_id,
    split(get_json_object(info,'$.action'),'#')[0] as action_type,
     split(get_json_object(info,'$.action'),'#')[1] as action_ts
  from test.sxw_testRowNumber
 where dt=20180131

結(jié)果如下：

2.4 取子串

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

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

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

select 
    substring(info,2,length(info)-2) as info
  from test.sxw_testRowNumber
 where dt=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é)果如下：

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

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

select 
    get_json_object(info,'$.userid') as user_id,
    count(case
          when split(get_json_object(info,'$.action'),'#')[0]=='0' then 1 
          else null end) as session_count
  from test.sxw_testRowNumber
 where dt=20180131
 group by get_json_object(info,'$.userid')

結(jié)果如下：

3直撤、分組排序函數(shù)

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

row_Number() OVER (partition by 分組字段 ORDER BY 排序字段 排序方式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(partition by user_id order by action_ts asc) as tn
from
    (
      select 
    get_json_object(info,'$.userid') as user_id,
    split(get_json_object(info,'$.action'),'#')[0] as action_type,
     split(get_json_object(info,'$.action'),'#')[1] as action_ts
  from test.sxw_testRowNumber
 where dt=20180131
    ) as t

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

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

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

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(partition by user_id order by action_ts asc) as tn,
    lag(action_ts,1) over(partition by user_id order by action_ts asc) as prev_ts,
    lead(action_ts,1) over(partition by user_id order by action_ts asc) as next_ts
from
    (
      select 
    get_json_object(info,'$.userid') as user_id,
    split(get_json_object(info,'$.action'),'#')[0] as action_type,
     split(get_json_object(info,'$.action'),'#')[1] as action_ts
  from test.sxw_testRowNumber
 where dt=20180131
    ) as t

結(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ǔ)句如下：

select
    t2.user_id,
    t2.action_type,
    t2.action_ts,
    t1.action_ts as session_ts
from
(
  select
    *,
    lead(action_ts,1) over(partition by user_id order by action_ts asc) as next_ts
from
    (
      select 
    get_json_object(info,'$.userid') as user_id,
    split(get_json_object(info,'$.action'),'#')[0] as action_type,
     split(get_json_object(info,'$.action'),'#')[1] as action_ts
  from test.sxw_testRowNumber
 where dt=20180131 
      and split(get_json_object(info,'$.action'),'#')[0] == '0'  
    ) as t
 ) t1   
inner join 
(
 select 
    get_json_object(info,'$.userid') as user_id,
    split(get_json_object(info,'$.action'),'#')[0] as action_type,
     split(get_json_object(info,'$.action'),'#')[1] as action_ts
  from test.sxw_testRowNumber
 where dt=20180131 
) t2
on t1.user_id = t2.user_id
where
( t2.action_ts >= t1.action_ts
    and
 t2.action_ts < t1.next_ts
)
or 
( t2.action_ts >= t1.action_ts
    and t1.next_ts is null
 )

我們來(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(partition by user_id order by action_ts asc) as next_ts
from
    (
      select 
    get_json_object(info,'$.userid') as user_id,
    split(get_json_object(info,'$.action'),'#')[0] as action_type,
     split(get_json_object(info,'$.action'),'#')[1] as action_ts
  from test.sxw_testRowNumber
 where dt=20180131 
      and split(get_json_object(info,'$.action'),'#')[0] == '0'  
    ) as t

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

select 
    get_json_object(info,'$.userid') as user_id,
    split(get_json_object(info,'$.action'),'#')[0] as action_type,
     split(get_json_object(info,'$.action'),'#')[1] as action_ts
  from test.sxw_testRowNumber
 where dt=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_ts
    and
 t2.action_ts < t1.next_ts
)
or 
( t2.action_ts >= t1.action_ts
    and t1.next_ts is null
 )

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