Hive常用函数

于 2021-10-10 21:09:00 发布
阅读量1.8k 收藏 19
点赞数
文章标签： hive sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_58149226/article/details/120368694
版权

1.书写顺序

-select
-from
-join
-on
-where
-group by
-having
-order by
-limit

2.执行顺序

- from  
- where  
- join   
- on  
- group by 
- select   
- having  
- distinct  
- order by 
- limit
- union / union on

3.Hive关系运算符[常用]

!=不等于
<> 不等于
==等等于
=等于
+加
-减
*乘
/除
>=大于等于
<=小于等于

4.数学函数[常用]

4.1 取整函数: round

select round(11.214563);
返回 11 
--四舍五入取整数部分

2. 指定精度取整函数: round
round(double a, int b) 
示例: 
select round(11.256,2);  -- 11.26

4.2 向下取整函数: floor

SELECT floor(10.954) ;
返回 10

4.3 向上取整 ceil / ceiling

select ceil(12.006); / select ceiling(12.006);
返回 13

4.4 取随机数函数: rand

语法: rand(),rand(int seed)

返回值: double

说明:返回一个double型0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列

select rand();
返回: 0.7400880291262728

-- 指定种子 返回的结果一样
select rand(50);
0.7297136425657874

select rand(50);
0.7297136425657874

4.5 幂运算函数: pow

语法: pow(double a, double p), power(double a, double p)

返回值: double

说明:返回a的p次方

select pow(2,5);
返回 32.0

4.6 开平方函数: sqrt

语法: sqrt(double x)

返回值: double

说明:返回x的平方根

select sqrt(4);
返回 2.0

4.7  二进制函数: bin

语法: bin(BIGINT x)

返回值: string

说明:返回x的二进制字符串

select bin(4);
返回 100

4.8 绝对值函数: abs

语法: abs(double x),abs(int x)

返回值: double OR int

说明:返回数值x的绝对值

select abs(-2.0);
返回 2

4.9 判断正负函数:sign

语法: sign(double a)

返回值: double

说明:如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0

select sign(10);
返回  1.0

select sign(-10.0);
返回 -1.0

select sign(0);
返回 0.0

 4.10 相反数函数negative

语法: negative(int x), negative(double x)

返回值: int or double

说明:返回-x or x的相反数

select negative(-5);
返回  5
select negative(5);
返回 -5

4.11 数学pi函数:pi

语法: pi()

返回值: double

说明:数学常数π

select pi();
返回 3.141592653589793

4.12 求N个数最大值函数:greatest

语法:greatest(T v1, T v2, …) N个数的最大值

返回值:T

说明:求最大值

select greatest(1,2,3);
返回 3

4.13 求N个数最小值函数:least

语法:least(T v1, T v2, …)

返回值:T

说明:求最小值

select least(1,2,3);
返回 1

4.14 银行家舍入法函数:bround

语法:bround(double a)

返回值:double

说明:银行家舍入法 四舍六入五成双

select bround(5.5)
返回 6
select bround(4.5)
返回 4

五.日期函数

5.1  UNIX时间戳转日期函数:from_unixtime

语法: from_unixtime(bigint unixtime, [string format])

返回值: string
说明: format格式可以是 “yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh”,“yyyy-MM-dd hh:mm”等

select from_unixtime(1634000000,'yyyy-MM-dd');
返回 2021-10-12

select from_unixtime(1634000000,'yyyy-MM-dd HH:mm:ss')
返回 2021-10-12 00:53:20

5.2  获取当前UNIX时间戳函数:unix_timestamp

语法: unix_timestamp()

返回值: bigint

说明:获得当前时区的UNIX时间戳

select unix_timestamp();
返回 1633679199

SELECT from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:dd');
返回 2021-10-08 15:48:10

5.3  日期转UNIX时间戳函数:unix_timestamp

语法: unix_timestamp(string date)

返回值: bigint

说明:只能将格式为'yyyy-MM-dd HH:mm:ss'的时间字符串转换成时间戳。如果转化失败，则返回0。

SELECT unix_timestamp('2021-10-08 15:50:00');
返回 1633708200

SELECT unix_timestamp('2021-10-08');
返回 0


5.4  指定日期格式 转UNIX时间戳函数:unix_timestamp

语法: unix_timestamp(string date, string pattern)

返回值: bigint

