二百三十九、Hive——Hive函数全篇

2024-06-05 08:52

本文主要是介绍二百三十九、Hive——Hive函数全篇,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

--创建测试数据库test
show databases ;
create database if not exists test;
use test;

一、关系运算

1、等值比较:=

select 1  where 1 = 1;   --1
select 1  where 0 = 1;   --NULL

2、不等值比较:<>

select 1 where 1 <> 2;   --1
select 1 where 2 <> 2;   --NULL

3、小于比较:<

select 1 where 1 < 2;   --1
select 1 where 3 < 2;   --NULL

4、小于等于比较:<=

select 1 where 1 <= 2;   --1
select 1 where 3 <= 2;   --NULL

5、大于比较:>

select 1 where 3 > 2;   --1
select 1 where 1 > 2;   --NULL

6、大于等于比较:>=

select 1 where 3 >= 2;   --1
select 1 where 1 >= 2;   --NULL

7、空值判断:IS NULL

select 1 where null is null ;   --1
select 1 where 1    is null ;   --NULL

8、非空判断:IS NOT NULL

select 1 where 1    is not null ;   --1
select 1 where null is not null ;   --NULL

9、LIKE 比较:LIKE

--字符 ”_” 表示任意单个字符、字符 ”%” 表示任意数量的字符
select 1 where 'footb' like 'foot_';         --1
select 1 where 'football' like 'foot____';   --1
select 1 where 'football' like 'foot%';      --1
select 1 where 'f' like 'foot_';             --NULL
--注意:否定比较时候用 NOT A LIKE B
select 1 where not 'f' like 'foot_';         --1

10、JAVA的LIKE 比较:RLIKE

select 1 where 'football' rlike '^f.*l$';   --1

11、正则匹配:REGEXP

select 1 where 'football' regexp '^f.*l$';   --1

二、数学运算

1、加法操作:+

select 1 + 2;   --3

2、减法操作:-

select 1 - 0.5;   --0.5

3、乘法操作:*

select 1 * 2;   --2

4、除法操作:/

select 1 / 2;   --0.5

5、取余操作:%

--通过 round 指定精度
select 5 % 2;   --1
select 2.3456 % 2;   --0.3456
select round(2.3456 % 2,2);   --0.35

6、位与操作:&

select 4 & 8;   --0

7、位或操作:|

select 4 | 8;   --12

8、位异或操作:^

select 4 ^ 8;   --12

9、按位取反操作:~

select ~ 4;   --  -5

三、逻辑运算

1、逻辑与操作:AND

select 1  where 1 = 1 and 2 = 2;   --1

2、逻辑与操作:OR

select 1  where 1 = 1 or 1 = 2;    --1

3、逻辑非操作:NOT

select 1 where not 1 = 2;   --1


四、条件运算

1、IF 函数:IF

select if(1=2,100,200);   --200

2、非空查找函数:COALESCE

--返回参数中的第一个非空值;如果所有值都为 NULL,那么返回NULL。
select coalesce(null,'100',null,'50');   --100

3、条件判断函数:CASE

--3.1 描述:如果 a 等于 b,那么返回 c;如果 a 等于 d,那么返回 e;否则返回 f。
select case 100 when 50 then 'tom' when 100 then 'jack' else 'ketty' end ;   --jack
--3.2 描述:如果 a 为 TRUE,则返回 b;如果 c 为 TRUE,则返回 d;否则返回 e。
select case when 1 = 2 then 'tom' when 1 = 1 then 'jack' else 'ketty' end ;  --jack

五、数值计算

1、近似函数:round

--说明:返回 double 类型的整数值部分 (遵循四舍五入)
select round(3.1415926);  --3

2、指定精度近似函数:round

--说明:返回指定精度 d 的 double类型
select round(3.1415926,4);  --3.1416

3、向下取整函数:floor

select floor(3.1415926);  --3

4、向上取整函数:ceil

