Spark Sql窗口函数Window的使用（1）

最新推荐文章于 2024-07-05 18:03:37 发布
最新推荐文章于 2024-07-05 18:03:37 发布
阅读量2.8k 收藏 10
点赞数 4
分类专栏： 实战-SparkSql 文章标签： spark sql实战 spark实战 窗口函数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zg_hover/article/details/109324902
版权

窗口函数的使用（1）

窗口是非常重要的统计工具，很多数据库都支持窗口函数。Spark从1.4开始支持窗口（window）函数。它主要有以下一些特点：

  • 先对在一组数据行上进行操作，这组数据被称为Frame。
  • 一个Frame对应当前处理的行
  • 通过聚合/窗口函数为每行返回一个新值
  • 可以使用SQL语法或DataFrame API。

准备工作

  • 准备依赖库
import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.types._
import org.apache.spark.sql.functions._
  • 准备数据
case class Salary(depName: String, empNo: Long, name: String, 
    salary: Long, hobby: Seq[String])
val empsalary = Seq(
  Salary("sales",     1,  "Alice",  5000, List("game",  "ski")),
  Salary("personnel", 2,  "Olivia", 3900, List("game",  "ski")),
  Salary("sales",     3,  "Ella",   4800, List("skate", "ski")),
  Salary("sales",     4,  "Ebba",   4800, List("game",  "ski")),
  Salary("personnel", 5,  "Lilly",  3500, List("climb", "ski")),
  Salary("develop",   7,  "Astrid", 4200, List("game",  "ski")),
  Salary("develop",   8,  "Saga",   6000, List("kajak", "ski")),
  Salary("develop",   9,  "Freja",  4500, List("game",  "kajak")),
  Salary("develop",   10, "Wilma",  5200, List("game",  "ski")),
  Salary("develop",   11, "Maja",   5200, List("game",  "farming"))).toDS
empsalary.createTempView("empsalary")
empsalary.show()

数据输出如下：

+---------+-----+------+------+---------------+
|  depName|empNo|  name|salary|          hobby|
+---------+-----+------+------+---------------+
|    sales|    1| Alice|  5000|    [game, ski]|
|personnel|    2|Olivia|  3900|    [game, ski]|
|    sales|    3|  Ella|  4800|   [skate, ski]|
|    sales|    4|  Ebba|  4800|    [game, ski]|
|personnel|    5| Lilly|  3500|   [climb, ski]|
|  develop|    7|Astrid|  4200|    [game, ski]|
|  develop|    8|  Saga|  6000|   [kajak, ski]|
|  develop|    9| Freja|  4500|  [game, kajak]|
|  develop|   10| Wilma|  5200|    [game, ski]|
|  develop|   11|  Maja|  5200|[game, farming]|
+---------+-----+------+------+---------------+

注：例子代码来源于网络。

基本Frame操作

通过partitionBy来获取基本Frame，然后基于获取到的Frame，来进行各种操作。

一个基本的Frame有以下的特征：

  • 通过在一列或多列上调用函数Window.partitionBy来创建Frame；
  • 每一行都有与之对应的frame；
  • 在同一个分区中，每一行的frame相同，但Ordered frame除外。
  • Aggregate/Window函数能够使用在frame的每个行上，并得到一个值。

在示例数据中，我们来计算以下的值：

  • 使用函数avg计算部门的平均工资
  • 使用函数sum来计算部门的总工资

代码如下：

