一文學完所有的 Hive SQL（兩萬字最全詳解）

Hive SQL 大全

本文基本涵蓋了 Hive 日常使用的所有 SQL，因爲 SQL 太多，所以將 SQL 進行了如下分類：

一、DDL 語句（數據定義語句）：

1. 對數據庫的操作：包含創建、修改數據庫

2. 對數據表的操作：分爲內部表及外部表，分區表和分桶表

二、DQL 語句（數據查詢語句）：

1. 單表查詢、關聯查詢

2. hive 函數：包含聚合函數，條件函數，日期函數，字符串函數等

3. 行轉列及列轉行：lateral view 與 explode 以及 reflect

4. 窗口函數與分析函數

5. 其他一些窗口函數

Hive 的 DDL 語法

對數據庫的操作

• 創建數據庫:

create database if not exists myhive;
說明：hive的表存放位置模式是由hive-site.xml當中的一個屬性指定的 :hive.metastore.warehouse.dir

創建數據庫並指定hdfs存儲位置 :
create database myhive2 location '/myhive2';

• 修改數據庫:

alter  database  myhive2  set  dbproperties('createtime'='20210329');

說明：可以使用 alter  database 命令來修改數據庫的一些屬性。但是數據庫的元數據信息是不可更改的，包括數據庫的名稱以及數據庫所在的位置

• 查看數據庫詳細信息

查看數據庫基本信息
hive (myhive)> desc  database  myhive2;

查看數據庫更多詳細信息
hive (myhive)> desc database extended  myhive2;

• 刪除數據庫

刪除一個空數據庫，如果數據庫下面有數據表，那麼就會報錯
drop  database  myhive2;

強制刪除數據庫，包含數據庫下面的表一起刪除
drop  database  myhive  cascade;

對數據表的操作

對管理表 (內部表) 的操作:

• 建內部表:

hive (myhive)> use myhive; -- 使用myhive數據庫
hive (myhive)> create table stu(id int,name string);
hive (myhive)> insert into stu values (1,"zhangsan");
hive (myhive)> insert into stu values (1,"zhangsan"),(2,"lisi");  -- 一次插入多條數據
hive (myhive)> select * from stu;

• hive 建表時候的字段類型:

OpEX09

對 decimal 類型簡單解釋下：
用法：decimal(11,2) 代表最多有 11 位數字，其中後 2 位是小數，整數部分是 9 位；如果整數部分超過 9 位，則這個字段就會變成 null；如果小數部分不足 2 位，則後面用 0 補齊兩位，如果小數部分超過兩位，則超出部分四捨五入
也可直接寫 decimal，後面不指定位數，默認是 decimal(10,0)  整數 10 位，沒有小數

• 創建表並指定字段之間的分隔符

create  table if not exists stu2(id int ,name string) row format delimited fields terminated by '\t' stored as textfile location '/user/stu2';

row format delimited fields terminated by '\t'  指定字段分隔符，默認分隔符爲 '\001'
stored as 指定存儲格式
location 指定存儲位置

• 根據查詢結果創建表

create table stu3 as select * from stu2;

• 根據已經存在的表結構創建表

create table stu4 like stu2;

• 查詢表的結構

只查詢表內字段及屬性
desc stu2;

詳細查詢
desc formatted  stu2;

• 查詢創建表的語句

show create table stu2;

對外部表操作

外部表因爲是指定其他的 hdfs 路徑的數據加載到表當中來，所以 hive 表會認爲自己不完全獨佔這份數據，所以刪除 hive 表的時候，數據仍然存放在 hdfs 當中，不會刪掉，只會刪除表的元數據

• 構建外部表

create external table student (s_id string,s_name string) row format delimited fields terminated by '\t';

• 從本地文件系統向表中加載數據

追加操作
load data local inpath '/export/servers/hivedatas/student.csv' into table student;

覆蓋操作
load data local inpath '/export/servers/hivedatas/student.csv' overwrite  into table student;

• 從 hdfs 文件系統向表中加載數據

load data inpath '/hivedatas/techer.csv' into table techer;

加載數據到指定分區
load data inpath '/hivedatas/techer.csv' into table techer partition(cur_date=20201210);

• 注意：
  1. 使用 load data local 表示從本地文件系統加載，文件會拷貝到 hdfs 上
  2. 使用 load data 表示從 hdfs 文件系統加載，文件會直接移動到 hive 相關目錄下，注意不是拷貝過去，因爲 hive 認爲 hdfs 文件已經有 3 副本了，沒必要再次拷貝了
  3. 如果表是分區表，load 時不指定分區會報錯
  4. 如果加載相同文件名的文件，會被自動重命名

對分區表的操作

• 創建分區表的語法

create table score(s_id string, s_score int) partitioned by (month string);

• 創建一個錶帶多個分區

create table score2 (s_id string, s_score int) partitioned by (year string,month string,day string);

注意：
hive 表創建的時候可以用 location 指定一個文件或者文件夾，當指定文件夾時，hive 會加載文件夾下的所有文件，當表中無分區時，這個文件夾下不能再有文件夾，否則報錯
當表是分區表時，比如 partitioned by (day string)， 則這個文件夾下的每一個文件夾就是一個分區，且文件夾名爲 day=20201123 這種格式，然後使用：msck  repair   table  score; 修復表結構，成功之後即可看到數據已經全部加載到表當中去了

• 加載數據到一個分區的表中

load data local inpath '/export/servers/hivedatas/score.csv' into table score partition (month='201806');

• 加載數據到一個多分區的表中去

load data local inpath '/export/servers/hivedatas/score.csv' into table score2 partition(year='2018',month='06',day='01');

• 查看分區

show  partitions  score;

• 添加一個分區

alter table score add partition(month='201805');

• 同時添加多個分區

 alter table score add partition(month='201804') partition(month = '201803');

注意：添加分區之後就可以在 hdfs 文件系統當中看到表下面多了一個文件夾

• 刪除分區

 alter table score drop partition(month = '201806');

對分桶表操作

將數據按照指定的字段進行分成多個桶中去，就是按照分桶字段進行哈希劃分到多個文件當中去
分區就是分文件夾，分桶就是分文件

分桶優點：

1. 提高 join 查詢效率
2. 提高抽樣效率

• 開啓 hive 的捅表功能

set hive.enforce.bucketing=true;

• 設置 reduce 的個數

set mapreduce.job.reduces=3;

• 創建桶表

create table course (c_id string,c_name string) clustered by(c_id) into 3 buckets;

桶表的數據加載：由於桶表的數據加載通過 hdfs  dfs  -put 文件或者通過 load  data 均不可以，只能通過 insert  overwrite 進行加載
所以把文件加載到桶表中，需要先創建普通表，並通過 insert  overwrite 的方式將普通表的數據通過查詢的方式加載到桶表當中去

• 通過 insert  overwrite 給桶表中加載數據

insert overwrite table course select * from course_common cluster by(c_id);  -- 最後指定桶字段

修改表和刪除表