select ceil(3.1415926);  --4

5、向上取整函数:ceiling

select ceiling(3.1415926);  --4

6、取随机函数:rand

--说明:返回一个 0 到 1 范围内的随机数。如果指定种子 seed,则会得到一个稳定的随机数序列。
select rand();  --0.766571624011412、再执行就变成0.9370576946962733
select rand(10);--0.7304302967434272、再执行还是0.7304302967434272

7、自然指数函数:exp

--说明:返回自然对数 e 的 a 次方
select exp(2);  --7.38905609893065

8、以 10 为底的对数函数:log10

--说明:返回以 10 为底的 a 的对数
select log10(100);  --2

9、以 2 为底的对数函数:log2

select log2(8);  --3

10、对数函数:log

select log(2,1024);  --10

11、幂运算函数:pow

select pow(2,6);  --64

12、幂运算函数:power

--说明:返回以 a 的 p 次幂,与 pow 功能相同。
select power(2,7);  --128

13、平方根函数:sqrt

--说明:返回 a 的 平方根
select sqrt(64);  --8

14、二进制函数:bin

select bin(5);  --101

15、十六进制函数:hex

select hex(21);  --15
select hex('abc');  --616263

16、反转十六进制函数:unhex

select unhex(616263);  --'616263'
select unhex('616263');--'616263'

17、进制转换函数:conv

--说明:将数值 num 从 from_base 进制转化到 to_base 进制
select conv(17,10,2);  --10001
select conv(17,10,16); --11

18、绝对值函数:abs

select abs(-1);   --1
select abs(3.14); --3.14

19、正取余函数:pmod

--说明:返回正的 a 除以 b 的余数
select pmod(9,4);   --1

20、正弦函数:sin

select sin(10);   --  -0.5440211108893698

21、反正弦函数:asin

select asin(-0.5440211108893698);   --  -0.5752220392306202

22、余弦函数:cos

select cos(2);   --  -0.4161468365471424

23、反余弦函数:acos

select acos(-0.4161468365471424);   --2

24、positive 函数:positive

--说明:返回 a 自身
select positive(-1);   --   -1

25、negative 函数:negative

--说明:返回 a 的相反数
select negative(-1);   --   1

六、日期函数

1、UNIX 时间戳转日期函数:fom_unixtime

--说明:转化 UNIX 时间戳(从1970-01-01 00:00:00 UTC 到指定时间的秒数)到当前时区的时间格式。
select from_unixtime(1717166095);   -- 2024-05-31 14:34:55

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

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

3、日期转 UNIX 时间戳函数:unix_timestamp

select unix_timestamp('2024-05-31 14:34:55');   --1717166095

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

select unix_timestamp('2024-05-31 14:34:55','yyyy-MM-dd HH:mm:ss') ;   --1717166095

5、日期时间转日期函数:to_date

select to_date('2024-05-31 14:34:55');   --2024-05-31

6、日期转年函数:year

select year('2024-05-31 14:34:55');   --2024

7、日期转月函数:month

select month('2024-05-31 14:34:55');   --5

8、日期转天函数:day

select day('2024-05-31 14:34:55');   --31

9、日期转小时函数:hour

select hour('2024-05-31 14:34:55');   --14

10、日期转分钟函数:minute

select minute('2024-05-31 14:34:55'); --34

11、日期转秒函数:second

select second('2024-05-31 14:34:55'); --55

12、日期转周函数:weekofyear

select weekofyear('2024-05-31 14:34:55');  --22

13、日期比较函数:datediff

select datediff('2024-05-31','2024-01-01');  --151

14、日期增加函数:date_add

select date_add('2024-05-01',30);  --2024-05-31

15、日期减少函数:date_sub

select date_sub('2024-05-31',30);  --2024-05-01

七、字符串函数

1、字符串长度函数:length

select length('zhangsan');  --8

2、字符串反转函数:reverse