说明:将指定时间字符串格式字符串转换成Unix时间戳。如果转化失败，则返回0

select unix_timestamp('2021-10-08','yyyy-MM-dd');
返回 1633651200


5.5  日期时间转日期函数:to_date

语法: to_date(string timestamp)

返回值: string

说明:返回日期时间字段中的日期部分

select to_date('2021-10-08 15:00:00');
返回 2021-10-08

5.6  日期转年函数: year

语法: year(string date)

返回值: int

说明:返回日期时间字段中的年

select year('2021-10-08 15:00:00');
select year('2021-10-08');
返回 2021

5.7  日期转月函数: month

语法: month(string date)

返回值: int

说明:返回日期时间字段中的月份

select month('2021-10-08 15:00:00');
select month('2021-10-08');
返回 10

5.8  日期转天函数: day

语法: day(string date)

返回值: int

说明:返回日期时间字段中的日期

select day('2021-10-08 15:00:00');
select day('2021-10-08');
返回 8

5.9  日期转小时函数: hour

语法: hour(string date)

返回值: int

说明:返回日期时间字段中的日期,注意格式错误则返回0

select hour('2021-10-08 15:00:00');

返回 15

select hour('2021-10-08');
返回 0

5.10 日期转分钟函数: minute

10. 日期转分钟函数: minute
语法: minute (string date)

返回值: int

说明:返回日期中的分钟。

select minute('2021-10-08 15:10:00');
返回 10

5.11 日期转秒函数: second

语法: second (string date)

返回值: int

说明:返回日期中的秒。

select second(‘2011-12-08 10:03:01’);
返回 1

5.12 日期转周函数:weekofyear

语法: weekofyear (string date)

返回值: int

说明:返回时间字符串位于一年中的第几个周

select weekofyear('2021-10-08 15:00:00');
返回 40

5.13 日期比较函数: datediff

语法: datediff(string enddate, string startdate)

返回值: int

说明:返回结束日期减去开始日期的天数。

select datediff('2021-10-08','2021-10-01');
返回 7

5.14 日期增加函数: date_add

语法: date_add(string startdate, int days)

返回值: string

说明:返回开始日期startdate增加days天后的日期。

select date_add(2021-10-01,7);
返回 2021-10-08

5.15 日期减少函数: date_sub

语法: date_sub(string startdate, int days)

返回值: string

说明: 返回开始日期startdate减少days天后的日期。

select date_sub('2021-10-08',8);
返回 2021-10-01

5.16 当前时间日期函数:current_date

语法:current_date()

返回值:date

说明:返回当前时间日期

select current_date();

返回 2021-10-08

5.17 当前时间日期函数:current_timestamp

语法:current_timestamp()

返回值:timestamp

说明:返回当前时间戳

select current_timestamp();
select last_day(current_timestamp());

返回 2021-10-08 12:25:22.339

5.18 月份增加函数:add_months

语法:add_months(string start_date, int num_months)

返回值:string

说明:返回当前时间下再增加num_months个月的日期

select add_months(‘2021-10-08’,2);
2021-12-08

5.19 最后一天的日期函数:last_day

语法:last_day(string date)

返回值:string

说明:返回这个月的最后一天的日期，忽略时分秒部分（HH:mm:ss）

select last_day(current_date());
select last_day(current_timestamp()); -- 忽略时分秒部分（HH:mm:ss）

返回 2021-10-31

5.20 下一个星期X所对应的日期函数:next_day

语法:next_day(string start_date, string day_of_week)

返回值:string

说明:返回当前时间的下一个星期X所对应的日期 如：next_day(‘2015-01-14’, ‘TU’) 
= 2015-01-20 以2015-01-14为开始时间，其下一个星期二所对应的日期为2015-01-20

select next_day(current_date(),'su');
2021-10-10

5.21 指定格式返回时间函数:date_format

语法:date_format(date/timestamp/string ts, string fmt)

返回值:string

说明:按指定格式返回时间date 如：date_format(“2016-06-22”,“MM-dd”)=06-22

select date_format(current_date(),'MM-dd');

返回 10-08

5.22 当前星期函数:dayofweek

语法:dayofweek(date)

返回值:int

说明:返回日期那天的周几

select dayofweek(current_date());

返回 6

 六、条件函数

6.1 If函数: if

语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)

返回值: T

