Hive之函数解析

1.1 查看系统自带的函数

-- 查看系统自带函数
show functions;
-- 查看自带函数的用法
desc/describe function 函数名;
-- 查看自带函数的具体用法
desc/describe function extended 函数;

1.2 窗口函数

1.2.1 相关函数说明

范围通过rows between .... and ....指定

1.2.2 数据准备

jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

1.2.3 需求

1、查询在2017年4月份购买过的顾客及总人数

select name,count(*) over() from business where substring(orderdate, 1, 7) ='2017-04' group by name; 

2、查询顾客的购买明细及月购买总额

-- hive语句
select name, orderdate, cost, sum(cost) over(partition by name,month(orderdate)) from business;
-- presto语句
select name, orderdate, cost, sum(cost) over(partition by name,substring(orderdate,1,7)) 
from hive.ceshi.business;

3、上述的场景，将每个顾客的cost按照日期进行累加

-- hive语句
SELECT name,orderdate,cost,
-- 所有行相加
sum(cost) OVER() AS sample1,
-- 按name分组,组内数据相加
sum(cost) OVER(PARTITION BY name) AS sample2,
-- 按name分组,组内数据累加
sum(cost) OVER(PARTITION BY name ORDER BY orderdate) AS sample3,
-- 和sample3一样,由起点到当前行的聚合
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 ceshi.business;

-- presto语句
select name,orderdate,cost, sum(cost) over(partition by name order by orderdate) as add_sum 
from hive.ceshi.business order by name,orderdate;

rows必须跟在order by 子句之后,对排序的结果进行限制,使用固定的行数来限制分区中的数据行数量

4、查询每个顾客上次的购买时间

-- hive语句
SELECT name, orderdate,lag(orderdate,1) over(PARTITION BY name ORDER BY orderdate) FROM ceshi.business;
-- presto语句
SELECT name, orderdate,lag(orderdate,1) over(PARTITION BY name ORDER BY orderdate) FROM hive.ceshi.business order by name,orderdate;

5、查询前20％时间的订单信息

select name, orderdate, cost
from (select name, orderdate, cost,
             ntile(5) over(order by orderdate) as group_id
      from hive.ceshi.business) as t1
where t1.group_id = 1;

6、创建本地business.txt文件

7、创建hive表并导入数据