• 修改表名稱

alter  table  old_table_name  rename  to  new_table_name;

• 增加 / 修改列信息

查詢表結構
desc score5;

添加列
alter table score5 add columns (mycol string, mysco string);

更新列
alter table score5 change column mysco mysconew int;

• 刪除表操作

drop table score5;

• 清空表操作

truncate table score6;

說明：只能清空管理表，也就是內部表；清空外部表，會產生錯誤

注意：truncate 和 drop：
如果 hdfs 開啓了回收站，drop 刪除的表數據是可以從回收站恢復的，表結構恢復不了，需要自己重新創建；truncate 清空的表是不進回收站的，所以無法恢復 truncate 清空的表
所以 truncate 一定慎用，一旦清空將無力迴天

向 hive 表中加載數據

• 直接向分區表中插入數據

insert into table score partition(month ='201807') values ('001','002','100');

• 通過 load 方式加載數據

 load data local inpath '/export/servers/hivedatas/score.csv' overwrite into table score partition(month='201806');

• 通過查詢方式加載數據

insert overwrite table score2 partition(month = '201806') select s_id,c_id,s_score from score1;

• 查詢語句中創建表並加載數據

create table score2 as select * from score1;

• 在創建表是通過 location 指定加載數據的路徑

create external table score6 (s_id string,c_id string,s_score int) row format delimited fields terminated by ',' location '/myscore';

• export 導出與 import 導入 hive 表數據（內部表操作）

create table techer2 like techer; --依據已有表結構創建表

export table techer to  '/export/techer';

import table techer2 from '/export/techer';

hive 表中數據導出

• insert 導出

將查詢的結果導出到本地
insert overwrite local directory '/export/servers/exporthive' select * from score;

將查詢的結果格式化導出到本地
insert overwrite local directory '/export/servers/exporthive' row format delimited fields terminated by '\t' collection items terminated by '#' select * from student;

將查詢的結果導出到HDFS上(沒有local)
insert overwrite directory '/export/servers/exporthive' row format delimited fields terminated by '\t' collection items terminated by '#' select * from score;

• Hadoop 命令導出到本地

dfs -get /export/servers/exporthive/000000_0 /export/servers/exporthive/local.txt;

• hive shell 命令導出

基本語法：（hive -f/-e 執行語句或者腳本 > file）

hive -e "select * from myhive.score;" > /export/servers/exporthive/score.txt

hive -f export.sh > /export/servers/exporthive/score.txt

• export 導出到 HDFS 上

export table score to '/export/exporthive/score';

Hive 的 DQL 查詢語法

SELECT [ALL | DISTINCT] select_expr, select_expr, ... 
FROM table_reference
[WHERE where_condition] 
[GROUP BY col_list [HAVING condition]] 
[CLUSTER BY col_list 
  | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list] 
] 
[LIMIT number]

注意：
1、order by 會對輸入做全局排序，因此只有一個 reducer，會導致當輸入規模較大時，需要較長的計算時間。
2、sort by 不是全局排序，其在數據進入 reducer 前完成排序。因此，如果用 sort by 進行排序，並且設置 mapred.reduce.tasks>1，則 sort by 只保證每個 reducer 的輸出有序，不保證全局有序。
3、distribute by(字段) 根據指定的字段將數據分到不同的 reducer，且分發算法是 hash 散列。
4、Cluster by(字段) 除了具有 Distribute by 的功能外，還會對該字段進行排序。
因此，如果分桶和 sort 字段是同一個時，此時，cluster by = distribute by + sort by

• WHERE 語句

select * from score where s_score < 60;

注意：
小於某個值是不包含 null 的，如上查詢結果是把 s_score 爲 null 的行剔除的

• GROUP BY 分組

select s_id ,avg(s_score) from score group by s_id;

分組後對數據進行篩選，使用having
 select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;

注意：
如果使用 group by 分組，則 select 後面只能寫分組的字段或者聚合函數
where 和 having 區別：
1 having 是在 group by 分完組之後再對數據進行篩選，所以 having 要篩選的字段只能是分組字段或者聚合函數
2 where 是從數據表中的字段直接進行的篩選的，所以不能跟在 gruop by 後面，也不能使用聚合函數

• join 連接

INNER JOIN 內連接：只有進行連接的兩個表中都存在與連接條件相匹配的數據纔會被保留下來
select * from techer t [inner] join course c on t.t_id = c.t_id; -- inner 可省略

LEFT OUTER JOIN 左外連接：左邊所有數據會被返回，右邊符合條件的被返回
select * from techer t left join course c on t.t_id = c.t_id; -- outer可省略

RIGHT OUTER JOIN 右外連接：右邊所有數據會被返回，左邊符合條件的被返回、
select * from techer t right join course c on t.t_id = c.t_id;

FULL OUTER JOIN 滿外(全外)連接: 將會返回所有表中符合條件的所有記錄。如果任一表的指定字段沒有符合條件的值的話，那麼就使用NULL值替代。
SELECT * FROM techer t FULL JOIN course c ON t.t_id = c.t_id ;

注：1. hive2 版本已經支持不等值連接，就是 join on 條件後面可以使用大於小於符號了; 並且也支持 join on 條件後跟 or (早前版本 on 後只支持 = 和 and，不支持> < 和 or)
2. 如 hive 執行引擎使用 MapReduce，一個 join 就會啓動一個 job，一條 sql 語句中如有多個 join，則會啓動多個 job

注意：表之間用逗號 (,) 連接和 inner join 是一樣的
select * from table_a,table_b where table_a.id=table_b.id;
它們的執行效率沒有區別，只是書寫方式不同，用逗號是 sql 89 標準，join 是 sql 92 標準。用逗號連接後面過濾條件用 where ，用 join 連接後面過濾條件是 on。

• order by 排序

全局排序，只會有一個reduce
ASC（ascend）: 升序（默認） DESC（descend）: 降序
SELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_score DESC;

注意：order by 是全局排序，所以最後只有一個 reduce，也就是在一個節點執行，如果數據量太大，就會耗費較長時間

• sort by 局部排序

每個MapReduce內部進行排序，對全局結果集來說不是排序。

設置reduce個數
set mapreduce.job.reduces=3;

查看設置reduce個數
set mapreduce.job.reduces;

查詢成績按照成績降序排列
select * from score sort by s_score;
 
將查詢結果導入到文件中（按照成績降序排列）
insert overwrite local directory '/export/servers/hivedatas/sort' select * from score sort by s_score;

• distribute by  分區排序

distribute by：類似MR中partition，進行分區，結合sort by使用

設置reduce的個數，將我們對應的s_id劃分到對應的reduce當中去
set mapreduce.job.reduces=7;

通過distribute by  進行數據的分區
select * from score distribute by s_id sort by s_score;

注意：Hive 要求 distribute by 語句要寫在 sort by 語句之前

• cluster by

當distribute by和sort by字段相同時，可以使用cluster by方式.
cluster by除了具有distribute by的功能外還兼具sort by的功能。但是排序只能是正序排序，不能指定排序規則爲ASC或者DESC。