select reverse('zhangsan');  --gnijgnaw

3、字符串连接函数:concat

select concat('zhan','gs','an');  --zhangsan

4、带分隔符字符串连接函数:concat_ws

select concat_ws(',','zhan','gs','an');  --zhan,gs,an

5、字符串截取函数:substr | substring

select substr('zhangsan',5);       --gsng
select substr('zhangsan',2,3);     --ang
select substring('zhangsan',2,4);  --angj

7、字符串转大写函数:upper | ucase

select upper('apple');  --APPLE

8、字符串转小写函数:lower | lcase

select lower('APPLE');  --apple

9、去空格函数:trim

select trim('   zhangsan  ');  --zhangsan

10、左边去空格函数:ltrim

select ltrim('   zhangsan  ');  --zhangsan

11、右边去空格函数:rtrim

select rtrim('   zhangsan  ');  --   zhangsan

12、正则表达式替换函数:regexp_replace

--语法:regexp_replace(string A, string B, string C)
--描述:将字符串 A 中的符合 Java 正则表达式 B 的部分替换为 C
select regexp_replace('zhangsan', 'ang|ing', '');  --wj

13、正则表达式解析函数:regexp_extract

--语法:regexp_extract(string subject, string pattern, int index)
--描述:将字符串 subject 按照 pattern 正则表达式的规则拆分,返回 index 指定的字符。
select regexp_extract('football', 'foo(.*?)(ball)', 1);  --t

14、URL 解析函数:parse_url

--描述:返回 URL 中指定的部分。partToExtract 的有效值为:HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
select parse_url('https://www.baidu.com/path/p.php?k1=v1&k2=v2#ref1', 'HOST');  --www.baidu.com
select parse_url('https://www.baidu.com/path/p.php?k1=v1&k2=v2#ref1', 'QUERY', 'k1');  --v1

15、JSON 解析函数:get_json_object

select get_json_object('{"name":"wj","age":"29"}', '$.name');  --wj

16、空字符串函数:space

--语法:space(int n)    描述:返回长度为 n 的字符串
select space(3);  --

17、重复字符串函数:repeat

select repeat('abc',3);  --abcabcabc

18、首字符 ASCII 函数:ascii

--描述:返回字符串 str 第一个字符的 ascii 码
select ascii('abc');  --97

19、左补足函数:lpad

--语法:lpad(string str, int len, string pad)   描述:将 str 用 pad 进行左补足到 len 位
select lpad('abc',10,'#@');  --#@#@#@#abc

20、右补足函数:rpad

select rpad('abc',10,'#@');  --abc#@#@#@#

21、分割字符串函数:split

--语法:split(string str, string pat)
--描述:按照 pat 字符串分割 str,会返回分割后的字符串数组。
select split('ab,cd,ef',',');   --["ab","cd","ef"]

22、集合查找函数:find_in_set

--语法:find_in_set(string str, string strList)
--描述:返回 str 在 strlist 第一次出现的位置,strlist 是用逗号分割的字符串。如果没有找到该 str 字符,则返回 0。
select find_in_set('cd','{ab,cd,ef}');   --2

八、窗口函数

create table t_window(
name        string,
orderdate   date,
cost        float
)
row format delimited fields terminated by ','
lines terminated by '\n';

--表数据
--[root@hurys22 opt]# cat t_window.txt
--jack,2015-01-01,10
--tony,2015-01-02,15
--jack,2015-02-03,23
--tony,2015-01-04,29
--jack,2015-01-05,46
--jack,2015-04-06,42
--tony,2015-01-07,50
--jack,2015-01-08,55
--mart,2015-04-08,62
--mart,2015-04-09,68
--neil,2015-05-10,12
--mart,2015-04-11,75
--neil,2015-06-12,80
--mart,2015-04-13,94

--加载本地数据
load data local inpath '/opt/t_window.txt' into table t_window;
--查看表数据
select * from t_window;

