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

コード2は、Tracesの種類の一覧とアクション数を取得するためのクエリです。計算量の削減と計算結果の固定のため、対象期間を2023年1月1日から31日までの間に絞り込んでいます。

コード2. traces.typeの種類ごとにカウントして多い順に並べ替えるクエリ

SELECT type, count(1) as cnt
FROM ethereum.traces
WHERE block_time BETWEEN
timestamp '2023-01-01 00:00:00' AND timestamp '2023-01-31 23:59:59'
GROUP BY type
ORDER BY cnt desc
LIMIT 100

図2に示されるとおり、Tracesの種類には、callやcreate、reward、suicideなどがあります。最も件数の多いcallは、Etherの送金やスマートコントラクトの関数を呼び出す場合に用いられます。createは、スマートコントラクトを新規作成する場合に用いられます。rewardは、ブロック生成時の報酬に関するログに用いられますが、Ethereumの報酬に関する仕様は頻繁に変更されており、現在では使用されていません。suicideは、スマートコントラクトの削除に関するアクションに用いられます。

図3で示すとおり、Etherscan上では、Tracesの内容はInternal Txnsというタブで確認することができます。

同様の内容を、Dune上でも参照してみましょう。コード3.を用いて、特定のトランザクションに紐づくTracesの情報を取得できます。トランザクションハッシュのみの指定でもデータは取得できますが、データの探索範囲を絞り込むためにWHERE句で期間も指定しています。

コード3. 指定したトランザクションのTracesを取得するクエリ

SELECT trace_address, call_type, "from", to, value, gas
FROM ethereum.traces
WHERE block_time >= timestamp '2023-10-20 00:00:00'
AND tx_hash = 0x7900ef4293837b14cdf8814396e45b9380636cdfb7c55fe05a7343628b8b1ae0
LIMIT 100

図3および図4を見比べて、表記の違いはあるものの、同様のデータが取得できていることを確認してみてください。

TransactionsとLogs

TracesがEVMの仕様によってほぼ自動的に出力されるログである一方、Logs（Event Logs）はスマートコントラクトの実装者が明示的に定義できるログの種類です。Logsは関数のデバッグ用途や、トランザクション内のイベントをブロックチェーン外のシステムから検知するためなどに用いられます。

コード3のテーブルをLogs用に変更して、さきほど見たトランザクションに関するLogsを取得するクエリがコード4です。

コード4. あるトランザクションのLogsを取得するクエリ

SELECT *
FROM ethereum.logs
WHERE block_time >= timestamp '2023-10-20 00:00:00'
AND tx_hash = 0x7900ef4293837b14cdf8814396e45b9380636cdfb7c55fe05a7343628b8b1ae0
LIMIT 100

図6に示すとおり、Etherscanなどのエクスプローラーでは、著名なコントラクトについてはLogsのデータもデコードされた状態で表示されます。一方、Ethereumブロックチェーン上の生データやDuneのLogsテーブル上では、データはバイナリ形式でエンコードされた状態であり、そのままでは理解が困難です。また、さまざまなフォーマットのログをデコードするには、そのログがどのようなフォーマットであるかという定義情報が必要です。

Dune上では、それぞれのコントラクトの定義情報を用いてデータをデコードしたテーブルを提供してくれています。さきほどまで参照していたトランザクションであれば、uniswap_v3というDEX（オンチェーン上の分散型取引所）のトランザクションとしてデコードされています。

コード5. uniswap_v3のトレードデータとしてデコードされたトランザクションを取得するクエリ

SELECT *
FROM uniswap_v3_ethereum.trades
WHERE block_time >= timestamp '2023-10-20 00:00:00'
AND tx_hash = 0x7900ef4293837b14cdf8814396e45b9380636cdfb7c55fe05a7343628b8b1ae0
LIMIT 100

Duneでは、デフォルトでデコードされていないデータであっても、利用者がスマートコントラクトの情報などを登録してデコードの申請をすることも可能となっているので、自身の作成したコントラクトや、比較的新しいコントラクトのデータを分析する場合に活用してください。

データ分析のためのSQL

前半でご紹介したTracesやLogsデータを対象として、データ分析でよく用いられるSQL構文のうち、使用頻度も高いCASE式とWindow関数についてご紹介します。

CASE式と集約関数

