1.數值函數

(1).round() 四舍五入

 

(2).ceil() 向上取整

 

(3).floor() 向下取整

 

2.字符串函數

(1).substring() 字符串截取

該函數一共有三個參數

1).第一個參數表示要截取的字符串

2).第二個參數表示截取的起始位置，可以匹配instr使用

3).第三個函數是一個可選參數，表示要截取的長度，如果不寫，則表示截取到最后的一位。

 

(2).replace(): 替換函數

 

(3).regexp_replace(): 正則替換

根據正則表達式替換匹配上的字符串為另外的字符串

如：select regexp_replace(“wod122”,”\d+”,”你好”);

將字符串中的數字替換為“你好”

 

(4).regexp() 正則匹配函數

語法：字符   regexp   需要匹配的正則表達式

如：select “adb123” regexp “\d+$”;

匹配以數字結尾的字符串，如果可以匹配上這返回true

 

(5).split(str,key): 切割字符串

將字符串str,根據key進行切割，返回一個字符串數組，這里的key是一個正則表達式

select split(“adfd1213adfs”,”\d+”);

--["adfd","adfs"]

 

(6).nvl(x,y). 空值替換函數

如果x為空，則默認值為y

如：select nvl(null,4); -- 4

將null替換為4

 

(7).concat() 字符串拼接函數

語法：concat(str1,str2,…)

 

(8).concat_ws(key,str1,str2,…)

將key后面參數的所有字符串，使用key分隔拼接

 

(9).get_json_object(str,y) 操作json的函數

Str: 為要操作的json

y:為要獲取的key,如果是json數組則需要使用$[x],key的方式去訪問

如：select get_json_object('[{"name":"年后"},{"name":"昊天"}]',"$[1].name");

--昊天

 

3.日期函數

(1).unix_timestamp[x]

返回當前時間或指定時間的時間戳

select unix_timestamp();-- 返回當前的時間戳

如果傳入時間，那么返回傳入參數的時間戳,返回的是0時區的時間

 

(2).from_unixtime(longtime,temp)

將時間戳轉換模版的時間格式

如：select from_unixtime(1730821688,'yyyy-MM-dd HH:mm:ss');

--2024-11-05 15:48:08

返回的是0時區的時間

 

(3).from_utc_timestamp(longtime,時區字符串)

將指定的時間戳轉換為指定的時區的時間

如：select from_utc_timestamp(cast(1730821688 as bigint) * 1000,"GMT+8")

-- 2024-11-05 23:48:08.000000000

 

(4).current_date()

返回當前的系統日期

 

(5). current_timestamp()

返回當前系統的時間，這里的時間包括時分秒，并且獲取的是當前機器所在的時區的時間

 

(6).month(): 獲取日期的月份

 

(7).day(): 獲取日期的天

 

(8).hour(): 獲取指定日期的小時

 

(9).year(): 返回給定的時間的年份

 

(10).datediff(t1,t2)

獲取給定兩個時間的相差的天數

是使用第一個時間減去第二個時間

 

(11).date_add(date,countday) 日期加一個天數

 

(12). date_format(date,key)

將給定的時間，轉換為指定時間的模版格式

 

4.流程控制函數

(1).case when 多分支函數

select stu_id,

       course_id,

       case

           when score <= 60 then "不及格"

           when score < 80 then "及格"

           when score >= 80 then "優秀"

           else "不存在"

           end

from score_info;

 

(2).if單分支函數

select  stu_id,

       course_id,

       if(score>=80,"優秀","不及格") as haha

       from score_info;

 

5.復雜類型函數

(1).array()數組數組構造函數

select `array`(1,2,3,4,5)

--[1,2,3,4,5]

 

(2).array_contains(array,x)

判斷一個元素是否在數組中，如果存在就返回true,否則返回flase

如：判斷5是否在數組中

Select array_contains(array(1,2,3,4,5))

 

(3).sort_array()

給數組排升序，而且無法修改排序的方式(以及只能排升序)

select sort_array(`array`(1,5,3,4,2))

--[1,2,3,4,5]

 

(4).size()獲取數組的長度

 

(5).map(key1,value1,key2,value2,…)

Map構造函數