1、统计函数:count

select count(name) from t_window;   --14
select count(distinct name) from t_window;  --4

2、求和函数:sum

select sum(cost) from t_window;     --661

3、平均值函数:avg

select avg(cost) from t_window;     --47.214285714285715

4、最小值函数:min

select min(cost) from t_window;     --10

5、最大值函数:max

select max(cost) from t_window;     --94

6、总体方差函数:var_pop

select var_pop(cost) from t_window;     --668.8826530612243
select round(var_pop(cost),2) from t_window;     --668.88

7、样本方差函数:var_samp

select var_samp(cost) from t_window;     --720.3351648351647

8、总体标准差函数:stddev_pop

select stddev_pop(cost) from t_window;    --25.862765765888696

9、样本标准差函数:stddev_samp

select stddev_samp(cost) from t_window;   --26.839060431303565

10、百分位函数:percentile

描述:求准确的 p 对应的百分位数,p 必须介于 0 和 1 之间,返回类型为 double,但是 col 字段目前只支持整数,不支持浮点数类型。
select percentile(cast(cost as int), 0.5) from t_window;   --48

11、百分位函数:percentile

select percentile(cast(cost as int),  array(0.2, 0.3, 0.5)) from t_window;      --[19.8,28.4,48.0]

12、近似百分位函数:percentile_approx

select percentile_approx(cost,0.5, 5) from t_window;    --37.125

13、近似百分位函数:percentile_approx

select percentile_approx(cost,array(0.2, 0.3, 0.5), 5) from t_window;   --[12.333333333333334,20.533333333333335,37.125]

14、直方图:histogram_numeric

select histogram_numeric(cost,100) from t_window;   --[{"x":10.0,"y":1.0},{"x":12.0,"y":1.0},{"x":15.0,"y":1.0},{"x":23.0,"y":1.0},{"x":29.0,"y":1.0},{"x":42.0,"y":1.0},{"x":46.0,"y":1.0},{"x":50.0,"y":1.0},{"x":55.0,"y":1.0},{"x":62.0,"y":1.0},{"x":68.0,"y":1.0},{"x":75.0,"y":1.0},{"x":80.0,"y":1.0},{"x":94.0,"y":1.0}]


九、聚合函数+over

1、partition by子句

select
name, orderdate, cost,sum(cost) over (partition by month(orderdate))
from t_window
;
--mart,2015-04-13,94,341

2、order by子句、 window子句

--unbounded preceding 起始行、unbounded following 结尾行、
--current row 当前行、1 preceding 前1行、1 following 后1行
select
name, orderdate, cost,
sum(cost) over() as sample1,--所有的cost列相加
sum(cost) over(partition by name) as sample2, --将按名字分组,然后把每一组的cost列相加
sum(cost) over(partition by name order by orderdate) as sample3, --将按名字分组,然后把每一组的cost列相加,根据日期排序
sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) as sample4,
sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row) as sample5,
sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following) as sample6,
sum(cost) over(partition by name order by orderdate rows between current row and unbounded following) as sample7
from t_window;
--mart,2015-04-08,62,661,299,62,62,62,130,299
--mart,2015-04-09,68,661,299,130,130,130,205,237
--mart,2015-04-11,75,661,299,205,205,143,237,169
--mart,2015-04-13,94,661,299,299,299,169,169,94

3、ntile子句

--用于将分组数据按照顺序切分成n片,返回当前切片值
select
name, orderdate, cost,
        ntile(3) over () as sample1
from t_window;
--mart,2015-04-13,94,1
--neil,2015-06-12,80,1

select
name, orderdate, cost,
       ntile(3) over () as sample1,
       ntile(3) over (partition by name) as sample2,
       ntile(2) over (partition by month(orderdate)) as sample3,
       ntile(3) over (partition by name order by cost desc ) as smaple4