CASE式は、条件式にしたがって値の場合分けをおこなうための式です。「CASE WHEN 【条件式】THEN【真の場合の値】ELSE【偽の場合の値】END」という構文を用います。コード6. に、Logsテーブルのtopicカラムの値が存在しているかどうかで0または1を出し分けるサンプルクエリを示します。SQLにおいて、値が存在していないフラグとして「NULL」を用い、NULLであるかどうかは「IS (NOT) NULL」を用います。

コード6. CASE式で値の場合分けをおこなうサンプルクエリ

SELECT
  topic0, topic3,
  CASE WHEN topic0 IS NOT NULL THEN 1 ELSE 0 END AS have_topic0,
  CASE WHEN topic3 IS NOT NULL THEN 1 ELSE 0 END AS have_topic3
FROM ethereum.logs
WHERE block_time BETWEEN
timestamp '2023-01-01 00:00:00' AND timestamp '2023-01-31 23:59:59'
LIMIT 100

このCASE式と集約関数を組み合わせてデータ分析を用いるテクニックがいくつかあります。例えば、Logsテーブルにおいて、topic0～3の値がレコード全体のうちどれくらいの割合で存在しているかを集計したい場合を考えます。直感的には、「topicの値が存在しているレコードの数」を「レコード全体の数」で割ることで割合を計算できます。これは、CASE式を用いて「topicの値が存在している場合は1、存在していない場合は0」という変換をおこない、それをAVG関数で平均してあげることでも計算できます。コード7.に、CASE式とAVG関数を用いて割合を計算するサンプルクエリを示します。

コード7.CASE式とAVG関数を組み合わせて割合を計算するサンプルクエリ

SELECT
  COUNT(1) AS cnt,
  AVG(CASE WHEN topic0 IS NOT NULL THEN 1 ELSE 0 END) AS have_topic0,
  AVG(CASE WHEN topic1 IS NOT NULL THEN 1 ELSE 0 END) AS have_topic1,
  AVG(CASE WHEN topic2 IS NOT NULL THEN 1 ELSE 0 END) AS have_topic2,
  AVG(CASE WHEN topic3 IS NOT NULL THEN 1 ELSE 0 END) AS have_topic3
FROM ethereum.logs
WHERE block_time BETWEEN
timestamp '2023-01-01 00:00:00' AND timestamp '2023-01-31 23:59:59'
LIMIT 100

また、CASE式と集約関数を組み合わせることで、レコードごとに保持していたデータをカラムごとに保持するフォーマットに変換することもできます。

コード8は、uniswap_v3のtradesテーブルから、USDT-WETHとUSDC-WETHという通貨ペアの取引に絞り込み、取引数や取引総額を集計するクエリです。

コード8. 通貨ペアの取引数・取引総額を集計するクエリ

SELECT token_pair, COUNT(1) AS cnt, SUM(amount_usd) AS total_amount_usd
FROM uniswap_v3_ethereum.trades
WHERE block_time BETWEEN
timestamp '2023-01-01 00:00:00' AND timestamp '2023-01-31 23:59:59'
AND token_pair IN ('USDT-WETH', 'USDC-WETH')
GROUP BY token_pair
LIMIT 100

コード8の集計結果を、日次の取引数や取引総額に細分化して分析することを考えます。このとき、GROUP BY句にblock_dateを加えることで求める結果は計算できますが、すべての計算結果が別々のレコードとして表示されるため、異なる通貨ペアの同じ日付での取引の比較などが少し難しくなります。そこで、CASE式とSUM関数を用いて、レコードは日付ごとに集約し、カラムで通貨ペアの場合分けをおこなうことで、同じ日付の取引を比較分析しやすいフォーマットに変換できます。

コード9.CASE式とSUM関数を用いて、異なる分類のデータをひとつのレコードに集約するクエリ

SELECT block_date,
  SUM(CASE WHEN token_pair = 'USDT-WETH' THEN 1 ELSE 0 END) AS usdt_weth_cnt,
  SUM(CASE WHEN token_pair = 'USDC-WETH' THEN 1 ELSE 0 END) AS usdc_weth_cnt,
  SUM(CASE WHEN token_pair = 'USDT-WETH' THEN amount_usd ELSE 0 END)
AS usdt_total_amount_usd,
  SUM(CASE WHEN token_pair = 'USDC-WETH' THEN amount_usd ELSE 0 END)
