Hive學習第九天--函式的用法

shmil發表於2024-08-01
Hive函式

1.1 Hive視窗函式

普通的聚合函式每組(Group by)只返回一個值，而開窗函式則可為視窗中的每行都返回一個值。
簡單理解，就是對查詢的結果多出一列，這一列可以是聚合值，也可以是排序值。
開窗函式一般就是說的是over()函式，其視窗是由一個 OVER 子句 定義的多行記錄
開窗函式一般分為兩類,聚合開窗函式和排序開窗函式。

-- 聚合格式
select sum(欄位名) over([partition by 欄位名] [ order by 欄位名]) as 別名,
	max(欄位名) over() as 別名 
from 表名;

-- 排序視窗格式
select rank() over([partition by 欄位名] [ order by 欄位名]) as 別名 from 表名;

注意點：

  • over()函式中的分割槽、排序、指定視窗範圍可組合使用也可以不指定，根據不同的業務需求結合使用
  • over()函式中如果不指定分割槽，視窗大小是針對查詢產生的所有資料，如果指定了分割槽，視窗大小是針對每個分割槽的資料

測試資料

-- 建立表
create table t_fraction(
name string,
subject string, 
score int) 
row format delimited fields terminated by ","
lines terminated by '\n';

-- 測試資料 fraction.txt
孫悟空,語文,10
孫悟空,數學,73
孫悟空,英語,15
豬八戒,語文,10
豬八戒,數學,73
豬八戒,英語,11
沙悟淨,語文,22
沙悟淨,數學,70
沙悟淨,英語,31
唐玄奘,語文,21
唐玄奘,數學,81
唐玄奘,英語,23

-- 上傳資料
load data local inpath '/usr/local/soft/hive_test/data/fraction.txt' into table t_fraction;

1.1.1 聚合開窗函式

sum(求和)

min(最小)

max(最大)

avg(平均值)

count(計數)

