一、檢視hive的函式庫

show functions;

二、檢視某個具體的函式

desc function extended 函式名;

三、數學函式

1、log(double base,double a) 簡介：底數為base的a的對數，base可以自定義 返回值型別：double

select log(10,100);
--返回2

2、pow(double base,double p) ：冪運算

select pow(2,3);
--返回8

3、conv(bigint/string v,int from_base,int to_base)

select conv(13,10,2);
--返回1102

4、pmod(int/double a,int/double b) 簡介：取餘運算

select pmod(2,4);
--返回2

5、 [a]sin(double/decimal a)/[a]cos(double/decimal
a)/[a]tan(double/decimal a) 簡介：正弦餘弦和正切函式，中括號的[a]加上之後就是反的

6、degrees(double/decimal a)#弧度轉化為角度
radians(double/decimal a)#角度轉化為弧度

7、 positive(int/double a)#返回數字本身

select positive(-10);
--返回-10

8、negative(int/double a)：返回相反數

select negative(10);
--返回-10

9、e():自然對數

select e();
--返回2.718281828459045

10、pi():圓周率

select pi();
--返回3.141592653589793

11、greatest(T…vs):橫向找出最大值

select greatest(1,4,10,-1);
--返回10

12、least(T…vs):橫向找出最小值

select least(-10,2,-13);
--返回-13

13、在1.0版本目前沒有
bround(a,b)#財務舍入法，對小數5的處理，四捨六入五取偶
factorial(a)#int 20以內的階乘，只能在20以內
shiftleft(a,b)#位左移
shiftright(a,b)#位右移

四、集合函式

1、 size(Map<K,V>/Array)，求map或array的長度，返回值是int。struct結構體是不可以使用size的

select size(hobbies) from clientinfo01;

2、map_keys(Map<K,V>)，獲取map裡的鍵，返回值是存放所有鍵的陣列

select map_keys(deliveryadd) from clientinfo1;

3、map_values(Map<K,V>)，獲取map裡的值，返回值是存放所有值的陣列

select map_values(deliveryadd) from clientinfo01;

4、array_contains(Array,T)，判斷引數1的陣列裡是否包含引數2的元素，返回布林值

select array_contains(array(1,2,3),2);

5、sort_array(Array)，對引數裡的陣列進行排序，從小到大

select sort_array(array('b','a','m','c'));

五、型別轉換函式

1、cast(expr as )把expr轉換為新的type型別，比如說在使用concat函式時，需要把數字型別的欄位轉換為字串型別的

select cast(1 as float);

select cast(trim('   1  ') as double);

六、日期函式

1、current_timestamp:獲取當前時間、

 select current_timestamp;

2、unix_timestamp:獲取時間戳

 select unix_timestamp();

3、from_unixtime:將時間戳轉換成format格式 轉換為預設時間形式

select from_unixtime(1607914645);  後面可以指定格式 年月日時分秒 

select from_unixtime(1607914645,'yyyy-MM-dd HH:mm:ss');
yyyy:年
MM：月
dd:日
HH:小時
mm:分鐘
ss秒

select from_unixtime(1607914645,'yyyy-MM-dd');

select substr(from_unixtime(1607914645),0,10);  利用substr擷取字串獲取天數

 select from_unixtime(unix_timestamp());  將時間戳轉換成日期格式

4、unix_timestamp(string datetime)#獲取指定日期時間的長整數，不加引數就是當前日期\

select unix_timestamp(current_timestamp());  日期轉換為時間戳

select unix_timestamp('2020-12-14','yyy-MM-dd');    0時

select from_unixtime(unix_timestamp('2020-12-14','yyy-MM-dd'));

select unix_timestamp('20201214','yyyyMMdd');

5、to_date:返回字串的日期部分

select to_date((current_timestamp));

6、year:只拿取年

select year(current_timestamp());

select year('2020-12-14');
--返回2020

 select year(2020/12/14);  
 --返回 null 格式不對返回null

7、month：取月份

 select month(current_timestamp());

8、day：取日期

select day(current_timestamp());

9、current_date:當前日期

