写点什么

Hive 的条件函数与日期函数汇总

作者:Steven
  • 2023-01-13
    河南
  • 本文字数:4827 字

    阅读完需:约 16 分钟

文章中汇总了 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 内置的条件函数和日期函数,可以作为一个函数字典,方便工作中使用。

发布于: 刚刚阅读数: 5
用户头像

Steven

关注

还未添加个人签名 2023-01-09 加入

还未添加个人简介

评论

发布
暂无评论
Hive的条件函数与日期函数汇总_Steven_InfoQ写作社区