以下兩種寫法等價
select * from score cluster by s_id;
select * from score distribute by s_id sort by s_id;

Hive 函數

聚合函數

hive支持 count(),max(),min(),sum(),avg() 等常用的聚合函數

注意：
聚合操作時要注意 null 值
count(*) 包含 null 值，統計所有行數
count(id) 不包含 null 值
min 求最小值是不包含 null，除非所有值都是 null
avg 求平均值也是不包含 null

• 非空集合總體變量函數: var_pop

語法: var_pop(col)
返回值: double
說明: 統計結果集中col非空集合的總體變量（忽略null）

• 非空集合樣本變量函數: var_samp

語法: var_samp (col)
返回值: double
說明: 統計結果集中col非空集合的樣本變量（忽略null）

• 總體標準偏離函數: stddev_pop

語法: stddev_pop(col)
返回值: double
說明: 該函數計算總體標準偏離，並返回總體變量的平方根，其返回值與VAR_POP函數的平方根相同

• 中位數函數: percentile

語法: percentile(BIGINT col, p)
返回值: double
說明: 求準確的第pth個百分位數，p必須介於0和1之間，但是col字段目前只支持整數，不支持浮點數類型

關係運算

支持：等值(=)、不等值(!= 或 <>)、小於(<)、小於等於(<=)、大於(>)、大於等於(>=)

空值判斷(is null)、非空判斷(is not null)

• LIKE 比較: LIKE

語法: A LIKE B
操作類型: strings
描述: 如果字符串A或者字符串B爲NULL，則返回NULL；如果字符串A符合表達式B 的正則語法，則爲TRUE；否則爲FALSE。B中字符”_”表示任意單個字符，而字符”%”表示任意數量的字符。

• JAVA 的 LIKE 操作: RLIKE

語法: A RLIKE B
操作類型: strings
描述: 如果字符串A或者字符串B爲NULL，則返回NULL；如果字符串A符合JAVA正則表達式B的正則語法，則爲TRUE；否則爲FALSE。

• REGEXP 操作: REGEXP

語法: A REGEXP B
操作類型: strings
描述: 功能與RLIKE相同
示例：select 1 from tableName where 'footbar' REGEXP '^f.*r$';
結果：1

數學運算

支持所有數值類型：加(+)、減(-)、乘(*)、除(/)、取餘(%)、位與(&)、位或(|)、位異或(^)、位取反(~)

邏輯運算

支持：邏輯與(and)、邏輯或(or)、邏輯非(not)

數值運算

• 取整函數: round

語法: round(double a)
返回值: BIGINT
說明: 返回double類型的整數值部分 （遵循四捨五入）
示例：select round(3.1415926) from tableName;
結果：3

• 指定精度取整函數: round

語法: round(double a, int d)
返回值: DOUBLE
說明: 返回指定精度d的double類型
hive> select round(3.1415926,4) from tableName;
3.1416

• 向下取整函數: floor

語法: floor(double a)
返回值: BIGINT
說明: 返回等於或者小於該double變量的最大的整數
hive> select floor(3.641) from tableName;
3

• 向上取整函數: ceil

語法: ceil(double a)
返回值: BIGINT
說明: 返回等於或者大於該double變量的最小的整數
hive> select ceil(3.1415926) from tableName;
4

• 取隨機數函數: rand

語法: rand(),rand(int seed)
返回值: double
說明: 返回一個0到1範圍內的隨機數。如果指定種子seed，則會等到一個穩定的隨機數序列
hive> select rand() from tableName; -- 每次執行此語句得到的結果都不同
0.5577432776034763

hive> select rand(100) ;  -- 只要指定種子，每次執行此語句得到的結果一樣的
0.7220096548596434

• 自然指數函數: exp

語法: exp(double a)
返回值: double
說明: 返回自然對數e的a次方
hive> select exp(2) ;
7.38905609893065

• 以 10 爲底對數函數: log10

語法: log10(double a)
返回值: double
說明: 返回以10爲底的a的對數
hive> select log10(100) ;
2.0

此外還有：以 2 爲底對數函數: log2()、對數函數: log()

• 冪運算函數: pow

語法: pow(double a, double p)
返回值: double
說明: 返回a的p次冪
hive> select pow(2,4) ;
16.0

• 開平方函數: sqrt

語法: sqrt(double a)
返回值: double
說明: 返回a的平方根
hive> select sqrt(16) ;
4.0

• 二進制函數: bin

語法: bin(BIGINT a)
返回值: string
說明: 返回a的二進制代碼表示
hive> select bin(7) ;
111

十六進制函數: hex()、將十六進制轉化爲字符串函數: unhex()
進制轉換函數: conv(bigint num, int from_base, int to_base) 說明: 將數值 num 從 from_base 進制轉化到 to_base 進制

此外還有很多數學函數：絕對值函數: abs()、正取餘函數: pmod()、正弦函數: sin()、反正弦函數: asin()、餘弦函數: cos()、反餘弦函數: acos()、positive 函數: positive()、negative 函數: negative()

條件函數

• If 函數: if

語法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
返回值: T
說明: 當條件testCondition爲TRUE時，返回valueTrue；否則返回valueFalseOrNull
hive> select if(1=2,100,200) ;
200
hive> select if(1=1,100,200) ;
100

• 非空查找函數: coalesce

語法: coalesce(T v1, T v2, …)
返回值: T
說明: 返回參數中的第一個非空值；如果所有值都爲NULL，那麼返回NULL
hive> select coalesce(null,'100','50') ;
100

• 條件判斷函數：case when (兩種寫法，其一)

語法: case when a then b [when c then d]* [else e] end
返回值: T
說明：如果a爲TRUE,則返回b；如果c爲TRUE，則返回d；否則返回e
hive> select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from tableName;
mary

• 條件判斷函數：case when (兩種寫法，其二)

語法: case a when b then c [when d then e]* [else f] end
返回值: T
說明：如果a等於b，那麼返回c；如果a等於d，那麼返回e；否則返回f
hive> Select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from tableName;
mary

日期函數

注：以下 SQL 語句中的 from tableName 可去掉，不影響查詢結果

1. 獲取當前 UNIX 時間戳函數: unix_timestamp

語法: unix_timestamp()
返回值: bigint
說明: 獲得當前時區的UNIX時間戳
hive> select unix_timestamp() from tableName;
1616906976

2. UNIX 時間戳轉日期函數: from_unixtime

語法: from_unixtime(bigint unixtime[, string format])
返回值: string
說明: 轉化UNIX時間戳（從1970-01-01 00:00:00 UTC到指定時間的秒數）到當前時區的時間格式
hive> select from_unixtime(1616906976,'yyyyMMdd') from tableName;
20210328

3. 日期轉 UNIX 時間戳函數: unix_timestamp