lag(獲取當前行上一行的資料）

-- 
select name,subject,score,sum(score) over() as sumover from t_fraction;
+-------+----------+--------+----------+
| name  | subject  | score  | sumover  |
+-------+----------+--------+----------+
| 唐玄奘   | 英語       | 23     | 321      |
| 唐玄奘   | 數學       | 81     | 321      |
| 唐玄奘   | 語文       | 21     | 321      |
| 沙悟淨   | 英語       | 31     | 321      |
| 沙悟淨   | 數學       | 12     | 321      |
| 沙悟淨   | 語文       | 22     | 321      |
| 豬八戒   | 英語       | 11     | 321      |
| 豬八戒   | 數學       | 73     | 321      |
| 豬八戒   | 語文       | 10     | 321      |
| 孫悟空   | 英語       | 15     | 321      |
| 孫悟空   | 數學       | 12     | 321      |
| 孫悟空   | 語文       | 10     | 321      |
+-------+----------+--------+----------+

select name,subject,score,
-- over()中如果什麼都不寫，視窗大小就是針對查詢到得全部資料
sum(score) over() as sum1,
-- 按照科目進行開窗，算每個科目的總分
sum(score) over(partition by subject) as sum2,
-- 在over中加上排序，就會將分數進行排序，按照名次相加得到加和，如果名次一樣，那麼同時都加上
sum(score) over(partition by subject order by score) as sum3, 

-- 由起點到當前行的視窗聚合，和sum3類似，區別是對於相同分數是不一樣的
sum(score) over(partition by subject order by score rows between unbounded preceding and current row) as sum4, 

-- 當前行和前面一行的視窗聚合
sum(score) over(partition by subject order by score rows between 1 preceding and current row) as sum5,

-- 當前行的前面一行到後面一行的視窗聚合  前一行+當前行+後一行
sum(score) over(partition by subject order by score rows between 1 preceding and 1 following) as sum6,

-- 當前行與後一行之和
sum(score) over(partition by subject order by score rows between current row and 1 following) as sum6,

-- 當前和後面所有的行
sum(score) over(partition by subject order by score rows between current row and unbounded following) as sum7
from t_fraction;

rows：行
unbounded preceding：起點
unbounded following：終點
n preceding：前 n 行
n following：後 n 行
current row：當前行


+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+
| name  | subject  | score  | sum1  | sum2  | sum3  | sum4  | sum5  | sum6  | sum7  |
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+
| 孫悟空   | 數學       | 12     | 359   | 185   | 12    | 12    | 12    | 31    | 185   |
| 沙悟淨   | 數學       | 19     | 359   | 185   | 31    | 31    | 31    | 104   | 173   |
| 豬八戒   | 數學       | 73     | 359   | 185   | 104   | 104   | 92    | 173   | 154   |
| 唐玄奘   | 數學       | 81     | 359   | 185   | 185   | 185   | 154   | 154   | 81    |
| 豬八戒   | 英語       | 11     | 359   | 80    | 11    | 11    | 11    | 26    | 80    |
| 孫悟空   | 英語       | 15     | 359   | 80    | 26    | 26    | 26    | 49    | 69    |
| 唐玄奘   | 英語       | 23     | 359   | 80    | 49    | 49    | 38    | 69    | 54    |
| 沙悟淨   | 英語       | 31     | 359   | 80    | 80    | 80    | 54    | 54    | 31    |
| 孫悟空   | 語文       | 10     | 359   | 94    | 10    | 10    | 10    | 31    | 94    |
| 唐玄奘   | 語文       | 21     | 359   | 94    | 31    | 31    | 31    | 53    | 84    |
| 沙悟淨   | 語文       | 22     | 359   | 94    | 53    | 53    | 43    | 84    | 63    |
| 豬八戒   | 語文       | 41     | 359   | 94    | 94    | 94    | 63    | 63    | 41    |
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+

rows必須跟在Order by 子句之後，對排序的結果進行限制，使用固定的行數來限制分割槽中的資料行數量。

OVER()：指定分析函式工作的資料視窗大小，這個資料視窗大小可能會隨著行的變而變化。

CURRENT ROW：當前行

n PRECEDING：往前n行資料

n FOLLOWING：往後n行資料

UNBOUNDED：起點，UNBOUNDED PRECEDING 表示從前面的起點， UNBOUNDED FOLLOWING表示到後面的終點

LAG(col,n,default_val)：往前第n行資料，col是列名，n是往上的行數，當第n行為null的時候取default_val

LEAD(col,n, default_val)：往後第n行資料，col是列名，n是往下的行數，當第n行為null的時候取default_val

NTILE(n)：把有序分割槽中的行分發到指定資料的組中，各個組有編號，編號從1開始，對於每一行，NTILE返回此行所屬的組的編號。

cume_dist（），計算某個視窗或分割槽中某個值的累積分佈。假定升序排序，則使用以下公式確定累積分佈：

​ 小於等於當前值x的行數 / 視窗或partition分割槽內的總行數。其中，x 等於 order by 子句中指定的列的當前行中的值。

聚合開窗函式實戰：
實戰1：Hive使用者購買明細資料分析

建立表和載入資料

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


建表載入資料
vim business.txt

create table business
(
name string, 
orderdate string,
cost int
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

load data local inpath "/hive_test/data/business.txt" into table business;
實戰1需求：

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

# 分析：按照日期過濾、分組count求總人數
select t1.name,t1.orderdate,count(1) over() as counts_04 from (select name,orderdate from business where month(orderdate)='04') t1;


select * from business where month(orderdate)='04';
select *,count(1) over() as counts from business where month(orderdate)='04';

需求2：查詢顧客的購買明細及月購買總額

# 分析：按照顧客分組、sum購買金額
select name,orderdate,cost,sum(cost) over(partition by name,month(orderdate))  from business;

需求3：上述的場景,要將cost按照日期進行累加

# 分析：按照顧客分組、日期升序排序、組內每條資料將之前的金額累加
select name,orderdate,cost,sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row)  from business;

select *,sum(cost) over(partition by name,month(cast(orderdate as date)) order by cast(orderdate as date) as leiji_cost,sum(cost) over(partition by name,month(cast(orderdate as date)) as month_cost from business;

需求4：查詢顧客上次的購買時間

·# 分析：查詢出明細資料同時獲取上一條資料的購買時間(肯定需要按照顧客分組、時間升序排序)
select name,orderdate,cost,lag(orderdate,1) over(partition by name order by orderdate) as last_time from business;


select 
	name
	,orderdate as `current_date`
	,cost
	,nvl(lag(orderdate,1) over(partition by name,month(cast(orderdate as date)) order by cast(orderdate as date)),'本月第一次消費') as `last_date`
	,nvl(lag(cost,1) over(partition by name,month(cast(orderdate as date)) order by cast(orderdate as date)),0) as last_cost
	,sum(cost) over(partition by name,month(cast(orderdate as date)) order by cast(orderdate as date)) as leiji_cost
    ,sum(cost) over(partition by name,month(cast(orderdate as date))) as month_cost from business;

需求5：查詢前20%時間的訂單資訊

分析：按照日期升序排序、取前20%的資料
select t1.name,t1.orderdate,t1.cost from (select name,orderdate,cost,ntile(5) over(order by orderdate) as n from business) t1 where t1.n=1;

1.1.2 排序開窗函式(重點)

  • RANK() 排序相同時會重複，總數不會變，會跳號
  • DENSE_RANK() 排序相同時會重複，總數會減少，不會跳號
  • ROW_NUMBER() 會根據順序計算
  • PERCENT_RANK()計算給定行的百分比排名。可以用來計算超過了百分之多少的人(當前行的rank值-1)/(分組內的總行數-1)
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) rnp,
percent_rank() over(partition by subject order by score) as percent_rank 
from t_fraction;

select name,subject,score,
rank() over(order by score) as row_number,
percent_rank() over(partition by subject order by score) as percent_rank
from t_fraction;
實戰2：Hive分析學生成績資訊

建立表語載入資料

name	subject	score
李毅	語文	87
李毅	數學	95
李毅	英語	68
黃仙	語文	94
黃仙	數學	56
黃仙	英語	84
小虎	語文	64
小虎	數學	86
小虎	英語	84
許文客	語文	65
許文客	數學	85
許文客	英語	78

建表載入資料
vim score.txt

create table score2
(
name string,
subject string, 
score int
) row format delimited fields terminated by "\t";

load data local inpath '/hive_test/data/score.txt' into table score;

需求1：每門學科學生成績排名(是否並列排名、空位排名三種實現)

分析：學科分組、成績降序排序、按照成績排名

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;

需求2：每門學科成績排名top 2的學生

select t1.name,t1.subject,t1.score from (select name,subject,score,row_number() over(partition by subject order by score desc) as rn from score2) t1 where t1.rn<3;

Hive 列轉行

列轉行：就是將一行的資料轉換成多行

lateral view explode:形成側寫表

  • 關鍵字：explode()、split()和LATERAL VIEW
    1. split():
      將一個字串按照指定字元分割，結果為一個array；
      2）explode():
      將一列複雜的array或者map拆分為多行,它的引數必須為map或array；
      3）lateral view:
      lateral view udtf(欄位名)表別名/表臨時名as列別名/列臨時名。lateral view用於和split, explode等UDTF一起使用，它能夠將一行資料拆成多行資料，在此基礎上可以對拆分後的資料進行聚合。lateral view首先為原始表的每行呼叫UDTF，UTDF會把一行拆分成一或者多行，lateralview再把結果組合，產生一個支援別名表的虛擬表。
  • 列轉行基本語法：
    select 欄位,欄位別名 from table_name lateral view explode（split(拆分欄位,分隔符)）表別名 as 欄位別名
/*原始資料
province       city
河南           鄭州市,開封市，洛陽市
河北           石家莊市，保定市
湖南           長沙市，岳陽市，常德市
*/
-- addr為表名
SELECT province,
       city_n
FROM addr LATERAL VIEW explode (split(city,',')) addr_tmp AS city_n;
/*
-- 查詢結果
河南      鄭州市
河南      開封市
河南      洛陽市
河北      石家莊市
河北      保定市
湖南      長沙市
湖南      岳陽市
湖南      常德市
*/

create table testArray2(
    name string,
    weight array<string>
)row format delimited 
fields terminated by '\t'
COLLECTION ITEMS terminated by ',';

小虎	"150","170","180"
火火	"150","180","190"

-- lateral view 形成側寫表，用於將炸裂後的表的欄位與原表進行關聯
-- 如果只需要獲取炸裂後的欄位，那麼可以不用和原表進行關聯，即可以不用lateral view
-- 在需要原表資料時，需要使用
-- explode：UDTF

select name,col1  from testarray2 lateral view explode(weight) t1 as col1;

小虎	150
小虎	170
小虎	180
火火	150
火火	180
火火	190


-----------------------------------------
select 
	a,b,c 
from (
    select 
    	a,max(case b when "A" then c end) as col_A
    		,max(case b when "B" then c end) as col_B 
    from group_score group by a) t1 
lateral view explode(map("A",col_A,"B",col_B)) tt1 as b,c;
------------------------------------------------

select key from (select explode(map('key1',1,'key2',2,'key3',3)) as (key,value)) t;

key1
key2
key3

select name,col1,col2  from testarray2 lateral view explode(map('key1',1,'key2',2,'key3',3)) t1 as col1,col2;
小虎	key1	1
小虎	key2	2
小虎	key3	3
火火	key1	1
火火	key2	2
火火	key3	3


select name,pos,col1  from testarray2 lateral view posexplode(weight) t1 as pos,col1;

小虎	0	150
小虎	1	170
小虎	2	180
火火	0	150
火火	1	180
火火	2	190

Hive 行轉列

行轉列：就是將多個列的資料轉換在一列下面

  • 關鍵字：collect_set() / collect_list()、concat_ws()

    1）collect_set()/collect_list()：
    collect_set( )函式只接受基本資料型別，作用是對引數欄位進行去重彙總，返回array型別欄位;
    collect_list()函式和collect_set( )作用一樣，只是前者不去重,後者去重。
    2）concat_ws()：
    concat_ws (separator,字串A/欄位名A,字串B/欄位名B…)是concat的特殊形式，第一個引數是分隔符，分隔符會放到要連線的字串之間,分隔符可以是字串，也可以是其他引數。如果分隔符為NULL,則結果為NULL,函式會忽略任何分隔符引數後的 NULL值。但是concat_ws( )不會忽略任何空字串。（然而會忽略所有的 NULL)，如果引數為string型別陣列則合併陣列內字串。
    例:concat_ws( ',', [ 'a ', 'b'])–> 'a,b'

  • 行轉列基本語法：
    select 分類欄位,concat_ws(',',collect_set(合併欄位)) as 別名 from table_name group 分類欄位

/* 原始資料
name       gender          times
張三         男            唐
李四         男            唐
王五         男            明
趙六         男            明
*/
-- 先用collect_set將列拼接在一起，然後再透過concat_ws進行展開拼接
SELECT a.gender_times,
       concat_ws(';',collect_set(a.name)) name
FROM
  (SELECT name,
          concat(gender, '_','times') gender_times
   FROM hero_info) t
GROUP BY t.gender_times;
/*查詢結果
gender_times    name    
男_唐     張三;李四
男_明     王五;趙六

// testLieToLine
name col1
小虎	150
小虎	170
小虎	180
火火	150
火火	180
火火	190

create table testLieToLine(
    name string,
    col1 int
)row format delimited 
fields terminated by '\t';


select name,collect_list(col1) from testLieToLine group by name;

// 結果
小虎	["150","180","190"]
火火	["150","170","180"]

select  t1.name
        ,collect_list(t1.col1) 
from (
    select  name
            ,col1 
    from testarray2 
    lateral view explode(weight) t1 as col1
) t1 group by t1.name;

相關文章