本篇內(nèi)容主要講解“Hive有哪些常見函數(shù)”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“Hive有哪些常見函數(shù)”吧!

創(chuàng)新互聯(lián)長期為上千客戶提供的網(wǎng)站建設(shè)服務，團隊從業(yè)經(jīng)驗10年，關(guān)注不同地域、不同群體，并針對不同對象提供差異化的產(chǎn)品和服務；打造開放共贏平臺，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為宜賓企業(yè)提供專業(yè)的成都網(wǎng)站制作、成都網(wǎng)站設(shè)計，宜賓網(wǎng)站改版等技術(shù)服務。擁有10多年豐富建站經(jīng)驗和眾多成功案例,為您定制開發(fā)。

1. HQL 判斷空的函數(shù)

https://www.w3school.com.cn/sql/func_date_format.asp

2. 時間轉(zhuǎn)換函數(shù)

year(string date)：返回年份部分的日期或時間戳字符串：year("1970-01-01 00:00:00") = 1970, year("1970-01-01") = 1970

https://blog.csdn.net/wzzfeitian/article/details/55097563

https://www.yiibai.com/hive/hive_built_in_functions.html

3. group by 函數(shù)，需要多次進行分組

數(shù)據(jù)類型轉(zhuǎn)換：https://blog.csdn.net/qq_31573519/article/details/100139218

4. hive獲取上個月第一天和最后一天的時間

//獲取上個月第一天
date('Y-m-01',strtotime('-1 month'));
 
//獲取上個月最后一天
date('Y-m-t',strtotime('-1 month'));

SELECT
--上個月第一天
concat(SUBSTR(DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP()),DAY(FROM_UNIXTIME(UNIX_TIMESTAMP()))),1,7) ,'-01'),
--上個月最后一天
DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP()),DAY(FROM_UNIXTIME(UNIX_TIMESTAMP())))

select
--上個月第一天
trunc(add_months(CURRENT_TIMESTAMP,-1),'MM'),
--上個月第一天
concat(substr(add_months(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-1),1,7),'-01'),
--上個月最后一天
date_sub(trunc(CURRENT_TIMESTAMP,'MM'),1);

5. 當月第一天

-- 當月第一天
SELECT TRUNC(sysdate(0), 'MM') ;

6. 前兩個月第一天

-- 前兩個月第一天
select TRUNC(add_months(CURRENT_TIMESTAMP, -2), 'MM');

7. CAST 轉(zhuǎn)換小數(shù)保留位數(shù)

cast(column_name as decimal(10,2)) cast函數(shù)四舍五入（推薦使用）

select cast(68.666666666666668 as decimal(10,2));
_c0
68.67

select cast(68.666666666666668 as decimal(10,3));
_c0
68.667

8. 求兩數(shù)除數(shù),保留兩位小數(shù)

ROUND((SUM(delivered_num) / SUM(plan_num) * 100),2)

cast(column_name as decimal(10,2)) cast函數(shù)四舍五入（推薦使用）

CAST((SUM(delivered_num) / SUM(plan_num) * 100) AS DECIMAL(10, 2))

9. Presto 引擎的數(shù)值轉(zhuǎn)換

SELECT 
CAST(
    CAST('913' AS DOUBLE) / CAST('1000' AS DOUBLE) * cast('100' as DOUBLE) as DECIMAL(10,2)
);

先轉(zhuǎn)成 double,在進行乘除運算,最后再轉(zhuǎn)成 decimal
==> 91.3

列別名一定要用"雙引號"

10. if 語句

if null 判斷語句

函數(shù)：
1. coalesce( value1,value2,… )
2. if( value1 is null, value2, value1)
IF( Test Condition, True Value, False Value ) 

例子：
hive> select coalesce(col1, col2, cols) as res1, if(col1 is null, if(col2 is null, col3, col2), col1) as res2
    > from(
    >     select 1 as col1, 2 as col2, 3 as col3 
    >     union all
    >     select null as col1, 2 as col2, 3 as col3 
    >     union all
    >     select null as col1, null as col2, 3 as col3 
    >     union all 
    >     select null as col1, null as col2, null as col3
    > ) as test

