主頁 >  其他 > 一文學完所有的Hive Sql（兩萬字最全詳解）

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

2021-04-02 11:01:59 其他

Hive Sql 大全

本文基本涵蓋了Hive日常使用的所有SQL，因為SQL太多，所以將SQL進行了如下分類： 一、DDL陳述句（資料定義陳述句）：
對資料庫的操作：包含創建、修改資料庫
對資料表的操作：分為內部表及外部表，磁區表和分桶表
二、DQL陳述句（資料查詢陳述句）：
單表查詢、關聯查詢
hive函式：包含聚合函式，條件函式，日期函式，字串函式等
行轉列及列轉行：lateral view 與 explode 以及 reflect
視窗函式與分析函式
其他一些視窗函式

文章首發于公眾號【五分鐘學大資料】，大資料領域原創技術號，每周更新大資料技術文及面試真題決議，關注后可領取精心制作大資料面試寶典！

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建表時候的欄位型別:

分類型別描述字面量示例
原始型別BOOLEANtrue/falseTRUE
TINYINT1位元組的有符號整數 -128~1271Y
SMALLINT2個位元組的有符號整數，-32768~327671S
INT4個位元組的帶符號整數1
BIGINT8位元組帶符號整數1L
FLOAT4位元組單精度浮點數1.0
DOUBLE8位元組雙精度浮點數1.0
DEICIMAL任意精度的帶符號小數1.0
STRING字串，變長“a”,’b’
VARCHAR變長字串“a”,’b’
CHAR固定長度字串“a”,’b’
BINARY位元組陣列無法表示
TIMESTAMP時間戳，毫秒值精度122327493795
DATE日期‘2016-03-29’
INTERVAL時間頻率間隔
復雜型別ARRAY有序的的同型別的集合array(1,2)
MAPkey-value,key必須為原始型別，value可以任意型別map(‘a’,1,’b’,2)
STRUCT欄位集合,型別可以不同struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1.0)
UNION在有限取值范圍內的一個值create_union(1,’a’,63)

對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

    1. 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

    1. 日期轉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

    1. 指定格式日期轉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

    1. 日期時間轉日期函式: to_date

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

    1. 日期轉年函式: 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

    1. 日期轉月函式: 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

    1. 日期轉天函式: 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

    1. 日期轉小時函式: hour

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

    1. 日期轉分鐘函式: minute

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

    1. 日期轉秒函式: second

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

    1. 日期轉周函式: weekofyear

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

    1. 日期比較函式: datediff

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

    1. 日期增加函式: date_add

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

    1. 日期減少函式: 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

    1. 字串反轉函式：reverse

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

    1. 字串連接函式：concat

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

    1. 帶分隔符字串連接函式：concat_ws

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

    1. 字串截取函式：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

    1. 字串截取函式：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

    1. 字串轉大寫函式：upper,ucase

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

    1. 字串轉小寫函式：lower,lcase

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

    1. 去空格函式：trim

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

    1. 左邊去空格函式：ltrim

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

    1. 右邊去空格函式：rtrim

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

    1. 正則運算式替換函式：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

    1. 正則運算式決議函式：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;

    1. 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

    1. 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;

    1. 空格字串函式：space

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

    1. 重復字串函式：repeat

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

    1. 首字符ascii函式：ascii

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

    1. 左補足函式：lpad

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

    1. 右補足函式：rpad

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

    1. 分割字串函式: split

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

    1. 集合查找函式: 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

復合型別構建操作

  • 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"}

    1. 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"}

    1. 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

    1. 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

    1. 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

復雜型別長度統計函式

    1. Map型別長度函式: size(Map<k .V>)

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

    1. array型別長度函式: size(Array)

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

    1. 型別轉換函式 ***

型別轉換函式: 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;

    1. 創建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;

    1. 加載資料

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;

    1. 使用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;

    1. 準備資料并加載資料

準備資料如下
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;

    1. 使用explode拆分Array

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

    1. 使用explode拆解Map

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

    1. 拆解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型別欄位，

資料準備:

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

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

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

