跳到主要内容

窗口函数(Window Function)

概述

窗口函数(Window Function)对一组相关行进行计算,同时为每个输入行返回一个结果。与聚合函数(Aggregate Function)不同,窗口函数不会将多行折叠为单个输出。

主要特点:

  • 对与当前行相关的行“窗口”进行操作
  • 为每个输入行返回一个值(不进行分组/折叠)
  • 可以访问窗口中其他行的值
  • 支持分区(Partitioning)和排序(Ordering),以实现灵活的计算

窗口函数类别

Databend 支持两大类窗口函数:

1. 专用窗口函数

这些函数专为窗口操作而设计。

排名函数:

函数描述并列处理示例输出
ROW_NUMBER顺序编号始终唯一1, 2, 3, 4, 5
RANK带间隙排名相同排名,后续有间隙1, 2, 2, 4, 5
DENSE_RANK无间隙排名相同排名,无间隙1, 2, 2, 3, 4

分布函数:

函数描述范围示例输出
PERCENT_RANK相对排名的百分比0.0 到 1.00.0, 0.25, 0.5, 0.75, 1.0
CUME_DIST累积分布0.0 到 1.00.2, 0.4, 0.6, 0.8, 1.0
NTILE划分为 N 个桶1 到 N1, 1, 2, 2, 3, 3

值访问函数:

函数描述用例
FIRST_VALUE窗口中的第一个值获取最高/最早的值
LAST_VALUE窗口中的最后一个值获取最低/最晚的值
NTH_VALUE窗口中的第 N 个值获取特定位置的值
LAG上一行的值与上一行比较
LEAD下一行的值与下一行比较

别名:

函数别名
FIRSTFIRST_VALUE
LASTLAST_VALUE

2. 用作窗口函数的聚合函数

这些是标准的聚合函数,可以与 OVER 子句一起使用以执行窗口操作。

函数描述窗口框架支持示例
SUM计算窗口内的总和SUM(sales) OVER (PARTITION BY region ORDER BY date)
AVG计算窗口内的平均值AVG(score) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
COUNT计算窗口内的行数COUNT(*) OVER (PARTITION BY department)
MIN返回窗口内的最小值MIN(price) OVER (PARTITION BY category)
MAX返回窗口内的最大值MAX(price) OVER (PARTITION BY category)
ARRAY_AGG将值收集到数组中ARRAY_AGG(product) OVER (PARTITION BY category)
STDDEV_POP总体标准差STDDEV_POP(value) OVER (PARTITION BY group)
STDDEV_SAMP样本标准差STDDEV_SAMP(value) OVER (PARTITION BY group)
MEDIAN中位数MEDIAN(response_time) OVER (PARTITION BY server)

条件变体

函数描述窗口框架支持示例
COUNT_IF条件计数COUNT_IF(status = 'complete') OVER (PARTITION BY dept)
SUM_IF条件求和SUM_IF(amount, status = 'paid') OVER (PARTITION BY customer)
AVG_IF条件平均值AVG_IF(score, passed = true) OVER (PARTITION BY class)
MIN_IF条件最小值MIN_IF(temp, location = 'outside') OVER (PARTITION BY day)
MAX_IF条件最大值MAX_IF(speed, vehicle = 'car') OVER (PARTITION BY test)

基本语法

所有窗口函数都遵循以下模式:

FUNCTION() OVER (
[ PARTITION BY column ]
[ ORDER BY column ]
[ window_frame ]
)
  • PARTITION BY:将数据划分为组
  • ORDER BY:对每个分区内的行进行排序
  • window_frame:定义要包含的行(可选)

常见用例

  • 排名:创建排行榜和 Top-N 列表
  • 分析:计算累计总和、移动平均值、百分位数
  • 比较:比较当前值与前一个/后一个值
  • 分组:在不丢失细节的情况下将数据划分为桶

有关详细的语法和示例,请参阅上面各个函数的文档。

开始使用 Databend Cloud
低成本
快速分析
多种数据源
弹性扩展
注册