Hadoop技术栈（四）HIVE常用函数汇总

函数汇总

常用函数

-- 当前前日期

select current_date;

select unix_timestamp();

-- 建议使用current_timestamp，有没有括号都可以

select current_timestamp();

-- 时间戳转日期

select from_unixtime(1505456567);

select from_unixtime(1505456567, 'yyyyMMdd');

select from_unixtime(1505456567, 'yyyy-MM-dd HH:mm:ss');

-- 日期转时间戳

select unix_timestamp('2019-09-15 14:23:00');

-- 计算时间差

select datediff('2020-04-18','2019-11-21');

select datediff('2019-11-21', '2020-04-18');

-- 查询当月第几天

select dayofmonth(current_date);

-- 计算月末:

select last_day(current_date);

-- 当月第1天:

select date_sub(current_date, dayofmonth(current_date)-1)

-- 下个月第1天:

select add_months(date_sub(current_date, dayofmonth(current_date)-1), 1)

-- 字符串转时间（字符串必须为：yyyy-MM-dd格式）

select to_date('2020-01-01');

select to_date('2020-01-01 12:12:12');

-- 日期、时间戳、字符串类型格式化输出标准时间格式

select date_format(current_timestamp(), 'yyyy-MM-dd HH:mm:ss');

select date_format(current_date(), 'yyyyMMdd');

select date_format('2020-06-01', 'yyyy-MM-dd HH:mm:ss');

-- 计算emp表中，每个人的工龄

select *, round(datediff(current_date, hiredate)/365,1)

workingyears from emp;

select * from fenmi_pay.pay_trade_order_202503 limit 1
-- --------------------------- 字符串 函数 --------------------------------- --
-- 反转字符串
select reverse(goods_name) from fenmi_pay.pay_trade_order_202503 limit 1
-- 字符串 字节字节字节 长度 这个utfmb3还是mb4决定中文字符用3字节还是4字节
select length(goods_name) from fenmi_pay.pay_trade_order_202503 limit 1
-- 连接字符串函数 没有连接符的
select concat(goods_name,pay_way_name,id) from fenmi_pay.pay_trade_order_202503 limit 1
-- 连接字符串函数 带连接符的 也可以不带连接符
select concat_ws(',',goods_name,pay_way_name,id) from fenmi_pay.pay_trade_order_202503 limit 1
-- 提取字串 哪个字段 从哪个字符开始（最少要是1） 提取长度多少
select substr(goods_name, 1, 5) from fenmi_pay.pay_trade_order_202503 limit 1
-- 有字母的字母全转为 大 写
select upper(goods_name),ucase(goods_name) from fenmi_pay.pay_trade_order_202503 limit 1
-- 有字母的字母全转为 小 写
select lower(goods_name),lcase(goods_name) from fenmi_pay.pay_trade_order_202503 limit 1
-- 去除字符串 前和后 一个或多个 空格
select trim(goods_name),ltrim(goods_name),rtrim(goods_name) from fenmi_pay.pay_trade_order_202503 limit 1
-- HIVE中的拆分字符串
-- select split(goods_name,'-') from fenmi_pay.pay_trade_order_202503 limit 1
-- 将字段重复 n 遍
select repeat(goods_name, 3) from fenmi_pay.pay_trade_order_202503 limit 1
-- 返回n个空格组成的字符串
select space(10);
-- 替换所有 字段 需要替换的字串 用来替换的新字串
select replace(goods_name,'-','') from fenmi_pay.pay_trade_order_202503 limit 1
-- 替换匹配 正则表达 的字符串 字段 需要替换的字串 用来替换的新字串
select regexp_replace(goods_name,'(\\d+)','null') from fenmi_pay.pay_trade_order_202503 limit 1
-- 提取匹配 正则表达 的字符串
-- select regexp_extract(goods_name,'(\\d+)',5) from fenmi_pay.pay_trade_order_202503 limit 1
-- 提取匹配 正则表达 的字符串 全部提取
-- select regexp_extract_all(goods_name,'(\\d+)') from fenmi_pay.pay_trade_order_202503 limit 1

