SparkSQL函数操作

1.5 SparkSQL函数操作

1.5.1 函数的定义

SQL中函数，其实说白了就是各大编程语言中的函数，或者方法，就是对某一特定功能的封装，通过它可以完成较为复杂的统计。这里的函数的学习，就基于Hive中的函数来学习。

1.5.2 函数的分类

功能分类

数值

1. round(x, [d]):对x保留d位小数，同时会四舍五入.

spark-sql> select round(0.33,1);
+-----------------+
| round(0.33, 1)  |
+-----------------+
| 0.3 ? ? ? ? ? ? |
+-----------------+

1. floor(x):获取不大于x的最大整数.

spark-sql> select floor(-3.5);
+--------------+
| FLOOR(-3.5)  |
+--------------+
| -4 ? ? ? ? ? |
+--------------+

1. ceil(x):获取不小于x的最小整数.

spark-sql> select ceil(-3.1);
+-------------+
| CEIL(-3.1)  |
+-------------+
| -3 ? ? ? ?  |
+-------------+

1. rand()：获取0到1之间的随机数，获取表中随机的两条记录

spark-sql> select * from test order by rand() limit 2;
+------+--------+
| tid  | tname  |
+------+--------+
| 1 ?  | abc ?  |
+------+--------+
or
spark-sql> select *, rand() rand from test order by rand limit 2;
+------+--------+---------------------+
| tid  | tname  | ? ? ?  rand ? ? ? ? |
+------+--------+---------------------+
| 1 ?  | abc ?  | 0.5080723866817868  |
+------+--------+---------------------+

1. 数学

取绝对值

spark-sql> select abs(-1);
+----------+
| abs(-1)  |
+----------+
| 1 ? ? ?  |
+----------+

取幂次方

spark-sql> select pow(2,3);
+--------------------------------------------+
| pow(CAST(2 AS DOUBLE), CAST(3 AS DOUBLE))  |
+--------------------------------------------+
| 8.0 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?  |
+--------------------------------------------+

平方根

spark-sql> select sqrt(16);
+---------------------------+
| SQRT(CAST(16 AS DOUBLE))  |
+---------------------------+
| 4.0 ? ? ? ? ? ? ? ? ? ? ? |
+---------------------------+

条件

spark-sql> select if(1=2,100,200);
+--------------------------+
| (IF((1 = 2), 100, 200))  |
+--------------------------+
| 200 ? ? ? ? ? ? ? ? ? ?  |
+--------------------------+
1 row selected (0.06 seconds)
spark-sql> Select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;
select 
(case 1 
when 1 then "1 = 1" 
when 2 then "2 = 2" 
else "no" 
end);
+----------------------------------------------------+
| CASE WHEN (100 = 50) THEN tom WHEN (100 = 100) THEN mary ELSE tim END |
+----------------------------------------------------+
| mary ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+----------------------------------------------------+

日期

1. UNIX时间戳转日期函数: from_unixtime

语法: from_unixtime(bigint unixtime[, string format])
返回值: string
说明: 转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式
举例：
spark-sql> select from_unixtime(1323308943,'yyyyMMdd');
+----------------------------------------------------+
| from_unixtime(CAST(1323308943 AS BIGINT), yyyyMMdd) |
+----------------------------------------------------+
| 20111208 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+----------------------------------------------------+

1. 获取当前UNIX时间戳函数: unix_timestamp

语法: unix_timestamp()
返回值: bigint
说明: 获得当前时区的UNIX时间戳
举例：
spark-sql> select unix_timestamp();
+----------------------------------------------------+
| unix_timestamp(current_timestamp(), yyyy-MM-dd HH:mm:ss) |
+----------------------------------------------------+
| 1626902929 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+----------------------------------------------------+

1. 日期转UNIX时间戳函数: unix_timestamp

语法: unix_timestamp(string date)
返回值: bigint
说明: 转换格式为"yyyy-MM-dd HH:mm:ss"的日期到UNIX时间戳。如果转化失败，则返回0。
举例：
spark-sql> select unix_timestamp('2011-12-07 13:01:03');
+----------------------------------------------------+
| unix_timestamp(2011-12-07 13:01:03, yyyy-MM-dd HH:mm:ss) |
+----------------------------------------------------+
| 1323234063 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+----------------------------------------------------+

1. 指定格式日期转UNIX时间戳函数: unix_timestamp

语法: unix_timestamp(string date, string pattern)
返回值: bigint
说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。
举例：
spark-sql> select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');
+----------------------------------------------------+
| unix_timestamp(20111207 13:01:03, yyyyMMdd HH:mm:ss) |
+----------------------------------------------------+
| 1323234063 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+----------------------------------------------------+

1. 日期时间转日期函数: to_date

语法: to_date(string timestamp)
返回值: string
说明: 返回日期时间字段中的日期部分。
举例：
spark-sql> select to_date('2011-12-08 10:03:01');
+-------------------------------+
| to_date(2011-12-08 10:03:01)  |
+-------------------------------+
| 2011-12-08 ? ? ? ? ? ? ? ? ?  |
+-------------------------------+

