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

窗口函数

窗口函数让您可以在与当前行相关的一组行上执行计算。您可以执行的一些计算类似于可以通过聚合函数完成的计算，但窗口函数不会导致行被聚合为单个输出 - 单独的行仍然会被返回。

标准窗口函数​

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 框架✅ (默认)
对 DateTime 的 RANGE OFFSET 框架使用 INTERVAL 语法❌ (请改用秒数来指定 ( 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)，或对 lead 使用 following
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 unbounded 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 - 定义在计算 aggregate_function 时如何对组内的行进行排序。
  • ROWS or RANGE - 定义框架的边界，aggregate_function 在框架内计算。
  • 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

相关内容​