語法: unix_timestamp(string date)
返回值: bigint
說明: 轉換格式爲"yyyy-MM-dd HH:mm:ss"的日期到UNIX時間戳。如果轉化失敗，則返回0。
hive>  select unix_timestamp('2021-03-08 14:21:15') from tableName;
1615184475

4. 指定格式日期轉 UNIX 時間戳函數: unix_timestamp

語法: unix_timestamp(string date, string pattern)
返回值: bigint
說明: 轉換pattern格式的日期到UNIX時間戳。如果轉化失敗，則返回0。
hive>  select unix_timestamp('2021-03-08 14:21:15','yyyyMMdd HH:mm:ss') from tableName;
1615184475

5. 日期時間轉日期函數: to_date

語法: to_date(string timestamp)
返回值: string
說明: 返回日期時間字段中的日期部分。
hive> select to_date('2021-03-28 14:03:01') from tableName;
2021-03-28

6. 日期轉年函數: year

語法: year(string date)
返回值: int
說明: 返回日期中的年。
hive> select year('2021-03-28 10:03:01') from tableName;
2021
hive> select year('2021-03-28') from tableName;
2021

7. 日期轉月函數: month

語法: month (string date)
返回值: int
說明: 返回日期中的月份。
hive> select month('2020-12-28 12:03:01') from tableName;
12
hive> select month('2021-03-08') from tableName;
8

8. 日期轉天函數: day

語法: day (string date)
返回值: int
說明: 返回日期中的天。
hive> select day('2020-12-08 10:03:01') from tableName;
8
hive> select day('2020-12-24') from tableName;
24

9. 日期轉小時函數: hour

語法: hour (string date)
返回值: int
說明: 返回日期中的小時。
hive> select hour('2020-12-08 10:03:01') from tableName;
10

10. 日期轉分鐘函數: minute

語法: minute (string date)
返回值: int
說明: 返回日期中的分鐘。
hive> select minute('2020-12-08 10:03:01') from tableName;
3

11. 日期轉秒函數: second

語法: second (string date)
返回值: int
說明: 返回日期中的秒。
hive> select second('2020-12-08 10:03:01') from tableName;
1

12. 日期轉周函數: weekofyear

語法: weekofyear (string date)
返回值: int
說明: 返回日期在當前的週數。
hive> select weekofyear('2020-12-08 10:03:01') from tableName;
49

13. 日期比較函數: datediff

語法: datediff(string enddate, string startdate)
返回值: int
說明: 返回結束日期減去開始日期的天數。
hive> select datediff('2020-12-08','2020-05-09') from tableName;
213

14. 日期增加函數: date_add

語法: date_add(string startdate, int days)
返回值: string
說明: 返回開始日期startdate增加days天后的日期。
hive> select date_add('2020-12-08',10) from tableName;
2020-12-18

15. 日期減少函數: date_sub

語法: date_sub (string startdate, int days)
返回值: string
說明: 返回開始日期startdate減少days天后的日期。
hive> select date_sub('2020-12-08',10) from tableName;
2020-11-28

字符串函數

1. 字符串長度函數：length

語法: length(string A)
返回值: int
說明：返回字符串A的長度
hive> select length('abcedfg') from tableName;
7

2. 字符串反轉函數：reverse

語法: reverse(string A)
返回值: string
說明：返回字符串A的反轉結果
hive> select reverse('abcedfg') from tableName;
gfdecba

3. 字符串連接函數：concat

語法: concat(string A, string B…)
返回值: string
說明：返回輸入字符串連接後的結果，支持任意個輸入字符串
hive> select concat('abc','def’,'gh')from tableName;
abcdefgh

4. 帶分隔符字符串連接函數：concat_ws

語法: concat_ws(string SEP, string A, string B…)
返回值: string
說明：返回輸入字符串連接後的結果，SEP表示各個字符串間的分隔符
hive> select concat_ws(',','abc','def','gh')from tableName;
abc,def,gh

5. 字符串截取函數：substr,substring

語法: substr(string A, int start),substring(string A, int start)
返回值: string
說明：返回字符串A從start位置到結尾的字符串
hive> select substr('abcde',3) from tableName;
cde
hive> select substring('abcde',3) from tableName;
cde
hive> select substr('abcde',-1) from tableName; （和ORACLE相同）
e

6. 字符串截取函數：substr,substring

語法: substr(string A, int start, int len),substring(string A, int start, int len)
返回值: string
說明：返回字符串A從start位置開始，長度爲len的字符串
hive> select substr('abcde',3,2) from tableName;
cd
hive> select substring('abcde',3,2) from tableName;
cd
hive>select substring('abcde',-2,2) from tableName;
de

7. 字符串轉大寫函數：upper,ucase

語法: upper(string A) ucase(string A)
返回值: string
說明：返回字符串A的大寫格式
hive> select upper('abSEd') from tableName;
ABSED
hive> select ucase('abSEd') from tableName;
ABSED

8. 字符串轉小寫函數：lower,lcase

語法: lower(string A) lcase(string A)
返回值: string
說明：返回字符串A的小寫格式
hive> select lower('abSEd') from tableName;
absed
hive> select lcase('abSEd') from tableName;
absed

9. 去空格函數：trim

語法: trim(string A)
返回值: string
說明：去除字符串兩邊的空格
hive> select trim(' abc ') from tableName;
abc

10. 左邊去空格函數：ltrim

語法: ltrim(string A)
返回值: string
說明：去除字符串左邊的空格
hive> select ltrim(' abc ') from tableName;
abc

11. 右邊去空格函數：rtrim

語法: rtrim(string A)
返回值: string
說明：去除字符串右邊的空格
hive> select rtrim(' abc ') from tableName;
abc

12. 正則表達式替換函數：regexp_replace

語法: regexp_replace(string A, string B, string C)
返回值: string
說明：將字符串A中的符合java正則表達式B的部分替換爲C。注意，在有些情況下要使用轉義字符,類似oracle中的regexp_replace函數。
hive> select regexp_replace('foobar', 'oo|ar', '') from tableName;
fb

13. 正則表達式解析函數：regexp_extract

語法: regexp_extract(string subject, string pattern, int index)
返回值: string
說明：將字符串subject按照pattern正則表達式的規則拆分，返回index指定的字符。
hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) from tableName;
the
hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 2) from tableName;
bar
hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 0) from tableName;
foothebar
strong>注意，在有些情況下要使用轉義字符，下面的等號要用雙豎線轉義，這是java正則表達式的規則。
select data_field,
regexp_extract(data_field,'.*?bgStart\\=([^&]+)',1) as aaa,
regexp_extract(data_field,'.*?contentLoaded_headStart\\=([^&]+)',1) as bbb,
regexp_extract(data_field,'.*?AppLoad2Req\\=([^&]+)',1) as ccc 
from pt_nginx_loginlog_st 
where pt = '2021-03-28' limit 2;

14. URL 解析函數：parse_url

語法: parse_url(string urlString, string partToExtract [, string keyToExtract])
返回值: string
說明：返回URL中指定的部分。partToExtract的有效值爲：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
hive> select parse_url
('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') 
from tableName;
www.tableName.com 
hive> select parse_url
('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1')
 from tableName;