select substr(current_timestamp(),0,10);
--擷取日期字串10位

select current_date();
--返回 2020-12-14 

10、date_add:日期增加

select date_add('2020-12-14',8);

select date_add('2020-12-14',-3);  三天前的時間

select date_add(current_timestamp(),2);

11、datediff：時間差值

select datediff('2020-12-20','2020-12-13');    7  前面的日期-後面的日期

12、date_format(date/timestamp/string date,string format)#引數1為日期格式的日期，根據引數2的需要返回一個字串值，引數2可以為YYYY-MM-dd hh:mm:ss中任意的一項或者多項，如果是一項，比如說M，則可以獲取該日期的月份

select date_format('2020-12-14','yyyy dd hh ss');

select date_format('2020-12-14','MM-dd');

13、add_months(string date,int months)#加減月數±3/6/12

select add_months(current_date(),-3);

14、next_day(string date,string dayOfWeek)#返回接下來第一個 引數2指定的“星期幾”格式 的日期

select next_day(current_date(),'Tu');
--從當前日期開始，返回下一個引數2的日期

15、last_day(string date)#返回該月的最後一天

select last_day(current_date());

16、trunc(string date,string format)#返回日期本年、本月的第一天，引數2的格式可以為多種YY/YEAR/MM/MONTH/MON

select trunc(current_date(),'MM');

select trunc(current_date(),'YY');

17、months_between(string datefrom,string dateto)#月數差

select months_between(current_date(),'2020-1-1');

測試題

a.本年第一天
select trunc(current_date(),'YY');
b.本月第一天
select trunc(current_date(),'MM');
c.本季度第一天
select concat_ws(’-’,cast(year(current_date()) as string),cast(ceil(month(current_date())/3)*3-2 as string),‘1’);
d求本週第一天的日期
select date_add(next_day(current_date(),‘SU’),-7);

七、條件函式

1、if(boolean,T vtrue,T vfalse)#巢狀時用case when

select if(false,0,1);
--返回1

2、 nvl(T value,T default)#和mysql裡的ifnull相同，如果是空值返回第二個引數，如果不是空值null則返回第一個引數的值

select nvl(null,2);
--返回2

3、coalesce(T…vs)#返回第一個非空null的值

select coalesce(null,4,null,9);
--返回4

4、case a when b then … when c then … else …end
case when a then … when b then … else … end

5、isnull(v)，isnotnull(v)#判斷是否是null,

select isnull(null);
--返回true

八、字串函式

1、ascii(v)：返回引數字串首位的位元組碼值

select ascii('ac');
--返回97

2、concat_ws(string sep,array/string…array):通過引數1指定的分隔符號，對引數2“可以是陣列或連續的多個字串”進行拼接，然後返回拼接好的字串。

select concat_ws('-','aa','bb');
--返回aa-bb

3、sentences(string sentence)#拆分長字串（句子）為字串陣列，返回一個巢狀陣列array，根據感嘆號！分割為多個陣列，每個陣列根據空格或者逗號分割為多個元素。

select sentences('hello world,my name is james,what is your name');

4、ngrams(array arr,int n,int k)#select ngrams(sentences(‘hello boy,how are you,hello word,hello you’),1,2);#第一個引數是多個字串組成陣列的集合（巢狀陣列），第二個引數是幾個連續的單詞，第三個引數是取前幾個，按N個單詞出現頻次，倒序取前K個，返回一個結構陣列（array<struct<string,double>>，陣列元素為{“ngram”:[“xxx”],“estfrequency”:n}）

select ngrams(sentences('hello kb10,how are you,hello boy,hello you'),1,2);

5、context_ngrams(array arr,array,int k)#與 sentences()函式一起使用,分詞後,統計分詞結果中與陣列中指定的單詞一起出現(包括順序)頻次最高的 TOP-K 結果，返回值是array<struct<string,double>>。

select context_ngrams(sentences('a b c d e f g h i a b d e'),array("a","b",null),10);

6、encode(string source,string charset)/decode(string source,string charset)
      #加密select encode(‘中國’,‘UTF-16BE’);
      #還原select decode(encode(‘中國’,‘UTF-16BE’),‘UTF-16BE’);

