sparksql 函数大全

数学函数

函数	简介	用法
acosh	反双曲余弦值	SELECT acosh(0.5);0.9624236501192069 SELECT acosh(3.5);1.9248473002384139
asinh	反双曲正弦	SELECT asinh(1.45);1.1667043308708802
atan2	弧度为单位的角度	SELECT atan2(6, 3);1.1071487177940904
atanh	反双曲正切	select atanh(0.54);0.6041556029622672
cosh	双曲余弦	select cosh(1.34);2.040444586973331
cot	余切	select cot(134);-0.5235533086264333
kurtosis	计算峰度值	select kurtosis(id) from data;-0.9354760165570974
count_min_sketch	count-min草图，是一种概率数据结构，用于使用次线性空间进行基数估计	https://zhuanlan.zhihu.com/p/369981005
expm1	e的N次方-1	select expm1(1);1.718281828459045
sinh	双曲正弦	select sinh(12.34);114330.97602621827
tanh	双曲正切	select tanh(0.34);0.32747739480870536
skewness	两个列的偏移量	SELECT skewness(id) from data;4.364357804719847
hypot	aa+bb=c*c 三角形三边原理，给两边求第三边	select hypot(3,4);5
randn	返回随机数 带负数	SELECT randn(0);1.6034991609278433
random	返回随机数 0-1	select random();0.8284189156403218
rint	返回参数值的最接近的数学整数的双精度数	select rint(1.5324);2.0
log1p	参数+1 log(e)	SELECT log1p(1.718281828459045);1

组函数

函数	简介	用法
mean	平均值	SELECT mean(user_id) from data;26
posexplode_outer	带下标的explode	SELECT posexplode_outer(array(10,20));0 10,1 20 SELECT posexplode_outer(map(10,20));0 10 20
cube	立方体group by 按照 group by(id,user_id,(id,user_id),null)	select id,user_id,count(1) from data group by cube(id,user_id);
explode_outer	数组展开 没有看出来和explode的区别 注释写的是不同，但是实际相同	select explode_outer(array(1,2,null));1,2,NULL
first	第一个值	select first(id) from data;1
grouping_id	分组的ID	select grouping_id(),id,user_id,count(1) from data group by cube(id,user_id); 类似hive的grouping__id
last	最后一个值	select last(id) from data;5
max_by	按照第二个参数的最大值，取对应第一个参数值	SELECT max_by(id, user_id) from data;1
min_by	按照第二个参数的最小值，取对应第一个参数值	SELECT min_by(id, user_id) from data;4
rollup	group by所有列，必须包含第一列 union group by null	select id,user_id,count(1) from data group by rollup(id,user_id);
spark_partition_id	返回partitionid	select spark_partition_id() from data limit 100; 我这结果全是0

统计函数

函数	简介	用法
approx_count_distinct	近似去重统计，速度快很多1/3 1000万	select approx_count_distinct(id,double(34.999)) from data; 7779436
approx_percentile	近似的百分比，速度快很多	SELECT approx_percentile(id, array(0.5, 0.4, 0.1)) from data; [4999694,3999009,1000000]
bit_and	返回所有数值的按位AND	select bit_and(id) from data;5
bit_count	按位计数，没有做预聚合，使用过程中崩了	select bit_count(id) from data;
bit_length	字节长度	select bit_length('abcd');32
bit_or	返回所有数值的按位OR	select bit_or(id) from data;7
bit_xor	返回所有数值的按位异或 相同为0 不同为1	select bit_or(id) from data;1

基本类型转换函数

函数	简介	用法
typeof	返回数据类型	select typeof(1.45);decimal(3,2) select typeof(2);int
bigint	强转为整数	select bigint(3.12);3
boolean	强转为boolean 函数写的还可以哈	select boolean('true');true select boolean('TruE');true select boolean('');NULL
decimal	强转为数字类型 不能限定小数位转成整数 很奇怪	select decimal(5.12); 5
double	强转为双浮点类型	select double('5.12');5.12
float	参数为浮点类型	select float('3.12');3.12
smallint	强转为整数类型	select smallint('10000');10000
string	强转为字符串类型	select string(15.45);15.45
tinyint	强转为整数类型	select tinyint('15');15
int	强转为整数类型	select int(1.56);1

日期函数

