Hive 的条件函数与日期函数汇总
文章中汇总了 hive 相关的一些条件函数 及 日期函数 ,如有错误的地方请私信
条件函数
assert_true(BOOLEAN condition)
解释
如果 condition 不为 true,则抛出异常,否则返回 null
使用案例
select assert_true(1<2) -- 返回 null
select assert_true(1>2) -- 抛出异常
coalesce(T v1, T v2, ...)
解释
返回第一个不为 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,则返回第一个值,否则返回第二个值
使用案例
select if(1 is null,0,1) -- 返回 1
select if(null is null,0,1) -- 返回 0
isnotnull(a)
解释
如果参数 a 不为 null,则返回 true,否则返回 false
使用案例
select isnotnull(1) -- 返回 true
select isnotnull(null) -- 返回 false
isnull(a)
解释
与 isnotnull 相反,如果参数 a 为 null,则返回 true,否则返回 false
使用案例
select isnull(null) -- 返回 true
select isnull(1) -- 返回 false
nullif(a, b)
解释
如果参数 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 默认值,否则返回 value 的值。在 null 值判断时,可以使用 if 函数给定默认值,也可以使用此函数给定默认值,使用该函数 sql 特别简洁。
使用案例
select nvl(1,0) -- 返回 1
select nvl(null,0) -- 返回 0
日期函数
add_months(DATE|STRING|TIMESTAMP start_date, INT num_months)
解释
start_date 参数可以是 string, date 或者 timestamp 类型,num_months 参数时 int 类型。返回一个日期,该日期是在 start_date 基础之上加上 num_months 个月,即 start_date 之后 null_months 个月的一个日期。如果 start_date 的时间部分的数据会被忽略。注意:如果 start_date 所在月份的天数大于结果日期月的天数,则返回结果月的最后一天的日期。
使用案例
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 天,所以返回下一个月的最后一天
current_date
解释
返回查询时刻的当前日期
使用案例
select current_date() -- 返回当前查询日期 2020-05-20
current_timestamp()
解释
返回查询时刻的当前时间
使用案例
select current_timestamp() -- 2020-05-20 14:40:47.273
datediff(STRING enddate, STRING startdate)
解释
返回开始日期 startdate 与结束日期 enddate 之前相差的天数
使用案例
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 基础上加上几天,然后返回加上几天之后的一个日期
使用案例
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 基础上减去几天,然后返回减去几天之后的一个日期,功能与 date_add 很类似
使用案例
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 类型的值转换为一个具体格式化的字符串。支持 java 的 SimpleDateFormat 格式,第二个参数 fmt 必须是一个常量
使用案例
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 时 00 分 00 秒
select date_format('2020-05-20', 'yyyy 年 MM 月 dd 日 HH 时 mm 分 ss 秒') ;
select date_format('2020-05-20', 'yy/MM/dd') -- 返回 20/05/20
dayofmonth(STRING date)
解释
返回一个日期或时间的天,与 day()函数功能相同
使用案例
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 必须是一个 date、timestamp 或者可以转为 date 、timestamp 的字符串。注意:Hive 2.2.0 版本之后支持该函数
使用案例
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,小时
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)
解释
返回时间的年份,可以用 extract 函数替代
使用案例
select year('2020-05-20 15:21:34'); -- 返回 2020
quarter(DATE|TIMESTAMP|STRING a)
解释
返回给定时间或日期的季度,1 至 4 个季度,可以用 extract 函数替代
使用案例
select quarter('2020-05-20 15:21:34'); -- 返回 2,第 2 季度
month(STRING date)
解释
返回时间的月份,可以用 extract 函数替代
使用案例
select month('2020-05-20 15:21:34') -- 返回 5
day(STRING date),
解释
返回一个日期或者时间的天,可以用 extract 函数替代
使用案例
select day("2020-05-20"); -- 返回 20
select day("2020-05-20 15:05:27.5"); -- 返回 20
hour(STRING date)
解释
返回一个时间的小时,可以用 extract 函数替代
使用案例
select hour('2020-05-20 15:21:34');-- 返回 15
minute(STRING date)
解释
返回一个时间的分钟值,可以用 extract 函数替代
使用案例
select minute('2020-05-20 15:21:34'); -- 返回 21
second(STRING date)
解释
返回一个时间的秒,可以用 extract 函数替代
使用案例
select second('2020-05-20 15:21:34'); --返回 34
from_unixtime(BIGINT unixtime [, STRING format])
解释
将将 Unix 时间戳转换为字符串格式的时间(比如 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)
解释
转换为特定时区的时间
使用案例
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)
解释
返回给定时间或日期所在月的最后一天,参数可以是'yyyy-MM-dd HH:mm:ss' 或者 'yyyy-MM-dd'类型,时间部分会被忽略
使用案例
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)
解释
返回一个字符串时间的日期部分,去掉时间部分,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)
解释
转换为世界标准时间 UTC 的时间戳,与 from_utc_timestamp 类似
使用案例
select to_utc_timestamp('2020-05-20 15:21:34', 'GMT'); -- 返回 2020-05-20 15:21:34.0
trunc(STRING date, STRING format)
解释
截断日期到指定的日期精度,仅支持月(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]])
解释
参数时可选的,当参数为空时,返回当前 Unix 是时间戳,精确到秒。可以指定一个具体的日期,转换为 Unix 时间戳格式
使用案例
-- 返回 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)
解释
返回一个日期或时间在一年中的第几周,可以用 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)
解释
参数 start_date 可以是一个时间或日期,day_of_week 表示星期几,比如 Mo 表示星期一,Tu 表示星期二,Wed 表示星期三,Thur 表示星期四,Fri 表示星期五,Sat 表示星期六,Sun 表示星期日。如果指定的星期几在该日期所在的周且在该日期之后,则返回当周的星期几日期,如果指定的星期几不在该日期所在的周,则返回下一个星期几对应的日期
使用案例
select next_day('2020-05-20','Mon');-- 返回当前日期的下一个周一日期:2020-05-25
select next_day('2020-05-20','Tu');-- 返回当前日期的下一个周二日期:2020-05-26
select next_day('2020-05-20','Wed');-- 返回当前日期的下一个周三日期:2020-05-27
-- 2020-05-20 为周三,指定的参数为周四,所以返回当周的周四就是 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
该函数比较重要:比如取当前日期所在的周一和周日,通过长用在按周进行汇总数据
select date_add(next_day('2020-05-20','MO'),-7); -- 返回当前日期的周一日期 2020-05-18
select date_add(next_day('2020-05-20','MO'),-1); -- 返回当前日期的周日日期 2020-05-24
months_between(DATE|TIMESTAMP|STRING date1, DATE|TIMESTAMP|STRING date2)
解释
返回 date1 和 date2 的月份差。如果 date1 大于 date2,返回正值,否则返回负值,如果是相减是整数月,则返回一个整数,否则会返回小数
使用案例
select months_between('2020-05-20','2020-05-20'); -- 返回 0
select months_between('2020-05-20','2020-06-20'); -- 返回-1
-- 相差的整数月
select months_between('2020-06-30','2020-05-31'); -- 返回 1
-- 非整数月,一个月差一天
select months_between('2020-06-29','2020-05-31'); -- 返回 0.93548387
小结
本文主要介绍了 Hive 的条件函数和日期函数,并给出了每个函数的解释说明和使用案例,本文覆盖了所有 Hive 内置的条件函数和日期函数,可以作为一个函数字典,方便工作中使用。
版权声明: 本文为 InfoQ 作者【Steven】的原创文章。
原文链接:【http://xie.infoq.cn/article/7fffd998986533187ee67c5d6】。文章转载请联系作者。
评论