SQLでイーサリアムの基礎的なトピックについて学ぶ

SQLクエリDune Analyticsトランザクション

初級

Paul Apivat

2021年5月11日

14 分の読書 minute read

イーサリアムに関するチュートリアルの多くはデベロッパ向けのものですが、データアナリストや、クライアント／ノードを実行することなくオンチェーンのデータを確認したい人々を対象とする学習リソースは多くありません。

このチュートリアルは、Dune Analytics(opens in a new tab)が提供するインターフェースを用いて、オンチェーンのデータに対して SQL（Structured Query Language）のクエリを実行することで、トランザクション、ブロック、ガスといったイーサリアムの基本的なコンセプトについての理解を深めるものです。

オンチェーンのデータは、イーサリアムやイーサリアム・ネットワークに関する理解を深めるのに役立つだけでなく、コンピュータ処理能力の経済学といった現在のイーサリアムが直面している課題（例：ガス代の上昇）や、より重要性が高いスケーリング・ソリューションに関する議論について、基本的な事項を理解する土台となるものです。

トランザクション

イーサリアムの新規ユーザーはまず、ETH 残高を持つエンティティであるユーザー管理アカウントを初期化する必要があります。イーサリアムのアカウントには、ユーザー管理アカウントとスマートコントラクトの 2 種類があります（ethereum.orgを参照)してください）。

すべてのアカウントは、Etherscan(opens in a new tab)のようなブロックエクスプローラーで表示できます。ブロックエクスプローラーは、イーサリアム上のデータポータルです。このポータルから、ブロックのデータ、トランザクション、マイナー、アカウント、および他のオンチェーンのアクティビティをリアルタイムで確認できます（こちらをご覧ください）。

しかし、外部のブロックエスプローラーが提供する情報と直接照合したい場合は、オンチェーンのデータに対するクエリを実行したいと思うかもしれません。 Dune Analytics(opens in a new tab)は、SQL に関する一定の知識を前提として、あらゆるユーザーにこのクエリ機能を提供します。

参考までに、イーサリアム・ファウンデーション (EF) のスマートコントラクトアカウントはEtherscan(opens in a new tab)で表示することができます。

EF のアカウントを含むすべてのアカウントは、トランザクションの送受信に使用できる公開アドレスを持つ点に留意してください。

Etherscan のアカウント残高は、通常のトランザクションと内部トランザクションで構成されています。内部トランザクションは誤解を招きやすい名前ですが、チェーンの状態を変更する 実際のトランザクションではありません。内部トランザクションとは、コントラクト（ソース(opens in a new tab)）を実行することで開始される「値の移転」を意味します。内部トランザクションは署名を持たないためブロックチェーンには 含まれず、Dune Analytics でクエリを実行することができません。

従って、このチュートリアルでは通常のトランザクションのみを取り上げます。通常のトランザクションに対しては、以下のようにクエリを実行します：

1WITH temp_table AS (
2SELECT
3    hash,
4    block_number,
5    block_time,
6    "from",
7    "to",
8    value / 1e18 AS ether,
9    gas_used,
10    gas_price / 1e9 AS gas_price_gwei
11FROM ethereum."transactions"
12WHERE "to" = '\xde0B295669a9FD93d5F28D9Ec85E40f4cb697BAe'
13ORDER BY block_time DESC
14)
15SELECT
16    hash,
17    block_number,
18    block_time,
19    "from",
20    "to",
21    ether,
22    (gas_used * gas_price_gwei) / 1e9 AS txn_fee
23FROM temp_table
すべて表示

これにより、Etherscan のトランザクションページで提供されるのと同一の情報が返されます。これら 2 つのソースを比較してみましょう：

Etherscan

Etherscan 上の EF のコントラクトのページ(opens in a new tab)

Dune Analytics

ダッシュボードは、こちら(opens in a new tab)からアクセスしてください。テーブルをクリックすると、クエリを確認できます（上記も参照してください）。

トランザクションの内容を見る

送信されたトランザクションには、（ソース）を含むいくつかの情報が含まれています。

