【第6回】Ethereumデータ分析演習2｜ブロックチェーンの仕組みとオンチェーンデータ分析

無事に実行が終了すると、図1のようなクエリ結果画面が表示されます。なお、SQLにおけるテーブルは、表計算ソフトにおけるテーブルとは異なり、順序を持たないデータの集合ですので、取得される10件のデータは実行ごとに異なるものが取得される可能性があります。今回の実行結果では、2015年9月12日のタイムスタンプが付与された、ブロック番号「224095」のデータが1件目に取得されています。

Duneで取得したデータが正しいかどうか、Etherscan（https://etherscan.io/）のサイトでも確認してみましょう。Etherscanの検索窓にブロック番号を入力して検索すると、該当ブロックの詳細情報を記載したページが閲覧できます。

図1と図2を比較してみて、TimestampやDifficultyなど、基本的な情報が一致していることを確認してみてください。

データ加工のためのSQL

上記のブロックデータを対象として、SQLを用いた簡単なデータ加工の演習をしてみましょう。

SQLにおけるデータ加工のための演算は、大きく分けて数値や文字列などのスカラ値を対象とした演算と、データの集合であるテーブル（リレーション）を対象とした演算が存在します。両者について具体例を確認してみましょう。

値を対象とした演算

図1で示したクエリの出力結果のうち、タイムスタンプを表す「2015-09-12 17:04」といったデータや、ブロック番号を表す「224095」といったデータに対して、SQLを用いて好きな加工を施すことができます。特に、ブロックチェーンのデータの多くはタイムスタンプを付与された時系列データですので、タイムスタンプを分析しやすい形に加工する機会が多くあります。

コード2に、タイムスタンプを取り扱う典型的な関数の記載例を示します。yearやmonthといった関数は、タイムスタンプから特定の要素を抽出する関数です。date_trunc関数は、タイムスタンプを指定した桁で切り捨てた値を取得する関数で、日次や週次でのデータ集計を実施したい場合によく用いられます。format_datetime関数はJavaの時刻関数ですが、Dune SQLではこのようなSQL標準には存在しないものの有用な関数も多く対応が行われています。

Dune SQLでどのような関数が実装されているかは、公式のドキュメント（https://dune.com/docs/query/DuneSQL-reference/Functions-and-operators/）を参照してください。

コード2. タイムスタンプを加工する関数例

SELECT time,
    year(time),
    month(time),
    day(time),
    date_trunc('day', time),
    date_trunc('week', time),
    format_datetime(time, 'MMM-dd-yyyy KK:mm:ss a +z'),
    number
FROM ethereum.blocks
WHERE number = 224095
LIMIT 10

SQLにおける値を対象とした演算の注意点として、対象テーブルに含まれるすべてのレコードのカラムに対して、同じ演算が適用される点に注意してください。例えば、100件のデータが存在した場合に、一般的な手続き型のプログラミング言語では、for文などのループ構文を用いて、1件ずつ演算を適用していくことが多いと思います。一方、SQLは集合演算を基本としているため、ループ構文などは存在せず、100件のデータすべてに必ず同じ演算が適用されることになります。

また、値を対象とした演算では、入力したテーブルに含まれるレコード数と、出力されるレコード数は必ず一致することにも注意してください。100件のレコードを含むテーブルに対して値を変更する演算を実行した場合、出力結果のレコード数も必ず100件となっています。

もし、期待している分析結果がレコード数の増減を伴うようなものであれば、次のテーブルを対象とした演算が必要です。

テーブルを対象とした演算

集合演算としてのSQLを使いこなすためには、テーブルに対して演算をおこなって別のテーブルを作成する、というイメージを持つことが重要です。

集合演算としてのSQLを体験する前準備として、さきほど示したdate_trunc関数を用いて、タイムスタンプを日付で切り落としした一時データを作成してみます。コード3では、date_truncの計算結果カラムに対して、AS句を用いて「day」という名前を付けています。コード2のような書き方では、出力結果のカラムが「_col1」「_col2」といったデフォルトの連番名となっていましたが、これらに分かりやすい名前をつけることで、分析の見通しやクエリの再利用性が向上します。

また、取得するレコードの条件をWHERE句で指定し、レコードの絞り込みをおこなっています。WHERE句の次に指定した式がTRUEとなるレコードだけが出力結果に表示されます。「timestamp ‘2023-01-01 00:00:00’ <= time」という条件式は、’2023-01-01 00:00:00’という文字列をtimestamp型に変換し、timeカラムの値と比較している式です。

コード3. date_trunc関数を用いた前処理

SELECT time, date_trunc('day', time) AS day, number, size
FROM ethereum.blocks
WHERE timestamp '2023-01-01 00:00:00' <= time

図4に示したとおり、タイムスタンプから時刻情報が切り落とされ、日付情報を示すdayカラムを持ったサンプルレコードを取得することができました。

このサンプルレコードを対象として、日付ごとにどれくらいのブロックが生成されているかを集計するクエリを記述してみましょう。

