Hive（五）常用函式

一年都在冬眠發表於2024-10-09
Hive函式

Hive常用函式

字串函式

返回值 函式 描述
string concat(string/binary A, string/binary B…) 對二進位制位元組碼或字串按次序進行拼接
int instr(string str, string substr) 查詢字串str中子字串substr出現的位置
int length(string A) 返回字串的長度
int locate(string substr, string str[, int pos]) 查詢字串str中的pos位置後字串substr第一次出現的位置
string lower(string A) /upper(string A) 將字串A的所有字母轉換成小寫/大寫字母
string regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT) 按正規表示式PATTERN將字串中符合條件的部分替換成REPLACEMENT所指定的字串
array split(string str, string pat) 按照正規表示式pat來分割字串str
string substr(string/binary A, int start, int len)substring(string/binary A, int start, int len) 對字串A,從start位置開始擷取長度為len的字串並返回
string trim(string A) 將字串A前後出現的空格去掉
map str_to_map(text[, delimiter1, delimiter2]) 將字串str按照指定分隔符轉換成Map
binary encode(string src, string charset) 用指定字符集charset將字串編碼成二進位制值
string concat_ws(separator,string A, string B…) 用指定拼接符separator對字串按次序進行拼接

型別轉換函式

返回值 函式 描述
“type” cast(expr as ) 將expr轉換成type型別 如：cast(“1” as BIGINT) 將字串1轉換成了BIGINT型別
binary binary(string/binary) 將輸入的值轉換成二進位制

數學函式

返回值 函式 描述
DOUBLE round(DOUBLE a) 返回對a四捨五入的BIGINT值
binary round(DOUBLE a, INT d) 返回對a四捨五入並保留d位小數位的值
BIGINT floor(DOUBLE a) 向下取整，如：6.10->6 -3.4->-4
DOUBLE rand(INT seed) 返回一個DOUBLE型隨機數，seed是隨機因子
DOUBLE power(DOUBLE a, DOUBLE p) 計算a的p次冪
DOUBLE abs(DOUBLE a) 計算a的絕對值

日期函式

返回值 函式 描述
string from_unixtime(bigint unixtime[, string format]) 將時間戳轉換成format格式
int unix_timestamp() 獲取本地時區下的時間戳
bigint unix_timestamp(string date) 將格式為yyyy-MM-dd HH:mm:ss的時間字串轉換成時間戳
string to_date(string timestamp) 返回時間字串的日期部分
int year(string date) month/day/hour/minute/second/weekofyear 返回時間字串的年份部分 返回月/天/時/分/秒/第幾周
int datediff(string enddate, string startdate) 計算開始時間到結束時間相差的天數
string date_add(string startdate, int days) 從開始時間startdate加上days
string date_sub(string startdate, int days) 從開始時間startdate減去days
date current_date 返回當前時間的日期
timestamp current_timestamp 返回當前時間戳
string date_format(date/timestamp/string ts, string fmt) 按指定格式返回時間date 如：date_format(“2016-06-22”,“MM-dd”)=06-22

集合函式

返回值 函式 描述
int size(Map<K.V>) 返回map中鍵值對個數
int size(Array) 返回陣列的長度
array map_keys(Map<K.V>) 返回map中的所有key
array map_values(Map<K.V>) 返回map中的所有value
boolean array_contains(Array, value) 如該陣列Array包含value返回true，否則返回false
array sort_array(Array) 對陣列進行排序

條件函式

返回值 函式 描述
T if(boolean testCondition, T valueTrue, T valueFalseOrNull) 如果testCondition為true就返回valueTrue,否則返回valueFalseOrNull
T nvl(T value, T default_value) value為NULL返回default_value,否則返回value
T COALESCE(T v1, T v2, …) 返回第一非null的值，如果全部都為NULL就返回NULL
T CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END 如果a=b就返回c,a=d就返回e，否則返回f
T CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END]* [ELSE f] END 如果a=ture就返回b,c= ture就返回d,否則返回e
boolean isnull(a) 如果a為null就返回true，否則返回false
boolean isnotnull (a) 如果a為非null就返回true，否則返回false

聚合函式

1、基礎聚合函式：sum()、count()、max()、min()、avg()
（1）沒有group by關鍵字就是對整個表聚合；有group by 就是對每個組聚合。

--1.分組聚合：統計男同學、女同學人數
select count(*) from student group by sex;
--2.表聚合：統計全班人數
select count(*) from student;
  • count(*)等價於count(1)：統計所有行，包括含有null值的行
  • count(col)：只會對col中非null進行統計。其他的基礎聚合函式也一樣的，對欄位聚合處理，若存在該欄位值為null，則忽略該行。
  • count(只能傳入一個引數)，如果要傳入多個引數，可以count(distinct col1, col2)，只要去重對結果沒有影響就可以這樣。

（2）聚合函式內：

  • 可以搭配if()、case when ... then else end、isnull()這種單列函式使用，但是聚合函式內不可搭配聚合函式
  • 先對每組資料內的所有行都執行單列函式，再對每組資料進行聚合
  • 可以搭配distinct關鍵字去重–>count(distinct col)
  • 聚合函式內使用struct集合資料型別，是對其第一個列操作。因此可以實現查詢女生年齡最大的人的姓名
    select max(struct(age,name) from student);

2、高階聚合函式
（1）collect_list()：收集並形成list集合，結果不去重
（2）collect_set()：收集並形成set集合，結果去重

select 
  sex,
  collect_list(job) as job
from employee
group by sex
-- 結果：
sex			job
男	["銷售"]
女	["行政","行政"]

相關文章