from t_window;
--jack,2015-01-08,55,2,3,2,1
--jack,2015-01-05,46,2,2,2,1
--jack,2015-04-06,42,2,2,2,2
--jack,2015-02-03,23,3,1,1,2
--jack,2015-01-01,10,3,1,1,3

4、序号函数row_number rank dense_rank子句

select
name, orderdate, cost,
       row_number() over () as t1,
       row_number() over (order by name) as t2,
       rank() over (order by name) as t3,
       dense_rank() over (order by name) as t4
from t_window
;
--jack,2015-01-01,10,14,1,1,1
--jack,2015-02-03,23,12,2,1,1
--jack,2015-01-05,46,10,3,1,1
--jack,2015-04-06,42,9,4,1,1
--jack,2015-01-08,55,7,5,1,1

5、前后函数LAG和LEAD函数

--返回上下数据行的数据
select
name, orderdate, cost,
       lag(orderdate,1) over (partition by name order by orderdate) as sample1,
       lag(orderdate,1,'1972-01-01') over (partition by name order by orderdate) as sample2,
       lead(orderdate,1,'3000-01-01') over (partition by name order by orderdate) as sample3
from t_window
;
--jack,2015-01-01,10,NULL,1972-01-01,2015-01-05
--jack,2015-01-05,46,2015-01-01,2015-01-01,2015-01-08
--jack,2015-01-08,55,2015-01-05,2015-01-05,2015-02-03
--jack,2015-02-03,23,2015-01-08,2015-01-08,2015-04-06
--jack,2015-04-06,42,2015-02-03,2015-02-03,3000-01-01

6、首尾函数first_value和last_value

--first_value取分组内排序后,截止到当前行,第一个值
--last_value取分组内排序后,截止到当前行,最后一个值
select
name, orderdate, cost,
       first_value(orderdate) over (partition by name order by orderdate) as time1,
       last_value(orderdate) over (partition by name order by orderdate) as time2
from t_window
;
--jack,2015-01-01,10,2015-01-01,2015-01-01
--jack,2015-01-05,46,2015-01-01,2015-01-05
--jack,2015-01-08,55,2015-01-01,2015-01-08
--jack,2015-02-03,23,2015-01-01,2015-02-03
--jack,2015-04-06,42,2015-01-01,2015-04-06

7、分布函数:percent_rank() 和  cume_dist()

--percent_rank() 等级值百分比  某个数值在数据集中的排位作为数据集的百分比值返回,此处的百分比值的范围为 0 到 1。
                --此函数可用于计算值在数据集内的相对位置。如班级成绩为例,返回的百分数30%表示某个分数排在班级总分排名的前30%。
--cume_dist()  累计分布值     如果按升序排列,则统计:小于等于当前值的行数/总行数。
                --如果是降序排列,则统计:大于等于当前值的行数/总行数。
select
name, orderdate, cost,
       rank() over (partition by name order by cost desc ) as r1,
       percent_rank() over (partition by name order by cost desc ) as r2,
       cume_dist() over (partition by name order by cost desc) as r3
from t_window
;
--jack,2015-01-08,55,1,0,0.2
--jack,2015-01-05,46,2,0.25,0.4
--jack,2015-04-06,42,3,0.5,0.6
--jack,2015-02-03,23,4,0.75,0.8
--jack,2015-01-01,10,5,1,1
--mart,2015-04-13,94,1,0,0.25
--mart,2015-04-11,75,2,0.3333333333333333,0.5
--mart,2015-04-09,68,3,0.6666666666666666,0.75
--mart,2015-04-08,62,4,1,1

这篇关于二百三十九、Hive——Hive函数全篇的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1032628

相关文章

C/C++错误信息处理的常见方法及函数

