HiveSQL的條件函數(shù)與日期函數(shù)全面匯總解析

點(diǎn)擊上方“數(shù)據(jù)管道”，選擇“置頂星標(biāo)”公眾號(hào)

干貨福利，第一時(shí)間送達(dá)

條件函數(shù)

assert_true(BOOLEAN condition)

• 解釋

如果condition不為true，則拋出異常，否則返回null

• 使用案例

select?assert_true(1<2)?--?返回null
select?assert_true(1>2)?--?拋出異常

coalesce(T v1, T v2, ...)

• 解釋

返回第一個(gè)不為null的值，如果都為null，則返回null

• 使用案例

select?coalesce(null,1,2,null)??--?返回1
select?coalesce(1,null)?--?返回1
select?coalesce(null,null)?--?返回null

if(BOOLEAN testCondition, T valueTrue, T valueFalseOrNull)

• 解釋

如果testCondition條件為true，則返回第一個(gè)值，否則返回第二個(gè)值

• 使用案例

select?if(1?is?null,0,1)??--?返回1
select?if(null?is?null,0,1)?--?返回0

isnotnull(a)

• 解釋

如果參數(shù)a不為null，則返回true，否則返回false

• 使用案例

select?isnotnull(1)?--?返回true
select?isnotnull(null)?--?返回false

isnull(a)

• 解釋

與isnotnull相反，如果參數(shù)a為null，則返回true，否則返回false

• 使用案例

select?isnull(null)?--?返回true
select?isnull(1)?--?返回false

nullif(a, b)

• 解釋

如果參數(shù)a=b，返回null，否則返回a值(Hive2.2.0版本)

• 使用案例

select?nullif(1,2)?--?返回1
select?nullif(1,1)?--?返回null

nvl(T value, T default_value)

• 解釋

如果value的值為null，則返回default_value默認(rèn)值，否則返回value的值。在null值判斷時(shí)，可以使用if函數(shù)給定默認(rèn)值，也可以使用此函數(shù)給定默認(rèn)值，使用該函數(shù)sql特別簡(jiǎn)潔。

• 使用案例

select?nvl(1,0)?--?返回1
select?nvl(null,0)?--?返回0

日期函數(shù)

add_months(DATE|STRING|TIMESTAMP start_date, INT num_months)

• 解釋

start_date參數(shù)可以是string, date 或者timestamp類(lèi)型，num_months參數(shù)時(shí)int類(lèi)型。返回一個(gè)日期，該日期是在start_date基礎(chǔ)之上加上num_months個(gè)月，即start_date之后null_months個(gè)月的一個(gè)日期。如果start_date的時(shí)間部分的數(shù)據(jù)會(huì)被忽略。注意：如果start_date所在月份的天數(shù)大于結(jié)果日期月的天數(shù)，則返回結(jié)果月的最后一天的日期。

• 使用案例

select?add_months("2020-05-20",2);?--?返回2020-07-20
select?add_months("2020-05-20",8);?--?返回2021-01-20
select?add_months("2020-05-31",1);?--?返回2020-06-30,5月有31天，6月只有30天，所以返回下一個(gè)月的最后一天

current_date

• 解釋

返回查詢時(shí)刻的當(dāng)前日期

• 使用案例

select?current_date()?--?返回當(dāng)前查詢?nèi)掌?020-05-20

current_timestamp()

• 解釋

返回查詢時(shí)刻的當(dāng)前時(shí)間

• 使用案例

select?current_timestamp()?--?2020-05-20?14:40:47.273

datediff(STRING enddate, STRING startdate)

• 解釋

返回開(kāi)始日期startdate與結(jié)束日期enddate之前相差的天數(shù)

• 使用案例

select?datediff("2020-05-20","2020-05-21");?--?返回-1
select?datediff("2020-05-21","2020-05-20");?--?返回1

date_add(DATE startdate, INT days)

• 解釋

在startdate基礎(chǔ)上加上幾天，然后返回加上幾天之后的一個(gè)日期

• 使用案例

select?date_add("2020-05-20",1);?--?返回2020-05-21,1表示加1天
select?date_add("2020-05-20",-1);?--?返回2020-05-19，-1表示減一天

date_sub(DATE startdate, INT days)

• 解釋

在startdate基礎(chǔ)上減去幾天，然后返回減去幾天之后的一個(gè)日期,功能與date_add很類(lèi)似

• 使用案例

select?date_sub("2020-05-20",1);?--?返回2020-05-19,1表示減1天
select?date_sub("2020-05-20",-1);?--?返回2020-05-21，-1表示加1天

date_format(DATE|TIMESTAMP|STRING ts, STRING fmt)

• 解釋

將date/timestamp/string類(lèi)型的值轉(zhuǎn)換為一個(gè)具體格式化的字符串。支持java的SimpleDateFormat格式，第二個(gè)參數(shù)fmt必須是一個(gè)常量

• 使用案例

