HiveSql基礎函數使用(一)

2021-01-08 初見君

一、hive函數

1、關係函數

2、日期函數

3、條件函數

4、字符串函數

5、統計函數

二、hiveQL

1、DDL

2、DML

三、其它

1、in()函數

2、lateral view 函數

3、row_number() 和rank()

4、grouping sets()函數

一、hive函數

1、關係函數

等值比較: =

語法：A=B。如果表達式A與表達式B相等，則為TRUE；否則為FALSE

不等值比較: <>

語法: A <> B。如果表達式A為NULL，或者表達式B為NULL，返回NULL；如果表達式A與表達式B不相等，則為TRUE；否則為FALSE

小於比較: <

語法: A < B。 如果表達式A為NULL，或者表達式B為NULL，返回NULL；如果表達式A小於表達式B，則為TRUE；否則為FALSE

小於等於比較: <=

語法: A <= B。如果表達式A為NULL，或者表達式B為NULL，返回NULL；如果表達式A小於或者等於表達式B，則為TRUE；否則為FALSE

大於等於比較: >=

語法: A >= B。如果表達式A為NULL，或者表達式B為NULL，返回NULL；如果表達式A大於或者等於表達式B，則為TRUE；否則為FALSE

空值判斷: IS NULL

語法: A IS NULL。如果表達式A的值為NULL，則為TRUE；否則為FALSE

非空判斷: IS NOT NULL

語法: A IS NOT NULL。 如果表達式A的值為NULL，則為FALSE；否則為TRUE

LIKE比較: LIKE

語法: A[NOT] LIKE B。如果字符串A或者字符串B為NULL，則返回NULL；如果字符串A符合表達式B的正則語法，則為TRUE；否則為FALSE。B中字符」_」表示任意單個字符，而字符」%」表示任意數量的字符。

舉例：

select * from dw.topic_order where partition_pay_date = '2016-04-22' and client_type like 『ip%』 ##能夠匹配以ip開頭的所有字符串

注意：對特殊字符進行轉譯時，注意要使用兩個反斜槓\。

JAVA的LIKE/REGEXP操作: RLIKE/REGEXP

語法: A RLIKE/REGEXP B。如果字符串A或者字符串B為NULL，則返回NULL；如果字符串A符合JAVA正則表達式B的正則語法，則為TRUE；否則為FALSE

舉例：select * from dw.topic_order where partition_pay_date = '2016-04-22' and client_type rlike/regexp '^android.*'

注意：通配符『%』在rlike/regexp函數中，只能匹配一個'%'字符，』_『也只能匹配一個』_『字符。例如：

select * from dw.topic_order where partition_pay_date = '2016-04-22' and client_type regexp 'ip%' ##只能匹配ip%這個字符串

2、日期函數

UNIX時間戳轉日期函數: from_unixtime

語法: from_unixtime(bigint unixtime[, string format])。轉化UNIX時間戳（從1970-01-01 00:00:00 UTC到指定時間的秒數）到當前時區的時間格式

舉例：select from_unixtime(1323308943,'yyyyMMdd') from dual; ##返回值為20111208

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

語法: unix_timestamp()。獲得當前時區的UNIX時間戳

舉例：select unix_timestamp() from dual; ##返回值為1323309615

日期轉UNIX時間戳函數: unix_timestamp

語法: unix_timestamp(string date)。轉換格式為"yyyy-MM-dd HH:mm:ss"的日期到UNIX時間戳。如果轉化失敗，則返回0。

舉例：select unix_timestamp('2011-12-07 13:01:03') from dual; ##返回值為1323234063

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

語法: unix_timestamp(string date, string pattern)。轉換pattern格式的日期到UNIX時間戳。如果轉化失敗，則返回0。

舉例：select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss') from dual; ##返回值為1323234063

日期時間轉日期函數: to_date

語法: to_date(string timestamp)。返回日期時間欄位中的日期部分。

舉例：select to_date('2011-12-08 10:03:01') from dual; ##返回值為2011-12-08

日期轉年函數: year

語法: year(string date)。 返回日期中的年。

日期轉月函數: month

語法: month (string date)。返回日期中的月份。

日期轉天函數: day