v1

15. json 解析函數：get_json_object

語法: get_json_object(string json_string, string path)
返回值: string
說明：解析json的字符串json_string,返回path指定的內容。如果輸入的json字符串無效，那麼返回NULL。
hive> select  get_json_object('{"store":{"fruit":\[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}], "bicycle":{"price":19.95,"color":"red"} },"email":"amy@only_for_json_udf_test.net","owner":"amy"}','$.owner') from tableName;

16. 空格字符串函數：space

語法: space(int n)
返回值: string
說明：返回長度爲n的字符串
hive> select space(10) from tableName;
hive> select length(space(10)) from tableName;
10

17. 重複字符串函數：repeat

語法: repeat(string str, int n)
返回值: string
說明：返回重複n次後的str字符串
hive> select repeat('abc',5) from tableName;
abcabcabcabcabc

18. 首字符 ascii 函數：ascii

語法: ascii(string str)
返回值: int
說明：返回字符串str第一個字符的ascii碼
hive> select ascii('abcde') from tableName;
97

19. 左補足函數：lpad

語法: lpad(string str, int len, string pad)
返回值: string
說明：將str進行用pad進行左補足到len位
hive> select lpad('abc',10,'td') from tableName;
tdtdtdtabc
注意：與GP，ORACLE不同，pad 不能默認

20. 右補足函數：rpad

語法: rpad(string str, int len, string pad)
返回值: string
說明：將str進行用pad進行右補足到len位
hive> select rpad('abc',10,'td') from tableName;
abctdtdtdt

21. 分割字符串函數: split

語法: split(string str, string pat)
返回值: array
說明: 按照pat字符串分割str，會返回分割後的字符串數組
hive> select split('abtcdtef','t') from tableName;
["ab","cd","ef"]

22. 集合查找函數: find_in_set

語法: find_in_set(string str, string strList)
返回值: int
說明: 返回str在strlist第一次出現的位置，strlist是用逗號分割的字符串。如果沒有找該str字符，則返回0
hive> select find_in_set('ab','ef,ab,de') from tableName;
2
hive> select find_in_set('at','ef,ab,de') from tableName;
0

複合類型構建操作

  1. Map 類型構建: map

語法: map (key1, value1, key2, value2, …)
說明：根據輸入的key和value對構建map類型
hive> Create table mapTable as select map('100','tom','200','mary') as t from tableName;
hive> describe mapTable;
t       map<string ,string>
hive> select t from tableName;
{"100":"tom","200":"mary"}

2. Struct 類型構建: struct

語法: struct(val1, val2, val3, …)
說明：根據輸入的參數構建結構體struct類型
hive> create table struct_table as select struct('tom','mary','tim') as t from tableName;
hive> describe struct_table;
t       struct<col1:string ,col2:string,col3:string>
hive> select t from tableName;
{"col1":"tom","col2":"mary","col3":"tim"}

3. array 類型構建: array

語法: array(val1, val2, …)
說明：根據輸入的參數構建數組array類型
hive> create table arr_table as select array("tom","mary","tim") as t from tableName;
hive> describe tableName;
t       array<string>
hive> select t from tableName;
["tom","mary","tim"]

複雜類型訪問操作

1. array 類型訪問: A[n]

語法: A[n]
操作類型: A爲array類型，n爲int類型
說明：返回數組A中的第n個變量值。數組的起始下標爲0。比如，A是個值爲['foo', 'bar']的數組類型，那麼A[0]將返回'foo',而A[1]將返回'bar'
hive> create table arr_table2 as select array("tom","mary","tim") as t
 from tableName;
hive> select t[0],t[1] from arr_table2;
tom     mary    tim

2. map 類型訪問: M[key]

語法: M[key]
操作類型: M爲map類型，key爲map中的key值
說明：返回map類型M中，key值爲指定值的value值。比如，M是值爲{'f' -> 'foo', 'b' -> 'bar', 'all' -> 'foobar'}的map類型，那麼M['all']將會返回'foobar'
hive> Create table map_table2 as select map('100','tom','200','mary') as t from tableName;
hive> select t['200'],t['100'] from map_table2;
mary    tom

3. struct 類型訪問: S.x

語法: S.x
操作類型: S爲struct類型
說明：返回結構體S中的x字段。比如，對於結構體struct foobar {int foo, int bar}，foobar.foo返回結構體中的foo字段
hive> create table str_table2 as select struct('tom','mary','tim') as t from tableName;
hive> describe tableName;
t       struct<col1:string ,col2:string,col3:string>
hive> select t.col1,t.col3 from str_table2;
tom     tim

複雜類型長度統計函數

• Map 類型長度函數: size(Map<k .V>)

語法: size(Map<k .V>)
返回值: int
說明: 返回map類型的長度
hive> select size(t) from map_table2;
2

• array 類型長度函數: size(Array)

語法: size(Array<T>)
返回值: int
說明: 返回array類型的長度
hive> select size(t) from arr_table2;
4

• 類型轉換函數

類型轉換函數: cast
語法: cast(expr as <type>)
返回值: Expected "=" to follow "type"
說明: 返回轉換後的數據類型
hive> select cast('1' as bigint) from tableName;
1

hive 當中的 lateral view 與 explode 以及 reflect 和窗口函數

使用 explode 函數將 hive 表中的 Map 和 Array 字段數據進行拆分

lateral view 用於和 split、explode 等 UDTF 一起使用的，能將一行數據拆分成多行數據，在此基礎上可以對拆分的數據進行聚合，lateral view 首先爲原始表的每行調用 UDTF，UDTF 會把一行拆分成一行或者多行，lateral view 在把結果組合，產生一個支持別名表的虛擬表。

其中 explode 還可以用於將 hive 一列中複雜的 array 或者 map 結構拆分成多行

需求：現在有數據格式如下

zhangsan child1,child2,child3,child4 k1:v1,k2:v2

lisi child5,child6,child7,child8 k3:v3,k4:v4

字段之間使用 \ t 分割，需求將所有的 child 進行拆開成爲一列

+----------+--+
| mychild  |
+----------+--+
| child1   |
| child2   |
| child3   |
| child4   |
| child5   |
| child6   |
| child7   |
| child8   |
+----------+--+

將 map 的 key 和 value 也進行拆開，成爲如下結果

+-----------+-------------+--+
| mymapkey  | mymapvalue  |
+-----------+-------------+--+
| k1        | v1          |
| k2        | v2          |
| k3        | v3          |
| k4        | v4          |
+-----------+-------------+--+

1. 創建 hive 數據庫

創建hive數據庫
hive (default)> create database hive_explode;
hive (default)> use hive_explode;

2. 創建 hive 表，然後使用 explode 拆分 map 和 array

hive (hive_explode)> create  table t3(name string,children array<string>,address Map<string,string>) row format delimited fields terminated by '\t'  collection items terminated by ',' map keys terminated by ':' stored as textFile;

