Spark SQL——开窗函数

标签: Spark SQL  开窗函数

开窗函数和聚合函数一样,都是对行的集合组进行聚合计算。

开窗用于为行定义一个窗口(这里的窗口是指运算将要操作的行的集合),它对一组值进行操作,不需要使用group by子句对数据进行分组,能够在同一行中同时返回基础行的列和聚合列。

开窗函数调用格式为:函数名(列) OVER(选项)

第一类:聚合开窗函数 --> 排列函数(列)OVER(选项),这里的选项可以是PARTITION BY 子句,但不可以是ORDER BY子句
第二类:排列开窗函数 --> 排序函数(列)OVER(选项),这里的选项可以是ORDER BY子句,也可以是OVER(PARTITION BY子句 ORDER BY子句),但是不可以是PARTITION BY子句。

代码示例:

def main(args: Array[String]): Unit = {
	val sparkConf = new SparkConf().setAppName("score").setMaster("local[*]")

	val sparkSession = SparkSession.builder().config(sparkConf).getOrCreate()
	
	import sparkSession.implicits._
	val scoreDF = sparkSession.sparkContext.makeRDD(Array(Score("a1", 1, 80),
														Score("a2", 1, 78),
														Score("a3", 1, 95),
														Score("a4", 2, 74),
														Score("a5", 2, 92),
														Score("a6", 3, 99),
														Score("a7", 3, 99),
														Score("a8", 3, 45),
														Score("a9", 3, 55),
														Score("a10", 3, 78))).toDF("name", "class
														", "score")
	scoreDF.createOrReplaceTempView("score")
	scoreDF.show()
}

1、聚合开窗函数

OVER关键字表示把聚合函数当成聚合开窗函数而不是聚合函数,SQL标准允许将所有聚合函数用作聚合开窗函数。
在这里插入图片描述
上面例子中开窗函数COUNT(*) OVER()对于查询结果的每一行都返回所有符合条件的行的条数。OVER关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果OVER关键字后的括号中的选项为空,则开窗函数会对结果集中的所有行进行聚合运算。

开窗函数的OVER关键字后括号中的可以使用PARTITION BY子句来定义行的分区来供进行聚合计算。与GROUP BY子句不同,PARTITION BY子句创建的分区是独立于结果集的,创建的分区只是供进行聚合计算的,而且不同的开窗函数所创建的分袂也不互相影响。

下面的SQL语句用于显示按照班级分组后每组的人数:

sparkSession.sql("select name, class, score, count(name) over(partition by class)
name_count from score").show()

查询结果如下所示:
在这里插入图片描述
OVER(PARTITION BY class)表示对结果集按照class进行分区,并且计算当前行所属的组的聚合计算结果。在同一个select语句中可以同时使用多个开窗函数,而且这些开窗函数并不会相互干扰。
比如下面SQL语句用于显示每一个人员的信息、所属城市的人员数和同龄人的人数。

sparkSession.sql("select name, class, score, count(name) over(partition by class)
name_count1 from score").show()
sparkSession.sql("select name, class, score, count(name) over(partition by score)
name_count2 from score").show()

2、排序开窗函数

对于排序开窗函数来讲,支持的开窗函数分别为ROW_NUMBER(行号)、RANK(排名)、DENSE_RANK(密集排名)和NTILE(分组排名)

sparkSession.sql("select name, class, score, row_number() over(order by score) rank from
score").show()
sparkSession.sql("select name, class, score, rank() over(order by score) rank from
score").show()
sparkSession.sql("select name, class, score, dense_rank() over(order by score) rank from
score").show()
sparkSession.sql("select name, class, score, ntile(6) over(order by score) rank from
score").show()

查询的结果分别为:

//第一个语句
+----+-----+-----+----+
|name|class|score|rank|
+----+-----+-----+----+
| a8| 3| 45| 1|
| a9| 3| 55| 2|
| a4| 2| 74| 3|
| a2| 1| 78| 4|
| a10| 3| 78| 5|
| a1| 1| 80| 6|
| a5| 2| 92| 7|
| a3| 1| 95| 8|
| a6| 3| 99| 9|
| a7| 3| 99| 10|
+----+-----+-----+----+

//第二个语句
+----+-----+-----+----+
|name|class|score|rank|
+----+-----+-----+----+
| a8| 3| 45| 1|
| a9| 3| 55| 2|
| a4| 2| 74| 3|
| a2| 1| 78| 4|
| a10| 3| 78| 4|
| a1| 1| 80| 6|
| a5| 2| 92| 7|
| a3| 1| 95| 8|
| a6| 3| 99| 9|
| a7| 3| 99| 9|
+----+-----+-----+----+

//第三个语句
+----+-----+-----+----+
|name|class|score|rank|
+----+-----+-----+----+
| a8| 3| 45| 1|
| a9| 3| 55| 2|
| a4| 2| 74| 3|
| a2| 1| 78| 4|
| a10| 3| 78| 4|
| a1| 1| 80| 5|
| a5| 2| 92| 6|
| a3| 1| 95| 7|
| a6| 3| 99| 8|
| a7| 3| 99| 8|
+----+-----+-----+----+

//第四个语句
+----+-----+-----+----+
|name|class|score|rank|
+----+-----+-----+----+
| a8| 3| 45| 1|
| a9| 3| 55| 1|
| a4| 2| 74| 2|
| a2| 1| 78| 2|
| a10| 3| 78| 3|
| a1| 1| 80| 3|
| a5| 2| 92| 4|
| a3| 1| 95| 4|
| a6| 3| 99| 5|
| a7| 3| 99| 6|
+----+-----+-----+----+

对于row_number() over(order by score) as rownum来说,这个排序开窗函数是按照score升序方式来排序,并得出排序结果的序号。

对于rank() over(order by score) as rank来说,这个排序形容函数是按FSalary升序方式来排序并得出排序结果的排名号,这个函数求出来的排名结果可以并列,并列排名之后的排名将是并列的排名加上并列数(简单说每个人只有一种排名,然后出现两个并列第一名的情况,这时候排在两个第一名后面的人将是第三名,就是没有了第二名但是有两个第一名。)

对于dense_rank() over(order by score) as dense_rank来说,这个排序函数是按照score升序的方式来排序并得出排序结果的排名号,这个函数与rank()函数不同在于并列排名之后的排名只是并列排名加1

对于 ntile(6) over(order by score)as ntile 来说,这个排序函数是按 FSalary 升序的方式来排序,然后 6 等分成 6 个组,并显示所在组的序号。

排序函数和聚合开窗函数类似,支持在OVER子句中使用PARTITION BY语句,例如:

sparkSession.sql("select name, class, score, row_number() over(partition by class order
by score) rank from score").show()
sparkSession.sql("select name, class, score, rank() over(partition by class order by score)
rank from score").show()
sparkSession.sql("select name, class, score, dense_rank() over(partition by class order
by score) rank from score").show()
sparkSession.sql("select name, class, score, ntile(6) over(partition by class order by score)
rank from score").show()

需要注意一点,在排序开窗函数中使用PARTITION BY子句需要放置在ORDER BY子句之前。

版权声明:本文为weixin_44240370原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/weixin_44240370/article/details/103322615

智能推荐

spring cloud netflix (07) 服务的消费者(feign)

前言 完整知识点:spring cloud netflix 系列技术栈 Feign (同步通信 HTTP通信) feign是基于接口完成服务与服务之间的通信的 搭建Feign服务 项目结构 项目搭建 pom.xml application类 application.yml 使用feign完成服务与服务之间的通信 feign是基于接口完成服务与服务之间的通信的...

AtCoder Beginner Contest 174 E.Logs

AtCoder Beginner Contest 174 E.Logs 题目链接 到最后才发现是二分,菜菜的我/(ㄒoㄒ)/~~ 我们直接二分 [1,max{a[i]}][1,max\lbrace a[i]\rbrace][1,max{a[i]}] 即可,对每一个 midmidmid,每个数 a[i]a[i]a[i] 只需要切 a[i]−1mid\frac{a[i]-1}{mid}mi...

小程序基础与实战案例

小程序开发工具与基础 小程序开发准备: 申请小程序账号( appid ) 下载并安装微信开发者工具 具体步骤如下: 先进入 微信公众平台 ,下拉页面,把鼠标悬浮在小程序图标上 然后点击 小程序开发文档 照着里面给的步骤,就可以申请到小程序账号了。 然后就可以下载 开发者工具 了 下载完打开后的界面就是这个样子 下面让我们来新建一个小程序开发项目: 在AppID输入自己刚刚注册的AppID就可以,或...

VMware centOS7 下通过minikube部署Kubernetes

1、环境准备: VMware CentOS-7-x86_64 CPU:2*2core 内存:8G 宿主机和虚拟机需网络互通,虚拟机外网访问正常 Centos发行版版本查看:cat /etc/centos-release root用户操作 2、禁用swap分区 Kubernetes 1.8开始要求关闭系统的Swap,可暂时关闭或永久禁用, 使用 $ free -m 确认swap是否为开启状态 $ s...

逻辑回归与scikit-learn

欢迎关注本人的微信公众号AI_Engine LogisticRegression 算法原理 一句话概括:逻辑回归假设数据服从伯努利分布,通过极大化似然函数(损失函数)的方法,运用梯度下降或其他优化算法来求解参数,来达到将数据二分类的目的。 定义:逻辑回归(Logistic Regression)是一种用于解决二分类(0 or 1)问题的机器学习方法,用于估计某种事物的可能性(不是概率)。比如某用户...

猜你喜欢

指针OR数组?用他们来表达字符串又有何不同?

cocowy的编程之旅 在学习C语言的过程中我们经常可以看到或者听到这样一句话:数组其实等价于指针,例如: 在这里可以轻松的看出输出后他们的值相等,其实在计算机内存里面,p为本地变量,有着他自己的作用域。而指针变量q保存着这个数组的首地址,通过*号指向这个地址保存的变量值。 然而我们再看一个例子: 这个时候计算机报错,这是为什么呢? 其实原因很简单,指针说指向的这个字符串的地址是位于计算机代码段地...

广度搜索

广度搜索的基本使用方法 广度搜索不同于深度搜索,是一种一步一步进行的过程,每一个点只记录一遍。需要用到队列记录每一步可以走到的位置,找到目标位置输出步数即可。 用到的知识:结构体、队列 如图 首先我们需要定义一个结构体来存储每个遍历到的点和步数 广搜不会用到递归,所以可以直接在主函数里写,这里需要定义一个结构体队列 初始化队列并将起始点入列 遍历 完整代码...

NIO Socket 编程实现tcp通信入门(二)

1、NIO简介 NIO面向通道和缓冲区进行工作,数据总是从通道读取到缓冲区中,或者从缓冲区写入到通道中。可以双向传输数据,是同步非阻塞式IO。NIO还引入了选择器机制,从而实现了一个选择器监听多个底层通道,减少了线程并发数。用NIO实现socket的Tcp通信需要掌握下面三个知识点: Buffer 缓冲区 Channel 通道 Selector 选择器   2、java.nio.Buff...

[字节码系列]ObjectWeb ASM构建Method Monitor

      在前面的篇章中,我们看到Java Instrutment的强大能力,本篇,我们将介绍如何使用ObjectWeb ASM的字节码增强能力构建Method Monitor       1.什么是ObjectWeb ASM      ObjectWeb ...

Core Location 电子围栏:入门

原文:Geofencing with Core Location: Getting Started 作者:Andy Pereira 译者:kmyhy 更新说明:Andy Pereira 将本教程升级至 Xcode 9.3 和 Swift 4.1。 Geofencing 会在设备进入/离开指定的电子围栏时通知应用程序。它可以让你写出一些很酷的应用程序,当你从家里出来时触发通知,或者在附近出现最爱的商...