使用 GitHub 数据编写查询在 ClickHouse 中
这个数据集包含了 ClickHouse 仓库的所有提交记录和更改。可以利用 ClickHouse 附带的原生 git-import 工具生成。
生成的数据为以下每个表提供一个 tsv 文件：
commits- 包含统计信息的提交记录。file_changes- 每个提交中更改的文件及其更改信息和统计数据。line_changes- 每个提交中每个更改文件的每一行更改，包含该行的完整信息和该行之前更改的信息。
截至 2022 年 11 月 8 日，每个 TSV 的大小和行数大致如下：
commits- 7.8M - 266,051 行file_changes- 53M - 266,051 行line_changes- 2.7G - 7,535,157 行
生成数据
这一步是可选的。我们免费分发这些数据 - 参见 下载和插入数据。
这将在 ClickHouse 仓库上大约需要 3 分钟（截至 2022 年 11 月 8 日，在 MacBook Pro 2021 上）。
可以通过工具的原生帮助获取完整的可用选项列表。
此帮助文档还提供了以上每个表的 DDL，例如：
这些查询应该可以在任何仓库上使用。欢迎探索并报告您的发现 一些关于执行时间的指导（截至 2022 年 11 月）：
- Linux -
~/clickhouse git-import- 160 分钟
下载和插入数据
以下数据可用于重现工作环境。或者，您可以在 play.clickhouse.com 中找到可用的数据集 - 参见 查询 以获取更多详细信息。
以下仓库的生成文件可以在下方找到：
- ClickHouse（2022 年 11 月 8 日）
- https://datasets-documentation.s3.amazonaws.com/github/commits/clickhouse/commits.tsv.xz - 2.5 MB
- https://datasets-documentation.s3.amazonaws.com/github/commits/clickhouse/file_changes.tsv.xz - 4.5MB
- https://datasets-documentation.s3.amazonaws.com/github/commits/clickhouse/line_changes.tsv.xz - 127.4 MB
- Linux（2022 年 11 月 8 日）
要插入这些数据，请通过执行以下查询准备数据库：
使用 INSERT INTO SELECT 和 s3 function 插入数据。例如，在下面，我们将 ClickHouse 文件插入到各自的表中：
commits
file_changes
line_changes
查询
该工具通过其帮助输出建议了几条查询。除了这些外，我们还回答了一些附加的补充问题。这些查询的复杂性大致按工具的任意顺序递增。
该数据集在 play.clickhouse.com 中可用以 git_clickhouse 数据库提供。我们为所有查询提供了此环境的链接，并根据需要调整数据库名称。请注意，由于数据收集时间的差异，play 结果可能与此处所示不同。
单个文件的历史
最简单的查询。在这里，我们查看 StorageReplicatedMergeTree.cpp 的所有提交消息。由于这些可能更有趣，我们按最近的消息排序。
我们还可以查看行变更，排除重命名，即我们不会显示在重命名前以不同名称存在的文件的变更：
请注意，这个查询还存在一个更复杂的变体，我们在考虑重命名时查找 逐行提交历史。
查找当前活跃文件
这对于后续分析非常重要，因为我们只想考虑仓库中当前的文件。我们将此集合估算为未被重命名或删除（然后重新添加/重命名）的文件。
请注意，在重命名过程中，似乎 dbms，libs，tests/testflows/ 目录下的文件存在损坏的提交历史。因此我们也排除这些。
注意，这允许文件重命名然后重新命名为其原始值。首先，我们对因重命名而删除的文件的 old_path 进行聚合。我们将其与每个 path 的最后一次操作合并。最后，我们过滤此列表，确保最后操作不是 Delete。
请注意，我们在导入时跳过了多个目录，例如：
--skip-paths 'generated\.cpp|^(contrib|docs?|website|libs/(libcityhash|liblz4|libdivide|libvectorclass|libdouble-conversion|libcpuid|libzstd|libfarmhash|libmetrohash|libpoco|libwidechar_width))/'
将此模式应用于 git list-files，报告为 18155。
因此，我们当前的解决方案是对当前文件的估计
这里的差异是由几个因素造成的：
- 重命名可能发生在文件的其他更改的同时。这些在 file_changes 中被列为单独的事件，但时间相同。
argMax函数无法区分这些 - 它会选择第一个值。插入的自然顺序（唯一知晓正确顺序的方法）不会在联合中保持，因此修改事件可能会被选择。例如，在src/Functions/geometryFromColumn.h文件在重命名为src/Functions/geometryConverters.h之前有几个修改。我们的当前解决方案可能会将一个修改事件选为最新变更，导致src/Functions/geometryFromColumn.h被保留。
- 损坏的提交历史 - 缺失删除事件。源和原因待定。
这些差异不应对我们的分析造成显著影响。我们欢迎该查询的改进版本。
列出修改次数最多的文件
限制在当前文件中，我们将修改次数视为删除和添加的总和。
提交通常发生在哪一天？
这在周五出现一些生产力减退是可以理解的。很高兴看到人们在周末提交代码！非常感谢我们的贡献者！
子目录/文件的历史 - 随着时间的推移行数、提交和贡献者数量
如果不加过滤，这将产生一个很大的查询结果，无法显示或可视化。因此，我们允许在以下示例中过滤一个文件或子目录。我们在这里使用 toStartOfWeek 函数按周分组 - 根据需要进行调整。
这些数据可视化效果很好。下面我们使用 Superset。
添加和删除的行：
提交和作者：
作者最多的文件数量
仅限当前文件。
仓库中最旧的代码行
仅限当前文件。
历史最长的文件
仅限当前文件。
我们的核心数据结构 Merge Tree 显然在不断演变，具有较长的编辑历史！
关于文档和代码分布的贡献者分布
在数据捕获期间，docs/ 文件夹中的更改由于提交历史非常混乱而被过滤掉。因此该查询的结果不准确。
在某些时间（例如，在发布日期附近）我们是否写更多的文档？我们可以使用 countIf 函数计算简单的比例，并使用 bar 函数可视化结果。
在月末附近可能多一些，但总体来看我们保持了良好的均匀分布。再次强调，由于在数据插入期间过滤了文档，因此这一点不可靠。
影响最大的作者
我们认为多样性是作者贡献的独特文件数量。
让我们看看谁在最近的工作中有最多样化的提交。我们不按日期限制，而是限制在某个作者的最近 N 次提交（在这种情况下，我们使用 3 次，可以根据需要进行修改）：
作者的最爱文件
在这里我们选择我们的创始人 Alexey Milovidov 并将分析限制在当前文件。
这很有道理，因为 Alexey 一直负责维护更改日志。但如果我们用文件的基本名称来识别他的热门文件 - 这样可以考虑重命名，并应重点关注代码贡献。
这可能更能反映他的兴趣领域。
最大的文件与最低的作者数量
为此，我们首先需要识别最大的文件。通过从提交历史重建每个文件，估算这一点将非常昂贵！
为了估算，假设我们限制在当前文件，我们对添加的行进行求和并减去删除的行。然后，我们可以计算长度与作者数量的比率。
文本字典可能不太现实，因此让我们通过文件扩展名过滤限制在代码上！
这其中存在一些近期偏见 - 较新文件的提交机会较少。那么如果我们将限制设定为至少 1 年前的文件呢？
按时间分布的提交和代码行数; 按工作日、按作者; 针对特定子目录
我们将其解释为按工作日添加和删除的行数。在这种情况下，我们重点关注 Functions directory
以及按时间段，
这个分布是合理的，因为我们的开发团队大多数在阿姆斯特丹。bar 函数帮助我们可视化这些分布：
显示哪些作者倾向于重写其他作者代码的矩阵
sign = -1 表示代码删除。我们排除了标点符号和空行的插入。
一个 Sankey 图（SuperSet）可以很好地可视化这一点。请注意，我们将 LIMIT BY 增加到 3，以获取每个作者的前 3 个代码删除者，以提高视觉效果的多样性。
Alexey 显然喜欢删除其他人的代码。我们将他排除，以便更平衡地查看代码删除。
谁是每周的最高贡献者百分比？
如果我们仅考虑提交数量：
好吧，可能有一些优势在于最长的贡献者 - 我们的创始人 Alexey。让我们将分析限制在过去一年。
这仍然有点简单，并未反映人们的工作。
一个更好的指标可能是每一天作为总工作量的一部分，谁是最高贡献者。注意，我们平等对待代码的删除和添加。
仓库中的代码年龄分布
我们将分析限制在当前文件。为了简单起见，我们将结果限制为深度为 2，每个根文件夹 5 个文件。根据需要进行调整。
哪些作者的代码被其他作者删除的比例？
对于这个问题，我们需要将作者编写的行数除以他们被另外贡献者删除的行数总和。
列出被重写次数最多的文件
解决这个问题的最简单方法可能是简单地计算每个路径的行修改次数（限制在当前文件）：
然而这并没有捕捉到“重写”的概念，即在任何提交中大部分文件的更改。这需要更复杂的查询。如果我们将重写定义为删除超过 50% 的文件和添加 50%。您可以根据自己的解读来调整查询。
查询仅限于当前文件。我们通过按 path 和 commit_hash 聚合列出所有文件更改，返回添加和删除的行数。使用窗口函数，我们通过执行累积和来估计文件的总大小，并估算任何变更对文件大小的影响为 lines added - lines removed。通过这项统计数据，我们可以计算每个更改中已添加或删除的文件百分比。最后，我们计算出构成重写的文件更改次数，即 (percent_add >= 0.5) AND (percent_delete >= 0.5) AND current_size > 50。注意，我们要求文件超过 50 行以避免较早对文件的贡献被计为重写。这也避免了对非常小文件的偏见，因它们在被重写时可能更容易。
哪一天的代码被重写的机会最大？
类似于 重写次数最多的文件 和 重写次数最多的文件或被最多作者重写，但我们按工作日起聚合。根据需要调整，例如每年的月份。
哪些作者的代码是最“粘”的？
我们定义“粘”是作者的代码在被重写之前保持的时间。与之前的问题 重写时间的平均值以及代码衰减的半衰期 相似 - 使用相同的重写指标，即对文件的添加和删除都是 50%。我们计算每位作者的平均重写时间，仅考虑贡献超过两个文件的贡献者。
作者连续提交的最大天数
该查询首先需要我们计算出作者提交的天数。通过窗口函数，按作者进行分区，我们可以计算出他们提交之间的天数。对于每个提交，如果距离上一个提交的时间为 1 天，我们将其标记为连续（1），否则标记为 0 - 将这个结果存储在 consecutive_day 中。
我们后续的数组函数计算每个作者最长的连续 1 的序列。首先，使用 groupArray 函数收集所有作者的 consecutive_day 值。然后将这个 1 和 0 的数组在 0 值处分割成子数组。最后，我们计算最长的子数组。
文件的逐行提交历史
文件可以被重命名。当发生这种情况时，出现一个重命名事件，其中 path 列被设置为文件的新路径，old_path 列表示之前的位置，例如：
这使得查看文件的完整历史变得具有挑战性，因为我们没有一个唯一的值来连接所有行或文件更改。
为了解决这个问题，我们可以使用用户定义函数（UDFs）。当前无法递归，因此为了识别文件的历史，我们必须定义一系列相互调用的 UDF。
这意味着我们最多只能追踪 5 层重命名历史。文件被重命名超过这个次数的可能性不大，因此目前这已足够。
通过调用 file_path_history('src/Storages/StorageReplicatedMergeTree.cpp')，我们递归通过重命名历史，每个函数使用 old_path 调用下一个层次。结果使用 arrayConcat 组合。
例如，
我们可以利用这一能力现在组装文件的整个历史提交。在这个例子中，我们展示每个 path 值的一个提交。
未解决的问题
Git blame
由于当前无法在数组函数中保持状态，因此准确的结果特别难以获得。这可以通过 arrayFold 或 arrayReduce 实现，实现每次迭代时保持状态。
一个大致解决方案，足够进行高层分析，可能如下所示：
我们欢迎在这里提供准确和改进的解决方案。
