简介
在今天的博文中，我们将重点介绍一个查询，该查询来自使用 ClickHouse 附带的 git-import 工具捕获的数据对 ClickHouse 代码库进行分析的结果，并在 本系列的上一篇文章 中进行了介绍。此查询使用了 ClickHouse 的两个强大功能：窗口函数和数组函数。
窗口函数从 ClickHouse 21.5 版本开始提供。PostgreSQL 文档 对此 SQL 功能进行了很好的概括。
窗口函数在与当前行相关的表行集中执行计算。这与可以使用聚合函数执行的计算类型类似。但是，与普通的聚合函数不同，使用窗口函数不会导致行分组为单个输出行 - 行会保留其独立身份。窗口函数可以访问查询结果的当前行以外的其他行。
我们经常发现新用户从一个例子中获益，以便充分理解此功能的强大功能及其核心概念。在本博文中，我们将使用来自最近对 ClickHouse Git 代码库的分析中的“哪个作者在连续天数内提交次数最多？”的问题的解决方案来介绍窗口函数。此查询还为我们提供了利用 数组函数 的机会：ClickHouse 的一个强大功能，经验丰富的用户会在特定运算符不可用时转向它。
所有示例都可以在我们的 play.clickhouse.com 环境中进行复制（查看 git_clickhouse 数据库）。或者，如果您想更深入地研究此数据集，ClickHouse Cloud 是一个很好的起点 - 使用免费试用版启动一个集群，加载数据，让我们处理基础设施，然后开始查询！
创建序列
窗口函数允许用户根据当前查询结果中捕获的周围行来对当前行执行计算。行的数量由“窗口”控制，窗口由 ROWS 或 RANGE 的数量定义。还可以为每列的唯一值的每一列创建单独的窗口 - 类似于聚合函数，通过 PARTITION BY 子句。可以使用函数跨窗口值计算行的值。下图可视化了此窗口概念的一些人工数据。在此示例中，我们有三个列。country 列用于将数据分区到多个窗口中。
一个明显的示例可能是使用这些窗口来计算每个国家/地区的移动平均值 - 以下子句计算每个国家/地区的过去 5 个点的温度的移动平均值。
avg(temperature) OVER (PARTITION BY country ORDER BY day ASC
Rows BETWEEN 5 PRECEDING AND CURRENT ROW) AS moving_avg_temp
语法中有一些关键部分
OVER子句创建窗口本身PARTITION BY为国家/地区的每个值创建一个窗口ORDER BY控制当前列的同行排序顺序。如果没有此项，结果中的所有其他值都将被视为同行。ROWS子句定义要考虑的行数，称为框架。我们有 多个选项 来定义范围。这里我们使用BETWEEN 5 PRECEDING AND CURRENT ROW来指定从前 5 行到当前行（包括当前行）的框架大小（请参见上图中橙色突出显示的框架）。RANGE子句是此子句的替代方法，它将行定义为与当前行的关系，例如，值差异。
现在，让我们将此功能应用于我们的 git 提交，并使用它来解决我们的问题。您可能还记得我们在以前的 博客文章 和 文档 中介绍过，git-import 工具会为代码库中的每个提交生成一行。为了回答我们的问题，我们只需要两个字段：time 和 author。使用这些字段，我们需要为每个作者/日期对获得一行。这可以通过使用 toStartOfDay 函数进行简单的 GROUP BY 操作来解决。
SELECT author, toStartOfDay(time) AS day FROM git.commits GROUP BY author, day ORDER BY author ASC, day ASC LIMIT 10 ┌─author──────────────────────┬─────────────────day─┐ │ 1lann │ 2022-03-07 00:00:00 │ │ 20018712 │ 2020-09-17 00:00:00 │ │ 243f6a8885a308d313198a2e037 │ 2020-12-10 00:00:00 │ │ 3ldar-nasyrov │ 2021-03-16 00:00:00 │ │ [email protected] │ 2019-04-26 00:00:00 │ │ ANDREI STAROVEROV │ 2021-05-09 00:00:00 │ │ ANDREI STAROVEROV │ 2021-05-10 00:00:00 │ │ ANDREI STAROVEROV │ 2021-05-11 00:00:00 │ │ ANDREI STAROVEROV │ 2021-05-13 00:00:00 │ │ ANDREI STAROVEROV │ 2021-05-14 00:00:00 │ └─────────────────────────────┴─────────────────────┘ 10 rows in set. Elapsed: 0.362 sec. Processed 61.90 thousand rows, 389.87 KB (171.06 thousand rows/s., 1.08 MB/s.)✎
对于每一行，我们现在需要确定它是否连续，即，比前一个值早 1 天。为此，我们需要通过 PARTITION BY 为每个作者创建一个窗口，并简单地使用 any 函数获取前一行，即
any(day) OVER (PARTITION BY author ORDER BY day ASC ROWS BETWEEN 1
PRECEDING AND CURRENT ROW) AS previous_commit
我们在下面可视化此步骤。请注意 ORDER BY 的重要性，它对每个分区窗口内的值进行排序
现在我们有了前一天作为 previous_commit 列，我们可以使用 dateDiff 函数计算与当前值的日期差。最后，我们添加一个 consecutive 列 使用条件语句 - 如果差异为 1，则设置为 1，否则设置为 0。
SELECT author, toDate(day) as day, any(day) OVER (PARTITION BY author ORDER BY day ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS previous_commit, dateDiff('day', previous_commit, day) AS day_diff, if(day_diff = 1, 1, 0) AS consecutive FROM ( SELECT author, toStartOfDay(time) AS day FROM git.commits GROUP BY author, day ORDER BY author ASC, day ASC ) LIMIT 10✎
┌─author──────────────────────┬────────day─┬─previous_commit─┬─day_diff─┬─consecutive─┐ │ 1lann │ 2022-03-07 │ 2022-03-07 │ 0 │ 0 │ │ 20018712 │ 2020-09-17 │ 2020-09-17 │ 0 │ 0 │ │ 243f6a8885a308d313198a2e037 │ 2020-12-10 │ 2020-12-10 │ 0 │ 0 │ │ 3ldar-nasyrov │ 2021-03-16 │ 2021-03-16 │ 0 │ 0 │ │ [email protected] │ 2019-04-26 │ 2019-04-26 │ 0 │ 0 │ │ ANDREI STAROVEROV │ 2021-05-09 │ 2021-05-09 │ 0 │ 0 │ │ ANDREI STAROVEROV │ 2021-05-10 │ 2021-05-09 │ 1 │ 1 │ │ ANDREI STAROVEROV │ 2021-05-11 │ 2021-05-10 │ 1 │ 1 │ │ ANDREI STAROVEROV │ 2021-05-13 │ 2021-05-11 │ 2 │ 0 │ │ ANDREI STAROVEROV │ 2021-05-14 │ 2021-05-13 │ 1 │ 1 │ └─────────────────────────────┴────────────┴─────────────────┴──────────┴─────────────┘ 10 rows in set. Elapsed: 0.020 sec. Processed 61.90 thousand rows, 389.87 KB (3.04 million rows/s., 19.17 MB/s.)
此时，我们的数据结构中已经隐含了答案。我们只需要为每个作者识别 consecutive 列中最长的“1”序列。为了解决此问题，我们需要转向数组函数。
计算连续值
ClickHouse 中的数组是头等公民，可以使用 多种函数 进行处理。这些函数意味着将我们的数据组织成数组通常会有所帮助，以解决否则看起来很复杂的问题。将我们之前的查询放在 CTE commit_days 中，我们可以用几行代码来解决我们的连续“1”问题
WITH commit_days AS
(
// earlier query
)
SELECT
author,
arrayMax(arrayMap(x -> length(x), arraySplit(x -> (x = 0), groupArray(consecutive)))) - 1 AS max_consecutive_days
FROM commit_days GROUP BY author ORDER BY max_consecutive_days DESC
LIMIT 10
这里发生了很多事情，隐藏在许多数组函数接受 高阶函数（在本例中是另一个数组函数）作为输入这一事实中。下面，使用 myrcc 作为示例，我们将展示如何
-
groupArray 函数用于为每个作者生成一行，其中
consecutive值作为数组 -
使用 arraySplit 函数，通过一个以 0 为分割条件的 lambda 函数将数据转换为子数组列表。
-
任何连续的 1 序列都会变成一个以 0 开头的子数组。
-
接下来，arrayMap 函数将这些子数组列表转换为子数组长度列表。
-
此列表中的最大数字（减去 1）实际上就是作者最长的提交序列长度。使用 arrayMax 函数可以轻松实现这一点。
将所有这些步骤组合在一起，我们需要先按最长的连续计数进行排序。那么，谁是提交时间最长的开发者呢？
WITH commit_days AS ( SELECT author, toDate(day) AS day, any(day) OVER (PARTITION BY author ORDER BY day ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS previous_commit, dateDiff('day', previous_commit, day) AS days_since_last, if(days_since_last = 1, 1, 0) AS consecutive FROM ( SELECT author, toStartOfDay(time) AS day FROM git.commits GROUP BY author, day ORDER BY author ASC, day ASC ) ) SELECT author, arrayMax(arrayMap(x -> length(x), arraySplit(x -> (x = 0), groupArray(consecutive)))) - 1 AS max_consecutive_days FROM commit_days GROUP BY author ORDER BY max_consecutive_days DESC LIMIT 5 ┌─author───────────┬─max_consecutive_days─┐ │ kssenii │ 32 │ │ Alexey Milovidov │ 30 │ │ alesapin │ 26 │ │ Azat Khuzhin │ 23 │ │ Nikolai Kochetov │ 15 │ └──────────────────┴──────────────────────┘ 5 rows in set. Elapsed: 0.028 sec. Processed 61.90 thousand rows, 389.87 KB (2.19 million rows/s., 13.78 MB/s.)✎
恭喜 Kssenii！
结论
在这篇文章中，我们演示了如何使用窗口函数和数组函数来解决 ClickHouse 中的序列问题。在本例中，我们识别了在我们的仓库中拥有最长连续提交天数的作者。然而，类似的技术可以应用于其他数据集以找到顺序模式。