说明: 当条件testCondition为TRUE时，返回valueTrue；
否则返回valueFalseOrNull（valueTrue，valueFalseOrNull为泛型）
类似于三元表达式

select if(10>5,1,0);
返回 1

select if(10<5,1,0);
返回 0

6.2 空查找函数: nvl

语法: nvl(T value, T default_value)
返回值: T

说明:如果value值为NULL就返回default_value,否则返回value

select nvl(null,1);
返回 1

select nvl(2,1);
返回 2

注意: select nvl('',1); 返回 ''

6.3 非空查找函数: COALESCE

语法: COALESCE(T v1, T v2,…)

返回值: T

说明: 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL

select coalesce(1,2,null,...); 
返回 1
select coalesce(null,null,2,...);
返回 2

select coalesce(null,null,null,...);
返回 null

6.4 条件判断函数：CASE

语法: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END

返回值: T

说明：如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f

select CASE 4 WHEN 5 THEN 5 WHEN 4 THEN 4 ELSE 3 END;
返回 4

6.5 条件判断函数：CASE

语法: CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END

返回值: T

说明：如果a为TRUE,则返回b；如果c为TRUE，则返回d；否则返回e

select
case 
when a > 10  and a < 20 then '10-19岁'
when a >= 20 and a < 30 then '20-30岁'
else '30岁以上'
end as '年龄段'

6.6 空值判断函数：isnull

语法: isnull( a )
返回值:boolean

说明：如果a为null就返回true，否则返回false

select isnull(5);
返回 false
select isnull(null);
返回 true

select * from ads_class
where classid is null;
返回满足条件的结果

6.7 非空值判断函数：isnotnull

语法: isnotnull ( a )

返回值:boolean

说明：如果a为非null就返回true，否则返回false

select isnotnull(5);
返回 true

select * from ads_class
where classid is not null;

返回满足条件的结果

七、聚合函数 [一般会和分组函数或开窗函数使用]

不统计null值
7.1  个数统计函数: count

语法:count(*), count(expr), count(DISTINCT expr[, expr…])

返回值: BIGINT

说明: count(*)统计检索出的行的个数，包括NULL值的行；
count(expr)返回指定字段的非空值的个数；
count(DISTINCTexpr[, expr_.])统计提供非NULL且去重后的expr表达式值的行数

select count(1) from  tb_class;
通常和分组函数一起使用

7.2  总和统计函数: sum

2. 总和统计函数: sum
语法: sum(col), sum(DISTINCT col)

返回值: double

说明: sum(col)统计结果集中col的相加的结果；sum(DISTINCT col)统计结果中col不同值相加的结果

select sum(money) from order

 7.3  平均值统计函数: avg

语法: avg(col), avg(DISTINCT col)

返回值: double

说明: avg(col)统计结果集中col的平均值；avg(DISTINCT col)统计结果中col不同值相加的平均值


7.4  最小值统计函数: min

语法: min(col)

返回值: double

说明:统计结果集中col字段的最小值


7.5  最大值统计函数: max

语法: maxcol)

返回值: double

说明:统计结果集中col字段的最大值


 

 八、分组函数

classnameageevent_daygongzi
1张三152021-05-01100
2李四142021-05-03200
3王五132021-05-05100
1赵六152021-04-30200
2张三172021-06-01300
group by的意思是根据by对数据按照哪个字段进行分组，或者是哪几个字段进行分组。
语法: group by 后边跟分组字段,多个字段可以用逗号隔开

select   字段    from   表名   where    条件     group   by       字段

或者

select   字段    from   表名   group  by    字段    having    过滤条件

注意：对于过滤条件，可以先用where，再用group  by或者是先用group  by，再用having
group by 后不能出现where函数,因为 where 先制行

 8.1 举例:

单个字段进行分组
select class, count(1) as cnt 
from tb_class 
group by class       -- 注意select 后不能出现没有参与分组的字段

多个字段分组[每个班级相同人名的人数]
select class,name,count(1) as cnt 
from tb_class 
group by class,name

8.2 常和聚合函数一起使用 count() , sum() , avg() , max() , min()

1. count() --每个班级的人数
select class,count(1) as cnt from tb_class group by class;
注意: 如果count(distinct 字段) 效率较低,如果数据量很大时,不建议使用
[因为使用count(distinct)语法聚合时reduce会变成一个来完成聚合]
可以使用 group by 加 count 来代替完成

