HIVE函数

深入解析：SQL函数与日期处理在数据库中的应用

原创已于 2022-02-24 00:30:27 修改 · 773 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#hive #hadoop #数据仓库

于 2021-12-28 15:09:59 首次发布

本文详细介绍了SQL中的数学函数（如round、floor、ceil等）、类型转换函数、日期时间操作（如from_unixtime、unix_timestamp等），并展示了如何在实际场景中运用这些函数进行数据处理和格式转换。

文章仅作为笔记使用，会持续更新

数学函数

-- round(DOUBLE a) 返回对a四舍五入的最大整数值,类型为double
select round(10.234); -- 返回10
select round(10.234,2); -- 返回10.23
select round(10.235,2); -- 返回10.24

-- floor(DOUBLE a) 向下取整
select floor(10.3); -- 10
select floor(-10.3); -- -11

-- ceil(DOUBLE a)/ceiling(double a) 向上取整
select ceil(10.3); -- 11
select ceiling(10.3); -- 11

-- rand() 返回一个0到1范围内的随机数
select rand();
-- rand(INT seed) 返回一个0到1范围内的随机数 seed不变，随机数不变
select rand(10);

-- pow(double a,double b)	a的b次方
select pow(3,2); -- 9

-- sqrt(double a) 开根号,若a为负数返回null
select sqrt(9); -- 3

-- abs(double a) 取绝对值
select abs(-10.1); -- 10.1

-- pmod(int a,int b) a除b的余数
select pmod(10,3); -- 1

类型转换函数

-- cast(expr as<type>) 将expr转换成type类型，如果转换失败将返回NULL
select CAST('1' AS int);
-- binary 将输入的值转换成二进制,一般不用

日期函数

-- from_unixtime(bigint unixtime) 将时间戳转换成format格式
select from_unixtime(1594604100); -- 2020-07-13 09:35:00
select from_unixtime(1594604100,'yyyy-MM-dd');-- 2020-07-13

-- unix_timestamp() 获取本地时区下的时间戳
select unix_timestamp(); -- 1594616493

-- unix_timestamp(string date) 将格式为yyyy-MM-dd HH:mm:ss的时间字符串转换成时间戳
select unix_timestamp('2020-07-13 09:35:00'); -- 1594604100

-- to_date(string timestamp) 返回时间字符串的日期部分
select to_date('2020-07-13 09:35:00'); -- 2020-07-13

-- year(string date) 返回时间字符串的年份部分
select year('2020-07-13 09:35:15'); -- 2020

-- month(string date) 返回时间字符串的月份部分
select month('2020-07-13 09:35:15'); -- 7

-- day(string date)	返回时间字符串的天(月份)
select day('2020-07-13 09:35:15'); -- 13

-- hour(string date) 返回时间字符串的小时
select hour('2020-07-13 09:35:15'); -- 9

-- minute(string date)	返回时间字符串的分钟
select minute('2020-07-13 09:35:15'); -- 35

-- second(string date)	返回时间字符串的秒
select second('2020-07-13 09:35:15'); -- 15

-- dayofweek(string date)	返回时间字符串位于一周中的第几天,星期天开始为第一天
select dayofweek('2021-12-28'); -- 3

-- datediff(string enddate, string startdate)	计算开始时间startdate 到结束时间enddate相差的天数
select datediff('2020-07-13 00:00:00','2020-07-11 09:35:00'); -- 2

-- date_add(string startdate, int days)	从开始时间startdate加上days天数,day 为负数则为减
select date_add('2020-07-13 09:35:00',2); -- 2020-07-15

-- date_sub(string startdate, int days)	从开始时间startdate减去days天数，为负数则为加
select date_sub('2020-07-13 09:35:00',2); -- 2020-07-11

-- current_date	返回当前时间日期
select current_date(); -- YYYY-MM-DD

-- add_months(string start_date, int num_months)	返回当前时间下再增加num_months个月的日期
select add_months('2021-12-28',3); -- 2022-03-28

-- last_day(string date)	返回这个月的最后一天的日期，忽略时分秒部分（HH:mm:ss）
select last_day('2021-07-28'); -- 2021-07-31

-- next_day(string start_date, string day_of_week)	返回当前时间的下一个星期X所对应的日期。 day_of_week：星期几的缩写
select next_day('2021-12-28','MO'); -- 2022-01-03

-- trunc(string date, string format) 返回时间的最开始年份或月份 ,注意所支持的格式为MONTH/MON/MM, YEAR/YYYY/YY
select trunc('2021-12-28','MM'); -- 2021-12-01
select trunc('2021-12-28','YYYY'); -- 2021-01-01

-- months_between(date1, date2)	返回date1减date2之间相差的月份，如date1>date2，则返回正，如果date1<date2,则返回负，否则返回0.0
select months_between('2021-12-28', '2021-07-28'); -- 5

-- date_format(date/timestamp/string ts, string fmt)	fmt格式
select date_format('2020-07-13','yyyy-MM'); -- 2020-07
select date_format('2020-07-13','yyyy'); -- 2020
select date_format('2020-07-13','yyyy-MM-dd'); -- 2020-07-13

将yyyyMMdd变成yyyy-MM-dd:
select to_date(from_unixtime(UNIX_TIMESTAMP('20210101','yyyyMMdd')));