Recipient：受信者のアドレス（「to」のクエリに該当したアドレス）。
Signature：トランザクションに署名するのは送信者の秘密鍵ですが、SQL でクエリできるのは送信者の公開アドレス（「from」）です。
Value：送信された ETH の量（ether列を参照してください）。
Data：ハッシュ化した任意のデータ（data列を参照してください）。
gasLimit：トランザクションで消費できるガスユニットの上限。ガスユニットは、計算ステップを示します。
maxPriorityFeePerGas：マイナーへのチップとして提供できるガス量の上限。
maxFeePerGas：トランザクションに対して支払い可能であるガス代の上限（baseFeePerGas と maxPriorityFeePerGas を含む）。

イーサリアムファウンデーションのパブリックアドレスへのトランザクションにつき、これらの具体的な情報をクエリしたい場合は以下を実行します：

1SELECT
2    "to",
3    "from",
4    value / 1e18 AS ether,
5    data,
6    gas_limit,
7    gas_price / 1e9 AS gas_price_gwei,
8    gas_used,
9    ROUND(((gas_used / gas_limit) * 100),2) AS gas_used_pct
10FROM ethereum."transactions"
11WHERE "to" = '\xde0B295669a9FD93d5F28D9Ec85E40f4cb697BAe'
12ORDER BY block_time DESC
すべて表示

ブロック

各トランザクションは、イーサリアム仮想マシン（EVM）の状態を変更します（ソース）。トランザクションは、ネットワークにブロードキャストされ、検証を経てブロックに追加されます。トランザクションごとに、ブロック番号が割り振られます。データを見るには、特定のブロック番号でクエリすることができます。ブロック番号: 12396854 は、執筆時点である 2021 年 11 月 5 日のイーサリアム・ファウンデーション内のトランザクションで最も最新のブロックです。

さらに、次の 2 つのブロックに対してクエリを実行すると、各ブロックが 1 つ前のブロックのハッシュ（親ハッシュ）を含んでいることが確認でき、ブロックチェーンがどのように形成されるかを理解できます。

各ブロックには、親ブロックに対する参照情報が含まれています。これは、hash列とparent_hash列の間に表示されます（ソース）。

Dune Analytics では、クエリ(opens in a new tab)は以下のように表示されます：

1SELECT
2   time,
3   number,
4   hash,
5   parent_hash,
6   nonce
7FROM ethereum."blocks"
8WHERE "number" = 12396854 OR "number" = 12396855 OR "number" = 12396856
9LIMIT 10
すべて表示

ブロックを調べるには、時間、ブロック番号、難易度、ハッシュ、親ハッシュ、およびノンスに対してクエリを実行します。

このクエリでは、トランザクションのリストだけは調べることができません。このためトランザクションのリストについては、state rootを使って後述する別のクエリを実行します。フルノードまたはアーカイブノードは、すべてのトランザクションと状態遷移を保存しますので、クライアントはいつでもチェーンの状態をクエリすることができます。これには大容量のストレージが必要になりますので、チェーンデータと状態データを分離することができます：

チェーンデータ（ブロックおよびトランザクションのリスト）
状態データ（各トランザクションによる状態遷移の結果）

状態ルートは後者（状態データ）であり、暗黙的なデータである（オンチェーンで保存されない）のに対し、チェーンデータは明示的なデータであり、チェーン自体に保存されます（ソース(opens in a new tab)）。

このチュートリアルでは、Dune Analytics を使って SQL でクエリ可能であるオンチェーンのデータを取り上げます。

すでに述べたように、各ブロックにはトランザクションのリストが含まれているので、特定のブロックに絞り込んでクエリを実行できます。さっそく、最新ブロック「12396854」を試してみましょう。