2.sum() -- 每个班级的工资总和
select class,sum(gongzi) as total_gongzi from tb_class group by class;

3.avg() --每个班级的平均年龄
select class, avg(age) from tb_class group by class;

4.max() --每个班级年龄的最大值
select class, max(age) from tb_class group by class;

5.min() --每个班级年龄的最小值
select class, min(age) from tb_class group by class;
写代码当牛马
关注
hive函数
夏革
03-21 239
行拆列 explode explode(ARRAY) 列表中的每个元素生成一行 explode(MAP) map中每个key-value对，生成一行，key为一列，value为一列 ---word count例子 select word,count(1) num from (select explode(array_x) word from table_x) a group by ...
Hive常用函数大全
最新发布
王璐的博客
05-29 766
Hive常用函数大全
Hive 函数
weixin_34261415的博客
09-21 95
1． 内置运算符在Hive有四种类型的运算符：· 关系运算符· 算术运算符· 逻辑运算符· 复杂运算内容较多，见《Hive官方文档》或者《hive常用运算和函数.doc》2． 内置函数https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF测试各种内置函数的快捷方法：创建一个dual表create table dual(...
[一起学Hive]之二–Hive函数大全-完整版
lxw1234.com的博客
06-24 919
Hive函数大全–完整版 现在虽然有很多SQL ON Hadoop的解决方案，像Spark SQL、Impala、Presto等等，但就目前来看，在基于Hadoop的大数据分析平台、数据仓库中，Hive仍然是不可替代的角色。尽管它的相应延迟大，尽管它启动MapReduce的时间相当长，但是它太方便、功能太强大了，做离线批量计算、ad-hoc查询甚至是实现数据挖掘算法，而且，和HBase、Spar
Hive函数
ShaneHolmes
03-12 235
Hive专栏 上一篇 主目录 下一篇 目录1 内置函数2 自定义函数 【前言】 1 内置函数 查看： show functions; //查看 desc function abs; //显示函数的详细信息： desc function extended concat;...
Hive常用函数大全.pdf
04-14
### Hive常用函数详解 #### 一、关系运算 在Hive中，关系运算是非常基础且重要的部分，它包括但不限于等值比较、不等值比较、大小比较等。这些操作对于进行数据筛选、条件判断等都非常关键。 ##### 1. 等值比较 ...
hive常用函数
04-17
在本文中，我们将深入探讨Hive的常用函数，包括时间函数、类型转换函数、用户自定义函数（UDF）以及用户自定义聚合函数（UDAF）等。 关系运算符是Hive中的基础函数，用于比较两个值是否相等。例如，A=B用于判断A...
HIVE函数
白飞飞_Alan
10-09 515
1、日期函数 返回值类型 名称 描述 string from_unixtime(int unixtime) 将时间戳（unix epoch秒数）转换为日期时间字符串，例如from_unixtime(0)="1970-01-01 00:00:00" bigint unix_tim
Hive-函数
张波的技术积累
02-03 643
1：使用show functions来显示所有的函数，       使用类似describe function   substr的命令来查看函数substr的定义       2：常见函数                           执行命令select array(1, 2, 3, 4) from dual；生成一行结果[1, 2, 3, 4]           
hive的函数
数据丁的博客
11-19 261
hive的函数 按照类型 udf 一对一 udaf 多对一 udtf 一对多 explode炸裂 按照来源 内置函数 显示内置函数：show functions 查看函数详细信息：desc function 函数名 查看函数详细的描述信息：desc function extended 函数名 常用的内置函数： 集合函数（收集函数）： map,array select array_co...
Hive中的函数
Intelligebce的博客
03-11 593
一、parse_url ：输入URL，抽取URL中的一部分内容 parse_url(url, partToExtract[, key]) partToExtract的选项包含[HOST,PATH,QUERY,REF,PROTOCOL,FILE,AUTHORITY,USERINFO]。 select parse_url('https://m.weibo.cn/u/1790868495', '...
Hive常用函数分类及应用场景总结
在Hive中，有许多常用的函数可以帮助用户进行数据处理和分析。 首先，在Hive命令行接口中，可以通过"SHOW FUNCTIONS"命令查看当前会话可用的函数数量，通过"DESC FUNCTION"命令可以查看函数的描述信息，以及使用...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值