如：select map(“hadoop”,1,”flink”,2)

 

(6).map_keys()獲取map的所有key

 

(7).map_values()獲取map的所有key

 

(8).struct()結構體函數

創建結構體，無需傳入結構體的字段名稱

如：select struct("hadoop","flink","java")

--{"col1":"hadoop","col2":"flink","col3":"java"}

 

(9).named_struct(字段名稱1，值1，字段名稱2，值2,…)

如：select named_struct("name","小顧","age",34)

 

6.高級聚合函數

(1).collect_list()

將查詢的列聚合在一個list集合中

如：select collect_list(job) from employee;

-- ["銷售","行政","研發","研發","銷售","行政","前臺","前臺"]

 

(2).collect_set()

將制定列的內容聚合在set集合中，并去重

如：select collect_set(job) from employee;

-- ["銷售","行政","研發","前臺"]

 

7.UDTF函數

(1).explode(arr) 接收數組時

將一個數組炸裂為多行，及：一行輸出多行

類似于Spark中的flatMap的扁平化操作

如：select explode(`array`(1,2,3,4,7))

--

1

2

3

4

7

 

(2).explode(map) 傳入map

會將key和value分別炸裂為兩列的多行元素

 

(3).posexplode(array)

會在explode基礎上返回每一行在數組中的索引

如：select posexplode(`array`(1,2,6,3))

--

0,1

1,2

2,6

3,3

 

(4).inline(array(struct()))

將結構體數組轉換為一張表的數據，如果不使用as修改字段名稱，默認使用結構體的字段名

 

(5).lateral view()

一般情況會配合UDTF函數使用。

如：select name,sex,friends,friends_temp from employee lateral view explode(friends) temp_table_name as friends_temp;

其中：temp_table_name為每一個行炸裂后的表的臨時名稱

friends_temp炸裂后字段，可能代表多個字段

結果如下：

 

 

8.窗口函數

(1).基于行的窗口函數

函數() over(范圍)

基于行的是指：上一行到當前行

如：sum(score) over (order by score rows between unbounded preceding and current row )

這里的先根據score排序升序，然后再重第一行到當前行就和得到的結果如下：

 

可以任意指定范圍。

 

(2).基于值的窗口函數

基于值的窗口函數與基于行的窗口函數語法類似，只是窗口函數的關鍵字rows變為了range

 

當窗口的范圍涉及到加減等計算操作時，指定的排序列及計算窗口的列必須是數值類型。

 

(3).lead() over() 和 lag() over()

1.lead獲取當前行的下邊某一行的某一列的值。

2.lag獲取當前行上邊某一行的某一列的值。

 

如：

select *,lead(score,1) over(partition by stu_id order by score) as temp_score from score_info;

這里的lead(score,1)表示獲取score的列下邊一行的值

注意：lag和lead窗口函數不支持自定義窗口的范圍

 

 

(4).first_value() 和 last_value()

First_value獲取當前窗口的第一行的值,

Last_value獲取當前窗口中第一行到當前行的最后一行，及當前行。

 

如：

select *

     , first_value(score,false) over (partition by stu_id order by score) as first_temp

     , last_value(score,false) over (partition by stu_id order by score)  as last_temp

from score_info;

這里的first_value(score,false)中，score表示要獲取值的列，false表示一個booler值，表示是否要跳過空值

結果如下：

 

 

(5).row_number() over()

排名函數，可以給每一行一個排名，如果值相同時，排名不會相同。

語法：row_number() over(partition by 分組的字段 order by 排序的字段 排序方式)。

 

如：

select t.*,row_number () over(partition by stu_id order by score desc) as rank_temp from score_info t;

結果如下：

 

 

(6).dense_rank() over() 和 rank() over()

排名函數，可以給每一行給一個排名，如果值相同時，排名相同。

dense_rank不會跳過排名,排名是連續的，而rank()會跳過排名。

如： 1，2，2，3  

dense_rank的排名為： 1，2，2，3；

而rank()的排名為：1，2，2，4

語法：dense_rank() over(partition by 分組的字段 order by 排序的字段 排序方式)

 

如：

select t.*,dense_rank() over(partition by stu_id order by score desc) as rank_temp from score_info t;

結果如下：