使用 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之前有几个修改。当前解决方案可能会选择 Modify 事件作为最新更改，导致src/Functions/geometryFromColumn.h被保留。
- 提交历史损坏 - 删除事件缺失。源和原因待定。
这些差异不应在很大程度上影响我们的分析。我们欢迎改进此查询的版本。
列出修改最多的文件
限于当前文件，我们认为修改的数量是删除和添加的总和。
提交通常发生在星期几？
这很符合周五的生产力下降的现象。很高兴看到人们在周末提交代码！非常感谢我们的贡献者！
子目录/文件的历史 - 行数、提交和贡献者随时间变化
如果不进行过滤，这将产生庞大的查询结果，无法显示或可视化。因此，我们允许在以下示例中过滤一个文件或子目录。我们按周分组使用 toStartOfWeek 函数 - 根据需要进行调整。
这些数据可视化效果良好。下面我们使用 Superset。
对于添加和删除的行：
对于提交和作者：
列出具有最多作者的文件
仅限于当前文件。
仓库中最旧的代码行
仅限当前文件。
历史最长的文件
仅限当前文件。
我们的核心数据结构，Merge Tree，显然正在不断演变，拥有悠久的编辑历史！
贡献者在文档和代码上的分布
在数据捕获期间，由于 docs/ 文件夹的提交历史非常不干净，因此已过滤掉更改。此查询的结果因此不准确。
我们在某些时间段（例如，发布日期附近）是否写了更多的文档？我们可以使用 countIf 函数计算一个简单的比例，并使用 bar 函数可视化结果。
月底附近的文档量可能稍多，但总体上我们保持良好的均匀分布。由于在数据插入期间对文档的过滤，这个数据再次不可靠。
影响力最广泛的作者
我们在这里考虑的多样性是作者贡献的独特文件数量。
让我们看看谁最近的提交具有最广泛的影响力。我们将限制为作者的最后 N 次提交（在此案例中，我们使用的是 3 ，但可以自由修改）：
作者最喜欢的文件
在此，我们选择我们的创始人 Alexey Milovidov 并将分析限制在当前文件。
这很有意义，因为 Alexey 负责维护变更日志。但如果我们使用文件的基本名称来识别他受欢迎的文件，这将允许重命名，并应更专注于代码贡献。
这或许更能反映他的兴趣领域。
最大的文件与最少作者
为此，我们首先需要确定最大的文件。假设我们限制在当前文件，通过从提交历史记录中重新构建每个文件的完整文件，将非常昂贵！
为了估算，假设我们限制在当前文件，我们将行增加总和减去删除。然后，我们可以计算长度与作者数量的比例。
文本字典可能不太现实，因此通过文件扩展名过滤仅限于代码！
这个结果存在一些时效偏差 - 较新的文件有较少的修改机会。那么，如果我们限制为至少 1 年前的文件呢？
按时间分布的提交和代码行数；按周天、作者；针对特定子目录
我们将其解释为每周天增加和删除的代码行数。在这种情况下，我们关注的是 Functions 目录
按时间段统计，
这种分布是合理的，因为我们大多数开发团队位于阿姆斯特丹。bar 函数帮助我们可视化这些分布：
显示作者之间互相重写代码的矩阵
sign = -1 表示代码删除。我们排除标点符号和空行的插入。
Sankey 图 (SuperSet) 允许我们很好地可视化这一点。请注意我们将 LIMIT BY 增加到 3，以获得每个作者的前 3 个代码移除者，从而改善视觉效果。
Alexey 显然喜欢删除其他人的代码。让我们排除他，以获得更平衡的代码移除视图。
每周天最高贡献者是谁？
如果只考虑提交数量：
好的，这里有一些可能的优势就在于最长的贡献者 - 我们的创始人 Alexey。让我们将分析限制为过去一年。
这仍然有点简单，并没有反映出人们工作的真正情况。
一个更好的指标可能是每一天的顶尖贡献者占其在过去一年中所有工作总量的比例。注意我们将代码的删除和添加视为相等。
代码在仓库中的年龄分布
我们将分析限制为当前文件。为了简洁起见，我们将结果限制为深度为 2，每个根文件夹 5 个文件。根据需要进行调整。
某作者的代码中有多少百分比被其他作者删除？
对于这个问题，我们需要一个作者写的行数除以他们被其他贡献者删除的行数总数。
列出被重写次数最多的文件？
解决这个问题最简单的方法可能是简单地计算每条路径的行修改次数 (限制为当前文件)，例如：
但这并不能捕捉到“重写”的概念，即任何提交中大量文件的更改。这需要更复杂的查询。如果我们认为重写是指文件的 50% 被删除和 50% 被添加，您可以根据自己的解释调整查询。
该查询仅限于当前文件。我们通过按 path 和 commit_hash 分组列出所有文件更改，返回添加和删除的行数。通过窗口函数，我们通过执行累积和估计在任何时刻文件的总大小，计算任何更改对文件大小的影响为 添加行 - 删除行。使用该统计信息，我们可以计算每个更改的文件已添加或删除的百分比。最后，我们计算构成重写的文件更改数量，即 (percent_add >= 0.5) AND (percent_delete >= 0.5) AND current_size > 50。请注意，我们要求文件行数超过 50 行，以避免早期对文件的贡献被计算为重写。这还避免了对非常小文件的偏见，因为这些文件更可能被重写。
代码在仓库中停留的概率最高的工作日是哪个？
为此，我们需要唯一地识别一行代码。我们通过路径和行内容来估计（因为同一行可能在文件中出现多次）。
我们查询添加的行，并将其与删除的行连接，过滤出后者发生在前者之后的情况。这为我们提供了已删除的行，从中可以计算这两个事件之间的时间。
最后，我们在该数据集上聚合，以计算代码根据一周中的每一天在仓库中的平均停留天数。
按平均代码年龄排序的文件
此查询与 代码在库中停留的最高概率的星期几 使用相同的原则 - 旨在通过路径和行内容唯一识别代码的行。这使我们能够识别添加和删除一行代码之间的时间。然而，我们只过滤当前的文件和代码，并对每个文件的时间在各行之间进行平均。
谁更倾向于编写更多的测试 / CPP 代码 / 注释？
我们可以从几个方面来解决这个问题。专注于代码与测试的比例，这个查询相对简单 - 计算对包含 tests 的文件夹的贡献数量，并计算与总贡献的比例。
请注意，我们限制用户进行超过 20 次更改，以专注于常规提交者，避免一次性贡献的偏差。
我们可以将这种分布绘制为直方图。
大多数贡献者编写的代码比测试多，这是可以预期的。
关于谁在贡献代码时添加最多注释呢？
请注意，我们按代码贡献进行排序。所有最大贡献者的 % 都出乎意料地高，这也是我们代码可读性强的部分原因。
随着时间的推移，作者的提交在代码/注释比例方面如何变化？
计算每位作者的这一指标非常简单，
然而，理想情况下，我们希望看到这在所有作者中的汇总变化，从他们开始提交的第一天开始。他们是否逐渐减少了编写的注释数量？
为了计算这一点，我们首先计算每个作者在时间上的注释比例 - 类似于 谁更倾向于编写更多的测试 / CPP 代码 / 注释？。这与每个作者的开始日期结合在一起，使我们能够按周偏移计算注释比例。
在对所有作者的平均每周偏移进行计算后，我们通过选择每第十周来对这些结果进行抽样。
令人鼓舞的是，我们的注释 % 相当稳定，并且在作者贡献的时间越长，注释的数量并没有减少。
代码被重写之前的平均时间和中位数（代码衰退的半衰期）是什么？
我们可以使用与 重写次数最多的文件或最多作者的文件列表 相同的原则来识别重写，但是考虑所有文件。使用窗口函数计算每个文件的重写之间的时间。由此，我们可以计算所有文件的平均值和中位数。
写代码的最糟糕时间是什么时候，代码被重写的几率最高？
类似于 重写之前的平均时间和中位数（代码衰退的半衰期）是什么？ 和 重写次数最多的文件或最多作者的文件列表，只不过我们按星期几聚合。根据需要调整，例如按年份的月份。
哪些作者的代码最容易重写？
我们定义“粘性”为作者的代码在重写之前持续的时间。与之前的问题 重写之前的平均时间和中位数（代码衰退的半衰期）是什么？ 类似 - 使用相同的重写指标，即 50% 的添加和 50% 的删除到文件。我们计算每位作者的平均重写时间，仅考虑处理超过两个文件的贡献者。
作者连续提交天数最多的统计
此查询首先需要我们计算作者提交的天数。通过分区作者，我们可以计算他们提交之间的天数。对于每个提交，如果距离上一个提交的时间为 1 天，我们将其标记为连续（1），否则标记为 0 - 将此结果存储在 consecutive_day 中。
我们随后的数组函数计算每位作者最长的连续 1 序列。首先，使用 groupArray 函数收集所有 consecutive_day 值。这个由 1 和 0 组成的数组在零值上拆分成子数组。最后，我们计算最长的子数组。
文件逐行提交历史
文件可以被重命名。当这种情况发生时，我们会得到一个重命名事件，其中 path 列设置为文件的新路径，old_path 表示之前的位置，例如：
这使得查看文件的完整历史变得具有挑战性，因为我们没有一个单一的值来连接所有行或文件的更改。
为了解决这个问题，我们可以使用用户定义函数（UDFs）。这些函数目前不能递归，因此为了识别文件的历史，我们必须定义一系列明确互相调用的 UDFs。
这意味着我们只能跟踪重命名到最大深度——下面的示例深度为 5。一个文件被重命名的次数不太可能超过这个深度，因此现在这一点是足够的。
通过调用 file_path_history('src/Storages/StorageReplicatedMergeTree.cpp')，我们可以通过重命名历史进行递归，每个函数使用 old_path 调用下一个级别。结果通过 arrayConcat 组合。
例如，
我们可以利用这一能力来组装文件的整个历史提交。在这个例子中，我们展示了每个 path 值的一个提交。
未解决的问题
Git blame
由于当前无法在数组函数中保持状态，因此获取准确结果特别困难。这将在 arrayFold 或 arrayReduce 中成为可能，这允许在每次迭代中保持状态。
一个近似的解决方案，足以进行高层分析，可能如下所示：
我们欢迎在此处提供准确和改进的解决方案。