《C/C++错误信息处理的常见方法及函数》C/C++是两种广泛使用的编程语言,特别是在系统编程、嵌入式开发以及高性能计算领域,:本文主要介绍C/C++错误信息处理的常见方法及函数,文中通过代码介绍... 目录前言1. errno 和 perror()示例:2. strerror()示例:3. perror(

Kotlin 作用域函数apply、let、run、with、also使用指南

《Kotlin作用域函数apply、let、run、with、also使用指南》在Kotlin开发中,作用域函数(ScopeFunctions)是一组能让代码更简洁、更函数式的高阶函数,本文将... 目录一、引言:为什么需要作用域函数?二、作用域函China编程数详解1. apply:对象配置的 “流式构建器”最

Android Kotlin 高阶函数详解及其在协程中的应用小结

《AndroidKotlin高阶函数详解及其在协程中的应用小结》高阶函数是Kotlin中的一个重要特性,它能够将函数作为一等公民(First-ClassCitizen),使得代码更加简洁、灵活和可... 目录1. 引言2. 什么是高阶函数?3. 高阶函数的基础用法3.1 传递函数作为参数3.2 Lambda

C++中::SHCreateDirectoryEx函数使用方法

《C++中::SHCreateDirectoryEx函数使用方法》::SHCreateDirectoryEx用于创建多级目录,类似于mkdir-p命令,本文主要介绍了C++中::SHCreateDir... 目录1. 函数原型与依赖项2. 基本使用示例示例 1:创建单层目录示例 2:创建多级目录3. 关键注

C++中函数模板与类模板的简单使用及区别介绍

《C++中函数模板与类模板的简单使用及区别介绍》这篇文章介绍了C++中的模板机制,包括函数模板和类模板的概念、语法和实际应用,函数模板通过类型参数实现泛型操作,而类模板允许创建可处理多种数据类型的类,... 目录一、函数模板定义语法真实示例二、类模板三、关键区别四、注意事项 ‌在C++中,模板是实现泛型编程

kotlin的函数forEach示例详解

《kotlin的函数forEach示例详解》在Kotlin中,forEach是一个高阶函数,用于遍历集合中的每个元素并对其执行指定的操作,它的核心特点是简洁、函数式,适用于需要遍历集合且无需返回值的场... 目录一、基本用法1️⃣ 遍历集合2️⃣ 遍历数组3️⃣ 遍历 Map二、与 for 循环的区别三、高

C语言字符函数和字符串函数示例详解

《C语言字符函数和字符串函数示例详解》本文详细介绍了C语言中字符分类函数、字符转换函数及字符串操作函数的使用方法,并通过示例代码展示了如何实现这些功能,通过这些内容,读者可以深入理解并掌握C语言中的字... 目录一、字符分类函数二、字符转换函数三、strlen的使用和模拟实现3.1strlen函数3.2st

MySQL中COALESCE函数示例详解

《MySQL中COALESCE函数示例详解》COALESCE是一个功能强大且常用的SQL函数,主要用来处理NULL值和实现灵活的值选择策略,能够使查询逻辑更清晰、简洁,:本文主要介绍MySQL中C... 目录语法示例1. 替换 NULL 值2. 用于字段默认值3. 多列优先级4. 结合聚合函数注意事项总结C

Java8需要知道的4个函数式接口简单教程

《Java8需要知道的4个函数式接口简单教程》:本文主要介绍Java8中引入的函数式接口,包括Consumer、Supplier、Predicate和Function,以及它们的用法和特点,文中... 目录什么是函数是接口?Consumer接口定义核心特点注意事项常见用法1.基本用法2.结合andThen链

MySQL 日期时间格式化函数 DATE_FORMAT() 的使用示例详解

《MySQL日期时间格式化函数DATE_FORMAT()的使用示例详解》`DATE_FORMAT()`是MySQL中用于格式化日期时间的函数,本文详细介绍了其语法、格式化字符串的含义以及常见日期... 目录一、DATE_FORMAT()语法二、格式化字符串详解三、常见日期时间格式组合四、业务场景五、总结一、