-- ----------------------------- 数学 函数 --------------------------------  --
-- 四舍五入 不保留小数点
select round(order_amount) from fenmi_pay.pay_trade_order_202503 limit 1
-- 四舍五入 保留 指定小数点位数
select round(rate, 3) from fenmi_pay.pay_trade_order_202503 limit 1
-- 向上取整
select ceil(7 / 3);
-- 向下取整
select floor(0.36);
-- 绝对值
select abs(-0.36);
-- 0-1随机数
select rand();
-- a的p次幂
select pow(2, 3);
select pow(10, log10(9669))
-- 开平方根
select sqrt(9);
-- e的a次幂
select exp(2);
-- 自然对数以e为底
select log(9669);
-- 以10为底的对数
select log10(9669);
-- 三角函数 参数是度数
select sin(90);

-- -------------------------------- 日期 函数 ------------------------- --
-- 将当前日期转换为时间戳
select unix_timestamp('2020-12-30')
-- 将指定日期转换为时间戳
select unix_timestamp('2023-05-26 14:49:53')
-- 将时间戳转换为时间
select from_unixtime(1609257600);
-- 提取日期部分
-- select to_date(create_time) from fenmi_pay.pay_trade_order_202503 limit 1
-- -- 提取年，月，日
select year(create_time),month(create_time),day(create_time),hour(create_time),minute(create_time),second(create_time)
from fenmi_pay.pay_trade_order_202503 limit 1
-- 时间相加 时间相减 字段类型必须date类型
select date_add(startdate, interval 1 day) as da, date_sub('2025-08-16', interval 1 day) from mydb1.marketing limit 1
-- 当前时间
select crrent_date();
-- 当前时间戳
select current_timestamp();
-- 一年中第几周
select WEEKOFYEAR('2025-08-16');
select DAYOFWEEK('2025-08-16')
select DAYOFMONTH('2025-08-16')
select DAYOFYEAR('2025-08-16')
-- 时间相差天数
select datediff(update_time, create_time) from fenmi_pay.pay_trade_order_202503 limit 1

-- ------------------------------ 条件 函数 ------------------------------- --
-- if 条件
select if (false, 1, 0);
-- 返回第一个非空值
select coalesce(1, 0);
-- 多条件分支
select case when startdate is null then 1 when 2 > 1 then 3 else 0 end as t from mydb1.marketing;
-- 判断null
select isnull(null);
-- 判断不是null
-- select isnotnull(1);
-- 为null,返回默认值 ifnull nvl
select ifnull(null, 1);
-- a == b 返回null 否则返回a
select nullif(startdate, '2018-09-03') from mydb1.marketing;

-- -------------------------------- 聚合函数 -------------------------------- --
-- 统计行数
select count(*) from mydb1.marketing;
-- 统计非空值的行数
select count(startdate) from mydb1.marketing;
-- 求和
select sum(sal) from mydb1.emp;
-- 求平均值
select avg(sal) from mydb1.emp;
-- 最小值
select min(sal) from mydb1.emp;
-- 最大值
select max(sal) from mydb1.emp;
-- 总体方差 VAR_POP(sal)
select variance(sal) from mydb1.emp;
-- 样本方差 VAR_SAMP(sal) 
select var_samp(sal) from mydb1.emp;
-- 总体标准差
select stddev_pop(sal) from mydb1.emp;
-- 样本标准差
select stddev_samp(sal) from mydb1.emp;
-- 总体协方差
select covar_pop(sal) from mydb1.emp;
-- 样本协方差
select covar_samp(sal) from mydb1.emp;
-- 相关系数
-- select corr(startdate, enddate) from mydb1.emp;
-- 将列值收集到一个集合set
-- select collect_set(sal) from mydb1.emp;
-- 将列值收集到一个列表list
-- select collect_list(sal) from mydb1.emp;
-- 计算第p百分位数 percentile：百分之50都小于这个值的sal值是啥
select percentile(sal, 0.5) from mydb1.emp;