select?date_format('2020-05-20',?'yyyy');?--?返回2020
select?date_format('2020-05-20',?'MM');?--?返回05
select?date_format('2020-05-20',?'dd');?--?返回20
--?返回2020年05月20日?00時(shí)00分00秒
select?date_format('2020-05-20',?'yyyy年MM月dd日?HH時(shí)mm分ss秒')?;
select?date_format('2020-05-20',?'yy/MM/dd')?--?返回?20/05/20

dayofmonth(STRING date)

• 解釋

返回一個(gè)日期或時(shí)間的天,與day()函數(shù)功能相同

• 使用案例

select?dayofmonth('2020-05-20')?--?返回20

extract(field FROM source)

• 解釋

提取 day, dayofweek, hour, minute, month, quarter, second, week 或者year的值，field可以選擇day, dayofweek, hour, minute, month, quarter, second, week 或者year，source必須是一個(gè)date、timestamp或者可以轉(zhuǎn)為 date 、timestamp的字符串。注意：Hive 2.2.0版本之后支持該函數(shù)

• 使用案例

select?extract(year?from?'2020-05-20?15:21:34.467');?--?返回2020，年
select?extract(quarter?from?'2020-05-20?15:21:34.467');?--?返回2，季度
select?extract(month?from?'2020-05-20?15:21:34.467');?--?返回05，月份
select?extract(week?from?'2020-05-20?15:21:34.467');?--?返回21，同weekofyear，一年中的第幾周
select?extract(dayofweek?from?'2020-05-20?15:21:34.467');?--?返回4,代表星期三
select?extract(day?from?'2020-05-20?15:21:34.467');?--?返回20，天
select?extract(hour?from?'2020-05-20?15:21:34.467');?--?返回15，小時(shí)
select?extract(minute?from?'2020-05-20?15:21:34.467');?--?返回21，分鐘
select?extract(second?from?'2020-05-20?15:21:34.467');?--?返回34，秒

year(STRING date)

• 解釋

返回時(shí)間的年份,可以用extract函數(shù)替代

• 使用案例

select?year('2020-05-20?15:21:34');?--?返回2020

quarter(DATE|TIMESTAMP|STRING a)

• 解釋

返回給定時(shí)間或日期的季度，1至4個(gè)季度,可以用extract函數(shù)替代

• 使用案例

select?quarter('2020-05-20?15:21:34');?--?返回2，第2季度

month(STRING date)

• 解釋

返回時(shí)間的月份,可以用extract函數(shù)替代

• 使用案例

select?month('2020-05-20?15:21:34')?--?返回5

day(STRING date),

• 解釋

返回一個(gè)日期或者時(shí)間的天,可以用extract函數(shù)替代

• 使用案例

select?day("2020-05-20");?--?返回20
select?day("2020-05-20?15:05:27.5");?--?返回20

hour(STRING date)

• 解釋

返回一個(gè)時(shí)間的小時(shí),可以用extract函數(shù)替代

• 使用案例

select?hour('2020-05-20?15:21:34');--?返回15

minute(STRING date)

• 解釋

返回一個(gè)時(shí)間的分鐘值,可以用extract函數(shù)替代

• 使用案例

select?minute('2020-05-20?15:21:34');?--?返回21

second(STRING date)

• 解釋

返回一個(gè)時(shí)間的秒,可以用extract函數(shù)替代

• 使用案例

select?second('2020-05-20?15:21:34');?--返回34

from_unixtime(BIGINT unixtime [, STRING format])

• 解釋

將將Unix時(shí)間戳轉(zhuǎn)換為字符串格式的時(shí)間(比如yyyy-MM-dd HH:mm:ss格式)

• 使用案例

select?from_unixtime(1589960708);?--?返回2020-05-20?15:45:08
select?from_unixtime(1589960708,?'yyyy-MM-dd?hh:mm:ss');?--?--?返回2020-05-20?15:45:08
select?from_unixtime(1589960708,?'yyyy-MM-dd');?--?返回2020-05-20

from_utc_timestamp(T a, STRING timezone)

• 解釋

轉(zhuǎn)換為特定時(shí)區(qū)的時(shí)間

• 使用案例

select?from_utc_timestamp('2020-05-20?15:21:34','PST');?--?返回2020-05-20?08:21:34.0
select?from_utc_timestamp('2020-05-20?15:21:34','GMT');?--?返回2020-05-20?15:21:34.0
select?from_utc_timestamp('2020-05-20?15:21:34','UTC');?--?返回2020-05-20?15:21:34.0
select?from_utc_timestamp('2020-05-20?15:21:34','DST');?--?返回2020-05-20?15:21:34.0
select?from_utc_timestamp('2020-05-20?15:21:34','CST');?--?返回2020-05-20?10:21:34.0

last_day(STRING date)

• 解釋

返回給定時(shí)間或日期所在月的最后一天，參數(shù)可以是'yyyy-MM-dd HH:mm:ss' 或者 'yyyy-MM-dd'類(lèi)型，時(shí)間部分會(huì)被忽略

• 使用案例

select?last_day('2020-05-20?15:21:34');?--?返回2020-05-31
select?last_day('2020-05-20');?--?返回2020-05-31?