select encode('中國','UTF-16BE');

select decode(encode('中國','UTF-16BE'),'UTF-16BE');

9、format_number(decimal number,int d)#將數值 x 的小數位格式化成 d位,四捨五入，返回值是個字串

select format_number(19.256,2);
--返回19.26

10、get_json_object()#字面意思，拿到json物件例項；解析 json 的字串json_string,返回 path 指定的內容。如果輸入的 json字串無效,那麼返回 NULL。
#select get_json_object(’{“name”:“henry”}’,’KaTeX parse error: Expected ‘EOF’, got ‘#’ at position 21: …’); #̲select get_json….info.city’);

select get_json_object('{"name":"henrry"}','$.name');

select get_json_object('{"name":"henrry","info":{"city":"nj"}}','$.info.city');

11、printf(string format,T…t)#%s %d
%.nf#格式化字串(類似拼接)，某種情況下可以取代concat#select
printf(’%s,%d,%.2f’,‘henry’,18,234423.34535);

select printf('%s,%d,%.3f','henrry',28,234423.34535);

12、like# % #模糊匹配#select * from shop where contact.mobile like ‘18%’;

select * from shop where contact.mobile like '18%';

13、rlike#正則的模糊匹配# [] {} ? + * \d \w … #select * from shop where contact.mobile rlike ‘18\d{9}’;

select * from shop where contact.mobile rlike '18\d{9}';

14、regexp_replace(string src,string newStr,string oldStr)#select regexp_replace(‘you me young’,‘you’,‘YOU’);#select regexp_replace(‘y1 me y_u youngad’,‘y\w{2,3}’,‘YOU’);找到以y開頭，後面有至少2個字串時，替換y和後面的3個字串為引數三的值

select regexp_replace('y1 me y_u youngad','y\\w{2,3}','YOU');

15、regexp_extract(string src,string regex,int index)#以括號為單位，分組要有界限，引數3是要提取的內容#select regexp_extract(‘namehenryokdalingduck’,‘name(.?)(ok)(.?)duck’,3);

select regexp_extract('namehenrryyokdalingduck','name(.*?)(ok)(.*?)duck',3);

16、split(string src,string regex)#正則分割，返回一個陣列#select split(regexp_replace(’[“henry”,“pola”,“ariel”]’,’
|
|"’,’’),’,’);#select split(‘henry.lili@foxmail.com’,’.|@’);

select split(regexp_replace('["henrry","pola","ariel"]','\\[|\\]|\"',''),',');

17、str_to_map(string src,string regex)#自動變為map鍵值，返回一組鍵值對map<string,string>#select         str_to_map(‘name:henry,age:22,gender:male’);#select         str_to_map(‘name#henry|age#22|gender#male’,’|’,’#’);select         str_to_map(‘name#henry|age#22+gender#male’,’||+’,’#’);#正則，|要轉義#

select str_to_map('name:henrry,age:22,gender:male');

select str_to_map('name#henrry|age#22|gender#male','\\|','#');

select str_to_map('name#henrry|age#22+gender#male','\\||\\+','#');

18、translate(string src,string chars,string dchars)#引數2的字元匹配多少替換多少#替換#select translate(‘abcabcabcabaac’,‘ab’,’’);a和b分別變成了，按照字母來的#select translate(‘abcabcabcabaac’,‘ab’,’*#’);

select translate('abcabcabcabaac','ab','*');

select translate('abcabcabcabaac','ab','*#');

19、initcap(string str)#單詞的首字母大寫#select initcap(‘henry haha’);

select initcap('i am fine,thank you');

20、substr(string src,int bigint [,int len])#select substr(‘henry’,2);#select substr(‘henry’,2,1);擷取字串，第一個引數是起始位置，最後一個引數是長度

select substr('2020-12-14 22:22:23.23',5,2);

21、locate(string sub,string src,int startPos)#startPos從1開始#select locate(‘en’,‘henry’,2);

select locate('ove','henrrylovelily',2);