-- 创建表
create table business(name string,orderdate string,cost int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
-- 导入数据
load data local in path "本地文件路径" into table business;

1.3 排序函数

（rank、dense_rank、row_number）

1.3.1 函数说明

RANK() 排序相同时会重复，总数不会变

DENSE_RANK() 排序相同时会重复，总数会减少

ROW_NUMBER() 会根据顺序计算

1.3.2 数据准备

孙悟空,语文,87
孙悟空,数学,95
孙悟空,英语,68
大海,语文,94
大海,数学,56
大海,英语,84
宋宋,语文,64
宋宋,数学,86
宋宋,英语,84
婷婷,语文,65
婷婷,数学,85
婷婷,英语,78

1.3.4 需求

计算每门学科成绩排名

select name,subject,score,
rank() over(partition by subject order by score desc) as rp,
dense_rank() over(partition by subject order by score desc) as drp,
row_number() over(partition by subject order by score desc) as rmp 
from score;

1.3.5 创建本地score.txt

1.3.6 创建hive表

create table ceshi.score(name string,subject string, score int) 
row format delimited fields terminated by ",";
load data local inpath '本地文件路径' into table ceshi.score;

1.4 常用日期函数

sparkSQL要想转时间戳或者其他时间类型需要先设置参数

set spark.sql.legacy.timeParserPolicy=LEGACY;

1、unix_timestamp

unix_timestamp([timeExp[, fmt]]) - Returns the UNIX timestamp of current or specified time.

返回当前或指定时间的时间戳

Arguments:

• timeExp - A date/timestamp or string. If not provided, this defaults to current time.
• fmt - Date/time format pattern to follow. Ignored if timeExp is not a string. Default value is "yyyy-MM-dd HH:mm:ss". See Datetime Patterns for valid date and time format patterns.

Examples:

> SELECT unix_timestamp();
 1476884637
> SELECT unix_timestamp('2016-04-08', 'yyyy-MM-dd');
 1460041200

Since: 1.5.0

2、from_unixtime

from_unixtime(unix_time[, fmt]) - Returns unix_time in the specified fmt.

在指定的 fmt 中返回 unix_time。

Arguments:

• unix_time - UNIX Timestamp to be converted to the provided format.
• fmt - Date/time format pattern to follow. See Datetime Patterns for valid date and time format patterns. The 'yyyy-MM-dd HH:mm:ss' pattern is used if omitted.

Examples:

SELECT from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
SELECT from_unixtime(1603843200, 'yyyy-MM-dd HH:mm:ss');
-- 1969-12-31 16:00:00

SELECT from_unixtime(0);
-- 1969-12-31 16:00:00

Since: 1.5.0

将时间戳转为日期格式

select from_unixtime(1603843200);
-- 后边可以增加要转换成的时间类型
SELECT from_unixtime(1603843200, 'yyyy-MM-dd HH:mm:ss');

3、current_date

当前返回当前年月日

select current_date;
-- 输出

4、current_timestamp

当前的日期加时间

select current_timestamp;

5、to_date

抽取日期部分

select to_date('2020-10-28 12:12:12');

6、year

获取年

select year('2020-10-28 12:12:12');

7、month

获取月

select month('2020-10-28 12:12:12');

8、day

获取日

select day('2020-10-28 12:12:12');

9、hour

获取小时

select hour('2020-10-28 12:12:12');

10、minute

获取分

select minute('2020-10-28 12:12:12');

11、second

获取秒

select second('2020-10-28 12:12:12');

12、weekofyear

当前时间是一年中的第几周

select weekofyear('2020-10-28 12:12:12');

13、dayofmonth

返回今天是当前月的第几天

select dayofmonth('2020-10-28 12:12:12');

14、months_between

两个日期间的月份

select months_between('2020-04-01','2020-10-28');

15、add_months

日期加减月

select add_months('2020-10-28',-3);

16、datediff

两个日期相差的天数

select datediff('2020-11-04','2020-10-28');

17、date_add

日期加天数

select date_add('2020-10-28',4);

18、date_sub

日期减天数

select date_sub('2020-10-28',-4);

19、last_day

日期的当月的最后一天

select last_day('2020-02-30');

20、date_format

格式化日期

select date_format('2020-10-28 12:12:12','yyyy/MM/dd HH:mm:ss');

21、next_day

-- 周指标相关,获取str下周一日期
next_day(to_date(str),'MO')

22、trunc

-- 返回日期最开始年份或月份。string1可为年(YYYY/YY/YEAR)或月 (MONTH/MON/MM)。
select trunc('2019-03-06','MM');
输出：'2019-03-01'
select trunc('2019-03-06','YYYY');
输出：'2019-01-01' 

23、日期之间切换

思想：先转换成时间戳，再由时间戳转换为对应格式。
--20171205转成2017-12-05 
select from_unixtime(unix_timestamp('20171205','yyyymmdd'),'yyyy-mm-dd') from dual;

--2017-12-05转成20171205
select from_unixtime(unix_timestamp('2017-12-05','yyyy-mm-dd'),'yyyymmdd') from dual;

24、取最近30天数据

datediff(CURRENT_TIMESTAMP ,gmt_create)<=30 

25、两个日期相差多少小时

select (unix_timestamp('2018-05-25 12:03:55') - unix_timestamp('2018-05-25 11:03:55'))/3600
-- 输出：1

26、两个日期相差多少分钟

select (unix_timestamp('2018-05-25 12:03:55') - unix_timestamp('2018-05-25 11:03:55'))/60

-- 输出：60

27、计算某天属于星期几

SELECT IF(pmod(datediff('2018-05-20', '1920-01-01') - 3, 7)='0', 7, pmod(datediff('2018-05-20', '1920-01-01') - 3, 7)) 
-- 输出：7

28、add_month

SELECT add_months('2016-08-31', 1);
-- 2016-09-30

29、dayofweek

-- 0对应的是周日，以此类推
SELECT dayofweek('2009-07-04');
-- 输出：7

30、dayofyear

返回今天是本年的第几天

SELECT dayofyear('2022-05-20');
-- 输出：140

31、make_date

以年、月、日字段创建日期

SELECT make_date(2013, 7, 15);
-- 输出：2013-07-15

32、now

spark的用法

返回当前年月日时分秒

select now();
-- 输出：2022-05-27 18:32:54.798000000

1.5 常用数学函数

1、round/bround/format_number

四舍五入

select round(1234.5678, 2);
-- 输出：1234.57
select bround(1234.5678, 2);
-- 输出：1234.57
select format_number(1234.5678, 2);
-- 输出：1,234.57
select format_number(1234.5678, '######.##');
-- 输出：1234.57

2、ceil/ceiling

向上取整

select ceil(3.14);
select ceil(3.54);

3、floor

向下取整

select floor(3.14);
select floor(3.54);

4、rand

排序函数

-- 取随机数函数 : rand
-- 语法: rand(),rand(int seed)
-- 返回值: double
-- 说明: 返回一个0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列
select rand(); 
-- 输出：0.5577432776034763

5、abs

绝对值函数

-- 绝对值函数 : abs
-- 语法: abs(double a) abs(int a)
-- 返回值: double int
-- 说明: 返回数值a的绝对值
select abs(‐3.9) from dual; 
-- 输出：3.9
select abs(10.9); 
-- 输出：10.9

6、pmod

-- (pmod(int a, int b))返回a除b的余数的绝对值。
select datediff('2018-01-02','2018-01-01')   
-- 输出：1
select pmod(datediff('2018-01-02','2018-01-01') + 1,7) 
-- 输出2

7、pow

幂运算函数

-- 语法: pow(double a, double p)
-- 返回值: double
-- 说明: 返回a的p次幂
select pow(2,4) ;
-- 16.0

8、sqrt

开平方根函数

-- 语法: sqrt(double a)
-- 返回值: double
-- 说明: 返回a的平方根
select sqrt(16) ;
-- 4.0

9、log10

对数函数

-- 语法: log10(double a)
-- 返回值: double
-- 说明: 返回以10为底的a的对数
select log10(100) ;
-- 2.0

10、exp

自然指数函数

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

12、cbrt

返回传入参数的立方根

select cbrt(8);
-- 输出：2

13、greatest

返回传入参数的最大值

SELECT greatest(10, 9, 2, 4, 3);
-- 输出：10

14、hypot

传入两个参数，返回多个参数的乘方之和的开方

select hypot(3,4);
-- 输出：5
-- 等价于
select sqrt(3*3+4*4);

14、least

返回传入参数的最小值

SELECT least(10, 9, 2, 4, 3);
-- 输出：2

15、avg/mean

求平均值

SELECT avg(col) FROM VALUES (1), (2), (3) AS tab(col);
-- 输出：2.0
SELECT mean(col) FROM VALUES (1), (2), (3) AS tab(col);
-- 输出：2.0

16、mod/%

取余数

SELECT MOD(2, 1.8);
-- 输出：0.2
SELECT 2 % 1.8;
-- 输出：0.2

17、log

取对数

SELECT log(10, 100);
-- 输出：2.0

18、sum

求和

1.6 常用字符串函数

1、upper/ucase

转大写

select upper('low');
select ucase('low');

2、lower/lcase

转小写

select lower('LOW');
select lcase('LOW');

3、length

返回字符串长度

select length("atguigu");

4、trim

前后去空格

select trim(" atguigu ");

5、lpad

向左补齐，到指定长度

select lpad('atguigu',9,'g');

6、rpad

向右补齐，到指定长度

select rpad('atguigu',9,'g');

8、split

-- 将字符串转化为数组，即：split('a,b,c,d' , ',') ==> ["a","b","c","d"]
select split("hadoop_spark_hive","_");
-- 输出: ["hadoop","spark","hive"]

10、reverse

返回字符串的反转结果

select reverse('abcd');
-- 输出: dcba

11、concat

将字符串拼接起来

select concat(strA, strB);
-- 输出: strAstrB

12、concat_ws

字符串拼接第一个字符为拼接符号,其余为要拼接的内容

select concat_ws(",","strA","strB");
-- 输出: strA,strB

13、substr/substring

按照给定的数字截取字符串一定长度内容

substr/substring
select substr('abcde',2);从第二个截，截到结尾
-- 输出: bcde
select substr('abcde',1,3);从第一个截，截三个长度
-- 输出: abc
select substr('wfeww',-2);从尾部截，截两个长度
-- 输出: tr

14、repeat

返回重复n次后的str字符串repeat(string str, int n)

select repeat('abc',5);
-- 输出: abcabcabcabcabc

15、ltrim

去除左侧空格

select ltrim(" AAA");
-- 输出: AAA

16、rtrim

去除右侧空格

select rtrim("AAA ");
-- 输出: AAA

18、parse_url

-- 语法: parse_url(string urlString, string partToExtract [, string keyToExtract])
-- 返回值: string
-- 说明：返回URL中指定的部分。partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
select parse_url('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') from tableName;
-- 输出：www.tableName.com 
select parse_url('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') from tableName;
-- 输出：v1

23、instr

返回字符串中某个元素的下标

-- 如果字符元素存在返回对应的下标，不存在返回0
select instr('Li jian','f');
-- 输出：0

24、initcap

将每个字母的首字母转换为大写，其他为小写

select initcap("LI JIAN IS BOY");
-- 输出：Li Jian Is Boy

25、left

获取传入参数左侧n位元素

SELECT left('Spark SQL', 3);
-- 输出：Spa

27、right

获取传入参数右侧n位元素

SELECT right('Spark SQL', 3);
-- 输出：SQL

28、locate

返回给定元素所在的下标

SELECT locate('bar', 'foobarbar');
-- 输出：4
SELECT locate('bar', 'foobarbar', 5);
-- 输出：7
SELECT POSITION('bar' IN 'foobarbar');
-- 输出：4

1.7 数组/集合操作

1、size

集合中元素的个数

select size(friends) from test3;

4、array_contains

判断array中是否包含某个元素

select array_contains(friends,'bingbing') from test3;

5、sort_array

将array中的元素排序

select sort_array(friends) from test3;

6、grouping_set(spark)

多维分析

7、flatten(spark)

将多个数组合并为一个数组

select flatten(array(`array`('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T','U', 'V', 'W', 'X', 'Y', 'Z'),`array`(269, 732, 677, 877, 953, 396, 268, 92, 173, 202, 682, 418, 411, 884, 337, 91, 153, 764, 546, 748,696, 398, 518, 806, 173, 716, 929, 935, 666,105),`array`('a','b','c')));
-- 输出：["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","269","732","677","877","953","396","268","92","173","202","682","418","411","884","337","91","153","764","546","748","696","398","518","806","173","716","929","935","666","105","a","b","c"]                      

8、filter(spark)

spark的函数

返回数组中符合条件的元素

select filter(`array`('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T','U', 'V', 'W', 'X', 'Y', 'Z'), x -> x < 'F');
-- 输出：["A","B","C","D","E"]
select filter(`array`(269, 732, 677, 877, 953, 396, 268, 92, 173, 202, 682, 418, 411, 884, 337, 91, 153, 764, 546, 748,696, 398, 518, 806, 173, 716, 929, 935, 666, 105), x -> x % 2 != 0);
-- 输出：[269,677,877,953,173,411,337,91,153,173,929,935,105]

9、element_at(spark)

spark函数

-- 根据下标获取数组或者map的元素值
select element_at(`array`('a','b','c','d','e','f'),2);
-- 输出：b
select element_at(`map`('a',1, 'b',2),'b');
-- 输出：2

10、array_union(spark)

spark函数

将两个数组拼接起来，并去重

select array_union(`array`(1,2,3,4,5),`array`(3,4,5,6,7,8)) as au;
-- 输出：[1,2,3,4,5,6,7,8]

11、arrays_overlap(spark)

spark函数

如果两个数组有交集返回true，没有交集返回false，两个数组都为null返回null

select arrays_overlap(`array`(1,2,3,4,5),`array`(3,4,5,6,7,8)) as ao;
-- 输出：true

12、array_repeat(spark)

spark函数

将元素复制多份并形成一个数组

select array_repeat(5,3) as ar;
-- 输出：[5,5,5]
select array_repeat("张三",3) as ar;
-- 输出：["张三","张三","张三"]

13、array_remove(spark)

spark函数

从数组中移除指定的元素

select array_remove(`array`(1,2,3,3,null,4,null,5),3) as ar;
-- 输出：[1,2,null,4,null,5]

14、array_distinct(spark)

spark函数

去除数组中的重复元素

select array_distinct(`array`(1,2,3,4,5,6,7,8,9,9,8,7,6,6,5,4,3,2,1)) as ad;
-- 输出：[1,2,3,4,5,6,7,8,9]

15、array_except(spark)

spark函数

显示集合1对于集合2的差集，也就是显示集合1独有的部分

select array_except(`array`(1,2,3,4,5),`array`(3,4,5,6,7,8)) as ae;
-- 输出：[1,2]

16、array_intersect(spark)

spark函数

显示集合1和集合2的并集，也就是集合1和集合2都有的部分

select array_intersect(`array`(1,2,3,3,4,5),`array`(3,3,4,4,5,6,7,8)) as ae;
-- 输出：[3,4,5]

17、array_join(spark)

spark函数

将集合内的元素用指定的字符拼接在一起，如果想要替换null值，在拼接字符后在增加一个替换元素

select array_join(`array`(1,2,3,3,null,4,null,5),'|+|') as aj;
-- 输出：1|+|2|+|3|+|3|+|4|+|5
select array_join(`array`(1,2,3,3,null,4,null,5),'|+|','kong') as aj;

18、array_max/array_min(spark)

spark函数

获取数组中的最大值/最小值

select array_max(`array`(1,2,3,3,null,4,null,5)) as am;
-- 输出：5
select array_min(`array`(1,2,3,3,null,4,null,5)) as am;
-- 输出：1

19、array_position(spark)

spark函数

返回数组中给定元素的下标，不存在返回0

select array_position(`array`(1,2,3,3,null,4,null,5),5) as ap;
-- 输出：8

20、array_sort(spark)

spark函数

对输入的数组进行排序，默认是升序

select array_sort(`array`(1,2,3,3,4,5,9,8,7,6)) as `as` ;
-- 输出：[1,2,3,3,4,5,6,7,8,9]
-- 想要降序排列
select array_sort(`array`(1,2,3,3,4,5,9,8,7,6), (left, right) ->
    case
    when left is null and right is null then 0
    when left is null then -1
    when right is null then 1
    when left < right then 1
    when left > right then -1
    else 0
    end) as `as`;
-- 或者使用reverse函数
select reverse(array_sort(`array`(1,2,3,3,4,5,9,8,7,6))) as `as`;

21、collect_list

列出传入字段的所有内容形成一个数组，不去重

-- 列出该字段所有的值，不去重
select collect_list(id) as id from tb;
-- 如果获取的是一个集合可以通过下标的方式获取对应值
select collect_list(id)[0] as id from tb;

22、collect_set

-- 列出某个字段所有的内容，会去重
select collect_set(id) as id from tb;
-- 如果获取的是一个集合可以通过下标的方式获取对应值
select collect_set(id)[0] as id from tb;

23、json_array_length(spark)

spark函数

返回最外层数组长度

SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
-- 输出：5

24、find_in_set

集合查找函数

select find_in_set('ab','ef,ab,de');
-- 输出：2
select find_in_set('at','ef,ab,de');
-- 输出：0

1.8 排序函数

1、order by

会对输入做全局排序，因此只有一个reducer（多个reducer无法保证全局有序）。只有一个reducer，会导致当输入规模较大时，需要较长的计算时间

-- 字段升序或者降序排列
select * from table order by 字段 asc/desc;

2、sort by

不是全局排序，其在数据进入reducer前完成排序

3、distribute by

按照指定的字段对数据进行划分输出到不同的reduce中

4、cluster by

除了具有distribute by 的功能外还兼具sort by 的功能。

1.9 条件函数

1、case when then else end

-- 第一种写法
select  dept_id,
		sum(case sex when '男' then 1 else 0 end) as male_count,
		sum(case sex when '女' then 1 else 0 end) as female_count
from emp_sex
group by dept_id;
-- 第二种写法
select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from tableName;

2、if

select  dept_id,
		sum(if(sex='男',1,0)) as male_count,
		sum(if(sex='女',1,0)) as female_count
from emp_sex
group by dept_id;

3、coalesce

-- 遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。
select coalesce(a,b,c,...);

4、isnull

-- 若a为null则返回true，否则返回false
select isnull(a)

5、isnotnull

-- 如果不为 null，则返回 true，否则返回 false。
SELECT isnotnull(1);
-- true

6、nvl

给值为NULL的数据赋值，它的格式是NVL(value， default_value) 。它的功能是如果value为NULL， 则NVL函数返回default_value的值， 否则返回value的值， 如果两个参数散都为NULL， 则返回NULL

select 字段1, nvl(字段1,字段2/默认值) from 表名;

7、count_if(spark)

如果满足条件就计数

count_if(expr) - Returns the number of TRUE values for the expression.

返回提供的表达式唯一且非空的行数。

Examples:

> SELECT count_if(col % 2 = 0) FROM VALUES (NULL), (0), (1), (2), (3) AS tab(col);
 2
> SELECT count_if(col IS NULL) FROM VALUES (NULL), (0), (1), (2), (3) AS tab(col);
 1

Since: 3.0.0

8、every(spark)

如果传入参数都为true，则返回true，否则返回false

select every(col1) from values (true),(true),(true) as tab(col1);
-- 输出：true

9、any(spark)

如果有一个值为真则返回真，否则返回假

SELECT any(col) FROM VALUES (true), (false), (false) AS tab(col);
-- 输出：false

10、ifnull(spark)

如果第一个参数为null，则返回第二个参数

SELECT ifnull(NULL, array('2'));
-- 输出：["2"]

1.10 运算符

1、算数运算符

2、比较运算符

3、逻辑运算符

1.11 类型转换

1、cast

将数据转换成指定的数据类型

select cast('1' as DOUBLE); 
-- 输出：1.0

2、ascii

转换成asc码

select ascii('b');
-- 输出：98

3、bin

转换成二进制

select bin(123123) as II 
-- 输出：11110000011110011

十六进制函数: hex()、将十六进制转化为字符串函数: unhex() 进制转换函数: conv(bigint num, int from_base, int to_base) 说明: 将数值num从from_base进制转化到to_base进制

此外还有很多数学函数：绝对值函数: abs()、正取余函数: pmod()、正弦函数: sin()、反正弦函数: asin()、余弦函数: cos()、反余弦函数: acos()、positive函数: positive()、negative函数: negative()

4、hex

转换为16进制

SELECT hex('Spark SQL');
-- 输出：537061726B2053514C

5、hash

返回传入参数的哈希值

SELECT hash('Spark', array(123), 2);
-- 输出：-1321691492

6、int(spark)

将数据转换为int类型

select `int`("12.34");
-- 输出：12

7、md5

返回MD5 128位校验码

SELECT md5('Spark');
-- 输出：8cde774d6f7333752ed72cacddb05126

1.12 数据类型

1、TINYINT

byte类型 1 byte有符号整数

2、SMALINT

short类型 2 byte有符号整数

3、INT

int类型 4 byte有符号整数

4、BIGINT

long类型 8 byte有符号整数

5、BOOLEAN

boolean类型 布尔类型，true或者false

6、FLOAT

float类型 单精度浮点数

7、DOUBLE

double类型 双精度浮点数

8、STRING

string类型 字符系列

9、TIMESTAMP

时间戳，纳秒精度

10、BINARY

字节数组

11、decimal

任意精度的带符号小数

12、varchar

可变字符串

13、char

固定长度字符串

14、date

日期

1.13 其他函数

1、limit

-- 分页展示
SELECT * FROM customers ORDER BY create_date LIMIT 2, 5;
-- 每页有5个,展示第三页的内容求m的值,公式为m=(3-1)*n  limit m,n

2、like

使用LIKE运算选择类似的值
选择条件可以包含字符或数字
%/*：代表零个或多个字符（任意个字符）
_：代表一个字符

-- 查找名字以A开头的信息
 select * from 表名 where 字段 LIKE 'A*';
-- 查找名字中第二个字母为A的信息
 select * from 表名 where 字段 LIKE '_A*';

3、rlike

RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件

-- 查找名字中带有A的信息
select * from 表名 where 字段 RLIKE '[A]';

4、partitioned by

-- 指定分区字段，并指定存储格式
CREATE TRANSACTIONAL TABLE transactional_table_test(key string, value string) PARTITIONED BY(dt string) STORED AS ORC;

5、percentile

-- 语法: percentile(BIGINT col, p)
-- 返回值: double
-- 说明: 求准确的第pth个百分位数，p必须介于0和1之间，但是col字段目前只支持整数，不支持浮点数类型
-- 求单个分位数
SELECT percentile(col, 0.3) FROM VALUES (0), (10) AS tab(col);
-- 求多个分位数
SELECT percentile(col, array(0.25, 0.75)) FROM VALUES (0), (10) AS tab(col);

6、inline/inline_out

将结构数据分解为表

SELECT inline(array(struct(1, 'a'), struct(2, 'b')));
 1  a
 2  b

1.14 字典

1、json_object_keys(spark)

以数组的形式返回所有的键

SELECT json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}');
-- 输出：["f1","f2"]

2、map_concat(spark)

返回两个字典的并集

SELECT map_concat(map(1, 'a', 2, 'b'), map(3, 'c'));
-- 输出：{1:"a",2:"b",3:"c"}

3、map_entries(spark)

将字典拆解成单独的字典元素

SELECT map_entries(map(1, 'a', 2, 'b'));
-- 输出：[{"key":1,"value":"a"},{"key":2,"value":"b"}]

4、map_filter(spark)

使用函数过滤掉字典中不符合条件的元素

SELECT map_filter(map(1, 0, 2, 2, 3, -1), (k, v) -> k > v);
-- 输出：{1:0,3:-1}

5、map_from_arrays(spark)

将两个数组映射为字典，元素不能为空

SELECT map_from_arrays(array(1.0, 3.0), array('2', '4'));
-- 输出：{1.0:"2",3.0:"4"}

6、map_keys

以数组的形式返回字典的keys

SELECT map_keys(map(1, 'a', 2, 'b'));
-- 输出：[1,2]

7、map_values

以数组的形式返回字典的所有values

SELECT map_values(map(1, 'a', 2, 'b'));
-- 输出：["a","b"]

8、map_zip_with(spark)

SELECT map_zip_with(map(1, 'a', 2, 'b'), map(1, 'x', 2, 'y'), (k, v1, v2) -> concat(v1, v2));
-- 输出：{1:"ax",2:"by"}

9、named_struct

已给定的字符组成字典

SELECT named_struct("a", 1, "b", 2, "c", 3);
-- 输出：{"a":1,"b":2,"c":3}

10、to_json(spark)

将数组转换为字典

> SELECT to_json(named_struct('a', 1, 'b', 2));
 {"a":1,"b":2}
> SELECT to_json(named_struct('time', to_timestamp('2015-08-26', 'yyyy-MM-dd')), map('timestampFormat', 'dd/MM/yyyy'));
 {"time":"26/08/2015"}
> SELECT to_json(array(named_struct('a', 1, 'b', 2)));
 [{"a":1,"b":2}]
> SELECT to_json(map('a', named_struct('b', 1)));
 {"a":{"b":1}}
> SELECT to_json(map(named_struct('a', 1),named_struct('b', 2)));
 {"[1]":{"b":2}}
> SELECT to_json(map('a', 1));
 {"a":1}
> SELECT to_json(array((map('a', 1))));
 [{"a":1}]
 
 select map_from_arrays(collect_list(M),collect_list(A)) from study_one_day.t20220607_1 group by Y;

11、map_from_arrays(spark)

将两个数组转换为字典

select map_from_arrays(collect_list(M),collect_list(A)) from study_one_day.t20220607_1 group by Y;

12、get_json_object

根据字典的键获取字典的值

SELECT get_json_object('{"a":"b"}', '$.a');
-- 输出：b
-- 语法: get_json_object(string json_string, string path)
-- 返回值: string
-- 说明：解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。
select  get_json_object('{"store":{"fruit":\[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}], "bicycle":{"price":19.95,"color":"red"} },"email":"amy@only_for_json_udf_test.net","owner":"amy"}','$.owner') from tableName;
-- ------------------------------------------------------------------------------------------------------------
select id,
       get_json_object(t.js, '$.name') as name,
       get_json_object(t.js, '$.age')  as age
from test02 as t;

13、json_tuple

批量获取json字段的内容

select t1.id,t2.* from  test02 as t1 lateral view json_tuple(t1.js, 'name','age') t2 as name,age;

1.15 正则

1、regexp_replace

使用正则表达式匹配目标字符串，匹配成功后替换！

SELECT regexp_replace('2020/10/25', '/', '-');
-- 输出：2020-10-25

2、regexp_extract

正则表达式解析函数

-- 语法: regexp_extract(string subject, string pattern, int index)
-- 返回值: string
-- 说明：将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。
select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) from tableName;
-- 输出：the
select regexp_extract('foothebar', 'foo(.*?)(bar)', 2) from tableName;
-- 输出：bar
select regexp_extract('foothebar', 'foo(.*?)(bar)', 0) from tableName;
-- 输出：foothebar
-- strong>注意，在有些情况下要使用转义字符，下面的等号要用双竖线转义，这是java正则表达式的规则。
select data_field,
	regexp_extract(data_field,'.*?bgStart\\=([^&]+)',1) as aaa,
	regexp_extract(data_field,'.*?contentLoaded_headStart\\=([^&]+)',1) as bbb,
	regexp_extract(data_field,'.*?AppLoad2Req\\=([^&]+)',1) as ccc 
from pt_nginx_loginlog_st 
where pt = '2021-03-28' limit 2;

3、rlike

一般用于筛选符合条件

select count(1) from sou_gou.user_find where user_id rlike '^[0-9]+$';
-- 统计user_id都是数字组成的

4、regexp_extract_all(spark)

SELECT regexp_extract_all('100-200, 300-400', '(\\d+)-(\\d+)', 1);
-- 输出：["100","300"]

5、regexp_like(spark)

SELECT regexp_like('%SystemDrive%\\Users\\John', '%SystemDrive%\\\\Users.*');
-- 输出：true

6、regexp

SELECT regexp('%SystemDrive%\\Users\\John', '%SystemDrive%\\\\Users.*');
-- 输出：true

1.16 常用聚合函数

1 求总行数

select count(1) as cnt from 表名;

2 求最大值

select max(字段) as max_value from 表名;

3 求最小值

select min(字段) as min_value from 表名;

4 求和

select sum(字段) as sum_value from 表名;

5 求平均值

select avg(字段) as avg_value from 表名;

1.19 数据类型

对decimal类型简单解释下： 用法：decimal(11,2) 代表最多有11位数字，其中后2位是小数，整数部分是9位；如果整数部分超过9位，则这个字段就会变成null；如果小数部分不足2位，则后面用0补齐两位，如果小数部分超过两位，则超出部分四舍五入 也可直接写 decimal，后面不指定位数，默认是 decimal(10,0) 整数10位，没有小数

本栏目推荐文章
• 函数的参数类型
• 【C语言】函数的递归调用
• NUS CS1101S：SICP JavaScript 描述：一、使用函数构建抽象
• asp.net mvc4 controller构造函数
• 函数返回值
• Hive常见问题
• 10-函数进阶-作用域
• 11-函数进阶-参数类型
• 9-函数基础
• vim中函数的跳转与回退

函数 Hive函数hive 函数coalesce笔记hive 函数hive sql 函数hive-dql hive dql 函数 数据库 数据hive 函数hive lag 函数 高阶explode hive 函数grouping hive 函数 大全hive 数据hive函数 时间