val overCategory = Window.partitionBy('depName)

// 基于窗口求平均值，和，最大值，最小值
val df = empsalary.withColumn("salaries", collect_list('salary) over overCategory).
				   withColumn("average_salary", (avg('salary) over overCategory).cast("int")).
				   withColumn("total_salary", sum('salary) over overCategory).
				   withColumn("min", min('salary) over overCategory).
				   withColumn("max", max('salary) over overCategory).
				   select("depName", "empNo", "name", "salary", "salaries", "average_salary", "total_salary","min","max")

df.show(false)

代码分步说明：

（1）创建一个Frame：val overCategory = Window.partitionBy('depName)

（2）基于Frame来计算平均值：

withColumn("average_salary", (avg('salary) over overCategory).cast("int")).

（3）基于Frame来求和，最大值，最小值等

withColumn("total_salary", sum('salary) over overCategory).
结果分析：
+---------+-----+------+------+--------------------+--------------+------------+----+----+
|  depName|empNo|  name|salary|            salaries|average_salary|total_salary| min| max|
+---------+-----+------+------+--------------------+--------------+------------+----+----+
|  develop|    7|Astrid|  4200|[4200, 6000, 4500...|          5020|       25100|4200|6000|
|  develop|    8|  Saga|  6000|[4200, 6000, 4500...|          5020|       25100|4200|6000|
|  develop|    9| Freja|  4500|[4200, 6000, 4500...|          5020|       25100|4200|6000|
|  develop|   10| Wilma|  5200|[4200, 6000, 4500...|          5020|       25100|4200|6000|
|  develop|   11|  Maja|  5200|[4200, 6000, 4500...|          5020|       25100|4200|6000|
|    sales|    1| Alice|  5000|  [5000, 4800, 4800]|          4866|       14600|4800|5000|
|    sales|    3|  Ella|  4800|  [5000, 4800, 4800]|          4866|       14600|4800|5000|
|    sales|    4|  Ebba|  4800|  [5000, 4800, 4800]|          4866|       14600|4800|5000|
|personnel|    2|Olivia|  3900|        [3900, 3500]|          3700|        7400|3500|3900|
|personnel|    5| Lilly|  3500|        [3900, 3500]|          3700|        7400|3500|3900|
+---------+-----+------+------+--------------------+--------------+------------+----+----+

从 结果中可以看出，结果是没有去重的，每一行都会有一个最后的结果。而且在窗口的每个Frame中的每行 结果 都不同。

对Frame进行排序操作

有序的Frame通过partitionBy and orderBy来进行创建。有序的Frame有以下几个特征：

  • 通过Window.partitionBy函数添加一个或多个列来创建
  • 在partitionBy函数后面添加orderBy列
  • 每一行都对应一个frame
  • frame的行和相同分区的每一行不同。默认，frame包含包含 所有的前面的行和目前行。
  • Aggregate/Window函数可以用到每一行row+frame，并产生一个值 。
示例代码
// 创建有序窗口
val overCategory = Window.partitionBy('depName).orderBy('salary desc)
// 对有序窗口进行运算
val df = empsalary.withColumn("salaries", collect_list('salary) over overCategory).
							withColumn("avg_salary", (avg('salary) over overCategory).cast("int")).
							withColumn("total_salary", sum('salary) over overCategory).
							select("depName", "empNo", "name", "salary", "salaries", "avg_salary", "total_salary")

df.show(false)
结果输出
scala> df.show(false)
+---------+-----+------+------+------------------------------+----------+------------+
|depName  |empNo|name  |salary|salaries                      |avg_salary|total_salary|
+---------+-----+------+------+------------------------------+----------+------------+
|develop  |8    |Saga  |6000  |[6000]                        |6000      |6000        |
|develop  |10   |Wilma |5200  |[6000, 5200, 5200]            |5466      |16400       |
|develop  |11   |Maja  |5200  |[6000, 5200, 5200]            |5466      |16400       |
|develop  |9    |Freja |4500  |[6000, 5200, 5200, 4500]      |5225      |20900       |
|develop  |7    |Astrid|4200  |[6000, 5200, 5200, 4500, 4200]|5020      |25100       |
|sales    |1    |Alice |5000  |[5000]                        |5000      |5000        |
|sales    |3    |Ella  |4800  |[5000, 4800, 4800]            |4866      |14600       |
|sales    |4    |Ebba  |4800  |[5000, 4800, 4800]            |4866      |14600       |
|personnel|2    |Olivia|3900  |[3900]                        |3900      |3900        |
|personnel|5    |Lilly |3500  |[3900, 3500]                  |3700      |7400        |
+---------+-----+------+------+------------------------------+----------+------------+

从结果可以看出，基于排序窗口进行的计算不再基于划分窗口的字段，而是基于排序的字段。排序的字段值相同时，窗口函数计算的结果相同，否则每一行的结果都不同。

而且，排序窗口的聚合计算具有累加性，排序好序的窗口中的每个值都认为是一个分组。

以上例子是通过salary字段值进行的排序，在进行窗口计算时，认为该字段排好序的每个值都是一个分组。

在分组中进行排名

下表列出了Spark支持的所有等级函数。

函数描述（在分区窗口内）注意
rank对每个分组行进行排名若排序字段的数据重复，则跳过该排名。例如：1 2 2 4…，若第2个和第3个值相同，第4行会排在第4位，这样第3位就会被跳过。
dense_rank对每个分组进行排名若排序字段的数据重复，不会跳过排名。例如：1 2 2 4…，在第4行的排名是3。
row_number行编号对同一个窗口的排序行进行编号。
ntileNtile id在窗口的每个分组内，再把数据分成多个堆。参数是堆的个数，比如：5行的分组，分成2对，会先按3个一堆进行分堆。
percent_rank(rank-1)/(total_rows-1)当需要获取窗口中每个分组的前25%的数据时，占比时很有用。
在窗口中使用rank()和dense_rank()函数
// 创建排序窗口
val overCategory = Window.partitionBy('depName).orderBy('salary desc)

// 在排序窗口中使用rank函数
val df = empsalary.withColumn("salaries", collect_list('salary) over overCategory).
					withColumn("rank", rank() over overCategory).
					withColumn("dense_rank", dense_rank() over overCategory).
					withColumn("row_number", row_number() over overCategory).
					withColumn("ntile", ntile(2) over overCategory).
					withColumn("percent_rank", percent_rank() over overCategory).
					select("depName", "empNo", "name", "salary", "rank", "dense_rank", "row_number", "ntile", "percent_rank")

执行完这两行代码后，就得到了一下的df：

scala> df.show(false)
+---------+-----+------+------+----+----------+----------+-----+------------+
|depName  |empNo|name  |salary|rank|dense_rank|row_number|ntile|percent_rank|
+---------+-----+------+------+----+----------+----------+-----+------------+
|develop  |8    |Saga  |6000  |1   |1         |1         |1    |0.0         |
|develop  |10   |Wilma |5200  |2   |2         |2         |1    |0.25        |
|develop  |11   |Maja  |5200  |2   |2         |3         |1    |0.25        |
|develop  |9    |Freja |4500  |4   |3         |4         |2    |0.75        |
|develop  |7    |Astrid|4200  |5   |4         |5         |2    |1.0         |
|sales    |1    |Alice |5000  |1   |1         |1         |1    |0.0         |
|sales    |3    |Ella  |4800  |2   |2         |2         |1    |0.5         |
|sales    |4    |Ebba  |4800  |2   |2         |3         |2    |0.5         |
|personnel|2    |Olivia|3900  |1   |1         |1         |1    |0.0         |
|personnel|5    |Lilly |3500  |2   |2         |2         |2    |1.0         |
+---------+-----+------+------+----+----------+----------+-----+------------+

从以上结果可以看出，在使用rank()函数时empNo为9的行排在了第4位，跳过了第3位。而dense_rank()却不会跳过。这样可以满足多种分组需求。

sql形式

可以把dataframe的形式，写成spark sql，如下：

SELECT
	depName, 
	empNo, 
	name, 
	salary,
	rank,
	dense_rank,
	row_number,
	ntile,
	percent_rank
FROM (
  SELECT
	depName, 
	empNo, 
	name, 
	salary,
    rank() OVER (PARTITION BY depName ORDER BY salary DESC) as rank,
    dense_rank() OVER (PARTITION BY depName ORDER BY salary DESC) as dense_rank,
    ntile(3) OVER (PARTITION BY depName ORDER BY salary DESC) as ntile,
    row_number() OVER (PARTITION BY depName ORDER BY salary DESC) as row_number,
    percent_rank() OVER (PARTITION BY depName ORDER BY salary DESC) as percent_rank
  FROM empsalary) tmp
WHERE
  rank <= 2

这里要注意，该SQL的最后添加了一个条件，通过添加条件可以很容易过滤数据，从而得到自己想要的结果。比如，我们希望能够通过窗口函数来计算：每个部门薪水排在前2名的员工。

窗口函数实战

找出每个部门薪水排在前3名的所有员工，注意：是所有员工。

解题思路：要找出每个部门薪水排在前3的所有员工，就首先需要按部门对员工进行分组。然后对组内的员工进行按薪水降序排序，再取前3名。

另外要注意，这里是找出所有的员工，包括重复的，所以应该使用dense_rank()函数，这样就不会溜掉薪水相同的员工名单。

sql形式的解法
SELECT
	depName, 
	empNo, 
	name, 
	salary,
	dense_rank
FROM (
  SELECT
		depName, 
		empNo, 
		name, 
		salary,
    dense_rank() OVER (PARTITION BY depName ORDER BY salary DESC) as dense_rank
  FROM empsalary) tmp
WHERE
  dense_rank <= 3
dataframe API形式的解法
// 创建排序窗口
val overCategory = Window.partitionBy('depName).orderBy('salary desc)

// 在排序窗口中使用rank函数
val df = empsalary.withColumn("dense_rank", dense_rank() over overCategory).
							filter('dense_rank <= 3).
							select("depName", "empNo", "name", "salary", "dense_rank")

小结

本文介绍了 基本窗口函数的使用，并介绍了排序窗口和基本窗口的不同点，并通过实际的例子进行了分析。

一 铭
关注
专栏目录
Window 窗口函数 (Spark Sql)
m0_69097184的博客
10-17 950
在 Spark SQL 中，Window 函数是一种用于在查询结果集中执行聚合、排序和分析操作的强大工具。它允许你在查询中创建一个窗口，然后对窗口内的数据进行聚合计算。
SparkSQL | 窗口函数
小哲数据
03-01 4070
import pandas as pd from pyspark.sql import SparkSession from pyspark.conf import SparkConf from pyspark.sql.types import * from pyspark.sql import functions as F, Window # 配置集群 config = SparkConf() ...
SparkStreaming -- 03 【window函数，与SparkSQL的整合】
xiaoxiao______的博客
12-08 400
文章目录1、window函数操作1.1、 window函数的简介1.2、 常用的窗口函数1.3、 案例演示：1.3.1、1.3.2、1.3.3、2、sparkStreaming和SparkSQL的整合 1、window函数操作 1.1、 window函数的简介 SparkStreaming提供了滑动窗口的操作。这样的话，就可以计算窗口内的n个micro-batch的数据，进行聚合. 窗口有两个参数： 窗口大小：指的就是有几个单位时间（time unit）的micro-batch 滑动周期：类似于定时器
SQL窗口函数详解
最新发布
weixin_45771607的博客
07-05 1300
窗口函数（Window Functions）是SQL中的一种功能强大的工具，用于对查询结果集中的每一行进行计算，而无需对数据进行分组（GROUP BY）。与聚合函数不同，窗口函数不会将行汇总到一条记录中，而是保留原始行，并在此基础上添加计算结果。窗口函数常用于排名、累积和、滑动窗口计算等。窗口帧定义了窗口函数计算的行范围。窗口帧由ROWS或RANGE子句指定。从2、3中，不难看出，这有一个易混淆的点（也是本人混淆过的点），那就是。
SQL Server Window函数
编程工作室
03-04 708
SQL Server Window函数根据一组行计算聚合值，并为每个组返回多行。SQL Server 一些常用的Window函数如下表所示 - 函数 描述 CUME_DIST 计算一组值中的值的累积分布 DENSE_RANK 为结果的分区内的每一行分配一个排名值，排名值没有间隙。 FIRST_VALUE 获取结果集的有序分区中第一行的值。 LAG 提...
Spark SQL 内置函数（六）Window Functions（基于 Spark 3.2.0）
热门推荐
Shockang的博客
12-05 1万+
前言 本文隶属于专栏《1000个问题搞定大数据技术体系》，该专栏为笔者原创，引用请注明来源，不足和错误之处请在评论区帮忙指出，谢谢！ 本专栏目录结构和参考文献请见1000个问题搞定大数据技术体系 正文 cume_dist() 描述 计算值相对于分区中所有值的位置。 实践 dense_rank() 描述 计算值组内某个值的排名。结果是 1 加上先前分配的排名值。与函数排名不同，dense_rank不会在排名序列中产生间隙。 实践 lag(input[, offset[, default]]) 描述
spark-sql time window使用
sanhongbo的博客
12-08 1338
spark sql time window 使用方式： window(t1.eventTime, “5 minute”, “1 minute”) 加在sql中通过grooup by 进行离线数据的开窗操作。
spark sql窗口函数window的使用（1）
03-16
Spark SQL中的窗口函数（window function）是一种可以在数据集的子集上进行聚合计算的函数。它可以在不改变原始数据集的情况下，对数据进行分组、排序、排名等操作，从而实现更加复杂的数据分析和处理。 窗口函数的...
spark-window:Spark 窗口函数的实验
06-05
该项目旨在改进 Spark SQL 的窗口函数。 它目前正在进行中。 此实现需要 SPARK 1.4.0 提供的功能。 改进 与当前实现相比的优点是： 本机 Spark-SQL，当前实现仅依赖于 Hive UDAF。 新的实现使用 Spark SQL 聚合。 ...
【Spark原理系列】 Window窗口计算原理用法示例源码分析
wang2leee的博客
09-28 536
Spark的窗口计算是通过以下步骤实现的：数据分区：首先，数据集根据指定的分区键进行分区。每个分区中的数据将作为一个独立的窗口进行计算。排序：在每个分区内，根据指定的排序规则对数据进行排序。这确保了在窗口函数应用之前，数据按照正确的顺序进行处理。窗口框架定义：根据窗口规范中定义的窗口帧类型、起始边界和结束边界，确定每个数据行所属的窗口范围。窗口函数计算：在每个窗口内，应用指定的窗口函数进行计算。窗口函数可以是聚合函数（如、）或分析函数（如、）。窗口函数结果返回：计算完窗口函数后，将结果返回给调用方。通常，结
spark的window窗口函数
01-09
标题“Spark的window窗口函数”指的是Spark SQL或DataFrame API中用于处理时间相关数据的函数。这些函数允许用户定义一个时间窗口，然后在该窗口内执行聚合操作，如计数、求和、平均等。 描述中的...
SparkSql中的窗口函数
大数据
07-20 1969
在Spark中,使用SQL或者DataFrame都可以操作窗口。窗口的使用有两个步骤1）定义窗口规则；2）定义窗口函数。在不同的范围内统计名次时,窗口函数非常得力。控制哪些行会被放在一起,会将同一个分组的数据放在同一台机器中处理窗口函数会针对每一个组中的每一条数据进行统计聚合或者rank,一个组又称为一个Frame分组由两个字段控制,Partition在整体上进行分组和分区而通过Frame可以通过当前行来更细粒度的分组控制"行号"来表示。通过某一个列的差值来表示。Partition。............
20. spark sql window函数初探：
元元的李树专栏
11-08 888
20. spark sql window函数初探： 先附上相关网站链接，后续给出自己的伪代码示例： https://blog.csdn.net/weixin_42379136/article/details/86675049 https://blog.csdn.net/Shie_3/article/details/82890897 https://databricks.com/blog/...
【spark内置函数】window
hyj
08-04 637
一、说明 这里的window函数是属于functions.scala文件中的内置函数。 window函数是在spark2.0.0中新增的API函数。 这里选取股票数据为测试数据：测试数据下载地址。 时间一定要整理成yyyy-MM-dd hh:mm:ss格式，其他格式内置函数不识别。 二、数据样式 --表头 id,date,openPrice,highestPrice,lowestPrice,closePrice,exchangeNum 代码,时间,开盘价,最高价,最低价,收盘价,成交量(手) -
SparkSql窗口函数源码分析（第一部分）
xiaoluobutou的专栏
02-09 490
窗口函数定义的接口类（在OVER子句或Window子句中指定）WindowSpecDefinition：定义了一个窗口函数应该包含哪些元素（定义了输入行的分区方式（按哪个字段分区）、定义分区内数据的该怎么排序（SortOrder类，按哪个字段排，升序还是降序）、定义了分区中的窗口框架（WindowFrame类））WindowSpecReference：代表引用一个窗口函数，比如下面这种用法，这种写法的好处就是：over 后面内容如果相同的话，可以只用写一次。
SQL进阶（四）：Window 函数技巧
qq_33489955的博客
03-13 1431
A通过每日登录的用户数，分析登录用户总数的趋势日期当日登录人数2023-07-011122023-07-024352023-07-031011我们希望输出的表是日期当日登录人数2023-07-011122023-07-023232023-07-03576。
SparkSQL创建RDD：Window算子【文字说明+关键代码】
wyqwilliam的博客
08-05 596
package com.bjsxt.sparkstreaming; import java.util.Arrays; import org.apache.spark.SparkConf; import org.apache.spark.api.java.function.FlatMapFunction; import org.apache.spark.api.java.function.Fu...
如何在SQL Server中使用Window函数
culuo4781的博客
07-27 1617
All database users know about regular aggregate functions which operate on an entire table and are used with a GROUP BY clause. But very few people use Window functions in SQL. These operate on a ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值