函数	简介	用法
timestamp	强转为时间戳类型	select timestamp(1661575380);2022-08-27 12:43:00
date	强转为日期 yyyy-MM-dd格式	select date('2022-08-08');2022-08-08
date_part	日期截取 year month week doy(day_year) days(day_month) hour minutes seconds	SELECT date_part('YEAR', now());2022
date_trunc	类似于hive的floor函数 日期的floor	SELECT date_trunc('YEAR', '2015-03-05T09:32:05.359');2015-01-01 00:00:00
dayofyear	一年中当前天数 这个函数hive还真没有	SELECT dayofyear('2022-08-08');220
extract	日期截取 date_part类似 year month week doy(day_year) days(day_month) hour minutes seconds	SELECT extract('days', now());27
make_date	输入参数转日期	SELECT make_date('2013', '7', '15');2013-07-15
make_interval	years,months,weeks,days,hours,mins,secs	SELECT make_interval(2022,08,11,12,12,30,01.001001);2022 years 8 months 89 days 12 hours 30 minutes 1.001001 seconds
make_timestamp	year,month,day,hour,min,sec[, timezone]	SELECT make_timestamp(2022,08,11,12,12,30);2022-08-11 12:12:30
now	返回现在时间	SELECT now();2022-08-27 12:52:30.356
weekday	返回周的天数	select weekday('2022-08-26');4
to_timestamp	字符串转日期格式	SELECT to_timestamp('20161231', 'yyyyMMdd');2016-12-31 00:00:00

集合函数

Array相关函数