-- -------------------------------- 窗口 函数 ---------------------------- --
-- 行号 UNBOUNDED无边界 PRECEDING当前行往上几行 和 FOLLOWING当前行往下几行
select row_number() over(partition by deptno order by sal range between 1 following AND 2 following) from mydb1.emp;
-- 数据分成n组，并返回组号
select ntile(2) over(partition by deptno) as gid from mydb1.emp;
-- 跳跃排名
select dense_rank() over(partition by deptno order by sal) from mydb1.emp;
-- 紧凑排名
select dense_rank() over(partition by deptno order by sal) from mydb1.emp;
-- 某一列值百分比排名：比如谁的业绩排在前10%，谁的成绩在第百分之50
-- PERCENT_RANK = (当前行排名 - 1) / (总行数 - 1) 其中，排名由ORDER BY子句决定，重复值会获得相同排名
select deptno,empname,sal,PERCENT_RANK() over (partition by deptno order by sal desc) from mydb1.emp;
-- 当前行 后面 第n行的某col值 比如：当前行后面第三行的结束日期
select *,lead(hiredate, 2, '超出行数') over(partition by deptno order by sal desc) as l_date from mydb1.emp;
-- 当前行 前面 第n行的某col值 比如：当前行前面第一行的结束日期
select *,lag(hiredate, 1, '超出行数') over(partition by deptno order by sal desc) as l_date from mydb1.emp;
-- 窗口内第一个值
select first_value(empname) over(partition by deptno) from mydb1.emp;
-- 窗口内最后一个值
select last_value(empname) over(partition by deptno) from mydb1.emp;
-- 汇总当前窗口的 1. 如果加了排序则从第一行到当前行的值；2.如果不加排序，就是所有行 求和，
select *,sum(sal) over(partition by deptno order by sal desc) from mydb1.emp;
-- 窗口行数统计或累计计数 ORDER BY会使窗口范围变为"从分区开始到当前行就是说逐笔交易累计计数
-- ROWS基于物理行号确定范围，严格按行位置计算
-- RANGE基于排序列的逻辑值范围计算，会合并相同值的行
-- 实际效果示例‌
-- 对于包含重复值的数据(如日期相同)：
-- ROWS会对每行独立计算，即使值相同
-- RANGE会将相同值的所有行纳入同一窗口范围
-- ‌边界定义差异‌
-- ROWS的UNBOUNDED PRECEDING指向分区第一物理行
-- RANGE的UNBOUNDED PRECEDING指向最小逻辑值
-- 使用INTERVAL单位时仅RANGE支持（如INTERVAL 2 DAY）
-- ‌性能考量‌
-- ROWS通常执行更快，因其只需计算固定行数；RANGE需要扫描值范围，对大数据集可能较慢
select *, count(*) over(partition by deptno order by sal desc rows between current row and unbounded following) from mydb1.emp;
-- ------------------------------- 集合 函数 ------------------------------ --
-- 多行转一行
SELECT deptno,GROUP_CONCAT(empname) FROM mydb1.emp GROUP BY deptno;

SELECT deptno, JSON_OBJECTAGG(empname, sal) FROM mydb1.emp GROUP BY deptno;

-- 自定义函数
CREATE FUNCTION SPLIT_STR(str VARCHAR(255), delim VARCHAR(12), pos INT)
RETURNS VARCHAR(255) RETURN SUBSTRING_INDEX(
  SUBSTRING_INDEX(str, delim, pos), delim, -1
);

-- ------------------------------- 类型转换 函数 -------------------------------- --
-- 类型转换
SELECT cast('-123' as signed);
SELECT CAST(123.456 AS DECIMAL(5,2));
SELECT CAST('2025-08-16' AS datetime);
-- ------------------------------- 其它 函数 -------------------------------- --
hash(col1, col2, ...)：返回哈希值。
md5(string)：返回MD5哈希值。
sha1(string), sha2(string, bitLength)：返回SHA哈希值。
concat_ws(separator, array)：将数组中的字符串用分隔符连接。
explode(array)：将数组中的元素拆分成多行（UDTF）。
explode(map)：将Map中的键值对拆分成多行（UDTF）。
posexplode(array)：将数组拆分成多行，并带位置索引。
inline(array<struct>)：将结构体数组拆分成多行。
json_tuple(jsonStr, k1, k2, ...)：从JSON字符串中提取多个键值（UDTF）。
parse_url(url, part)：解析URL