まず、テーブルに対して演算を適用することをイメージするために、コード4のような書き換えをおこなってみてください。コード3に限らず、すべてのSELECT文の出力結果はテーブル（リレーション）の形式をしているので、その出力結果をFROM句に指定して、さらに別のSELECT文を記述することが可能です。コード4の実行結果は、図4で示したコード3の実行結果と同じです。

コード4. 「コード3」のSELECT文をFROM句に指定したクエリ

SELECT *
FROM (
    SELECT time, date_trunc('day', time) AS day, number, size
    FROM ethereum.blocks
    WHERE timestamp '2023-01-01 00:00:00' <= time
)

集約関数

テーブルを対象とした演算として代表的なものに、集約関数と呼ばれる関数群があります。コード5は、コード3の出力結果のテーブルに対して、集約関数であるcount, max, min関数を適用したクエリです。

コード5. 「コード3」の出力結果に集約関数を適用したクエリ

SELECT count(1), max(day), min(day)
FROM (
    SELECT time, date_trunc('day', time) AS day, number, size
    FROM ethereum.blocks
    WHERE timestamp '2023-01-01 00:00:00' <= time
)

コード5の計算結果は、FROM句で指定したテーブルに含まれるレコード数、dayカラムの最大値と最小値を示しています。ここで、出力結果のテーブルのレコード数が1件となっていることに注意してください。テーブルを対象とした演算は、値を対象とした演算と異なり、入力テーブルのレコード数と出力テーブルのレコード数は必ずしも一致しません。

GROUP BY句

集約関数をGROUP BY句と組み合わせて使うことで、集約をおこなう粒度を指定することができます。GROUP BY dayと指定することで、dayが同じ値を持つレコード群を単位として集約関数を適用し、dayをキーとした新たなテーブルが生成されています。なお、計算結果の表示順はデフォルトでは不定のため、ORDER BY句を用いることでソートして表示することができます。

コード6. 日付ごとにブロック数をカウントするクエリ

SELECT day, count(1)
FROM (
    SELECT time, date_trunc('day', time) AS day, number, size
    FROM ethereum.blocks
    WHERE timestamp '2023-01-01 00:00:00' <= time
)
GROUP BY day
ORDER BY day

WITH句（共通テーブル式）

任意のSELECT文の結果はFROM句に指定して新たなSELECT文への入力として指定できますが、多くのSELECT文がネストしたクエリは可読性が下がる可能性があります。

WITH句を用いた共通テーブル式という構文を用いると、SELECT文の結果に一時的なテーブル名を付与することができ、一連のクエリのなかで再利用ができるようになります。

コード7は、コード3のSELECT文の結果にtmpという名前を付け、WITH句を用いて共通テーブル式化したクエリです。出力結果はコード6と同様になります。

コード7. WITH句を用いた「コード6」の書き換え

WITH tmp AS (
    SELECT time, date_trunc('day', time) AS day, number, size
    FROM ethereum.blocks
    WHERE timestamp '2023-01-01 00:00:00' <= time
)
SELECT day, count(1)
FROM tmp
GROUP BY day
ORDER BY day

JOIN句

テーブルを対象とした演算には、レコードをフィルタしたり集約したりするWHERE句や集約関数とは逆に、複数のテーブルを組み合わせてレコード数やカラムを増加させる演算も存在します。

上記のblocksテーブルに含まれる情報だけでは、ブロックに含まれるトランザクション数などを把握することができませんでした。ブロックごとのトランザクション数の集計は、transactionsテーブルを用いて計算できます。transactionsテーブルの計算結果と、blocksテーブルのカラム情報を組み合わせて結果に表示させたい場合は、複数のテーブルを指定したキーで結合するJOIN句が利用できます。

コード8は、WITH句による共通テーブル式で、トランザクション数を集計したtx_countテーブルを作成し、blocksテーブルのnumberカラムと、tx_countテーブルのblock_numberカラムとをキーにして結合したクエリです。

コード8. ブロックに含まれるトランザクション数の集計クエリ

WITH tx_count AS (
    SELECT block_number, COUNT(1) AS tx_count
    FROM ethereum.transactions
    GROUP BY block_number
)
SELECT number, time, miner, difficulty, tx_count
FROM ethereum.blocks AS b
JOIN tx_count AS t
ON b.number = t.block_number
ORDER BY b.number DESC
LIMIT 10

次回予告

今回の記事では、ブロックチェーンの基本的なデータ構造であるブロックやトランザクションのテーブルをサンプルとして、データ分析で役立つSQLの基本的な構文や、SQL特有の思考方法について紹介しました。次回記事では、EVMのデータ構造の深掘りや、発展的なSQLの構文について解説しながら、オンチェーンデータ分析の演習を進める予定です。

連載一覧

【第1回】ブロックチェーンとは
【第2回】ビットコインの仕組み
【第3回】イーサリアムの仕組み
【第4回】ビッグデータ分析のためのSQL基礎
【第5回】Ethereumデータ分析演習1
【第6回】Ethereumデータ分析演習2
【第7回】Ethereumデータ分析演習3
【第8回】Ethereumデータ分析演習4

#Ethereum #オンチェーン分析