語法: day (string date)。返回日期中的天。

日期轉小時函數: hour

語法: hour (string date)。 返回日期中的小時。

日期轉分鐘函數: minute

語法: minute (string date)。返回日期中的分鐘。

舉例：select minute('2011-12-08 10:03:01') from dual; ##返回值為3

日期轉秒函數: second

語法: second (string date)。 返回日期中的秒。

舉例：select second('2011-12-08 10:03:01') from dual; ##返回值為1

日期轉周函數: weekofyear

語法: weekofyear (string date)。返回日期在當前的周數。

舉例：select weekofyear('2011-12-08 10:03:01') from dual; ##返回值為49

日期比較函數: datediff

語法: datediff(string enddate, string startdate)。返回結束日期減去開始日期的天數。

舉例： select datediff('2012-12-08','2012-05-09') from dual; ##返回值為213

日期增加函數: date_add

語法: date_add(string startdate, int days)。 返回開始日期startdate增加days天后的日期。

舉例：select date_add('2012-12-08',10) from dual; ##返回值為2012-12-18

日期減少函數: date_sub

語法: date_sub (string startdate, int days)。 返回開始日期startdate減少days天后的日期。

舉例：select date_sub('2012-12-08',10) from dual; ##返回值為2012-11-28

註：幾個日期函數在MySQL和Hive裡的區別

Hive: SELECT FROM_UNIXTIME( 1249488000, '%Y%m%d') 結果為 %2009%0%6Hive： SELECT FROM_UNIXTIME( 1249488000, 'yyyy-MM-dd') 結果為 2009-08-06MySQL：SELECT FROM_UNIXTIME( 1249488000, '%Y%m%d') 結果為 20090806MySQL：SELECT FROM_UNIXTIME( 1249488000, '%Y-%m-%d') 結果為 2009-08-06

3、條件函數

If 函數: if

語法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)

說明: 當條件testCondition為TRUE時，返回valueTrue；否則返回valueFalseOrNull

舉例：select if(app_name = 'group',object_id,null) as deal_id from dw.topic_order where partition_pay_date = '2016-04-22'

非空查找函數: COALESCE

語法: COALESCE(T v1, T v2, …)

說明: 返回參數中的第一個非空值；如果所有值都為NULL，那麼返回NULL

舉例：select coalesce(uuid,'') as uuid from dw.topic_order where partition_pay_date = '2016-04-22'

條件判斷函數：CASE

語法 : CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END

說明：如果 a 等於 b ，那麼返回 c ；如果 a 等於 d ，那麼返回 e ；否則返回 f

舉例：

select object_id,user_id,uuid, case when client_type like 'ip%' then 'ios' when client_type like 'andr%' then 'android' else 'other' end as utm_medium from dw.topic_order where partition_pay_date = '2016-04-22'

注意：相對而言，case when是最全的條件函數，可以用於判斷多種條件；次之是if函數，屬於二分判斷；最後是coalesce函數，該函數只能對空值和非空進行判斷。

4、字符串函數

字符串長度函數：length

語法: length(string A)。返回字符串A的長度

字符串反轉函數：reverse

語法: reverse(string A)。返回字符串A的反轉結果

舉例：select reverse(abcedfg』) from dual; ##返回值為gfdecba

字符串連接函數：concat

語法: concat(string A, string B…)。返回輸入字符串連接後的結果，支持任意個輸入字符串

舉例：select count(distinct if(partition_is_paid = 1,null,concat(coalesce(uuid,''),coalesce(deal_id,'')))) as order_cnt from dw.topic_order where partition_pay_date = '2016-04-22'

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

語法: concat_ws(string SEP, string A, string B…)。返回輸入字符串連接後的結果，SEP表示各個字符串間的分隔符

舉例：select concat_ws(',','abc','def','gh') from dual; ##返回值為abc,def,gh

字符串截取函數：substr,substring

語法: substr(string A, int start),substring(string A, int start)。返回字符串A從start位置到結尾的字符串

舉例：select substr('abcde',3) from dual; ##返回值cde

字符串截取函數：substr,substring

語法: substr(string A, int start, int len),substring(string A, int start, int len)。返回字符串A從start位置開始，長度為len的字符串