結(jié)果：
    res1   res2
    1      1
    2      2
    3      3
    null   null

11. 一對多表關(guān)聯(lián)去重統(tǒng)計

背景：假設(shè)我們是一個電商網(wǎng)站，用用戶下單，訂單有來源數(shù)據(jù)，每個訂單會有多個訂單明細，訂單明細記錄了商品相關(guān)信息、以及商品的出庫、發(fā)貨、收貨時間都不同。

需求：想要統(tǒng)計訂單維度相關(guān)的數(shù)據(jù)，要求統(tǒng)計出訂單不同來源下的出庫單量、發(fā)貨單量、收貨單量。

分析：訂單和訂單明細是一對多的關(guān)系，一個訂單有多個明細，每個明細的商品都有各自的出庫、發(fā)貨、收貨時間，也有可能未及時發(fā)貨、出庫、收貨之類的導致為空，想要按照訂單維度統(tǒng)計這些量，只需要判斷訂單中對應的多個明細中，只有出庫、發(fā)貨。收貨時間即可，然后再匯總。

實現(xiàn)：

1. 先統(tǒng)計基礎(chǔ)的數(shù)據(jù)，訂單與訂單明細關(guān)聯(lián)，查詢訂單id、訂單來源、以及根據(jù)出庫時間、發(fā)貨時間、收貨時間是否存在來判斷是否出庫、是否發(fā)貨、是否收貨；

2. 根據(jù)訂單來源、訂單id進行分組，然后利用單行函數(shù) max 取出具體id分組下的是否出庫、發(fā)貨、收貨值；

3. 最后在統(tǒng)計上一步查的數(shù)據(jù)，再按照訂單來源分組，利用單行函數(shù) sum 來求訂單總數(shù)、出庫、發(fā)貨、收貨總單量。

此時就需要使用 group by 進行分組，同時，分組完畢之后，在 select 列上需要針對未分組的字段進行單行函數(shù)，比如 max、min、sum等等。

with base_data as (
	select
		source, -- 下單來源
		max(if_out) if_out, -- 是否出庫
		max(if_send) if_send, -- 是否發(fā)貨
		max(if_received) if_received -- 是否收貨
	from (
		select
			o.id, -- 訂單id
			o.source, -- 來源
			(
				case when od.out_time is not null then 1
				else 0
				end
			) as if_out, -- 是否出庫
			(
				case when od.send_time is not null then 1
				else 0
				end
			) as if_send, -- 是否發(fā)貨
			(
				case when od.received_time is not null then 1
				else 0
				end
			) as if_received -- 是否收貨
		from order o
		left join order_detail od on o.id = od.order_id
		where o.create_time > '2021-04-01'
	) a 
	group by source,id
)
select
	source, -- 下單來源
	sum(1), -- 訂單數(shù)量
	sum(if_out), -- 出庫總單量
	sum(if_send), -- 發(fā)貨總單量
	sum(if_received) -- 收貨總單量
from base_data
group by source

其實就是一個訂單包含多個商品數(shù)據(jù)，每個商品的三種不同的時間都不同，想要算按照整個訂單來統(tǒng)計三個時間，只需要取訂單對應的多個商品數(shù)據(jù)，取它們的最小或最大時間來進行分組統(tǒng)計。

注意 distinct 只是針對整行數(shù)據(jù)去重，如果查詢結(jié)果有多列，比如 user_id、name、age、order_id 等等，查詢的數(shù)據(jù)中 user_id、name、age 都是一樣的，但是 order_id 有多個，那么使用 distinct 只會去除四個字段相同的行數(shù)據(jù)。

到此，相信大家對“Hive有哪些常見函數(shù)”有了更深的了解，不妨來實際操作一番吧！這里是創(chuàng)新互聯(lián)網(wǎng)站，更多相關(guān)內(nèi)容可以進入相關(guān)頻道進行查詢，關(guān)注我們，繼續(xù)學習！