實作步驟:

    1. 創建本地constellation.txt，匯入資料

node03服務器執行以下命令創建檔案，注意資料使用\t進行分割
cd /export/servers/hivedatas
vim constellation.txt

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

    1. 創建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;

    1. 按需求查詢資料

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 ",";

    1. 加載資料

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

    1. 按需求查詢資料

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 ',';

    1. 準備資料并加載資料

cd /export/servers/hivedatas
vim test_udf 

檔案內容如下:
1,2
4,3
6,4
7,5
5,6

    1. 加載資料

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

    1. 使用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 ',';

    1. 準備資料

cd /export/servers/hivedatas
vim test_udf2

檔案內容如下:
java.lang.Math,min,1,2
java.lang.Math,max,2,3

    1. 加載資料

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

    1. 執行查詢

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;
（這里，根據天和月進行聚合，和根據天聚合結果一樣，因為有父子關系，如果是其他維度組合的話，就會不一樣）

微信搜索公眾號【五分鐘學大資料】 ，每周首發原創大資料技術文，深入框架原理，大廠面試真題等

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/271548.html

標籤：其他

上一篇：關于我以及我為什么要學習編程

下一篇：Pulsar的Topic、Subscription和Cursors作業原理

標籤雲
其他(157675) Python(38076) JavaScript(25376) Java(17977) C(15215) 區塊鏈(8255) C＃(7972) AI(7469) 爪哇(7425) MySQL(7132) html(6777) 基礎類(6313) sql(6102) 熊猫(6058) PHP(5869) 数组(5741) R(5409) Linux(5327) 反应(5209) 腳本語言(PerlPython)(5129) 非技術區(4971) Android(4554) 数据框(4311) css(4259) 节点.js(4032) C語言(3288) json(3245) 列表(3129) 扑(3119) C++語言(3117) 安卓(2998) 打字稿(2995) VBA(2789) Java相關(2746) 疑難問題(2699) 细绳(2522) 單片機工控(2479) iOS(2429) ASP.NET(2402) MongoDB(2323) 麻木的(2285) 正则表达式(2254) 字典(2211) 循环(2198) 迅速(2185) 擅长(2169) 镖(2155) 功能(1967) .NET技术(1958) Web開發(1951) python-3.x(1918) HtmlCss(1915) 弹簧靴(1913) C++(1909) xml(1889) PostgreSQL(1872) .NETCore(1853) 谷歌表格(1846) Unity3D(1843) for循环(1842)

