Hive 函数
1. Hive 函数分类
从输入输出的角度,可以将Hive的函数分为3类:标准函数、聚合函数、表生成函数
-
标准函数:以一行中的一列或多列数据作为输入的参数且返回结果是一个值的函数。
标准函数返回值只有一个,返回值类型为基本数据类型或复杂数据类型,如cast()
-
聚合函数:以多行的零个或多个列的数据作为输入且返回单一值的函数。
聚合函数常与
group by
子句结合使用。例如 sum(), count(), max()等 -
表生成函数:接受零个或多个输入且产生多列或多行输出的函数。
1.1 查看函数命令
-
show functions
命令用于显示当前Hive会话中加载的所有函数,包括 内置函数、自定义函数 -
desc function function_name
和desc function extended function_name
两个命令可以用于查看指定函数名称的描述,extended 关键字可以显示的更加详细
1.2 调用函数
通过在查询语句中调用函数名,并传入参数来调用函数,函数的调用可以用于 select
与 where
子句中,包括以下3种典型情况:
(1) select concat(cola, colb) as x from table_name;
(2)select concat('abc', 'def');
(3)select * from table_name where length(col)<10;
2. Hive内置函数
-
数据集:
testData.txt
-
创建数据库:
create database wedw_tmp;
-
创建数据表:
tmp_url_info
create table tmp_url_info(
user_id string comment "用户id",
visit_url string comment "访问url",
visit_cnt int comment "浏览次数/pv",
visit_time timestamp comment "浏览时间",
visit_date string comment "浏览日期"
)
row format delimited
fields terminated by ','
stored as textfile;
2.1 字符【字符串】相关
字符相关的函数侧重于对字符串进行处理。以下时使用频率高的字符函数。
-
concat
字符拼接,对多个字符串或二进制字符码按照参数顺序进行拼接。
concat(string|binary A, string|binary B...)
select concat('a','b','c');
-
concat_ws
按照指定分隔符将字符或者数组进行拼接;第一个参数是分隔符。concat_ws(string SEP, array)/concat_ws(string SEP, string A, string B...)
select concat_ws('','a','b','c') #将数组列表元素按照指定分隔符拼接,类似于python中的join方法 select concat_ws('',array('a','b','c'))
-
instr
查找字符串str中子字符串substr出现的位置,如果查找失败将返回0,如果任一参数为Null将返回null,注意位置为从1开始的,如果查找失败返回0
select user_id, visit_time, visit_date, visit_cnt from wedw_tmp.tmp_url_info where instr(visit_time,'10')>0;
-
length
统计字符串的长度 length(string a)
select length('abc');
-
trim
将字符串前后的空格去掉,和java中的trim方法一样,
#最后会得到sfssf sdf sdfds select trim(' sfssf sdf sdfds ');
-
upper
字符串中所有的字母转为大写 upper(string a)
select upper(concat_ws('', customer_fname, customer_lname)) as fullname from customers limit 10;
-
lower
字符串中所有的字母转为小写 lower(string a)
-
substr
截取字符串中从指定位置开始,指定长度的子字符串并返回,其中长度可选,默认截取到末尾。
substr(string a, int start, [int length ])
2.2 类型转换函数
cast(expr as type)
, 将 expr 的数据类型转换为 type 类型,如果转换失败,返回null
2.3 聚合函数
聚合函数是在一组多行数据中进行计算并返回单一值的函数。常用的聚合函数如下:
count() sum() max() min() avg()
2.4 数学函数
-
round
round(double a)
返回对a四舍五入的BIGINT值round(double a, int d)
返回对a四舍五入,保留d位小数的值select round(4/3),round(4/3,2);
-
ceil
求不小于给定实数的最小整数;向上取整
ceil(double a), ceiling(double a)
select ceil(4/3),ceiling(4/3);
-
floor
对给定的实数向下取整
floor(double a)
select floor(4/3);
示例:对订单总金额进行四舍五入,要求精度位小数点后两位。
select order_id, round(sum(cast(order_items.order_ite_subtotal as float)),2)
from orders join order_items on orders_id = order_items.order_item_order_id
group by order_id limit 10;
2.5 日期函数
from_unxitime
from_unixtime(bigint unixtime[, string format])
将时间的秒值转换成format格式(format可为“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh”,“yyyy-MM-dd hh:mm”等等)
select from_unixtime(1599898989,'yyyy-MM-dd') as current_time
unix_timestamp
unix_timestamp():获取当前时间戳
unix_timestamp(string date):获取指定时间对应的时间戳
通过该函数结合from_unixtime使用,或者可计算两个时间差等
select
unix_timestamp() as current_timestamp,--获取当前时间戳
unix_timestamp('2020-09-01 12:03:22') as speical_timestamp,--指定时间对于的时间戳
from_unixtime(unix_timestamp(),'yyyy-MM-dd') as current_date --获取当前日期
to_date
to_date(string timestamp)
返回时间字符串的日期部分
--最后得到2020-09-10
select to_date('2020-09-10 10:31:31')
year
year(string date)
返回时间字符串的年份部分
--最后得到2020
select year('2020-09-02')
month
month(string date)
返回时间字符串的月份部分
--最后得到09
select month('2020-09-10')
day
day(string date)
返回时间字符串的天
--最后得到10
select day('2002-09-10')
date_add
date_add(string startdate, int days)
从开始时间startdate加上days
--获取当前时间下未来一周的时间
select date_add(now(),7)
--获取上周的时间
select date_add(now(),-7)
date_sub
date_sub(string startdate, int days)
从开始时间startdate减去days
--获取当前时间下未来一周的时间
select date_sub(now(),-7)
--获取上周的时间
select date_sub(now(),7)
示例:统计月度订单数量
select from_unxitime(unix_timestamp(order_date), "yyyy-MM") as year_month,
count(order_id) from orders
group by from_unxitime(unix_timestamp(order_date), "yyyy-MM")
2.6 条件函数
if
if(boolean testCondition, T valueTrue, T valueFalseOrNull):判断函数,很简单
如果testCondition 为true就返回valueTrue,否则返回valueFalseOrNull
--判断是否为user1用户
select
distinct user_id,
if(user_id='user1',true,false) as flag
from wedw_tmp.tmp_url_info
case when
CASE a WHEN b THEN c [WHEN d THEN e] [ELSE f] END
如果a=b就返回c,a=d就返回e,否则返回f 如CASE 4 WHEN 5 THEN 5 WHEN 4 THEN 4 ELSE 3 END 将返回4
相比if,个人更倾向于使用case when
--仍然以if上面的列子
select
distinct user_id,
case when user_id='user1' then 'true'
when user_id='user2' then 'test'
else 'false' end as flag
from wedw_tmp.tmp_url_info
case when 案例:
示例:根据商品价格将商品分为3个级别:0~100, 100~200及200以上,并分别统计各档商品个数
select level, count(*) from (select *, case when product_price<100 then 1
when product_price between 100 and 200 then 2
else 3 end as level
from products) as a
group by level;
子查询中分组使用 over()窗口对象
-
over使用,按照字段名进行分组
over(partition by 字段名)
-
over对整个表进行分组为一个组
over()