3. 加載數據

node03執行以下命令創建表數據文件
 mkdir -p /export/servers/hivedatas/
 cd /export/servers/hivedatas/
 vim maparray
內容如下:
zhangsan child1,child2,child3,child4 k1:v1,k2:v2
lisi child5,child6,child7,child8 k3:v3,k4:v4

hive表當中加載數據
hive (hive_explode)> load data local inpath '/export/servers/hivedatas/maparray' into table t3;

4. 使用 explode 將 hive 當中數據拆開

將array當中的數據拆分開
hive (hive_explode)> SELECT explode(children) AS myChild FROM t3;

將map當中的數據拆分開

hive (hive_explode)> SELECT explode(address) AS (myMapKey, myMapValue) FROM t3;

使用 explode 拆分 json 字符串

需求: 需求：現在有一些數據格式如下：

a:shandong,b:beijing,c:hebei|1,2,3,4,5,6,7,8,9|[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]

其中字段與字段之間的分隔符是 |

我們要解析得到所有的 monthSales 對應的值爲以下這一列（行轉列）

4900

2090

6987

1. 創建 hive 表

hive (hive_explode)> create table explode_lateral_view
                   > (`area` string,
                   > `goods_id` string,
                   > `sale_info` string)
                   > ROW FORMAT DELIMITED
                   > FIELDS TERMINATED BY '|'
                   > STORED AS textfile;

2. 準備數據並加載數據

準備數據如下
cd /export/servers/hivedatas
vim explode_json

a:shandong,b:beijing,c:hebei|1,2,3,4,5,6,7,8,9|[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]

加載數據到hive表當中去
hive (hive_explode)> load data local inpath '/export/servers/hivedatas/explode_json' overwrite into table explode_lateral_view;

3. 使用 explode 拆分 Array

hive (hive_explode)> select explode(split(goods_id,',')) as goods_id from explode_lateral_view;

4. 使用 explode 拆解 Map

hive (hive_explode)> select explode(split(area,',')) as area from explode_lateral_view;

5. 拆解 json 字段

hive (hive_explode)> select explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')) as  sale_info from explode_lateral_view;

然後我們想用get_json_object來獲取key爲monthSales的數據：

