Writing Queries in ClickHouse using GitHub Data
このデータセットには、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
クエリ
ツールは、そのヘルプ出力を介していくつかのクエリを提案しています。我々は、これらに加え、いくつかの追加の補足的な質問に対しても回答しました。これらのクエリは、ツールの任意の順序に対して、約増加する複雑さで構成されています。
このデータセットは、git_clickhouse データベース内で play.clickhouse.com で利用可能です。すべてのクエリに対してこの環境へのリンクを提供し、必要に応じてデータベース名を適応しています。データ収集の時期の違いにより、プレイ結果はここに示されているものと異なる場合がありますのでご注意ください。
単一ファイルの履歴
最もシンプルなクエリです。ここでは StorageReplicatedMergeTree.cpp のすべてのコミットメッセージを見ていきます。これらはおそらくもっと興味深いので、最近のメッセージから順に並べ替えます。
リネームイベントの前に存在したファイルの変更を表示しないことにより、リネームを除外して行の変更を確認することもできます：
これは、ファイルがリネームされ、その後元の値に再リネームされることも許可します。まず、リネームの結果として削除されたファイルのリストのために old_path を集約します。このリストを最後の操作を持つすべての path で UNION します。最後に、最終イベントが 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が報告されます。
我々の現在の解決策は、従って現在のファイルの推定値です。
ここでの違いは、いくつかの要因によって引き起こされます：
- リネームは、ファイルへの他の変更と同時に発生する可能性があります。これらはファイル変更の中で別々のイベントとしてリストされていますが、同じ時間で行われます。
argMax関数はそれらを区別する方法を持っていないため、最初の値を選択します。挿入の自然順序（正しい順序を知る唯一の手段）は、union全体で維持されないため、修正イベントが選択される可能性があります。例えば、以下の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年前のファイルに制限するとどうなるでしょうか？
Commits and lines of code distribution by time; by weekday, by author; for specific subdirectories
これを曜日ごとの追加および削除された行数として解釈します。この場合、Functionsディレクトリ に焦点を当てます。
そして、時刻別に、
この分布は、私たちの開発チームのほとんどがアムステルダムにいることを考慮すると納得がいきます。 bar 関数がこれらの分布を視覚化するのに役立ちます：
Matrix of authors that shows what authors tends to rewrite another authors code
sign = -1 はコード削除を示します。句読点と空行の追加は除外します。
Sankeyチャート（SuperSet）を使用すると、これをうまく視覚化できます。ここでは、各著者に対して上位3件のコード削除者を取得するために、LIMIT BY を3に増やして可視性を向上させます。
Alexeyは他の人のコードを削除するのが明らかに好きです。彼を除外してコード削除のバランスを見ることにしましょう。
Who is the highest percentage contributor per day of week?
コミットの数で考慮する場合：
OK、ここには我々の創設者Alexeyによる最も長い貢献者の利点があります。分析を過去1年間に制限しましょう。
これはまだ少し単純で、人々の仕事を反映していません。
より良い指標は、過去1年間の実行された全作業の割合として毎日最高の貢献者を特定することかもしれません。削除と追加のコードを同様に扱うことに注意してください。
Distribution of code age across repository
現在のファイルに分析を制限します。簡潔さのために、結果を深さ2に制限し、ルートフォルダごとに5ファイルを制限します。必要に応じて調整してください。
What percentage of code for an author has been removed by other authors?
この質問については、著者によって書かれた行数を、他の貢献者によって削除された行数の合計で割ります。
List files that were rewritten most number of times?
この質問の最も単純なアプローチは、パスごとの行の変更回数を単純にカウントすることかもしれません（現在のファイルに制限されています）。例えば：
これは「書き換え」の概念を捉えていないことに注意してください。ただし、コミットの中でファイルの大部分が変更される場合です。このためには、より複雑なクエリが必要です。書き換えを、ファイルの50%以上が削除され、50%以上が追加された場合と考えます。このクエリは現在のファイルのみに制限されます。pathとcommit_hashでグループ化し、追加された行数と削除された行数を返すことで、ファイル変更をリストします。ウィンドウ関数を用いて、任意の時点でファイルの合計サイズを累積合計で推定し、ファイルサイズへの影響を行の追加 - 行の削除として評価します。この統計を使用して、各変更に対して追加されたまたは削除されたファイルの割合を計算できます。最後に、書き換えを構成するファイル変更の回数をカウントします。すなわち(percent_add >= 0.5) AND (percent_delete >= 0.5) AND current_size > 50です。ファイルの初期の貢献をカウントを回避するために50行以上である必要があります。これにより、小さなファイルが書き換えられる可能性が高くなるというバイアスも避けます。
What weekday does the code have the highest chance to stay in the repository?
これに対しては、コードの行を一意に特定する必要があります。これは（同じ行がファイルに複数回現れる可能性があるため）、パスと行の内容を使用して見積もります。
追加された行をクエリし、これは削除された行と結合し、後者が前者よりも最近発生したケースにフィルタリングします。これにより、削除された行が得られ、これら2つのイベント間の時間を計算できます。
最後に、週の各曜日に対して行がリポジトリに滞留する平均日数を計算するために、このデータセットを集約します。
平均コード年齢でソートされたファイル
このクエリは、コードがリポジトリに留まる確率が最も高い曜日は何かという原則と同じです。パスと行内容を使用してコードの行をユニークに特定することを目的としています。これにより、行が追加されてから削除されるまでの時間を特定することができます。ただし、現在のファイルとコードのみにフィルタリングし、行ごとに各ファイルの時間を平均します。
誰がより多くのテスト / CPP コード / コメントを書く傾向があるか
この質問にはいくつかのアプローチがあります。コードとテストの比率に焦点を当てると、このクエリは比較的シンプルです。testsを含むフォルダへの貢献の数をカウントし、全体の貢献数に対する比率を計算します。
特定の偏りを避けるため、20 回以上の変更を行ったユーザーに限定します。
この分布をヒストグラムとしてプロットすることができます。
ほとんどの貢献者は、予想通り、テストよりも多くのコードを書いています。
コードを貢献するときに最も多くコメントを追加するのは誰でしょうか？
コードの貢献をもとにソートしています。意外と高い%は、私たちの最大の貢献者に見られ、私たちのコードが非常に読みやすい理由の一部です。
作者のコミットが時間に伴ってコード / コメントの割合に関してどう変化するか
これを作者ごとに計算するのは簡単です。
理想的には、すべての作者がコミットを開始した最初の日から、これがどのように集計で変化するかを確認したいです。彼らは徐々に書くコメントの数を減らしているのでしょうか？
これを計算するために、まず各作者のコメントの割合を時間の経過とともに算出します。これは、誰がより多くのテスト / CPP コード / コメントを書く傾向があるかに似ています。これらは各作者の開始日と結合され、コメントの割合を週のオフセットで計算することができます。
すべての作者にわたって、平均を週のオフセットで計算した後、結果をサンプリングして10週ごとに選択します。
励ましいことに、私たちのコメントの%はかなり一定しており、著者が貢献を続けるにつれて低下することはありません。
コードが書き直されるまでの平均時間と中央値（コード減衰の半減期）は何か？
前のクエリで示した最も多く書き直されたファイルや複数の著者によるファイルの一覧の同じ原則を使用して、すべてのファイルを考慮に入れて書き直しを特定できます。ウィンドウ関数を使用して各ファイルの書き直し間の時間を計算します。これにより、すべてのファイルでの平均と中央値を計算できます。
いつコードを書くのが最も悪いか（最も書き直される可能性が高いコード）
コードが書き直されるまでの平均時間と中央値（コード減衰の半減期）と最も多くの著者によって書き直されたファイルの一覧と類似していますが、曜日ごとに集約します。必要に応じて調整してください（たとえば、月ごと）。
どの著者のコードが最も「粘着性」があるか
「粘着性」とは、著者のコードがどのくらいの期間書き直されずに保持されるかを定義します。前の質問コードが書き直されるまでの平均時間と中央値（コード減衰の半減期）に類似しており、書き直しの基準として、ファイルへの追加が50％、削除も50％することを考慮しています。著者ごとに平均書き直し時間を計算し、2つ以上のファイルを持つ貢献者のみを考慮します。
作者ごとの連続コミット日数
このクエリでは、最初に作者がコミットした日を計算する必要があります。ウィンドウ関数を使用して、作者ごとにコミット日をパーティション化し、コミット間の日数を計算します。各コミットに対して、前回のコミットからの時間が1日であれば連続しているとマークし（1）、そうでなければ0とします。この結果をconsecutive_dayに保存します。
続いて、各著者の最長連続1の配列を計算するために、配列関数を使用します。最初にgroupArray関数を使用して、著者のすべてのconsecutive_day値を集約します。この1と0の配列を0の値で分割し、サブ配列に分けます。最後に、最長のサブ配列を計算します。
Line by line commit history of a file
ファイルは名前を変更できます。これが発生すると、path カラムはファイルの新しいパスに設定され、old_path は以前の場所を表します。例えば：
これにより、ファイルの完全な履歴を表示することが難しくなります。なぜなら、すべての行またはファイルの変更を結びつける単一の値がないからです。
これに対処するために、ユーザー定義関数 (UDF) を使用します。これらは現在、再帰的にすることはできないため、ファイルの履歴を特定するには、互いに明示的に呼び出す一連の UDF を定義する必要があります。
つまり、名前の変更を最大深度まで追跡できます - 以下の例は 5 深です。ファイルがこれ以上名前を変更される可能性は低いため、現時点ではこれで十分です。
file_path_history('src/Storages/StorageReplicatedMergeTree.cpp') を呼び出すことで、名前の変更履歴を再帰的に探索します。各関数は old_path を用いて次のレベルを呼び出します。結果は arrayConcat を使用して結合されます。
例えば：
この機能を使用して、ファイルの完全な履歴に対するコミットを組み立てることができます。この例では、各 path 値に対して 1 つのコミットを示します。
Unsolved Questions
Git blame
これは、現在のところ配列関数で状態を保持できなため、正確な結果を得るのが特に難しいです。各反復で状態を保持できる arrayFold または arrayReduce を使用することで可能になるでしょう。
高レベルの分析に十分な近似解は次のようになります：
ここでの正確で改善された解決策を歓迎します。
