Apache DataFusion 窗口函数完全指南

Apache DataFusion 窗口函数完全指南

datafusion Apache DataFusion SQL Query Engine 项目地址: https://gitcode.com/gh_mirrors/datafu/datafusion

什么是窗口函数

窗口函数是SQL中一种强大的分析工具，它允许我们对一组相关的行执行计算，同时保持每行的独立性。与普通聚合函数不同，窗口函数不会将多行合并为单行输出，而是为每行返回一个计算结果。

在Apache DataFusion中，窗口函数通过OVER子句实现，这使得它们能够访问当前行所在"窗口"中的其他行数据。这种特性使得窗口函数非常适合用于排名、移动平均、累计求和等分析场景。

窗口函数基础语法

窗口函数的基本语法结构如下：

函数名(参数) OVER (
    [PARTITION BY 分组表达式]
    [ORDER BY 排序表达式 [ASC|DESC]]
    [窗口帧定义]
)

其中：

• PARTITION BY：将数据分成多个分区，窗口函数在每个分区内独立计算
• ORDER BY：确定分区内行的处理顺序
• 窗口帧定义：指定计算时考虑的行范围

窗口函数类型

Apache DataFusion支持多种类型的窗口函数，主要包括：

1. 聚合窗口函数

所有标准聚合函数（如SUM、AVG、COUNT等）都可以作为窗口函数使用。例如：

SELECT 
    depname, 
    empno, 
    salary, 
    AVG(salary) OVER (PARTITION BY depname) AS dept_avg_salary
FROM empsalary;

2. 排名函数

ROW_NUMBER()

为分区内的每一行分配一个唯一的序号，从1开始。

SELECT 
    depname, 
    empno, 
    salary,
    ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC) AS row_num
FROM empsalary;

RANK()

计算排名，相同值会得到相同排名，后续排名会有间隔。

DENSE_RANK()

计算排名，相同值会得到相同排名，但后续排名连续无间隔。

NTILE(n)

将分区数据分成n个大致相等的组，并返回每行所属的组号。

3. 分析函数

LAG/LEAD

访问当前行之前(LAG)或之后(LEAD)的行数据。

SELECT 
    empno,
    salary,
    LAG(salary, 1, 0) OVER (ORDER BY empno) AS prev_salary,
    LEAD(salary) OVER (ORDER BY empno) AS next_salary
FROM empsalary;

FIRST_VALUE/LAST_VALUE/NTH_VALUE

获取窗口帧中的第一个、最后一个或第N个值。

窗口帧详解

窗口帧定义了窗口函数计算时考虑的行范围，语法如下：

{ RANGE | ROWS | GROUPS } BETWEEN 起始点 AND 结束点

其中：

• ROWS：基于物理行偏移
• RANGE：基于逻辑值范围
• GROUPS：基于组偏移

常用帧范围定义：

• UNBOUNDED PRECEDING：从分区开始
• CURRENT ROW：当前行
• n PRECEDING/FOLLOWING：当前行前/后n行
• UNBOUNDED FOLLOWING：到分区结束

示例：

SELECT 
    empno,
    salary,
    AVG(salary) OVER (ORDER BY salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg,
    SUM(salary) OVER (ORDER BY empno ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sum
FROM empsalary;

命名窗口

当查询中需要重复使用相同的窗口定义时，可以使用WINDOW子句命名窗口：

SELECT 
    SUM(salary) OVER w,
    AVG(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

实际应用场景

1. 部门内薪资分析：计算每个员工薪资与部门平均薪资的差异
2. 销售排名：按销售额对销售人员进行排名
3. 移动平均：计算股票价格的7日移动平均
4. 同比环比：计算月度销售额的环比增长率
5. 数据填充：使用前后值填充缺失数据

性能考虑

使用窗口函数时应注意：

• 合理使用PARTITION BY减少分区大小
• 避免不必要的排序操作
• 窗口帧范围不宜过大
• 考虑数据分布特点优化查询

总结

Apache DataFusion的窗口函数为数据分析提供了强大的工具，通过灵活的分区、排序和窗口帧定义，可以实现复杂的分析计算而不改变原始数据行结构。掌握窗口函数的使用可以显著提升SQL查询的分析能力，是每个数据工程师和分析师都应该掌握的重要技能。

datafusion Apache DataFusion SQL Query Engine 项目地址: https://gitcode.com/gh_mirrors/datafu/datafusion