系統內置函式

1）查看系統自帶的函式

hive> show functions;

2）顯示自帶的函式的用法

hive> desc function upper;

3）詳細顯示自帶的函式的用法

hive> desc function extended upper;

常用內置函式

NVL

nvl(v1，v2)
引數：第一個引數是欄位，第二個引數是常量值或者某個欄位；
功能是如果v1為NULL，則回傳v2的值，否則回傳value的值，如果兩個引數都為NULL ，則回傳NULL

CASE WHEN THEN ELSE END

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

CONCAT(string A/col, string B/col…)

引數：字串或者字串欄位名

CONCAT_WS(separator, str1, str2,…)

• 第一個引數剩余引數間的分隔符
• 如果分隔符是 NULL，回傳值也將為 NULL
• 后面的引數是字串或者array
• 函式會跳過分隔符引數后的任何 NULL 和空字串，分隔符將被加到被連接的字串之間;

COLLECT_SET(col)

• 是一個聚合函式，將一列中的多行合并成一行
• 只接受基本資料型別
• 作用是將某欄位的值進行去重匯總，回傳值為array型別欄位，
• collect_list(col) 函式和此函式的功能一致，只不過不會進行去重

案例2

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

射手座,A            大海|鳳姐
白羊座,A            孫悟空|豬八戒
白羊座,B            宋宋|蒼老師

select
    concat_ws(',',constellation,blood_type) 
	concat_ws('|',collect_set(name))
from
	person_info
group by 
	constellation,blood_type

EXPLODE(col)

• udtf函式
• 引數型別：array型別或者map型別
• 功能：相當于flatmap，將一行拆分成多行

LATERAL VIEW

• LATERAL VIEW udtf(expression) tableAlias AS columnAlias
• 用于和split、 explode等UDTF一起使用，它能夠將一列資料拆成多行資料，在此基礎上可以對拆分后的資料進行聚合

案例3

需求
將電影分類中的陣列資料展開，結果如下：

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

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

說明：
將movie_info表側寫，將其movie_info表的category欄位值用炸裂函式炸開，然后側寫形成臨時表movie_info_tmp 并將炸裂的新行取名為category_name

說白了側寫的作用就是表欄位炸裂后，形成多行，但是還和原來表關聯

視窗函式

OVER()

• 指定分析函式所作用的資料視窗大小，這個資料視窗大小可能會隨著行的變而變化

視窗大小指定

• CURRENT ROW：當前行
• n PRECEDING：往前n行資料
• n FOLLOWING：往后n行資料
• UNBOUNDED PRECEDING 表示從前面的起點，
• UNBOUNDED FOLLOWING表示到后面的終點
• LAG(col,n,default_val)：往前第n行資料
• LEAD(col,n, default_val)：往后第n行資料
• NTILE(n)：把有序視窗的行分發到指定資料的組中，各個組有編號，編號從1開始，對于每一行，NTILE回傳此行所屬的組的編號，注意：n必須為int型別，

案例

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. group by后over()視窗

（1） 查詢在2017年4月份購買過的顧客及總人數

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

結論：select是在group by后面執行的，因此over()視窗大小是group by后的

2. over(partition by xxx)

（2）查詢顧客的購買明細及每個用戶的
月購買總額

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

3.over(partition by xx order by xx)

（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 子句之后，對排序的結果進行限制，使用固定的行數來限制磁區中的資料行數量
• 當order by 遇到相同的資料，那么視窗開到相同資料中的最后一條

id
1
2
3
3
4
5

select id,
	   sum(id) over(order by id)
from ...

4. lag() over()

（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;

lag(欄位名，前面第n行，如果前面第n行沒有該值的默認值)

5.ntile(n)

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

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

6.rank()

• RANK() 排序相同時會重復，總數不會變
• DENSE_RANK() 排序相同時會重復，總數會減少
• ROW_NUMBER() 會根據順序計算
  ·

select name,
	   subject,
       score,
       rank() over(partition by subject order by score desc) rp,
       dense_rank() over(partition by subject order by score desc) drp,
       row_number() over(partition by subject order by score desc) rmp
from score;

name    subject score   rp      drp     rmp
孫悟空  數學    95      1       1       1
宋宋    數學    86      2       2       2
婷婷    數學    85      3       3       3
大海    數學    56      4       4       4
宋宋    英語    84      1       1       1
大海    英語    84      1       1       2
婷婷    英語    78      3       2       3
孫悟空  英語    68      4       3       4
大海    語文    94      1       1       1
孫悟空  語文    87      2       2       2
婷婷    語文    65      3       3       3
宋宋    語文    64      4       4       4