舉例：select substr('abcde',3,2) from dual; ##返回值為cd

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

語法: upper(string A) ucase(string A)。返回字符串A的大寫格式

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

語法: lower(string A) lcase(string A)。返回字符串A的小寫格式

去空格函數：trim

語法: trim(string A)。去除字符串兩邊的空格

舉例：select trim(' abc ') from dual; ##返回值為abc

左邊去空格函數：ltrim

語法: ltrim(string A)。去除字符串左邊的空格

右邊去空格函數：rtrim

語法: rtrim(string A)。去除字符串右邊的空格

正則表達式替換函數：regexp_replace

語法: regexp_replace(string A, string B, string C)。將字符串A中的符合java正則表達式B的部分替換為C。

舉例：select regexp_replace('foobar', 'oo|ar', '') from dual; ##返回值為fb

正則表達式解析函數：regexp_extract

語法: regexp_extract(string subject, string pattern, int index)。將字符串subject按照pattern正則表達式的規則拆分，返回index指定的字符。

舉例：select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) from dual; ##返回值為the

注意：（1）在有些情況下要使用轉義字符，下面的等號要用雙豎線轉義，這是java正則表達式的規則。

（2）index的值不能大於pattern中()的個數。

URL解析函數：parse_url

語法: parse_url(string urlString, string partToExtract [, string keyToExtract])。返回URL中指定的部分。partToExtract的有效值為：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.

舉例：select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') from dual; ##返回值為facebook.com

json解析函數：get_json_object

語法: get_json_object(string json_string, string path)。解析json的字符串json_string,返回path指定的內容。如果輸入的json字符串無效，那麼返回NULL。

空格字符串函數：space

語法: space(int n)。返回長度為n的字符串

舉例：select length(space(10)) from dual; ##返回值為10

重複字符串函數：repeat

語法: repeat(string str, int n)。返回重複n次後的str字符串

首字符ascii函數：ascii

語法: ascii(string str)。返回字符串str第一個字符的ascii碼

舉例：select ascii('abcde') from dual; ##返回值為97

左補足函數：lpad

語法: lpad(string str, int len, string pad)。將str進行用pad進行左補足到len位

舉例：select lpad('abc',10,'td') from dual; ##返回值為tdtdtdtabc

右補足函數：rpad

語法: rpad(string str, int len, string pad)。將str進行用pad進行右補足到len位

分割字符串函數: split

語法: split(string str, string pat)。按照pat字符串分割str，會返回分割後的字符串數組

舉例：select split('abtcdtef','t') from dual; ##返回值為["ab","cd","ef"]

集合查找函數: find_in_set

語法: find_in_set(string str, string strList)。返回str在strlist第一次出現的位置，strlist是用逗號分割的字符串。如果沒有找該str字符，則返回0

舉例：select find_in_set('ab','ef,ab,de') from dual; ##返回值為2

5、統計函數

個數統計函數: count

語法: count(*), count(expr), count(DISTINCT expr[, expr_.])。count(*)統計檢索出的行的個數，包括NULL值的行；count(expr)返回指定欄位的非空值的個數；count(DISTINCT expr[, expr_.])返回指定欄位的不同的非空值的個數

總和統計函數: sum

語法: sum(col), sum(DISTINCT col)。sum(col)統計結果集中col的相加的結果；sum(DISTINCT col)統計結果中col不同值相加的結果

平均值統計函數: avg

語法: avg(col), avg(DISTINCT col)。avg(col)統計結果集中col的平均值；avg(DISTINCT col)統計結果中col不同值相加的平均值

最小值統計函數: min

語法: min(col)。 統計結果集中col欄位的最小值

最大值統計函數: max

語法: max(col)。統計結果集中col欄位的最大值

中位數函數：precentile語法: percentile(BIGINT col, p)。求準確的第pth個百分位數，p必須介於0和1之間，但是col欄位目前只支持整數，不支持浮點數類型。

