数学函数
| 函数 | 简介 | 用法 |
|---|---|---|
| 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; |
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/tech/database/282541.html