HiveSql基礎函數使用(一)

2021-01-10 初見君

一、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數據倉庫實戰
    Hive原理和功能介紹Hive是建立在 Hadoop 上的數據倉庫基礎構架。它提供了一系列的工具，可以用來進行數據提取轉化加載（ETL），這是一種可以存儲、查詢和分析存儲在 Hadoop 中的大規模數據的機制。
  • 大數據基礎知識:Hadoop分布式系統介紹
    Hadoop生態除了基礎Hadoop，發展到今天Hadoop已經擁有非常完善和龐大的開源生態圈：HDFS提供文件存儲，YARN提供資源管理，在此基礎上，進行各種處理，包括mapreduce、Tez、Sprak、Storm等等，以滿足不同要求的數據使用場景。HDFS架構
  • 大數據分析工程師入門9-Spark SQL
    本文為《大數據分析師入門課程》系列的第9篇，在本系列的第8篇-Spark基礎中，已經對Spark做了一個入門介紹，在此基礎上本篇拎出Spark SQL，主要站在使用者的角度來進行講解，需要注意的是本文中的例子的代碼均使用Scala語言。
  • 這個函數讓SQL效率提升99%
    簡介窗口函數（window function）， 也可以被稱為 OLAP函數 或 分析函數。窗口函數是在 ISO SQL 標準中定義的。窗口是用戶指定的一組行。窗口函數計算從窗口派生的結果集中各行的值。可以在單個查詢中將多個排名或聚合窗口函數與單個 FROM 子句一起使用。
  • 大數據分析工程師面試集錦3-SQL/SparkSql/HiveQL
    面試題庫01SQL基礎知識考察對於面試初級數據分析師來說，SQL的面試重點會放在基礎知識的考察，如果最基本的基礎概念和語法都不能熟練回答出來的話，通過面試的機率就會很低。下面兩張圖是SQL基礎概念和基礎語法的考題大綱圖，接下來圍繞圖中提到的概念來列舉幾個常見面試題。圖1 基礎概念圖2 基礎語法考題模擬題1：你覺得SQL是一種什麼樣的語言，說說你對它的認識。
  • sparksql 窗口函數原理
    一、窗口函數是啥在單表數據操作中，一般有下面兩種操作範式：針對單條數據的映射操作，例如每條數據加一的時候。將數據分組後的聚合操作，例如進行分組統計的時候。在第一種範式中有這樣一種情況，當你要生成某條目標數據的時候你需要用到前後N條數據參與計算。
  • Hive建表和內外部表的使用
    一、普通建表方式create table stu_info(id int,name string)ROW FORMAT DELIMITEDFIELDS TERMINATED BY ' '載入數據load data local inpath '/data/hivetest/stu_info_local' into table stu_info;load data inpath '/data/hivetest/stu_info_local
  • PandaSQL:一個讓你能夠通過SQL語句進行pandas的操作的python包
    這篇文章將介紹一種在pandas的dataframe中使用SQL的python包，並且使用一個不等連結的查詢操作來介紹PandasSQL的使用方法。不等連接(Non-equi join)假設你必須連接兩個dataframe。其中一個顯示了我們對某些商品進行促銷的時間段。第二個是事務Dataframe。
  • HIVE 窗口函數詳解
    聚合函數對其所作用的每一組記錄輸 出一條結果，而窗口函數對其所作用的窗口中的每一行記錄輸出一條結果。一些聚合函 數，如 sum, max, min, avg,count 等也可以當作窗口函數使用。窗口函數的實現原理在用group-by處理數據分組時，每一行只能進入一個分組。
  • 停止使用Pandas並開始使用Spark+Scala
    眾所周知，這是一件好事，每當我們使用團隊數據集時，我們總是會得到相同的對象。　　現在，我們基於函數添加一列。這可以通過使用UserDefinedFunctions來完成。 我們還使用了大小寫匹配，因為這在Scala中比if-else更好，但是兩者都可以。　　我們還需要導入另一個有用的spark函數col，該函數用於引用列。
  • c使用sql server專題及常見問題 - CSDN
    為php添加 sqlsrv 擴展去微軟官網 https://www.microsoft.com/en-us/download/ 搜索php ,點擊 Microsoft Drivers for PHP for SQL Server 下載最新版的 sqlsrvXX.exe( 我下載的是SQLSRV32.EXE )運行解壓, 得到一堆DLL, 選取適合自己
  • server sql 作業 使用專題及常見問題 - CSDN
    use masterGO/* --開啟sql server代理sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Agent XPs', 1; GO RECONFIGURE GO*/--定義創建作業
  • 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、支持行列轉換
  • sql 餘數 - CSDN
    SQL是一門面向集合的語言，四則運算裡的和、差、積已經加入到標準SQL，但由於其標準化進程比較緩慢，一些集合運算在主流的資料庫如MySQL、HiveSQL中還未實現。本節給大家介紹，SQL中集合運算的使用方法及其在使用中需要注意的地方，分為兩個部分：第一部分，講解基礎概念，熟悉的同學可以跳過；第二部分，結合實際案例，介紹集合運算的SQL解法和實現思路；目錄：
  • hive中兩個數的和怎麼超市_hive中hive中月,天的數據導入兩級分區...
    暑假實習使用了兩個月的odps ，回學校看了下hadoop 的hive ，讓我對資料庫與數據倉庫增進了一些理解，記錄下來。簡而言之，資料庫是面向事務的設計，數據倉庫是面向主題設計的。資料庫一般存儲在線交易數據，數據倉庫存儲的一般是歷史數據。
  • 春眠不覺曉,SQL 知多少?|原力計劃
    LISTAGG 函數：可以將多行數據轉換成指定分隔符的字符串。多態表函數：不需要預先定義返回類型的表函數，允許開發人員利用動態 SQL 創建強大而複雜的自定義函數。新的數據類型 DECFLOAT。這篇文章詳細介紹了 SQL:2016 的新功能：https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016SQL:2011SQL:2011 最主要的新功能之一就是增強了對時態資料庫的支持
  • 使用exp進行SQL報錯注入 - 51CTO.COM
    當我們拿到MySQL裡的函數時，作者比較感興趣的是其中的數學函數，它們也應該包含一些數據類型來保存數值。所以作者就跑去測試看哪些函數會出現溢出錯誤。然後作者發現，當傳遞一個大於709的值時，函數exp()就會引起一個溢出錯誤。
  • 沒錯,純SQL查詢語句可以實現神經網絡
    我們將有一個維度為 2 的單隱層和 ReLU 激活函數。輸出層的二分類將使用 softmax 函數。我們在實現網絡時遵循的步驟將是在 Karpathy’s CS231n 指南（https://cs231n.github.io/neural-networks-case-study/）中展示的基於 SQL 版本的 Python 示例。
  • 對SQL Server跨文件組的表進行分區
    先決條件　　在開始此實驗之前，您必須：　　使用 Transact-SQL 在 Microsoft® SQL Server® 資料庫中創建資料庫對象的經驗。　　實驗設置　　實驗場景　　Adventure Works Cycles 的數據倉庫中有幾個非常大的表。