函数	简介	用法
aggregate	数组、函数的初始值、函数表达式	SELECT aggregate(array(1, 2, 3), 0, (x,y)->x+y);6
array_distinct	数组内去重	select array_distinct(collect_list(id)) from data;[1,2,3,4,5]
array_except	数组内去除	select array_except(col,col) from (select collect_list(id) col from data); []
array_intersect	数组内inner join	select array_intersect(col,col) from (select collect_list(id) col from data); [1,2,3,4,5]
array_join	数组每个元素进行字符串拼接 第三个参数替换NULL	SELECT array_join(array('a', null ,'c'), '
array_max	数组内最大值	select array_max(col) from (select collect_list(id) col from data);
array_min	数组内最小值	select array_min(col) from (select collect_list(id) col from data);
array_position	元素在数组中的位置 数组、元素(可以是列)	select array_position((select collect_list(id) col from data),id) from data ;1,2,3,4,5
array_remove	数组移除当前元素，数组不共享	select array_remove((select collect_list(id) col from data),id) from data ;[2,3,4,5],[1,3,4,5],[1,2,4,5],[1,2,3,5],[1,2,3,4]
array_repeat	把元素重复当作数组返回	select array_repeat('12',5);["12","12","12","12","12"]
array_sort	数组排序	SELECT array_sort(array('3', '2', '1'), (x, y) -> case when xy then 1 end);["1","2","3"]
array_union	union操作元素去重	SELECT array_union(array(1, 2, 3), array(1, 3, 5));[1,2,3,5]
arrays_overlap	inner join && count(1) ,只包含null返回null	SELECT arrays_overlap(array(1,2), array(1));true
arrays_zip	带数组下标的zip	SELECT arrays_zip(array(4), array(7,8));[{"0":4,"1":7},{"0":null,"1":8}]
cardinality	数组长度	SELECT cardinality(array('b', 'd', 'c', 'a'));4
forall	数组内元素都执行这个操作，结果符合预期返回true	SELECT forall(array(1, 2, 3), x -> x % 2 == 0);false
element_at	元素在集合中查找	select element_at((select collect_list(id) col from data),int(id)) from data ;
filter	过滤	SELECT filter(array(1, 2, 3), x -> x % 2 == 1);[1,3]
slice	数组切数组	SELECT slice(array(1, 2, 3, 4), 2, 5);[2,3,4]
transform	数组元素map	SELECT transform(array(1, 2, 3), x -> x + 1);[2,3,4]
flatten	数组内部还是数组的展开 不去重	SELECT flatten(array(array(1, 2), array(1, 4)));[1,2,1,4]
sequence	返回数组 仅支持整数和时间类型	SELECT sequence(6,9);[6,7,8,9] SELECT sequence(6,0);[6,5,4,3,2,1,0]
shuffle	给数组随机排序	SELECT shuffle(array(1, 20, 3, 5));[1,5,3,20]
inline_outer	数组内部还是struct的展开 不去重	SELECT inline_outer(array(struct(1, 'a'), struct(1, 'a')));
zip_with	数组压缩	SELECT zip_with(array(1, 2), array(3, 4), (x, y) -> x + y);[4,6]

map相关函数

函数	简介	用法
map_concat	map内部还是map的展开 重复报错	SELECT map_concat(map(1, 'a', 2, 'b'), map(3, 'c'));{1:"a",2:"b",3:"c"}
map_entries	map转成数组+内部key方式	SELECT map_entries(map(1, 'a', 2, 'b'));[{"key":1,"value":"a"},{"key":2,"value":"b"}]
map_filter	map过滤	SELECT map_filter(map(1, 'a', 2, 'b'),(k,y)->(k>=1));{1:"a",2:"b"}
map_from_arrays	map转数组方式	SELECT map_from_arrays(array(1.0, 3.0), array('2', '4'));{1.0:"2",3.0:"4"}
map_from_entries	array转map	SELECT map_from_entries(array((1, 'a'), (2, 'b')));{1:"a",2:"b"}
map_zip_with	按照给定方式压缩map	SELECT map_zip_with(map(1, 'a', 2, 'b'), map(1, 'x', 2, 'y'), (k, v1, v2) -> concat(v1, v2));{1:"ax",2:"by"}
transform_keys	对map的key进行函数操作	SELECT transform_keys(map('a',1,'b',2),(k,v)->v+1); {2:1,3:2}
transform_values	对map的value进行函数操作	SELECT transform_values(map('a',1,'b',2),(k,v)->v+1); {"a":2,"b":3}

集合转文件相关函数

函数	简介	用法
from_csv	按照csv格式解析字符串	SELECT from_csv('1, 0.8', 'a INT, b DOUBLE');{"a":1,"b":0.8}
from_json	按照json格式解析字符串	SELECT from_json('{"a":1, "b":0.8}', 'a INT, b DOUBLE');
schema_of_csv	返回csv类型的schema	SELECT schema_of_csv('a b',map("sep"," "));STRUCT<_c0: STRING, _c1: STRING>
schema_of_json	返回json类型的schema	select schema_of_json('{"c1":01, "c2":0.1}', map('allowNumericLeadingZeros', 'true', 'prefersDecimal', 'true'));
to_csv	struct转csv	SELECT to_csv(struct('a', 1));1 named_struct只取value
to_json	struct转json	SELECT to_json(named_struct('a', 1, 'b', 2));{"a":1,"b":2} SELECT to_json(struct('a', 1, 'b', 2));{"col1":"a","col2":1,"col3":"b","col4":2}

判断函数

函数	简介	用法
any	参数为boolean类型的列，至少有一行为true就返回true	select any(desc) from data;true
every	参数为boolean类型的列，全部为true就返回true	select every(desc) from data;false
ifnull	参数1为空，就返回参数2	SELECT ifnull(1/3, 2);0.3333333333333333
isnan	表达式为NAN，则返回true	select isnan('NAN');true
nanvl	表达式不为NAN，返回第二个参数值	SELECT nanvl(cast('NaN' as double), 123);123
nvl2	参数1为空则返回参数3，否则返回参数2	SELECT nvl2(NULL, 2, 1);1
signum	当参数为负、0或正时，返回-1.0、0.0或1.0
some	参数为boolean类型的列，至少有一行为true就返回true	select some(desc) from data;
bool_and	所有都是true返回true	select bool_and(desc) from data;false
bool_or	有一个true返回true	select bool_or(desc) from data;true
count_if	带条件的count	select count_if(id % 2 ==0) from data;2

字符串函数

函数	简介	用法
char	ASCII 码转换为字符	SELECT char(72);H
position	字符串查找	SELECT position('d', 'abcdefgh');4
overlay	替换下标位4的字符	SELECT overlay('SparkSQL','_',4);Spa_kSQL
xxhash64	64位的hash码 参数，数据类型，随机数种子	SELECT xxhash64('Spark',array(123),2);
format_string	类似字符串拼接函数	SELECT format_string("id%suser_id%s", id,user_id) from data;id1user_id1

分区记录函数

函数	简介	用法
input_file_block_length	返回文件大小	select input_file_block_length() from data;
input_file_block_start	返回文件读取起始位置	select input_file_block_start() from data;0
input_file_name	返回文件地址	select input_file_name() from data;file:/root/spark-warehouse/data/dt=20220801/data_min
monotonically_increasing_id	64为ID，高31位分区ID低33位记录号，分区小于10亿，分区内记录小于80亿正确	select monotonically_increasing_id() from data limit 10;