介绍
在今天的文章中，我们重点介绍一个查询，该查询来自对 ClickHouse 仓库的分析，使用了 git-import 工具（随 ClickHouse 分发）捕获的数据，并在本系列的前一篇文章中介绍过。此查询使用了 ClickHouse 的两个强大功能：窗口函数和数组函数。
自 21.5 版本起，ClickHouse 中已提供窗口函数。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.)✎
对于每一行，我们现在需要确定它是否是连续的，即比前一个值晚一天。为此，我们需要通过 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 中，我们可以用几行代码解决我们的连续 1s 问题
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 函数使用 lambda 条件（在 0 上分割）将其转换为子数组列表。
-
任何连续的 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 中的序列问题。在我们的案例中，我们确定了对我们自己的仓库连续提交天数最多的作者。然而，类似的技术可以应用于其他数据集以查找顺序模式。