hive (hive_explode)> select get_json_object(explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')),'$.monthSales') as  sale_info from explode_lateral_view;


然後掛了FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions
UDTF explode不能寫在別的函數內
如果你這麼寫，想查兩個字段，select explode(split(area,',')) as area,good_id from explode_lateral_view;
會報錯FAILED: SemanticException 1:40 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'good_id'
使用UDTF的時候，只支持一個字段，這時候就需要LATERAL VIEW出場了

配合 LATERAL  VIEW 使用

配合 lateral view 查詢多個字段

hive (hive_explode)> select goods_id2,sale_info from explode_lateral_view LATERAL VIEW explode(split(goods_id,','))goods as goods_id2;

其中LATERAL VIEW explode(split(goods_id,','))goods相當於一個虛擬表，與原表explode_lateral_view笛卡爾積關聯

也可以多重使用

hive (hive_explode)> select goods_id2,sale_info,area2
                    from explode_lateral_view 
                    LATERAL VIEW explode(split(goods_id,','))goods as goods_id2 
                    LATERAL VIEW explode(split(area,','))area as area2;也是三個表笛卡爾積的結果

最終，我們可以通過下面的句子，把這個 json 格式的一行數據，完全轉換成二維表的方式展現

hive (hive_explode)> select get_json_object(concat('{',sale_info_1,'}'),'$.source') as source,get_json_object(concat('{',sale_info_1,'}'),'$.monthSales') as monthSales,get_json_object(concat('{',sale_info_1,'}'),'$.userCount') as monthSales,get_json_object(concat('{',sale_info_1,'}'),'$.score') as monthSales from explode_lateral_view LATERAL VIEW explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{'))sale_info as sale_info_1;

總結：

Lateral View 通常和 UDTF 一起出現，爲了解決 UDTF 不允許在 select 字段的問題。Multiple Lateral View 可以實現類似笛卡爾乘積。Outer 關鍵字可以把不輸出的 UDTF 的空結果，輸出成 NULL，防止丟失數據。

行轉列

相關參數說明:

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

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

COLLECT_SET(col)：函數只接受基本數據類型，它的主要作用是將某字段的值進行去重彙總，產生 array 類型字段。

數據準備:

aWWoQK

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

射手座,A            老王|鳳姐
白羊座,A            孫悟空|豬八戒
白羊座,B            宋宋

實現步驟:

1. 創建本地 constellation.txt，導入數據

node03服務器執行以下命令創建文件，注意數據使用\t進行分割
cd /export/servers/hivedatas
vim constellation.txt

數據如下: 
孫悟空 白羊座 A
老王 射手座 A
宋宋 白羊座 B       
豬八戒 白羊座 A
鳳姐 射手座 A

2. 創建 hive 表並導入數據

創建hive表並加載數據
hive (hive_explode)> create table person_info(
                    name string, 
                    constellation string, 
                    blood_type string) 
                    row format delimited fields terminated by "\t";
                    
加載數據
hive (hive_explode)> load data local inpath '/export/servers/hivedatas/constellation.txt' into table person_info;

3. 按需求查詢數據

hive (hive_explode)> select
                        t1.base,
                        concat_ws('|', collect_set(t1.name)) name
                    from
                        (select
                            name,
                            concat(constellation, "," , blood_type) base
                        from
                            person_info) t1
                    group by
                        t1.base;

列轉行

所需函數:

EXPLODE(col)：將 hive 一列中複雜的 array 或者 map 結構拆分成多行。

LATERAL VIEW

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解釋：用於和 split, explode 等 UDTF 一起使用，它能夠將一列數據拆成多行數據，在此基礎上可以對拆分後的數據進行聚合。

數據準備:

cd /export/servers/hivedatas
vim movie.txt
文件內容如下:  數據字段之間使用\t進行分割
《疑犯追蹤》 懸疑,動作,科幻,劇情
《Lie to me》 懸疑,警匪,動作,心理,劇情
《戰狼2》 戰爭,動作,災難

需求: 將電影分類中的數組數據展開。結果如下：

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

實現步驟:

1. 創建 hive 表

create table movie_info(
    movie string, 
    category array<string>) 
row format delimited fields terminated by "\t"
collection items terminated by ",";

2. 加載數據

load data local inpath "/export/servers/hivedatas/movie.txt" into table movie_info;

3. 按需求查詢數據

select
    movie,
    category_name
from 
    movie_info lateral view explode(category) table_tmp as category_name;

reflect 函數

reflect 函數可以支持在 sql 中調用 java 中的自帶函數，秒殺一切 udf 函數。

需求 1: 使用 java.lang.Math 當中的 Max 求兩列中最大值

實現步驟:

1. 創建 hive 表

create table test_udf(col1 int,col2 int) row format delimited fields terminated by ',';

2. 準備數據並加載數據

cd /export/servers/hivedatas
vim test_udf 

文件內容如下:
1,2
4,3
6,4
7,5
5,6

3. 加載數據

hive (hive_explode)> load data local inpath '/export/servers/hivedatas/test_udf' overwrite into table test_udf;

4. 使用 java.lang.Math 當中的 Max 求兩列當中的最大值

hive (hive_explode)> select reflect("java.lang.Math","max",col1,col2) from test_udf;

需求 2: 文件中不同的記錄來執行不同的 java 的內置函數

實現步驟:

1. 創建 hive 表

hive (hive_explode)> create table test_udf2(class_name string,method_name string,col1 int , col2 int) row format delimited fields terminated by ',';

2. 準備數據

cd /export/servers/hivedatas
vim test_udf2

文件內容如下:
java.lang.Math,min,1,2
java.lang.Math,max,2,3

3. 加載數據

hive (hive_explode)> load data local inpath '/export/servers/hivedatas/test_udf2' overwrite into table test_udf2;

4. 執行查詢

hive (hive_explode)> select reflect(class_name,method_name,col1,col2) from test_udf2;

需求 3: 判斷是否爲數字

實現方式:

使用 apache commons 中的函數，commons 下的 jar 已經包含在 hadoop 的 classpath 中，所以可以直接使用。

select reflect("org.apache.commons.lang.math.NumberUtils","isNumber","123")

窗口函數與分析函數

在 sql 中有一類函數叫做聚合函數, 例如 sum()、avg()、max() 等等, 這類函數可以將多行數據按照規則聚集爲一行, 一般來講聚集後的行數是要少於聚集前的行數的。但是有時我們想要既顯示聚集前的數據, 又要顯示聚集後的數據, 這時我們便引入了窗口函數。窗口函數又叫 OLAP 函數 / 分析函數，窗口函數兼具分組和排序功能。

窗口函數最重要的關鍵字是 partition by 和 order by。

具體語法如下：over (partition by xxx order by xxx)

sum、avg、min、max

準備數據

建表語句:
create table test_t1(
cookieid string,
createtime string,   --day 
pv int
) row format delimited 
fields terminated by ',';

加載數據：
load data local inpath '/root/hivedata/test_t1.dat' into table test_t1;

cookie1,2020-04-10,1
cookie1,2020-04-11,5
cookie1,2020-04-12,7
cookie1,2020-04-13,3
cookie1,2020-04-14,2
cookie1,2020-04-15,4
cookie1,2020-04-16,4

開啓智能本地模式
SET hive.exec.mode.local.auto=true;

SUM 函數和窗口函數的配合使用：結果和 ORDER BY 相關, 默認爲升序。

select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime) as pv1 
from test_t1;

select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2
from test_t1;

select cookieid,createtime,pv,
sum(pv) over(partition by cookieid) as pv3
from test_t1;

select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4
from test_t1;

select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5
from test_t1;

select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6
from test_t1;


pv1: 分組內從起點到當前行的pv累積，如，11號的pv1=10號的pv+11號的pv, 12號=10號+11號+12號
pv2: 同pv1
pv3: 分組內(cookie1)所有的pv累加
pv4: 分組內當前行+往前3行，如，11號=10號+11號， 12號=10號+11號+12號，
                        13號=10號+11號+12號+13號， 14號=11號+12號+13號+14號
pv5: 分組內當前行+往前3行+往後1行，如，14號=11號+12號+13號+14號+15號=5+7+3+2+4=21
pv6: 分組內當前行+往後所有行，如，13號=13號+14號+15號+16號=3+2+4+4=13，
        14號=14號+15號+16號=2+4+4=10

如果不指定 rows between, 默認爲從起點到當前行;

如果不指定 order by，則將分組內所有值累加;

關鍵是理解 rows between 含義, 也叫做 window 子句：

preceding：往前

following：往後

current row：當前行

unbounded：起點

unbounded preceding 表示從前面的起點

unbounded following：表示到後面的終點

AVG，MIN，MAX，和 SUM 用法一樣。

row_number、rank、dense_rank、ntile

準備數據

cookie1,2020-04-10,1
cookie1,2020-04-11,5
cookie1,2020-04-12,7
cookie1,2020-04-13,3
cookie1,2020-04-14,2
cookie1,2020-04-15,4
cookie1,2020-04-16,4
cookie2,2020-04-10,2
cookie2,2020-04-11,3
cookie2,2020-04-12,5
cookie2,2020-04-13,6
cookie2,2020-04-14,3
cookie2,2020-04-15,9
cookie2,2020-04-16,7
 
CREATE TABLE test_t2 (
cookieid string,
createtime string,   --day 
pv INT
) ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
stored as textfile;
  
加載數據：
load data local inpath '/root/hivedata/test_t2.dat' into table test_t2;

• ROW_NUMBER() 使用

  ROW_NUMBER() 從 1 開始，按照順序，生成分組內記錄的序列。

SELECT 
cookieid,
createtime,
pv,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn 
FROM test_t2;

• RANK 和 DENSE_RANK 使用

  RANK() 生成數據項在分組中的排名，排名相等會在名次中留下空位 。

  DENSE_RANK() 生成數據項在分組中的排名，排名相等會在名次中不會留下空位。

SELECT 
cookieid,
createtime,
pv,
RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 
FROM test_t2 
WHERE cookieid = 'cookie1';

• NTILE

  有時會有這樣的需求: 如果數據排序後分爲三部分，業務人員只關心其中的一部分，如何將這中間的三分之一數據拿出來呢? NTILE 函數即可以滿足。

  ntile 可以看成是：把有序的數據集合平均分配到指定的數量（num）個桶中, 將桶號分配給每一行。如果不能平均分配，則優先分配較小編號的桶，並且各個桶中能放的行數最多相差 1。

  然後可以根據桶號，選取前或後 n 分之幾的數據。數據會完整展示出來，只是給相應的數據打標籤；具體要取幾分之幾的數據，需要再嵌套一層根據標籤取出。

SELECT 
cookieid,
createtime,
pv,
NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1,
NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2,
NTILE(4) OVER(ORDER BY createtime) AS rn3
FROM test_t2 
ORDER BY cookieid,createtime;

其他重要窗口函數

lag,lead,first_value,last_value

• LAG
  LAG(col,n,DEFAULT) 用於統計窗口內往上第 n 行值第一個參數爲列名，第二個參數爲往上第 n 行（可選，默認爲 1），第三個參數爲默認值（當往上第 n 行爲 NULL 時候，取默認值，如不指定，則爲 NULL）

SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,
LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time 
FROM test_t4;


last_1_time: 指定了往上第1行的值，default爲'1970-01-01 00:00:00'  
                 cookie1第一行，往上1行爲NULL,因此取默認值 1970-01-01 00:00:00
                 cookie1第三行，往上1行值爲第二行值，2015-04-10 10:00:02
                 cookie1第六行，往上1行值爲第五行值，2015-04-10 10:50:01
last_2_time: 指定了往上第2行的值，爲指定默認值
         cookie1第一行，往上2行爲NULL
         cookie1第二行，往上2行爲NULL
         cookie1第四行，往上2行爲第二行值，2015-04-10 10:00:02
         cookie1第七行，往上2行爲第五行值，2015-04-10 10:50:01

• LEAD

與 LAG 相反 LEAD(col,n,DEFAULT) 用於統計窗口內往下第 n 行值第一個參數爲列名，第二個參數爲往下第 n 行（可選，默認爲 1），第三個參數爲默認值（當往下第 n 行爲 NULL 時候，取默認值，如不指定，則爲 NULL）

SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LEAD(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time,
LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time 
FROM test_t4;

• FIRST_VALUE

  取分組內排序後，截止到當前行，第一個值

 SELECT cookieid,
 createtime,
 url,
 ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
 FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1 
 FROM test_t4;

• LAST_VALUE

取分組內排序後，截止到當前行，最後一個值

SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1 
FROM test_t4;

如果想要取分組內排序後最後一個值，則需要變通一下：

SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1,
FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime DESC) AS last2 
FROM test_t4 
ORDER BY cookieid,createtime;

特別注意 order  by

如果不指定 ORDER BY，則進行排序混亂，會出現錯誤的結果

SELECT cookieid,
createtime,
url,
FIRST_VALUE(url) OVER(PARTITION BY cookieid) AS first2  
FROM test_t4;

cume_dist,percent_rank

這兩個序列分析函數不是很常用，注意：序列函數不支持 WINDOW 子句

• 數據準備

d1,user1,1000
d1,user2,2000
d1,user3,3000
d2,user4,4000
d2,user5,5000
 
CREATE EXTERNAL TABLE test_t3 (
dept STRING,
userid string,
sal INT
) ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
stored as textfile;

加載數據：
load data local inpath '/root/hivedata/test_t3.dat' into table test_t3;

• CUME_DIST  和 order byd 的排序順序有關係

  CUME_DIST 小於等於當前值的行數 / 分組內總行數  order 默認順序 正序 升序 比如，統計小於等於當前薪水的人數，所佔總人數的比例

 SELECT 
 dept,
 userid,
 sal,
 CUME_DIST() OVER(ORDER BY sal) AS rn1,
 CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2 
 FROM test_t3;
 
 rn1: 沒有partition,所有數據均爲1組，總行數爲5，
      第一行：小於等於1000的行數爲1，因此，1/5=0.2
      第三行：小於等於3000的行數爲3，因此，3/5=0.6
 rn2: 按照部門分組，dpet=d1的行數爲3,
      第二行：小於等於2000的行數爲2，因此，2/3=0.6666666666666666

• PERCENT_RANK

  PERCENT_RANK 分組內當前行的 RANK 值 - 1 / 分組內總行數 - 1

  經調研 該函數顯示現實意義不明朗 有待於繼續考證

  SELECT 
  dept,
  userid,
  sal,
  PERCENT_RANK() OVER(ORDER BY sal) AS rn1,   --分組內
  RANK() OVER(ORDER BY sal) AS rn11,          --分組內RANK值
  SUM(1) OVER(PARTITION BY NULL) AS rn12,     --分組內總行數
  PERCENT_RANK() OVER(PARTITION BY dept ORDER BY sal) AS rn2 
  FROM test_t3;
  
  rn1: rn1 = (rn11-1) / (rn12-1) 
      第一行,(1-1)/(5-1)=0/4=0
      第二行,(2-1)/(5-1)=1/4=0.25
      第四行,(4-1)/(5-1)=3/4=0.75
  rn2: 按照dept分組，
       dept=d1的總行數爲3
       第一行，(1-1)/(3-1)=0
       第三行，(3-1)/(3-1)=1

grouping sets,grouping__id,cube,rollup

這幾個分析函數通常用於 OLAP 中，不能累加，而且需要根據不同維度上鑽和下鑽的指標統計，比如，分小時、天、月的 UV 數。

• 數據準備

2020-03,2020-03-10,cookie1
2020-03,2020-03-10,cookie5
2020-03,2020-03-12,cookie7
2020-04,2020-04-12,cookie3
2020-04,2020-04-13,cookie2
2020-04,2020-04-13,cookie4
2020-04,2020-04-16,cookie4
2020-03,2020-03-10,cookie2
2020-03,2020-03-10,cookie3
2020-04,2020-04-12,cookie5
2020-04,2020-04-13,cookie6
2020-04,2020-04-15,cookie3
2020-04,2020-04-15,cookie2
2020-04,2020-04-16,cookie1
 
CREATE TABLE test_t5 (
month STRING,
day STRING, 
cookieid STRING 
) ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
stored as textfile;

加載數據：
load data local inpath '/root/hivedata/test_t5.dat' into table test_t5;

• GROUPING SETS

grouping sets 是一種將多個 group by 邏輯寫在一個 sql 語句中的便利寫法。

等價於將不同維度的 GROUP BY 結果集進行 UNION ALL。

GROUPING__ID，表示結果屬於哪一個分組集合。

SELECT 
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID 
FROM test_t5 
GROUP BY month,day 
GROUPING SETS (month,day) 
ORDER BY GROUPING__ID;

grouping_id表示這一組結果屬於哪個分組集合，
根據grouping sets中的分組條件month，day，1是代表month，2是代表day

等價於 
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM test_t5 GROUP BY month UNION ALL 
SELECT NULL as month,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM test_t5 GROUP BY day;

再如：

SELECT 
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID 
FROM test_t5 
GROUP BY month,day 
GROUPING SETS (month,day,(month,day)) 
ORDER BY GROUPING__ID;

等價於
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM test_t5 GROUP BY month 
UNION ALL 
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM test_t5 GROUP BY day
UNION ALL 
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM test_t5 GROUP BY month,day;

• CUBE

根據 GROUP BY 的維度的所有組合進行聚合。

SELECT 
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID 
FROM test_t5 
GROUP BY month,day 
WITH CUBE 
ORDER BY GROUPING__ID;

等價於
SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM test_t5
UNION ALL 
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM test_t5 GROUP BY month 
UNION ALL 
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM test_t5 GROUP BY day
UNION ALL 
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM test_t5 GROUP BY month,day;

• ROLLUP

是 CUBE 的子集，以最左側的維度爲主，從該維度進行層級聚合。

比如，以month維度進行層級聚合：
SELECT 
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID  
FROM test_t5 
GROUP BY month,day
WITH ROLLUP 
ORDER BY GROUPING__ID;

--把month和day調換順序，則以day維度進行層級聚合：
 
SELECT 
day,
month,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID  
FROM test_t5 
GROUP BY day,month 
WITH ROLLUP 
ORDER BY GROUPING__ID;
（這裏，根據天和月進行聚合，和根據天聚合結果一樣，因爲有父子關係，如果是其他維度組合的話，就會不一樣）

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/NOegafO9BOzhbseQ7HzUQw