相關焦點

  • Hive SQL使用過程中的奇怪現象
    hive是基於Hadoop的一個數據倉庫工具，用來進行數據的ETL，這是一種可以存儲、查詢和分析存儲在Hadoop中的大規模數據的機制。hive能將結構化的數據文件映射為一張資料庫表，並提供SQL查詢功能。Hive SQL是一種類SQL語言，與關係型資料庫所支持的SQL語法存在微小的差異。
  • 搞懂Hadoop、MapReduce、Hive、HBase、YARN及Spark的區別與聯繫
    構建抽象模型：Map和ReduceMapReduce借鑑了函數式語言中的思想，用Map和Reduce兩個函數，提供了高層的並行編程抽象模型。hive數據倉庫工具能將結構化的數據文件映射為一張資料庫表，並提供SQL查詢功能，能將SQL語句轉變成MapReduce任務來執行。hive與關係型資料庫的SQL略有不同，但支持了絕大多數的語句如DDL、DML以及常見的聚合函數、連接查詢、條件查詢。
  • hive之編譯源碼
    使用hive1.2.2，使用hadoop2.7.2，使用jdk1.8.0.>    在使用linux的時候，進行依賴的安裝就是一件痛苦的事，有了yum之後生活幸福感大大提高，要是手動去安裝依賴，並且追查依賴關係。。。
  • Sql注入攻擊基本原理
    （如果未報錯，不代表不存在 Sql 注入，因為有可能頁面對單引號做了過濾，這時可以使用判斷語句進行注入，因為此為入門基礎課程，就不做深入講解了）4.2 判斷Sql注入漏洞的類型通常 Sql 注入漏洞分為 2 種類型：數字型字符型其實所有的類型都是根據資料庫本身表的類型所產生的，在我們創建表的時候會發現其後總有個數據類型的限制，而不同的資料庫又有不同的數據類型
  • BeetlSQL 3.0.0-M1 第一個版本發布 - OSCHINA - 中文開源技術交流...
    經過8個月的重構，BeetlSQL3裡程碑1已經完成BeetlSQL是一款資料庫訪問工具庫，廣泛應用到企業應用，網際網路項目。相比於BeetlSQL2,新版支持更多數據來源，包括支持JDBC的傳統資料庫，大數據NOSQL，以及大數據SQL查詢引擎，在易用性何擴展性也做了大幅度修改。
  • R-數據挖掘常用函數合集
    () 函數 讀取Systat()函數read.table() 函數 讀取表格read.xls() 函數 讀取Excel表格readHTMLTable() 函數 讀取網上數據函數sqlClear() 函數 刪除表中的內容sqlColumns() 函數 返回資料庫表的列信息sqlDrop() 函數 從資料庫中刪除一個表
  • 你知道mysql的find_in_set()函數嗎?
    在sql查詢時，如果查詢條件是一個值是一組數據中的其中一個時，可以使用in來實現。可是如果要實現的查詢的條件是下面描述的這樣，要怎麼做呢。先看表結構。表結構很簡單，再添加幾條測試數據。這樣的存儲結構有的朋友可能看見過，也用過。
  • 帶你快速了解spark sql
    01spark sql架構spark sql是一種可以通過sql執行spark任務的分布式解析引擎。它能夠將用戶編寫的sql語言解析成RDD對應的分布式任務，由於spark是基於內存去處理、計算數據集，所以其執行速度非常快。spark sql對應的結構可以總結為下圖所示：DataSet，顧名思義，就是數據集的意思，它是 Spark 1.6 新引入的接口。
  • 女朋友都能看懂的,SQL優化乾貨
    一、什麼情況會不走索引1、模糊查詢，在欄位開頭模糊select * from teacher where name like '%老師'優化：是從1開始計算，如果沒有找到就直接返回0 ,所以可以使用如下sql：select * from teacher where INSTR(name,'老師')>02、使用了in和not in，會全表掃描普通查詢：
  • sqltoy-orm-4.16.11 發版,部分功能優化
    的十四個關鍵特點：1、最簡最直觀的sql編寫方式(不僅僅是查詢語句)，採用條件參數前置處理規整法，讓sql語句部分跟客戶端保持高度一致2、sql中支持注釋(規避了對hint特性的影響,知道hint嗎?搜oracle hint)，和動態更新加載，便於開發和後期維護整個過程的管理3、支持緩存翻譯和反向緩存條件檢索(通過緩存將名稱匹配成精確的key)，實現sql簡化和性能大幅提升4、支持快速分頁和分頁優化功能，實現分頁最高級別的優化，同時還考慮到了cte多個with as情況下的優化支持5、支持並行查詢6、根本杜絕sql注入問題，以後不需要討論這個話題7、支持行列轉換
  • 使用explain和show profile來分析SQL語句實現優化SQL語句
    SQL語句優化是建立在慢查詢分析的基礎上，通過慢查詢定位有問題的SQL語句，關於慢查詢的介紹及其分析工具，可以參考[mysql慢查詢及慢查詢日誌分析工具]一、通過explain查詢1 用法：explain sql2 作用：用於分析sql語句(1)、id:執行explain的一個編號(沒有實際意義）(2)、
  • Excel基礎函數IF的7個使用技巧,絕不是簡單的判斷哦!
    IF函數，恐怕是大家接觸的最早，用的最多的一個函數了吧……如果你僅僅用它來做簡單的判斷，那就有點兒浪費IF函數了……一、IF函數：基礎功能及語法結構。解讀：1、示例中用到了數組公式，所以需要用Ctrl+Shift+Enter來填充；將所有的值返回之後，用Sum函數來求和。2、如果只是為了達到單條件求和，完全可以使用Sumif函數來完成哦。關於Sumif函數的用法，請查閱歷史消息中的相關文章哦！
  • sqltoy-orm-4.16.16 發版,並行查詢場景增強、級聯增加排序
    致謝：       感謝廣大網友的積極使用和反饋，提出了非常好的意見，讓sqltoy再次變得嚴謹和細膩！
  • Hive函數大全(含例子)之字符串函數(String Functions)
    字符串函數 String Functionsascii(string str)返回結果: 返回字符串str首字母的十進位ascii碼返回類型: int結果為 ACBcontext_ngrams(array<array<string>>, array<string>, int K, int pf)返回結果: 使用
  • MySQL函數基礎——字符串函數詳解
    使用CONCAT _WS函數連接帶分隔符的字符串，輸入語句如下：CONCAT_ WS(-', 'Ist,'2nd', '3rd")使用分隔符『-』 將3個字符串連接成-一個字符串，結果為「1st-2nd-3rd」 ； CONCAT_ WS(*', 'Ist', NULL, '3rd")使用分隔符『*』將兩個字符串連接成一個字符串
  • mysql┃多個角度說明sql優化,讓你吊打面試官!
    1.3欄位類型大小的限制 這點在我們公司的sql建表規範上就會明確寫到。 我這裡簡單的舉個例子，比如varchar，要使用varchar(255),這裡會有幾點考量： 一.255剛好會消耗一個字節的存儲單元，但是256會導致消耗兩個字節的存儲單元。
  • Excel的IFS函數與IF函數的使用區別
    IF函數的使用方法：1.在E2單元格中輸入函數：=IF(D2<60,"不及格",IF(D2<70,"及格",IF(D2<80,"中等",IF(D2<90,"良好","優秀"))))
  • 極致性能 sqltoy-orm-4.12.9 發版 - OSCHINA - 中文開源技術交流...
    具有JPA模式的CRUD功能(即CRUD無需寫sql)，無需寫Dao，sqltoy提供了SqlToyLazyDao，同時提供了quickvo從資料庫生成POJO。 根本上杜絕了sql注入問題 最科學的sql編寫方式* sqltoy的sql編寫(支持嵌套)select *from sqltoy_device_order_info t where #[
  • oracle sql 布爾值專題及常見問題 - CSDN
    0x01 decode 函數布爾盲注decode(欄位或欄位的運算，值1，值2，值3）這個函數運行的結果是，當欄位或欄位的運算的值等於值1時，該函數返回值2，否則返回3當然值1，值2，值3也可以是表達式，這個函數使得某些
  • Mybatis的sql組裝詳解
    上一篇分析了SqlSession執行sql的過程，其中並沒有分析sql是從哪裡來的，今天就來仔細分析下。Sql來源從上一篇的最後一步執行sql那裡倒推sql的來源，源碼主要過程如下圖：可以看到最後是通過BoundSql直接獲取的sql，然後往前倒推最後發現是通過MappedStatement的getBoundSql方法返回的。