Spark Sql 函数

一、 字符串函数

1. 字符串截取

1.1 字符串截取之substring_index函数

substring_index(str,delim,count)
其中：str:要处理的字符串；delim:分隔符；count:计数

①count为正数的情况下，从左往右数，第count个分隔符的左边的全部内容
例子：str=www.baidu.com
substring_index(str,‘.’,1) 结果是：www
substring_index(str,‘.’,2) 结果是：www.baidu

②count为负数的情况下，从右往左数，第count个分隔符的右边的全部内容
例子：
substring_index(str,‘.’,-1) 结果是：com

③如果想要中间的，则嵌套操作
例子：
substring_index(substring_index(str,‘.’,2),‘.’,-1) 结果是：baidu

1.2 字符串截取之substring函数和substr函数

参考：https://blog.csdn.net/bowenlaw/article/details/102484978
substr(string A, int start，int len),substring(string A, int start,int len) 两者用法一致
其中：A：处理的字符串；start：截取的开始位置(坐标从1开始)
len：截取的长度，若函数为两个参数，则默认截取到末尾。

例子

spark.sql("select substring('abcdeffff',3) from data1").show()

+-----------------------------------+
|substring(abcdeffff, 3, 2147483647)|
+-----------------------------------+
|                            cdeffff|
|                            cdeffff|
+-----------------------------------+


spark.sql("select substring('abcdeffff',-1) from data1").show()

+------------------------------------+
|substring(abcdeffff, -1, 2147483647)|
+------------------------------------+
|                                   f|
|                                   f|
+------------------------------------+


spark.sql("select substring('abcdeffff',3,5) from data1").show()

+--------------------------+
|substring(abcdeffff, 3, 5)|
+--------------------------+
|                     cdeff|
|                     cdeff|
+--------------------------+

2. 字符串查找

2.1 字符串查找函数之instr函数

instr(String str,string substr)
返回值：int（返回字符串substr在str中首次出现的位置，坐标从1开始）
例子：
instr(‘sbacba’,‘ba’) 结果是：2

2.2 字符串查找函数之locat函数

locate(string substr, string str,[int pos])
返回值：int(返回字符串 substr 在 str 中从 pos 开始查找,首次出现的位置，坐标从1开始)
例子：
locate(‘a’,‘abcda’,1) 结果是：1
locate(‘a’,‘abcda’,2) 结果是：5

3. 字符串大小写转换函数

小写转为大写：UPPER(字段名)
大写转为小写：LOWER(字段名)

4. NVL函数

NVL(E1, E2)的功能为：如果E1为NULL，则函数返回E2，否则返回E1本身。

二、转换数据类型函数

1. cast函数

cast（expression as data_type）
as：用于分隔两个参数，在as之前的是要处理的数据，as之后是要转换的数据类型。
Decimal不属于浮点类型，可以在定义时划整数部分以及小数部分的位数。
例子：
①cast(‘12’ as int) 结果是：12

②cast(‘12.5’ as int) 结果是：报错
原因：cast函数和convert函数都不能执行四舍五入或截断操作。由于12.5不能用int数据类型来表示，所以会报错。

③cast(‘12.5’ as decimal(9,2)) 结果是：12.50
9是精度，总的数字位数，包括左边和右边位数的总和。
2是小数位数，是小数点右边的位数。

④cast(‘12.5’ as decimal) 结果是：12
精度和小数位数默认值分别是18与0。如果在decimal类型中不提供这两个值，将截断小数部分，并不会像②一样报错。

三、数学运算

round 四舍五入
floor 取左值
ceil 取右值

spark-sql> select round(1.2356);
1
Time taken: 0.788 seconds, Fetched 1 row(s)
spark-sql> select round(1.6356);
2
Time taken: 0.046 seconds, Fetched 1 row(s)
 
spark-sql> select floor(1.2356);
1
Time taken: 0.082 seconds, Fetched 1 row(s)
spark-sql> select floor(1.6356);
1
Time taken: 0.038 seconds, Fetched 1 row(s)
 
spark-sql> select ceil(1.2356);
2
Time taken: 0.039 seconds, Fetched 1 row(s)
spark-sql> select ceil(1.6356);
2
Time taken: 0.035 seconds, Fetched 1 row(s)

round函数的语法是：ROUND(number,num_digits)，即：Round（数值，保留的小数位数)
Number：需要进行四舍五入的数字。
Num_digits：指定的位数，按此位数进行四舍五入。
①如果 num_digits 大于 0，则四舍五入到指定的小数位。
ROUND(3.19, 1) 将 3.19 四舍五入到一个小数位 (3.2)
ROUND(2.649, 1) 将 2.649 四舍五入到一个小数位 (2.6)
ROUND(-5.574, 2) 将 -5.574 四舍五入到两小数位 (-5.57)

②如果 num_digits 等于 0，则四舍五入到最接近的整数。

③如果 num_digits 小于 0，则在小数点左侧进行四舍五入。
ROUND(18.8, -1) 将 18.8 四舍五入到小数点左侧一位 (20)。这个参数-1表示取整到十位数。