1. 日期转年函数: year

语法: year(string date)
返回值: int
说明: 返回日期中的年。
举例：
spark-sql> select year('2011-12-08 10:03:01');
+------------------------------------------+
| year(CAST(2011-12-08 10:03:01 AS DATE))  |
+------------------------------------------+
| 2011 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+------------------------------------------+

1. 日期转月函数: month

语法: month (string date)
返回值: int
说明: 返回日期中的月份。
举例：
spark-sql> select month('2011-12-08 10:03:01');
+-------------------------------------------+
| month(CAST(2011-12-08 10:03:01 AS DATE))  |
+-------------------------------------------+
| 12 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?  |
+-------------------------------------------+

1. 日期转天函数: day

语法: day (string date)
返回值: int
说明: 返回日期中的天。
举例：
spark-sql> select day('2011-12-08 10:03:01');
+-----------------------------------------+
| day(CAST(2011-12-08 10:03:01 AS DATE))  |
+-----------------------------------------+
| 8 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+-----------------------------------------+

1. 日期转小时函数: hour

语法: hour (string date)
返回值: int
说明: 返回日期中的小时。
举例：
spark-sql> select hour('2011-12-08 10:03:01');
+-----------------------------------------------+
| hour(CAST(2011-12-08 10:03:01 AS TIMESTAMP))  |
+-----------------------------------------------+
| 10 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?  |
+-----------------------------------------------+

1. 日期转分钟函数: minute

语法: minute (string date)
返回值: int
说明: 返回日期中的分钟。
举例：
spark-sql> select minute('2011-12-08 10:03:01') ;
+-------------------------------------------------+
| minute(CAST(2011-12-08 10:03:01 AS TIMESTAMP))  |
+-------------------------------------------------+
| 3 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+-------------------------------------------------+

1. 日期转秒函数: second

语法: second (string date)
返回值: int
说明: 返回日期中的秒。
举例：
spark-sql>  select second('2011-12-08 10:03:01');
+-------------------------------------------------+
| second(CAST(2011-12-08 10:03:01 AS TIMESTAMP))  |
+-------------------------------------------------+
| 1 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+-------------------------------------------------+

1. 日期转周函数: weekofyear

语法: weekofyear (string date)
返回值: int
说明: 返回日期在当前的周数。
举例：
spark-sql> select weekofyear('2011-12-08 10:03:01') ;
+------------------------------------------------+
| weekofyear(CAST(2011-12-08 10:03:01 AS DATE))  |
+------------------------------------------------+
| 49 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+------------------------------------------------+

1. 日期比较函数: datediff

语法: datediff(string enddate, string startdate)
返回值: int
说明: 返回结束日期减去开始日期的天数。
举例：
spark-sql> select datediff('2012-12-08','2012-05-09');
+----------------------------------------------------+
| datediff(CAST(2012-12-08 AS DATE), CAST(2012-05-09 AS DATE)) |
+----------------------------------------------------+
| 213 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?  |
+----------------------------------------------------+

1. 日期增加函数: date_add

语法: date_add(string startdate, int days)
返回值: string
说明: 返回开始日期startdate增加days天后的日期。
举例：
spark-sql> select date_add('2012-12-08',10);
+-----------------------------------------+
| date_add(CAST(2012-12-08 AS DATE), 10)  |
+-----------------------------------------+
| 2012-12-18 ? ? ? ? ? ? ? ? ? ? ? ? ? ?  |
+-----------------------------------------+

1. 日期减少函数: date_sub

语法: date_sub (string startdate, int days)
返回值: string
说明: 返回开始日期startdate减少days天后的日期。
举例：
spark-sql> select date_sub('2012-12-08',10);
+-----------------------------------------+
| date_sub(CAST(2012-12-08 AS DATE), 10)  |
+-----------------------------------------+
| 2012-11-28 ? ? ? ? ? ? ? ? ? ? ? ? ? ?  |
+-----------------------------------------+

字符串

注意：数据库中的字符串索引从1开始，而不是0

1. length（str） 返回字符串str的长度

spark-sql> select length(123);
+------------------------------+
| length(CAST(123 AS STRING))  |
+------------------------------+
| 3 ? ? ? ? ? ? ? ? ? ? ? ? ?  |
+------------------------------+

1. instr(str, substr),作用等同于str.indexOf(substr)

spark-sql> select instr("www.qfnb.com","b");
+-------------------------+
| instr(www.qfnb.com, b)  |
+-------------------------+
| 8 ? ? ? ? ? ? ? ? ? ? ? |
+-------------------------+

1. substr substring(str, pos[, len]):从str的pos位置开始，截取子字符串，截取len的长度，如果不传len，截取余下所有。