熱門瀏覽
  • 網閘典型架構簡述

    網閘架構一般分為兩種：三主機的三系統架構網閘和雙主機的2+1架構網閘。 三主機架構分別為內端機、外端機和仲裁機。三機無論從軟體和硬體上均各自獨立。首先從硬體上來看，三機都用各自獨立的主板、記憶體及存盤設備。從軟體上來看，三機有各自獨立的作業系統。這樣能達到完全的三機獨立。對于“2+1”系統，“2”分為 ......

    uj5u.com 2020-09-10 02:00:44 more
  • 如何從xshell上傳檔案到centos linux虛擬機里

    如何從xshell上傳檔案到centos linux虛擬機里及：虛擬機CentOs下執行 yum -y install lrzsz命令，出現錯誤：鏡像無法找到軟體包 前言 一、安裝lrzsz步驟 二、上傳檔案 三、遇到的問題及解決方案 總結 前言 提示：其實很簡單，往虛擬機上安裝一個上傳檔案的工具 ......

    uj5u.com 2020-09-10 02:00:47 more
  • 一、SQLMAP入門

    一、SQLMAP入門 1、判斷是否存在注入 sqlmap.py -u 網址/id=1 id=1不可缺少。當注入點后面的引數大于兩個時。需要加雙引號， sqlmap.py -u "網址/id=1&uid=1" 2、判斷文本中的請求是否存在注入 從文本中加載http請求，SQLMAP可以從一個文本檔案中 ......

    uj5u.com 2020-09-10 02:00:50 more
  • Metasploit 簡單使用教程

    metasploit 簡單使用教程 浩先生， 2020-08-28 16:18:25 分類專欄： kail 網路安全 linux 文章標簽： linux資訊安全 編輯 著作權 metasploit 使用教程 前言 一、Metasploit是什么？ 二、準備作業 三、具體步驟 前言 Msfconsole ......

    uj5u.com 2020-09-10 02:00:53 more
  • 游戲逆向之驅動層與用戶層通訊

    驅動層代碼： #pragma once #include <ntifs.h> #define add_code CTL_CODE(FILE_DEVICE_UNKNOWN,0x800,METHOD_BUFFERED,FILE_ANY_ACCESS) /* 更多游戲逆向視頻www.yxfzedu.com ......

    uj5u.com 2020-09-10 02:00:56 more
  • 北斗電力時鐘（北斗授時服務器）讓網路資料更精準

    北斗電力時鐘（北斗授時服務器）讓網路資料更精準 北斗電力時鐘（北斗授時服務器）讓網路資料更精準 京準電子科技官微——ahjzsz 近幾年，資訊技術的得了快速發展，互聯網在逐漸普及，其在人們生活和生產中都得到了廣泛應用，并且取得了不錯的應用效果。計算機網路資訊在電力系統中的應用，一方面使電力系統的運行 ......

    uj5u.com 2020-09-10 02:01:03 more
  • 【CTF】CTFHub 技能樹 彩蛋 writeup

    ?碎碎念 CTFHub：https://www.ctfhub.com/ 筆者入門CTF時時剛開始刷的是bugku的舊平臺，后來才有了CTFHub。 感覺不論是網頁UI設計，還是題目質量，賽事跟蹤，工具軟體都做得很不錯。 而且因為獨到的金幣制度的確讓人有一種想去刷題賺金幣的感覺。 個人還是非常喜歡這個 ......

    uj5u.com 2020-09-10 02:04:05 more
  • 02windows基礎操作

    我學到了一下幾點 Windows系統目錄結構與滲透的作用 常見Windows的服務詳解 Windows埠詳解 常用的Windows注冊表詳解 hacker DOS命令詳解（net user / type /md /rd/ dir /cd /net use copy、批處理 等） 利用dos命令制作 ......

    uj5u.com 2020-09-10 02:04:18 more
  • 03.Linux基礎操作

    我學到了以下幾點 01Linux系統介紹02系統安裝，密碼啊破解03Linux常用命令04LAMP 01LINUX windows： win03 8 12 16 19 配置不繁瑣 Linux：redhat,centos(紅帽社區版)，Ubuntu server,suse unix:金融機構，證券，銀 ......

    uj5u.com 2020-09-10 02:04:30 more
  • 05HTML

    01HTML介紹 02頭部標簽講解03基礎標簽講解04表單標簽講解 HTML前段語言 js1.了解代碼2.根據代碼 懂得挖掘漏洞 （POST注入/XSS漏洞上傳）3.黑帽seo 白帽seo 客戶網站被黑帽植入劫持代碼如何處理4.熟悉html表單 <html><head><title>TDK標題，描述 ......

    uj5u.com 2020-09-10 02:04:36 more