-- -------------------------------- 自定义函数 -------------------------------- --

-- ------------------------------- 类型转换 函数 -------------------------------- --

insert into mydb1.emp (select * from mydb1.emp)
insert into db.tb_1 values ('','','',9,''), ('','','',9,''), ('','','',9,'');

with tmp as (select empno,empname,deptno,sal,
row_number() over (partition by deptno order by sal desc) as sal_rank,
sum(sal) over (partition by deptno) as t_sal,
round(sal / sum(sal) over (partition by deptno), 2) as zb
from mydb1.emp)
select * from tmp where sal_rank < 4;

with tmp as (
select year(hiredate) hire_y, count(1) totalCnt
from mydb1.emp group by hire_y
)
select sum(totalCnt) from tmp
select *, sum(totalCnt) over (order by hire_y) accCnt from tmp

with tmp1 as (
    select 
    brand,
    startdate,
    enddate,
    row_number() over(partition by brand order by startdate) as rn,
    lag(enddate) over(partition by brand order by startdate) as prev_enddate
    from mydb1.marketing
),
tmp2 as (
    select *,
    case 
        when prev_enddate is null then datediff(enddate, startdate) + 1 -- else 0 这里一定不能有else
        when startdate > prev_enddate + 1 then datediff(enddate, startdate) + 1 
        else datediff(enddate, prev_enddate) + 1  
    end as act_day_cnt
    from tmp1
)
select brand,sum(act_day_cnt) as totalCnt from tmp2 group by brand;

-- SUM(salary) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 指定了窗口的范围，从当前行往前的前两行到当前行的范围。也就是说，对于每个月份的行，窗口函数会计算该行和前两个月份的行的工资总和。
-- OVER (ORDER BY month ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)，计算每个月份及其后两个月份的工资总和
with tmp1 as (
    select 
    brand,
    startdate,
    enddate,
    row_number() over(partition by brand order by startdate) as rn,
    lag(enddate) over(partition by brand order by startdate) as prev_enddate
    from mydb1.marketing
),
tmp2 as (
    select *,
    -- last_value first_value( 窗口内最后一个值 窗口内第一个值
    sum(
        case 
            when prev_enddate is null then datediff(enddate, startdate) + 1 -- else 0 这里一定不能有else
            when startdate > prev_enddate + 1 then datediff(enddate, startdate) + 1 
            else datediff(enddate, prev_enddate) + 1  
        end
    ) over (partition by brand) as act_day_cnt -- over里边的order by startdate 加了排序就会把顺序相同的相加，比如两个时间相同，和就会加起来 这个sum汇总每一行都是会计算该窗口当前行及前面所有行的累积和
    from tmp1
)
select brand,act_day_cnt from tmp2 where rn = 1;

select *,sum(if(empname != 'KING', 1, 0)) over(partition by deptno) as tco from mydb1.emp

select *,count(*) over(partition by deptno) as tco from mydb1.emp where empname = 'GLARK'

unbounded preceding 。组内第一行数据

n preceding 。组内当前行的前 n 行数据

current row 。当前行数据

n following 。组内当前行的后 n 行数据

unbounded following 。组内最后一行数据

-- explode，炸裂函数

-- 就是将一行中复杂的 array 或者 map 结构拆分成多行

select explode(array('A','B','C')) as col;

select explode(map('a', 8, 'b', 88, 'c', 888));

-- lateral view 的基本使用

with t1 as (

select 'OK' cola, split('www.baidu.com', '\\.') colb

)

select cola, colc

from t1

lateral view explode(colb) t2 as colc;

自定义UDF函数java代码

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;
public class nvl extends UDF {
public Text evaluate(final Text t, final Text x) {
if (t == null || t.toString().trim().length()==0) {
return x;
}
return t;
}
}

添加开发的jar包（在Hive命令行中）

add jar /home/hadoop/hiveudf.jar;

创建临时函数。指定类名一定要完整的路径，即包名加类名

create temporary function mynvl as "cn.lxq.hive.udf.nvl";

在Hive命令行中创建永久函数

create function mynvl1 as 'cn.lxq.hive.udf.nvl' using jar 'hdfs:/user/hadoop/jar/hiveudf.jar';