to_date(STRING timestamp)

• 解釋

返回一個(gè)字符串時(shí)間的日期部分，去掉時(shí)間部分，2.1.0之前版本返回的是string，2.1.0版本及之后返回的是date

• 使用案例

select?to_date('2020-05-20?15:21:34');?--?返回2020-05-20
select?to_date('2020-05-20');?--?返回2020-05-20

to_utc_timestamp(T a, STRING timezone)

• 解釋

轉(zhuǎn)換為世界標(biāo)準(zhǔn)時(shí)間UTC的時(shí)間戳,與from_utc_timestamp類(lèi)似

• 使用案例

select?to_utc_timestamp('2020-05-20?15:21:34',?'GMT');?--?返回2020-05-20?15:21:34.0

trunc(STRING date, STRING format)

• 解釋

截?cái)嗳掌诘街付ǖ娜掌诰龋瑑H支持月（MONTH/MON/MM）或者年（YEAR/YYYY/YY）

• 使用案例

select?trunc('2020-05-20',?'YY');???--?返回2020-01-01，返回年的1月1日
select?trunc('2020-05-20',?'MM');???--?返回2020-05-01，返回月的第一天
select?trunc('2020-05-20?15:21:34',?'MM');???--?返回2020-05-01

unix_timestamp([STRING date [, STRING pattern]])

• 解釋

參數(shù)時(shí)可選的，當(dāng)參數(shù)為空時(shí)，返回當(dāng)前Unix是時(shí)間戳，精確到秒。可以指定一個(gè)具體的日期，轉(zhuǎn)換為Unix時(shí)間戳格式

• 使用案例

--?返回1589959294
select?unix_timestamp('2020-05-20?15:21:34','yyyy-MM-dd?hh:mm:ss');
--?返回1589904000
select?unix_timestamp('2020-05-20','yyyy-MM-dd');

weekofyear(STRING date)

• 解釋

返回一個(gè)日期或時(shí)間在一年中的第幾周，可以用extract替代

• 使用案例

select?weekofyear('2020-05-20?15:21:34');?--?返回21，第21周
select?weekofyear('2020-05-20');?--?返回21，第21周

next_day(STRING start_date, STRING day_of_week)

• 解釋

參數(shù)start_date可以是一個(gè)時(shí)間或日期，day_of_week表示星期幾，比如Mo表示星期一，Tu表示星期二，Wed表示星期三，Thur表示星期四，F(xiàn)ri表示星期五，Sat表示星期六，Sun表示星期日。如果指定的星期幾在該日期所在的周且在該日期之后，則返回當(dāng)周的星期幾日期，如果指定的星期幾不在該日期所在的周，則返回下一個(gè)星期幾對(duì)應(yīng)的日期

• 使用案例

select?next_day('2020-05-20','Mon');--?返回當(dāng)前日期的下一個(gè)周一日期:2020-05-25
select?next_day('2020-05-20','Tu');--?返回當(dāng)前日期的下一個(gè)周二日期:2020-05-26
select?next_day('2020-05-20','Wed');--?返回當(dāng)前日期的下一個(gè)周三日期:2020-05-27
--?2020-05-20為周三，指定的參數(shù)為周四，所以返回當(dāng)周的周四就是2020-05-21
select?next_day('2020-05-20','Th');
select?next_day('2020-05-20','Fri');--?返回周五日期2020-05-22
select?next_day('2020-05-20','Sat');?--?返回周六日期2020-05-23
select?next_day('2020-05-20','Sun');?--?返回周六日期2020-05-24

該函數(shù)比較重要：比如取當(dāng)前日期所在的周一和周日，通過(guò)長(zhǎng)用在按周進(jìn)行匯總數(shù)據(jù)

select?date_add(next_day('2020-05-20','MO'),-7);?--?返回當(dāng)前日期的周一日期2020-05-18
select?date_add(next_day('2020-05-20','MO'),-1);?--?返回當(dāng)前日期的周日日期2020-05-24

months_between(DATE|TIMESTAMP|STRING date1, DATE|TIMESTAMP|STRING date2)

• 解釋

返回 date1 和 date2 的月份差。如果date1大于date2，返回正值，否則返回負(fù)值，如果是相減是整數(shù)月，則返回一個(gè)整數(shù)，否則會(huì)返回小數(shù)

• 使用案例

select?months_between('2020-05-20','2020-05-20');?--?返回0
select?months_between('2020-05-20','2020-06-20');?--?返回-1
--?相差的整數(shù)月
select?months_between('2020-06-30','2020-05-31');?--?返回1
--?非整數(shù)月，一個(gè)月差一天
select?months_between('2020-06-29','2020-05-31');?--?返回0.93548387

小結(jié)

本文主要介紹了Hive的條件函數(shù)和日期函數(shù)，并給出了每個(gè)函數(shù)的解釋說(shuō)明和使用案例，本文覆蓋了所有Hive內(nèi)置的條件函數(shù)和日期函數(shù)，可以作為一個(gè)函數(shù)字典，方便工作中使用。