AS usdc_total_amount_usd
FROM uniswap_v3_ethereum.trades
WHERE block_time BETWEEN
timestamp '2023-01-01 00:00:00' AND timestamp '2023-01-31 23:59:59'
AND token_pair IN ('USDT-WETH', 'USDC-WETH')
GROUP BY block_date
ORDER BY block_date
LIMIT 100

Window関数

SQLのベースとなっている関係代数や集合論では、データの順序を扱うために特殊な手順が必要でした。しかし、SQLが時系列データやその他の順序付きデータの分析用途にも用いられるようになり、データ分析に特化した構文の需要が高まりました。そこで導入されたのが、Window関数と呼ばれる関数群です。

コード10は、コード9で計算したuniswap_v3の日次取引データに対して、Window関数を用いたサンプルクエリです。Window関数の多くは、集約関数に続いてOVER句を用いる形で記述できます。

例えば、SUM() OVER()関数を用いると、GROUP BYによるレコードの集約をおこなうことなく、各レコードに対して全体のデータの合計値を付加することができます。また、OVER句の中にORDER BY句で順序を指定することで、先頭からそのレコードまでの累計値を計算して各レコードに付加することができます。LAGやLEAD関数は、現在のレコードの前後のレコードの値を参照するために用いられます。例えば、１月２日の取引数を保有しているレコードに対して、１月１日や１月３日など、他の日付のレコードを参照することが可能です。

コード10. Window関数でデータの累計や総計を計算するサンプルクエリ

WITH daily_trades AS (
  SELECT block_date,
    SUM(CASE WHEN token_pair = 'USDT-WETH' THEN 1 ELSE 0 END) AS usdt_weth_cnt,
    SUM(CASE WHEN token_pair = 'USDC-WETH' THEN 1 ELSE 0 END) AS usdc_weth_cnt,
    SUM(CASE WHEN token_pair = 'USDT-WETH' THEN amount_usd ELSE 0 END)
AS usdt_total_amount_usd,
    SUM(CASE WHEN token_pair = 'USDC-WETH' THEN amount_usd ELSE 0 END)
AS usdc_total_amount_usd
  FROM uniswap_v3_ethereum.trades
  WHERE block_time BETWEEN
    timestamp '2023-01-01 00:00:00' AND timestamp '2023-01-31 23:59:59'
  AND token_pair IN ('USDT-WETH', 'USDC-WETH')
  GROUP BY block_date
)
SELECT
  block_date,
  usdt_weth_cnt AS daily_cnt,
  SUM(usdt_weth_cnt) OVER(ORDER BY block_date) AS accum_cnt,
  SUM(usdt_weth_cnt) OVER() AS total_cnt,
  LAG(usdt_weth_cnt) OVER(ORDER BY block_date) AS prev_daily_count,
  LEAD(usdt_weth_cnt) OVER(ORDER BY block_date) AS next_daily_count
FROM daily_trades
ORDER BY block_date
LIMIT 100

Window関数では、OVER句の中にPARTITION BY句を用いて、集約範囲のグルーピングをおこなうこともできます。コード11では、Tracesテーブルの各レコードに対して、同じトランザクションIDを持つレコードの総数や、同じトランザクションIDを持つレコード内でtrace_address順に並べ替えたときのインデックス番号を付与する例を示します。

コード11. PARTITION BY句によるWindow関数のグルーピング例

SELECT tx_hash, trace_address,
  COUNT(1) OVER(PARTITION BY tx_hash) AS trace_cnt,
  ROW_NUMBER() OVER(PARTITION BY tx_hash ORDER BY trace_address) AS trace_idx
FROM ethereum.traces
WHERE block_time BETWEEN
timestamp '2023-01-01 00:00:00' AND timestamp '2023-01-01 23:59:59'
ORDER BY tx_hash, trace_address
LIMIT 100

CASE式やWindow関数を用いることで、SQLを単なるデータ抽出だけでなく、高度なデータ分析のツールとして活用できます。SQLを用いて計算した結果は、別のSQLクエリの入力に使うこともできるため、分析結果の共有や再利用性も高く、複数人のチームやプロジェクトによる大規模なデータ分析にも有用です。

次回予告

今回の記事では、TracesやLogsといったEVMのデータ構造の深掘り、および、CASE式やWindow関数といった分析用途で頻出するSQL構文について解説しました。次回記事では、本連載の最終回として、より発展的なオンチェーンデータ分析の事例についてご紹介します。

連載一覧

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

#Ethereum #オンチェーン分析