目录
12. 窗口函数
目标
理解窗口操作的语义, 掌握窗口函数的使用
步骤
案例1, 第一名和第二名
窗口函数介绍
案例2, 最优差值
12.1 第一名和第二名案例
掌握如何使用 SQL
和 DataFrame
完成名次统计, 并且对窗口函数有一个模糊的认识, 方便后面的启发
12.1.1 需求介绍
数据集 :product
: 商品名称 categroy
: 类别 revenue
: 收入
12.1.2 需求分析
从数据集中得到每个类别收入第一的商品和收入第二的商品;关键点是, 每个类别, 收入前两名
方案1: 使用常见语法子查询
-
问题1:
Spark
和Hive
这样的系统中, 有自增主键吗? 没有 -
问题2: 为什么分布式系统中很少见自增主键? 因为分布式环境下数据在不同的节点中, 很难保证顺序
-
解决方案: 按照某一列去排序, 取前两条数据
-
遗留问题: 不容易在分组中取每一组的前两个
SELECT * FROM productRevenue ORDER BY revenue LIMIT 2
方案1不可行
方案2: 计算每一个类别的按照收入排序的序号, 取每个类别中的前两个
思路步骤
-
按照类别分组
-
每个类别中的数据按照收入排序
-
为排序过的数据增加编号
-
取得每个类别中的前两个数据作为最终结果
使用 SQL
就不太容易做到, 需要一个语法, 叫做窗口函数
12.1.3 代码编写
1.创建初始环境
class WindowFunction {
@Test
def firstSecond(): Unit = {
val spark = SparkSession.builder()
.appName("window")
.master("local[6]")
.getOrCreate()
import spark.implicits._
val data = Seq(
("Thin", "Cell phone", 6000),
("Normal", "Tablet", 1500),
("Mini", "Tablet", 5500),
("Ultra thin", "Cell phone", 5000),
("Very thin", "Cell phone", 6000),
("Big", "Tablet", 2500),
("Bendable", "Cell phone", 3000),
("Foldable", "Cell phone", 3000),
("Pro", "Tablet", 4500),
("Pro2", "Tablet", 6500)
)
val source = data.toDF("product", "category", "revenue")
}
}
方式一: SQL
语句
SELECT
product,
category,
revenue
FROM (
SELECT
product,
category,
revenue,
dense_rank() OVER (PARTITION BY category ORDER BY revenue DESC) as rank
FROM productRevenue) tmp
WHERE
rank <= 2
窗口函数在 SQL
中的完整语法如下:
function OVER (PARITION BY ... ORDER BY ... FRAME_TYPE BETWEEN ... AND ...)
方式二: 使用 DataFrame
的命令式 API
val window: WindowSpec = Window.partitionBy('category)
.orderBy('revenue.desc)
source.select('product, 'category, 'revenue, dense_rank() over window as "rank")
.where('rank <= 2)
.show()
-
WindowSpec
: 窗口的描述符, 描述窗口应该是怎么样的 -
dense_rank() over window
: 表示一个叫做dense_rank()
的函数作用于每一个窗口
12.1.4 总结
-
在
Spark
中, 使用SQL
或者DataFrame
都可以操作窗口 -
窗口的使用有两个步骤
-
定义窗口规则
-
定义窗口函数
-
-
在不同的范围内统计名次时, 窗口函数非常得力
12.2 窗口函数
目标
了解窗口函数的使用方式, 能够使用窗口函数完成统计
步骤
窗口函数的逻辑
窗口定义部分
统计函数部分
12.2.1 窗口函数的逻辑
(1)从 逻辑 上来讲, 窗口函数执行步骤大致可以分为如下几步
dense_rank() OVER (PARTITION BY category ORDER BY revenue DESC) as rank
(a): 根据 PARTITION BY category
, 对数据进行分组
(b):分组后, 根据 ORDER BY revenue DESC
对每一组数据进行排序
(c):在 每一条数据 到达窗口函数时, 套入窗口内进行计算
(2)从语法的角度上讲, 窗口函数大致分为两个部分
dense_rank() OVER (PARTITION BY category ORDER BY revenue DESC) as rank
-
函数部分
dense_rank()
-
窗口定义部分
PARTITION BY category ORDER BY revenue DESC
窗口函数和 GroupBy
最大的区别, 就是 GroupBy
的聚合对每一个组只有一个结果, 而窗口函数可以对每一条数据都有一个结果
说白了, 窗口函数其实就是根据当前数据, 计算其在所在的组中的统计数据
12.2.2 窗口定义部分
dense_rank() OVER (PARTITION BY category ORDER BY revenue DESC) as rank
(1)Partition
定义
控制哪些行会被放在一起, 同时这个定义也类似于 Shuffle
, 会将同一个分组的数据放在同一台机器中处理
(2)Order
定义
在一个分组内进行排序, 因为很多操作, 如 rank
, 需要进行排序
(3)Frame
定义
释义
-
窗口函数会针对 每一个组中的每一条数据 进行统计聚合或者
rank
, 一个组又称为一个Frame
-
分组由两个字段控制,
Partition
在整体上进行分组和分区 -
而通过
Frame
可以通过 当前行 来更细粒度的分组控制举个栗子, 例如公司每月销售额的数据, 统计其同比增长率, 那就需要把这条数据和前面一条数据进行结合计算了
有哪些控制方式?
12.2.3 函数部分
dense_rank() OVER (PARTITION BY category ORDER BY revenue DESC) as rank
如下是支持的窗口函数
类型 | 函数 | 解释 |
---|---|---|
排名函数 |
|
|
|
| |
|
| |
分析函数 |
| 获取这个组第一条数据 |
| 获取这个组最后一条数据 | |
|
| |
|
| |
聚合函数 |
| 所有的 |
12.2.4 总结
窗口操作分为两个部分
-
窗口定义, 定义时可以指定
Partition
,Order
,Frame
-
函数操作, 可以使用三大类函数, 排名函数, 分析函数, 聚合函数
12.3 最优差值案例
目标
能够针对每个分类进行计算, 求得常见指标, 并且理解实践上面的一些理论
步骤
需求介绍
代码实现
12.3.1 需求介绍
(1)源数据集
(2)需求
统计每个商品和此品类最贵商品之间的差值
(3)目标数据集
12.3.2 代码实现
(1)步骤
创建数据集
创建窗口, 按照
revenue
分组, 并倒叙排列应用窗口
(2)代码
val spark = SparkSession.builder()
.appName("window")
.master("local[6]")
.getOrCreate()
import spark.implicits._
import org.apache.spark.sql.functions._
val data = Seq(
("Thin", "Cell phone", 6000),
("Normal", "Tablet", 1500),
("Mini", "Tablet", 5500),
("Ultra thin", "Cell phone", 5500),
("Very thin", "Cell phone", 6000),
("Big", "Tablet", 2500),
("Bendable", "Cell phone", 3000),
("Foldable", "Cell phone", 3000),
("Pro", "Tablet", 4500),
("Pro2", "Tablet", 6500)
)
val source = data.toDF("product", "category", "revenue")
val windowSpec = Window.partitionBy('category)
.orderBy('revenue.desc)
source.select(
'product, 'category, 'revenue,
((max('revenue) over windowSpec) - 'revenue) as 'revenue_difference
).show()