1SELECT * FROM ethereum."transactions"
2WHERE block_number = 12396854
3ORDER BY block_time DESC`

Dune では、このような SQL 出力が得られます：

ブロックチェーンにこの 1 つのブロックが追加されると、イーサリアム仮想マシン（EVM）の状態が変化します。ブロックチェーンでは、一度に数十、時には数百ものトランザクションが検証されます。このブロックの場合、222 件のトランザクションが含まれていました。

実際にトランザクションが成功した件数を調べるには、成功したトランザクションのみを絞り込むフィルターを追加します：

1WITH temp_table AS (
2    SELECT * FROM ethereum."transactions"
3    WHERE block_number = 12396854 AND success = true
4    ORDER BY block_time DESC
5)
6SELECT
7    COUNT(success) AS num_successful_txn
8FROM temp_table

ブロック 12396854 では、計 222 件のトランザクションのうち、204 件が正常に検証されました：

トランザクションリクエストは、毎秒あたり数十回発生しますが、ブロックがコミットされるのはおよそ 15 秒に 1 回です（ソース）。

約 15 秒ごとに 1 つのブロックが生成されることを確認するには、1 日に含まれる合計の秒数（86,400 秒）を 15 で割ることで、1 日に生成される平均ブロック数（およそ 5,760）が分かります。

2016 年から現在までに、イーサリアムで生成された 1 日あたりのブロック数については、この表を参照してください：

この期間に毎日生成されたブロックの平均数は、約 5,874 です。

クエリは、次のように行います：

1# query to visualize number of blocks produced daily since 2016
2
3SELECT
4    DATE_TRUNC('day', time) AS dt,
5    COUNT(*) AS block_count
6FROM ethereum."blocks"
7GROUP BY dt
8OFFSET 1
9
10# average number of blocks produced per day
11
12WITH temp_table AS (
13SELECT
14    DATE_TRUNC('day', time) AS dt,
15    COUNT(*) AS block_count
16FROM ethereum."blocks"
17GROUP BY dt
18OFFSET 1
19)
20SELECT
21    AVG(block_count) AS avg_block_count
22FROM temp_table
すべて表示

2016 年から現在までに 1 日に生成されたブロック数の平均は、5,874 をわずかに上回っています。反対に、86,400 秒を平均ブロック数である 5,874 で割ると 14.7 となるため、およそ 15 秒に 1 回の頻度でブロックが生成されたことが分かります。

ガス

ブロックのサイズは、制限されています。ブロックの最大サイズは、ネットワーク需要に応じて 12,500,000 ユニットから 25,000,000 ユニットの間で動的に変化します。ブロックのサイズを制限する理由は、フルノードに対してディスク容量や処理速度の要件（ソース）が過剰な負担となるのを防ぐために、無駄に大きなサイズのブロックが発生することを防ぐためです。

ブロックに対するガス上限という概念を理解するには、トランザクションをバッチ処理するために利用できるブロックのスペースをどれだけ供給できるか、と考えるとよいでしょう。ブロックのガス上限に対してもクエリを実行できるので、2016 年から現在までのグラフは以下のようになります：

1SELECT
2    DATE_TRUNC('day', time) AS dt,
3    AVG(gas_limit) AS avg_block_gas_limit
4FROM ethereum."blocks"
5GROUP BY dt
6OFFSET 1

さらに、イーサリアム・ブロックチェーン上で実行された処理（トランザクションの送信、スマートコントラクトの呼び出し、NFT のミント）のために実際に支払われた 1 日あたりのガスを調べることもできます。これは、利用可能なイーサリアムのブロックスペースに対する需要を示します：

1SELECT
2    DATE_TRUNC('day', time) AS dt,
3    AVG(gas_used) AS avg_block_gas_used
4FROM ethereum."blocks"
5GROUP BY dt
6OFFSET 1

これら 2 つのグラフを比較することで、 需要と供給の関係を確認することができます：

ここから、ブロックスペースが十分に供給されている場合、ガス価格はブロックスペースへの需要に応じて上下することが分かります。

最後に、イーサリアムチェーンにおける 1 日のガス価格の平均値を調べたい場合、クエリ時間が非常に長くなるため、イーサリアム・ファウンデーションがトランザクション 1 件あたりに支払ったガス代の平均値を調べるようにクエリを絞り込みます。

2016 年から現在までに、イーサリアム・ファウンデーションのアドレスに対して実行されたすべてのトランザクションにおいて支払われたガス価格を確認することができます。クエリは、以下のように実行します：

1SELECT
2    block_time,
3    gas_price / 1e9 AS gas_price_gwei,
4    value / 1e18 AS eth_sent
5FROM ethereum."transactions"
6WHERE "to" = '\xde0B295669a9FD93d5F28D9Ec85E40f4cb697BAe'
7ORDER BY block_time DESC

まとめ

このチュートリアルでは、クエリを実行し、オンチェーンのデータを確認することで、イーサリアムの基本的な概念やイーサリアム・ブロックチェーンの仕組みについて学びました。

このチュートリアルで使用したすべてのコードをまとめたダッシュボードは、こちら(opens in a new tab)からアクセスしてください。

データを通じて Web3 の知識をさらに深めたい方は、私を Twitter でフォローしてください(opens in a new tab)。

最終編集者: @HiroyukiNaito(opens in a new tab), 2023年8月15日