跳到主要内容
跳到主要内容

窗口函数

窗口函数允许您对与当前行相关的一组行执行计算。 您可以进行的一些计算与聚合函数相似，但窗口函数不会导致行被分组到单个输出中 - 仍然返回单独的行。

标准窗口函数​

ClickHouse 支持定义窗口和窗口函数的标准语法。下表指示某个功能是否当前受支持。

特性支持？
临时窗口定义 (count(*) over (partition by id order by time desc))✅
涉及窗口函数的表达式，例如 (count(*) over ()) / 2)✅
WINDOW 子句 (select ... from table window w as (partition by id))✅
ROWS 框架✅
RANGE 框架✅ (默认)
INTERVAL 语法用于 DateTime RANGE OFFSET 框架❌ (而是指定秒数（RANGE 可以与任何数字类型一起使用）。)
GROUPS 框架❌
在框架内计算聚合函数 (sum(value) over (order by time))✅ (所有聚合函数均受支持)
rank()、dense_rank()、row_number()✅
别名： denseRank()
percent_rank()✅ 有效地计算数据集中某个值在分区中的相对地位。此函数有效地替换了更冗长和计算密集型的原始 SQL 计算，表示为 ifNull((rank() OVER(PARTITION BY x ORDER BY y) - 1) / nullif(count(1) OVER(PARTITION BY x) - 1, 0), 0)
别名： percentRank()
lag/lead(value, offset)❌
您可以使用以下任一变通方法：
1) any(value) over (.... rows between <offset> preceding and <offset> preceding), 或 following 用于 lead
2) lagInFrame/leadInFrame，这类似，但遵循窗口框架。要获得与 lag/lead 相同的行为，使用 rows between unbounded preceding and unbounded following
ntile(buckets)✅
例如指定窗口 (partition by x order by y rows between unbounded preceding and unrounded following)。

ClickHouse 特定的窗口函数​

还有以下 ClickHouse 特定的窗口函数：

nonNegativeDerivative(metric_column, timestamp_column[, INTERVAL X UNITS])​

通过 timestamp_column 查找给定 metric_column 的非负导数。 INTERVAL 可以省略，默认是 INTERVAL 1 SECOND。 每行计算的值如下：

  • 第一行： 0
  • 对于第 ithi_{th} 行，计算为 metrici−metrici−1timestampi−timestampi−1∗interval{\text{metric}_i - \text{metric}_{i-1} \over \text{timestamp}_i - \text{timestamp}_{i-1}} * \text{interval}。

语法​

  • PARTITION BY - 定义如何将结果集拆分为组。
  • ORDER BY - 定义在计算聚合函数时如何在组内对行进行排序。
  • ROWS or RANGE - 定义框架的边界，聚合函数在框架内计算。
  • WINDOW - 允许多个表达式使用相同的窗口定义。

函数​

这些函数只能用作窗口函数。

  • row_number() - 在其分区内对当前行进行编号，从 1 开始。
  • first_value(x) - 返回在其有序框架内计算的第一个值。
  • last_value(x) - 返回在其有序框架内计算的最后一个值。
  • nth_value(x, offset) - 返回在其有序框架中针对第 n 行（偏移量）计算的第一个非 NULL 值。
  • rank() - 在其分区内对当前行进行排名，并留有间隙。
  • dense_rank() - 在其分区内对当前行进行排名，无间隙。
  • lagInFrame(x) - 返回在有序框架中当前行之前的指定物理偏移行的值。
  • leadInFrame(x) - 返回在有序框架中当前行之后的偏移行的值。

示例​

让我们来看一些如何使用窗口函数的示例。

行编号​

聚合函数​

比较每个球员的薪水与他们团队的平均值。

比较每个球员的薪水与他们团队的最大值。

按列分区​

框架绑定​

真实世界示例​

以下示例解决常见的真实世界问题。

每个部门的最大/总薪水​

累积总和​

移动/滑动平均（每 3 行）​

移动/滑动平均（每 10 秒）​

移动/滑动平均（每 10 天）​

温度以秒精度存储，但使用 Range 和 ORDER BY toDate(ts) 我们形成一个大小为 10 个单位的框架，由于 toDate(ts) 单位为天。

参考​

GitHub 问题​

窗口函数初始支持的路线图在 此问题。

所有与窗口函数相关的 GitHub 问题都有 comp-window-functions 标签。

测试​

这些测试包含当前支持的语法的示例：

https://github.com/ClickHouse/ClickHouse/blob/master/tests/performance/window_functions.xml

https://github.com/ClickHouse/ClickHouse/blob/master/tests/queries/0_stateless/01591_window_functions.sql

Postgres 文档​

https://www.postgresql.org/docs/current/sql-select.html#SQL-WINDOW

https://www.postgresql.org/docs/devel/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS

https://www.postgresql.org/docs/devel/functions-window.html

https://www.postgresql.org/docs/devel/tutorial-window.html

MySQL 文档​

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html

相关内容​