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

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

初級

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)。

最終編集者: @pettinarip(opens in a new tab), 2023年12月4日