spark-sql> select substr("www.qfnb.com",5);
+--------------------------------------+
| substr(www.qfnb.com, 5, 2147483647)  |
+--------------------------------------+
| qfnb.com ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+--------------------------------------+

1. substring_index(str, delim, count):将字符串str使用delim进行分割，返回强count个使用delim拼接的子字符串。

spark-sql> select substr("www.qfnb.com",".",2);
+------------------------------------------+
| substr(www.qfnb.com, CAST(. AS INT), 2)  |
+------------------------------------------+
| ww ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+------------------------------------------+

1. concat(str1, str2)拼接字符串

spark-sql> select concat("www.qfnb.com",".",2);
+---------------------------------------------+
| concat(www.qfnb.com, ., CAST(2 AS STRING))  |
+---------------------------------------------+
| www.qfnb.com.2 ? ? ? ? ? ? ? ? ? ? ? ? ? ?  |
+---------------------------------------------+

1. concat_ws(separator, str1, str2):使用指定分隔符来拼接字符串

spark-sql> select concat_ws("www.qfnb.com",".",2);
+------------------------------------------------+
| concat_ws(www.qfnb.com, ., CAST(2 AS STRING))  |
+------------------------------------------------+
| .www.qfnb.com2 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+------------------------------------------------+

统计函数

略

特殊

• array:返回数组 collect_set：返回一个元素不重复的set集合 collect_list：返回一个元素可重复的list集合 split(str, regex):使用regex分隔符将str进行切割，返回一个字符串数组 explode(array):将一个数组，转化为多行 ? ?cast(type1 as type2)：将数据类型type1的数据转化为数据类型type2 eg：使用sql方式统计WordCount

select 
 ? tmp.word, 
 count(1) counts 
from (
 ?select 
 ?      explode(split(line, "\\s+")) word 
 ?from test_wc
) tmp 
group by tmp.word 
order by counts desc, tmp.word;

实现方式分类

• UDF(User Defined function)用户自定义函数 一路输入，一路输出，比如year，date_add, instr

• UDAF(User Defined aggregation function)用户自定义聚合函数 多路输入，一路输出,常见的聚合函数，count、sum、collect_list

• UDTF(User Defined table function)用户自定义表函数 一路输入，多路输出，explode

• 开窗函数 Row_Number() --->分组topN的求解

select
  tmp.* 
from (
 ?select
 ?  name,
 ?  age,
 ?  married,
 ?  height,
 ?  row_number() over(partition by married order by height) rank
 ?from teacher
) tmp
where tmp.rank < 3

1.5.3 自定义函数

概述

当系统提供的这些函数，满足不了咱们的需要的话，就只能进行自定义相关的函数，一般自定义的函数两种，UDF和UDAF。

UDF

一路输入，一路输出，完成就是基于scala函数。

通过模拟获取字符串长度的udf来学习自定义udf操作。

代码实现

object _01SparkSQLUDFOps {
 ? ?def main(args: Array[String]): Unit = {
 ? ? ? ?// 创建执行入口
 ? ? ? ?val spark = SparkSession.builder()
 ? ? ? ? ? ? ? ? ?  .master("local[*]")
 ? ? ? ? ? ? ? ? ?  .appName("SparkSQLUDF")
 ? ? ? ? ? ? ? ? ?  .getOrCreate()
 ? ? ? ?// 导入隐式转换及构建数据
 ? ? ? ?import spark.implicits._
 ? ? ? ?val rdd = spark.sparkContext.parallelize(List(
 ? ? ? ? ? ?"songhaining",
 ? ? ? ? ? ?"yukailu",
 ? ? ? ? ? ?"liuxiangyuan",
 ? ? ? ? ? ?"maningna"
 ? ? ?  ))
 ? ? ? ?//使用sparksession进行udf和udaf的注册
// ? ? ?  spark.udf.register[Int, String]("myLen", (str:String) => myStrLength(str))
// ? ? ?  spark.udf.register[Int, String]("myLen", str => myStrLength(str))
 ? ? ? ?spark.udf.register[Int, String]("myLen", myStrLength)
 ? ? ? ?// 创建表
 ? ? ? ?val df = rdd.toDF("name")
 ? ? ? ?df.createOrReplaceTempView("test")
?
 ? ? ? ?//求取每个字符串的长度
 ? ? ? ?val sql =
 ? ? ? ? ? ?"""
 ? ? ? ? ? ? ?|select
 ? ? ? ? ? ? ?|  name,
 ? ? ? ? ? ? ?|  length(name) nameLen,
 ? ? ? ? ? ? ?|  myLen(name) myNameLen
 ? ? ? ? ? ? ?|from test
 ? ? ? ? ? ?""".stripMargin
 ? ? ? ?spark.sql(sql).show()
?
 ? ? ? ?spark.stop()
 ?  }
 ? ?//自定义udf
 ? ?def myStrLength(str:String):Int = str.length
}

Guff_hys_python数据结构,大数据开发学习,python实训项目-CSDN博客