最新发布
  • 2023年最新微信小程式抓包教程

    01 開門見山 隔一個月發一篇文章，不過分。 首先回顧一下《微信系結手機號資料庫被脫庫事件》，我也是第一時間得知了這個訊息，然后跟蹤了整件事情的經過。下面是這起事件的相關截圖以及近日流出的一萬條資料樣本： 個人認為這件事也沒什么，還不如關注一下之前45億快遞資料查詢渠道疑似在近日復活的訊息。 訊息是 ......

    uj5u.com 2023-04-20 08:48:24 more
  • web3 產品介紹：metamask 錢包 使用最多的瀏覽器插件錢包

    Metamask錢包是一種基于區塊鏈技術的數字貨幣錢包，它允許用戶在安全、便捷的環境下管理自己的加密資產。Metamask錢包是以太坊生態系統中最流行的錢包之一，它具有易于使用、安全性高和功能強大等優點。 本文將詳細介紹Metamask錢包的功能和使用方法。 一、 Metamask錢包的功能 數字資 ......

    uj5u.com 2023-04-20 08:47:46 more
  • vulnhub_Earth

    前言 靶機地址->>>vulnhub_Earth 攻擊機ip：192.168.20.121 靶機ip：192.168.20.122 參考文章 https://www.cnblogs.com/Jing-X/archive/2022/04/03/16097695.html https://www.cnb ......

    uj5u.com 2023-04-20 07:46:20 more
  • 從4k到42k，軟體測驗工程師的漲薪史，給我看哭了

    清明節一過，盲猜大家已經無心上班，在數著日子準備過五一，但一想到銀行卡里的余額……瞬間心情就不美麗了。最近，2023年高校畢業生就業調查顯示，本科畢業月平均起薪為5825元。調查一出，便有很多同學表示自己又被平均了。看著這一資料，不免讓人想到前不久中國青年報的一項調查：近六成大學生認為畢業10年內會 ......

    uj5u.com 2023-04-20 07:44:00 more
  • 最新版本 Stable Diffusion 開源 AI 繪畫工具之中文自動提詞篇

    🎈 標簽生成器 由于輸入正向提示詞 prompt 和反向提示詞 negative prompt 都是使用英文，所以對學習母語的我們非常不友好 使用網址：https://tinygeeker.github.io/p/ai-prompt-generator 這個網址是為了讓大家在使用 AI 繪畫的時候 ......

    uj5u.com 2023-04-20 07:43:36 more
  • 漫談前端自動化測驗演進之路及測驗工具分析

    隨著前端技術的不斷發展和應用程式的日益復雜，前端自動化測驗也在不斷演進。隨著 Web 應用程式變得越來越復雜，自動化測驗的需求也越來越高。如今，自動化測驗已經成為 Web 應用程式開發程序中不可或缺的一部分，它們可以幫助開發人員更快地發現和修復錯誤，提高應用程式的性能和可靠性。 ......

    uj5u.com 2023-04-20 07:43:16 more
  • CANN開發實踐：4個DVPP記憶體問題的典型案例解讀

    摘要：由于DVPP媒體資料處理功能對存放輸入、輸出資料的記憶體有更高的要求（例如，記憶體首地址128位元組對齊），因此需呼叫專用的記憶體申請介面，那么本期就分享幾個關于DVPP記憶體問題的典型案例，并給出原因分析及解決方法。 本文分享自華為云社區《FAQ_DVPP記憶體問題案例》，作者：昇騰CANN。 DVPP ......

    uj5u.com 2023-04-20 07:43:03 more
  • msf學習

    msf學習 以kali自帶的msf為例 一、msf核心模塊與功能 msf模塊都放在/usr/share/metasploit-framework/modules目錄下 1、auxiliary 輔助模塊，輔助滲透（埠掃描、登錄密碼爆破、漏洞驗證等） 2、encoders 編碼器模塊，主要包含各種編碼 ......

    uj5u.com 2023-04-20 07:42:59 more
  • Halcon軟體安裝與界面簡介

    1. 下載Halcon17版本到到本地 2. 雙擊安裝包后 3. 步驟如下 1.2 Halcon軟體安裝 界面分為四大塊 1. Halcon的五個助手 1) 影像采集助手：與相機連接，設定相機引數，采集影像 2) 標定助手：九點標定或是其它的標定，生成標定檔案及內參外參，可以將像素單位轉換為長度單位 ......

    uj5u.com 2023-04-20 07:42:17 more
  • 在MacOS下使用Unity3D開發游戲

    第一次發博客，先發一下我的游戲開發環境吧。 去年2月份買了一臺MacBookPro2021 M1pro(以下簡稱mbp)，這一年來一直在用mbp開發游戲。我大致分享一下我的開發工具以及使用體驗。 1、Unity 官網鏈接： https://unity.cn/releases 我一般使用的Apple ......

    uj5u.com 2023-04-20 07:40:19 more

Copyright © 2010-2020 有解無憂