Hadoop之Hive函式

🌻各位小伙伴，大家好，昨天我們學習了Hive中特別重要的內容磁區表和分桶表，感興趣的的小伙伴可以查看👇:

• 磁區表: Hadoop之Hive的磁區表.
• 分桶表: Hadoop之Hive分桶表.

🚩今天我們來學習Hive的函式部分，這一部分內容較多，也很重要！

1.Hive函式的分類

在Hive中，函式的類別有3種：

• 普通函式UDF:輸入一行資料，輸出一行資料（一進一出）
• 聚合函式UDAF:輸出多行資料，輸出一行資料（多進一出）
• 炸裂函式UDTF:輸出一行資料，輸出多行資料（一進多出）

2.查詢系統自帶函式

• 查看系統自帶函式

show functions;

• 顯示自帶函式的用法

desc function max;

• 詳細顯示自帶函式的用法

desc function extended max;

3.常用的內置函式

3.1 NVL 空欄位賦值

NVL：給值為 NULL 的資料賦值，它的格式是 NVL( value，default_value)，它的功能是如果 value 為 NULL，則 NVL 函式回傳 default_value 的值，否則回傳 value 的值，如果兩個引數都為 NULL ，則回傳 NULL，

• 如果員工的 comm 為 NULL，則用-1 代替

select comm ,nvl(comm,-1) from emp;

結果如下：

3.2 CASE WHEN THEN ELSE END

條件賦值陳述句·

-- 比如我們需要通過分數來看學生是A、B、C還是D
case scores when scores>90 then "A" when scores>80 then "B" when scores>60 then "C" else "D" end

• 構造資料集

小虎	A	男
小明	A	男
麻辣香鍋	B	男
鳳姐	A	女
婷姐	B	女
婷婷	B	女

• 需求，要實作以下樣式：
  
• 實作

select dept_id,
 sum(case sex when '男' then 1 else 0 end) male_count,
 sum(case sex when '女' then 1 else 0 end) female_count
from emp_sex
group by dept_id;

結果如下：

3.3 CONCAT （行轉列）

CONCAT(string A/col, string B/col…)：回傳輸入字串連接后的結果，支持任意個輸入字串，

CONCAT_WS(separator, str1, str2,…)：它是一個特殊形式的 CONCAT()，第一個引數剩余引數間的分隔符，分隔符可以是與剩余引數一樣的字串，如果分隔符是 NULL，回傳值也將為 NULL，這個函式會跳過分隔符引數后的任何 NULL 和空字串，分隔符將被加到被連接的字串之間;

COLLECT_SET(col)：函式只接受基本資料型別，它的主要作用是將某欄位的值進行去重
匯總，產生 Array 型別欄位

• CONCAT的用法

--拼接字符
select concat('A','-','B','-','C');
--拼接欄位
select concat(deptno,'-',dname) from dept;

• CONCAT_WS用法
  不能存放非字符型的欄位，會報錯，還可以存放陣列

--先指定分隔符，后接欄位
select concat_ws('-','A','B','C');
--拼接非字符型別欄位會報錯，單可以拼接為陣列的欄位
select concat_ws('-',deptno,dname) from dept;

• COLLECT_SET(col）用法

--collect_list 將某列資料拿出來形成一個陣列（可以重復）
select collect_list(id) from student04;
--collect_set 將某列資料拿出來形成一個集合（不能重復）
select collect_list(id) from student04;

如：

結果是這樣，因為本來就沒有重復，所以list和set是一樣的

• 案例：

# 匯入資料
孫悟空	白羊座	A
小虎	射手座	A
小白	白羊座	B
豬八戒	白羊座	A
小明	射手座	A
姿態	白羊座	B

• 要求

把星座和血型一樣的人歸類到一起，結果如下：

• 解決方法

SELECT
t1.con_blood,
CONCAT_WS("|",collect_set(t1.name))
FROM (
SELECT
NAME,
CONCAT_WS(',',constellation,blood_type) con_blood
FROM person_info
)t1
GROUP BY t1.con_blood;

結果如下：

3.4 EXPLODE（列轉行）

EXPLODE(col)：將 hive 一列中復雜的 Array 或者 Map 結構拆分成多行，
LATERAL VIEW：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解釋：用于和 split, explode 等 UDTF 一起使用，它能夠將一列資料拆成多行資料，在此基礎上可以對拆分后的資料進行聚合，

• 資料準備

《疑犯追蹤》	懸疑,動作,科幻,劇情
《Lie to me》	懸疑,警匪,動作,心理,劇情
《戰狼 2》	戰爭,動作,災難

• 需求
  
• EXPLODE(col)的用法

--split(欄位，分隔符)可以將string轉化為Array型別 
--將一列資料轉化為多行但必須是陣列型別
select explode(split(category,",")) from movie_info;

• 實作需求

SELECT movie,
category_name
FROM movie_info
lateral VIEW
explode(split(category,",")) movie_info_tmp AS category_name;

總結一下，如果只需要將某列資料轉化成行則只需要exlpode函式，如果轉化成行后還需要與原表進行關聯，則需要加上lateral VIEW函式，

4.視窗函式

視窗函式就一個函式：
OVER()：指定分析函式作業的資料視窗大小，這個資料視窗大小可能會隨著行的變而變，我們將通過一組案例來了解視窗函式，

• 資料集準備:name，orderdate，cost

jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

• 需求如下
  1.查詢在 2017 年 4 月份購買過的顧客及總人數
  2.查詢顧客的購買明細及月購買總額
  3.上述的場景, 將每個顧客的 cost 按照日期進行累加
  4.查詢每個顧客上次的購買時間
  5.查詢前 20%時間的訂單資訊

• 實作需求
  1.查詢在 2017 年 4 月份購買過的顧客及總人數

select name,count(*) over () 
from business
where substring(orderdate,1,7) = '2017-04'
group by name;

2.查詢顧客的購買明細及月購買總額

select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) 
from business;

3.上述的場景, 將每個顧客的 cost 按照日期進行累加

select name,orderdate,cost,
sum(cost) over() as sample1,--所有行相加
sum(cost) over(partition by name) as sample2,--按 name 分組，組內資料相加
sum(cost) over(partition by name order by orderdate) as sample3,--按 name
分組，組內資料累加
sum(cost) over(partition by name order by orderdate rows between 
UNBOUNDED PRECEDING and current row ) as sample4 ,--和 sample3 一樣,由起點到
當前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1 
PRECEDING and current row) as sample5, --當前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1 
PRECEDING AND 1 FOLLOWING ) as sample6,--當前行和前邊一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current 
row and UNBOUNDED FOLLOWING ) as sample7 --當前行及后面所有行
from business;
--rows 必須跟在 order by 子句之后，對排序的結果進行限制，使用固定的行數來限制磁區中的資料行數量

4.查詢每個顧客上次的購買時間

select name,orderdate,cost,
lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) 
as time1, lag(orderdate,2) over (partition by name order by orderdate) as time2 
from business;

5.查詢前 20%時間的訂單資訊

select * from (
select name,orderdate,cost, ntile(5) over(order by orderdate) sorted from business) t
where sorted = 1;

參考資料

《大資料Hadoop3.X分布式處理實戰》