【コピペで学べる】Dune Analytics: 初心者向けガイド
初めに
この記事は2022年6月7日に公開された@0xPhillan氏による「Dune Analytics: A Guide for Complete Beginners」を許可を得た上で翻訳した記事になります。
*原文執筆時(2022年6月7日)から時間が経過しているため、検索結果やスクリーンショットの情報と実際の情報が異なる場合があります。予めご了承ください。(実データと照らし合わせながら、進めていくので問題ない内容となっています。)
導入
Dune Analyticsの使い方を学ぶDune AnalyticsとSQLの完全な初心者向けのガイドです。また、最新の情報に興味がある方は@0xPhillanをフォローをお願いいたします。
Dune Analyticsは、現在公開されている、無料のブロックチェーンデータ分析ツールです。Dune Analyticsを使えば、SQLを使ってDuneのウェブサイトからクエリできる公開データベースを通じて、ブロックチェーンデータにほぼリアルタイムでアクセスできるようになります。
Duneはブロックチェーンのデータをデータベースに追加する前にデコードします。つまり、バイトコードを自分で把握する必要がありません。
Duneのデータセットエクスプローラーを使えば、データセット、特定のスマートコントラクト、イベント、またはコールを確認することができるようになります。
Duneの最近の発表によると、Duneはブロックチェーンのデータをデコードしてからデータベースに追加しています。Duneが最近発表したV2エンジンはパフォーマンスを10倍向上させています。
このブログを通じてできるようになること
パート 1: Dune インターフェースの概要と機能の理解
パート 2: SQL を使って独自のクエリとチャートを作成する(基礎から)
パート 3: ダッシュボードを整理、成形する
このステップバイステップガイドでは、Pooly NFTのダッシュボードのクエリを構築します。
https://dune.com/phillan/pooly-nft-by-0xphillan
パート1 / Dune の概要と機能
Dune.comにアクセスして、 ウェブサイトを初めて開くと、下記のようなウィンドウが表示されます。
このウィンドウは、上部にダッシュボード、クエリ、ウィザード(ユーザー)を切り替えるビューチェンジャーがあります。
左側にダッシュボード、クエリ、ウィザード（ユーザー）のリスト、右側に検索設定を確認できる詳細ビューがあります。
パート1.1 / ダッシュボード
ダッシュボードは、一連のチャート、カウンター、その他の情報として配置されたクエリの一覧であり、特定のデータに関するデータをユーザーに提供するものです。
下記のスクリーンショットでは、 @hildobbyが作成した Ethereumダッシュボードを開いています。ここでは、Duneのデータベースから引き出されているEthereumに関する、あらゆる種類のデータを、合計、時系列等のチャートとして閲覧することができます。
Dune では、すべてのダッシュボードが公開されています。つまり、自分で作ったものや他の人が作ったダッシュボードを、誰でも見ることができ、フォーク（コピー）することもできます。このことにより、ダッシュボードの作成時間が大幅に短縮され、他のユーザーのクエリを参考にしてカスタマイズして使用することもできます。
パート1.2 / クエリ
ダッシュボードはクエリの集合体で、ダッシュボード要素のタイトルをクリックすると、そのチャートのSQLクエリに移動することができます。
ここでは、クエリ (上、ブラック ボックス) と出力チャート (下) の 2 つの主要な画面上の要素を確認できます。どのブロックまたはチャートをクリックしても、ユーザーがそのチャートをどのように作成したかを確認することができます。
パート1,3 / フォーク
ダッシュボード全体やチャートのクエリだけを自分のアカウントに保存したい場合は、右上の「フォーク」をクリックすると、フォークされた画面上のすべてが新しいウィンドウに複製され、そこで編集してから自分のアカウントにビューを保存することができます。
@hildobby https://dune.com/queries/663019/1231425
イーサリアムの価格チャートをフォークしてみましょう。
クエリで「Fork」を押すと、以前のコードが既にコピーされた状態でクエリ エディターが表示されます。
パート1,4 / クエリエディタ
ここでは、画面上の様々な要素を紹介します。
1, Query location & name - 保存を押すと、名前を変更することができます。
2, Dataset explorer - 特定のデータセットを検索します。
3, Query window - SQLクエリをここに入力します。
4, Visualization selector- クエリの結果、フォークされた折れ線グラフ、新しい可視化のいずれを表示するかを選択します。
5, Run - クエリウィンドウに表示されているクエリを実行します。
6, Results/Visualization - クエリの結果、またはクエリの結果で作成したビジュアライゼーションを表示します。
7, Save - (フォークした)クエリを保存します。
パート1,5 / データセットエクスプローラーとデータカテゴリー
データセットエクスプローラーを詳しく見ていきましょう。データセットエクスプローラーには、6つの機能エリアがあります。
1, Chain selection
2, Dataset search
3, Browse raw blockchain data
4, Browse data of decoded contracts
5, Browse abstractions
6, Browse community-supplied data
Dataset Selection
どのチェーンのデータを利用するかを選択できます。「Dune Engine V2 (Beta)」を選択すると、マルチチェーンクエリや10倍の性能向上など、Duneの最新の機能拡張を利用することができます。
他のチェーンを選択すると、カテゴリーの選択（前の画像の項目3～6）が消え、代わりにコントラクトコールと対話可能なイベントのリストが表示されます。
Searc
検索フィールドに検索パラメータを入力すると、Duneはそのキーワードを何らかの形で含むすべてのテーブルを検索します。
注意: Dune Engine V2 と古い検索機能では、異なる方法で結果を返します。旧検索機能はすべての結果のリストを返しますが、Dune Engine V2 はネストされた結果のリストを返します。今回はV2エンジンを使用する予定です。
Raw Blockchain Data
ブロックチェーンデータをクリックすると、Duneがサポートする様々なブロックチェーンに対するクエリを、データ構造の形で加工された状態で見つけることができます。構造の各レベルで、探している特定の検索結果をフィルタリングするオプションも使用可能です。
これは、高レベルのブロックチェーンデータを取得するための非常に迅速かつ便利な方法です。
Decoded Projects
このページでは、Duneによってデコードされたプロジェクトを見つけることができます。デコードされたプロジェクトは、Duneチームが分解し、ラベルを付け、ユーザーが特定のデータを簡単かつ標準的に参照できるように表にしたものです。
検索結果が入れ子構造になっていることにお気づきでしょうか。最上位には検索可能なプロジェクトがあり、下位にはそのプロジェクト内の特定のスマートコントラクトをフィルタリングすることがます。
また、最後にそのスマートコントラクトから生成されたさまざまなテーブルが表示されます。テーブルのどれかをクリックすると、ブロックチェーンのデータと同じように、カラムのリストが表示されます。
Abstractions
抽象化とは、さまざまなクエリとデータの断片を組み合わせ、形成するカスタムテーブルのことです。抽象化されたテーブルを利用することで、ユーザーは様々なデータを手動で組み合わせることなく、特定のデータをより簡単に照会することができます。
一般的に、抽象化機能は大きく2つのカテゴリーに分けられます。
・分野別データ：分野に特化したデータ
・プロジェクトの抽象化 プロジェクト固有のデータ
抽象化のサブメニューから、セクター別かプロジェクト別かを指定するラベルの付いた抽象化リストを見ることができます。
Community
コミュニティセクションは、抽象化セクションの延長と考えることができますが、データ集計は、Duneコミュニティのメンバーによって提供されるものです。
コミュニティセクションのエントリーが1つしかないのを不思議に思うかもしれません（「flashbots」） - それはDune Engine V2がリリースされたばかりだからです。
今後、信頼できるコミュニティメンバーによって、より多くのコミュニティビルドデータセットが作成されることが期待されます。
Dataset Explorer Labels
下の図は、Dune EngineV2のローンチ時点で、Dune内のデータがどのようにまとめられているかを示しています。
4つの主要なデータカテゴリは、Rawblockchain date(ブロックチェーンデータ)、Decoded project(デコードされたプロジェクト)、Abstractions(抽象化)、Community(コミュニティ9で、様々なデータタイプを保持できるテーブル形式で様々なブロックチェーンのデータが保持されています。
パート1,6 / 作成したクエリの保存方法
まず、このクエリを保存してみましょう。保存すると、いくつかのことが起こります。まず、クエリに名前を付けるよう求められます。
名前を入力したら、（1）クエリの場所と名前が選んだデータのものに更新されていること、（2）クエリが実行されています。これは、Duneがデータベースから最新のデータを取得していることを意味し、データベースは様々なブロックチェーンから最新のデータで定期的に更新されています。
クエリの実行が終了すると、(3)クエリの結果が表示されます。
ここから、(1) 「Query results」, 「Line Chart」, 「New visualization」 のいずれかをクリックすると、(2) 結果/可視化ボックスとその下に表示される (3) 選択に関する設定内容が更新されます。ここで 「Add to dashboard」 ボタンを押すと、クエリ結果や視覚化を新規または既存のダッシュボードにすばやく追加できます。
(1) 右上の円をクリックし、(2) 「My queries」をクリックすると、アカウントのクエリリストが開きます。
クエリリストには、これまでにアカウントに保存したすべてのクエリが含まれます。以下のスクリーンショットでは、作成された最新のクエリを確認できます。
最初のクエリをフォークして保存し、視覚化することができました。
フォークできることがDuneの価値の源泉であり、他のウィザード(ユーザー)が先に作ったものをもとに、新しいクエリを簡単かつ迅速に作成することができるようになります。フォークした複数のクエリを組み合わせて、あなただけのダッシュボードを作ることができます。
フォークせずに、ゼロからダッシュボード（クエリと可視化の集合体）を作ってみましょう。これは、あなたの特定のプロジェクトに適したブロックチェーンの詳細がどこにあるのかを教えると同時に、SQLの基本を学ぶものです。
パート 2 / 最初のクエリの作成
このセクションでは、実際に下記の作業を行います。
1,特定のプロジェクトに必要な正しい情報を見つける方法
2,いくつかの基本的なSQL
まず、ダッシュボードの構成を決めます。
Pool Together (DeFi プロトコル)によるPooly NFTs を初期の題材とします。
https://mint.pooltogether.com/
Dune で「Pooly」を検索すると、コミュニティによって作成された Pooly NFT トラッカーがいくつか見つかります。
@0xbills によって作成された Pooly ダッシュボードのをクリックし、「Folk」 をクリックしてみましょう。
https://dune.com/0xbills/Pooly-NFT
ただ、今回は、ブロックチェーン分析の方法を学び、SQL も学ぶためにゼロから独自のものを構築します。
パート2.1 / 構築するクエリの決定
まず、ダッシュボードに表示するグラフを決めます。 Pooly がホームページに構築したビューを再構築しましょう。
以下の 2 つのスクリーンショットを詳しく見てみると、オンチェーンのデータに基づいた指標を見ることができます。
ソース: https://mint.pooltogether.com/
ソース: https://mint.pooltogether.com/
ソース: https://mint.pooltogether.com/
上記のページからできそうなこと
・調達資金と目標調達額の比較（ETH建て
・米ドル建ての資金調達額と目標額の比較
・サポーターの総数（Poolyを購入したユニークなアドレス）
・アドレス、アドレスごとのNFT購入数、消費されたETHの合計を降順で表示するリーダーボード
・3種類のNFTの最大供給量と残り供給量
ただし上記のデータや要素は、時間のスナップショットに過ぎません。
別の分析指標も追加してみましょう。
・時間の経過とともに上昇した ETH の時系列チャート
現状では、Poolyのサイトと同じ方法でビューを作成することはできませんが、同じデータ (およびそれ以上のデータ) をキャプチャしてダッシュボードを作成することはできます。
パート2.2 / 取得情報の整理
Dune を始める前に、情報を整理する必要があります。 Web サイトから、PoolTogether が3種類のNFT を販売していることがわかります。
・Supporter – 0.1 ETH で収集可能な 9 のランダムなコレクションのうちの 1 つ
・Lawyer – 1 ETH で 1 つのアートワークのみ
・Judge – 75 ETH のアートワークは 1 つだけ
Poolyは3つのNFTを1つの契約で販売しているのか、それとも3つの異なる契約で販売しているのでしょうか？
Etherscanを確認し、Poolyに関連するスマートコントラクトを見つけられるかどうか見てみましょう。Etherscan.ioを開いたら、「Pooly」と入力し、これらのスマートコントラクトの所有者が、Etherscanに登録しているかどうかを確認します。
3つのスマートコントラクトがあり、3つのNFTコレクションにそれぞれ対応しているようです。さらに、それぞれのPoolyがERC721トークンであることも分かりました。
3つのコレクションをそれぞれ開き、アドレスの上にカーソルを置くと表示されるコピーアイコンをクリックして、スマートコントラクトのアドレスをコピーします。
また、ページの下部には、最近のすべての取引も表示されるので、後でトラブルシューティングを行う際に役立ちます。
ソース: https://etherscan.io/token/0x90B3832e2F2aDe2FE382a911805B6933C056D6ed
クエリを作るために、Dune から適切なデータを取得するには、これらのスマートコントラクト アドレスが必要です。
0.1 ETH Pooly Supporter:
0x90B3832e2F2aDe2FE382a911805B6933C056D6ed1.0 ETH Pooly Lawyer:
0x3545192b340F50d77403DC0A64cf2b32F03d00A975 ETH Pooly Judge:
0x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523パート2.3 / Duneで最初のクエリを作成する準備をする
まず、dune.com にアクセスし、画面右上の 「New Query」をクリックします。
クエリエディターが開き、クエリの作業を開始できます。
パート2.31 / クエリ1：ETHで調達した資金
まず、左上の「7.Dune Engine V2 (Beta)」から「1.Ethereum」に切り替えます。PoolyはEthereum上にあるので、このクエリにはEthereumのデータが必要です。現状では、「1.Ethereum」のテーブルは、ベータ版になったばかりの「Dune Engine V2」より使いやすいのでこちらを選択します。
最初のクエリでは、ETH 建てで調達した資金を表示するカウンターを作成します。これを行うには、以下のコードをDuneのクエリフィールドにコピーし、「run」（またはCTRL+Enter）を押します。
select SUM("value"/1e18) from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
https://dune.com/queries/882266
上のコードは、Dune のデータベースを解析して要求された特定のデータを取得する SQLクエリです。Dune のデータベースは様々なテーブルの集合体であり、それぞれが引き出したい特定の情報を格納してます。
・必要なデータ (テーブルのどの列) を指定します。
・そのデータを変換するかどうか
・どのテーブルからデータを取得するか
・データをフィルタリングするかどうか
上記を説明するために、上記のコードを少しずつ実行してみます。以下のコードを Dune のクエリ エディターにコピーして実行します。
select * from ethereum.transactions
where "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'多くの情報を含むテーブルを取得できます。
SQL コードの要素分解
このコードは、「select all columns from transactions table within ethereum category where value in to column is \x3545192b340F50d77403DC0A64cf2b32F03d00A9 」と記載してあります。
わかりやすく言い換えると「Pooly2 (1 ETH) スマートコントラクトとのすべてのスマートコントラクトのやりとりがあるテーブルを見せてください」という意味です。
テーブル内のカラムを見るためにクエリを実行する必要はありません。データエクスプローラーを使えば、便利な検索機能でさまざまなテーブルヘッダを調べることができます。
3行目を完全に削除してフィルターを取り除くこともできますが、この場合、巨大なテーブルの値が返され、クエリの実行に長い時間がかかることになります。クエリの精度が高ければ高いほど、実行速度は向上します。
今回は、資金調達額だけを返したいので、すべてのカラムは必要ありません。そこで、「value」カラムのみを取得するようにコードを調整しましょう。
select "value" from ethereum.transactions
where "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'出力される列のデータが「value」だけになりました。
ETHではなく Wei建てになっているため、出力された値がかなり大きい形になっています。これを修正するには、「value」列に演算子を適用するする必要があります。
select "value"/1e18 from ethereum.transactions
where "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
SQLの1e18は10^18と同じで、Duneに1,000,000,000,000で割ってWeiの代わりにETHで表示するように指示を行いました。
また、値のリストではなく合計値が欲しいだけなので、「Valu」/1e18 を SUM() 文で囲みました。
select SUM("value"/1e18) from ethereum.transactions
where "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
Pooly2 に費やされた ETH の合計が表示されました。そして、3つのPooly NFTスマートコントラクトすべてに費やされた合計を取得したいので、他のスマートコントラクトに関する詳細を含めます。
そのために。さらに 2 行を追加する必要があります。
select SUM("value"/1e18) from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
@0xPhillan https://dune.com/queries/882266
「or」コマンドは「where」コマンドと連動しており、「to」列の値をフィルタリングする際に、1番目の値、2番目の値、3番目の値が見つかった場合にその行を考慮するよう指定するものです。
これで、3つのPooly契約に合計で773.7ETHが費やされたことがわかります。Pooly のWeb サイトにアクセスして、正しいかどうかを確認してみましょう。
Pooly Websiteでは776.5ETHと表示されています。ただ、2.8ETHも差があります。
Duneはブロックチェーンのデータを定期的に同期しています。そして、彼らがデータベースに同期するデータセットは非常に巨大であるため、ある程度の時間がかかっているという状態だと思います。
今後1～2時間で、データがリフレッシュされると予想されます。
さて、クエリが完了したので、後でダッシュボードに表示するためのカウンターを設定する必要があります。クエリ結果ボックスの下にある「new visualization」をクリックし、表示されるドロップダウンメニューから「counter」をクリックします。
最後に、「Add visualization」をクリックします。
カウンターが表示され、下にスクロールすると、さまざまな設定が表示されます。好みに合わせて設定を調整することができます。
(1)「Add to dashboard」をクリックし、(2)「New dashboard」を選択します。(3)ダッシュボードに名前を付けて、(4)「Save dashboard」をクリックします。ダッシュボードリストに新しいダッシュボードが表示されます。ここから、ビジュアライゼーションを追加したいダッシュボードの (5) "追加" をクリックします。追加されると、ラベルが「add」から「added」に変わります。
このサブメニューでダッシュボードの名前 (「Pooly NFT by 0xPhillan」) をクリックすると、トラッカーが表示されたダッシュボードに移動します。
すべてのクエリの設定が完了したら、ダッシュボードの編集に戻ります。
パート2.32 / クエリ 2: 米ドルで調達された資金
2つのアプローチがあります。
1, NFTの購入に使用された資金の米ドル建ての現在価値を使用する
2, 購入時の資金のUSDの価値を使用する
Etherscanでスマートコントラクトを見ると、776.5ETHの大部分がすでにスマートコントラクトから移動されており、執筆時点ではPooly NFTスマートコントラクトに299.2ETHが残っていることが確認できます。
Poolyのウェブサイトのスクリーンショットを見ると、776.5ETHは1411,249米ドル（1,817米ドル/ETH）で評価されており、Poolyのスマートコントラクト所有者が米ドルと取引せず、ETHとして資金を持っている可能性が示唆されています。
最終的に、Poolyがどちらのアプローチを取っているかを確認するのは難しいですが、USDの価値に対する両方のアプローチを試した方がいいと考えています。
1, 現在の価値とは、その資金が現在どの程度の価値を持っているのかを示す。
2, 購入時の価値から、購入者が意図したUSD寄付額を知ることができます。
というわけで、両方のクエリを作成します。
パート2.33 / クエリ 2a: 現在の ETH値で米ドルで調達された資金
これについては、前のコードをベースとして使用し、追加の行を挿入して現在の USD の値を取得します。 まず、作成したばかりのクエリをフォークしましょう。
次に、コードを次のように調整します。
select SUM("value"/1e18) * (
SELECT "price" FROM prices.usd
WHERE "symbol" = 'WETH'
AND "minute" < now() - interval '1 hours'
ORDER BY "minute" DESC
LIMIT 1
)
from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'SUM("value"/1e18)コマンドの直後に、乗算演算子*と大きなコードブロックが追加されていることにお気づきでしょう。
Duneでは、「Run selection」をクリックすることで、クエリの特定の部分をハイライトして、その部分のみを実行することができます。ここでは、(1) 括弧内の行だけをハイライトし、(2) その選択部分を実行してみましょう。
0xPhillan / https://dune.com/queries/883725
クエリ結果には、WETHの最新のUSD価格が表示されます。ここで追加すると、WETH の最新の価格に調達した ETH の額が乗算され、米ドルの価値が得られます。 このコードを分解してみましょう。
1, prices.usd テーブルから 「price」 カラムを選択します。
2, シンボル列を "WETH" でフィルタリングする。
3, 過去 1 時間のタイムエントリのみを検索 (これにより、クエリが大幅に高速化されます)
4, 降順で並べる (新しいものから)
5, クエリを1つの結果に限定する（最初の結果、すなわち最新の価格）。
このコード ブロックをよりよく理解するために、クエリを少し調整してみましょう。 (1) 「price」 を * (すべての列を返す) に置き換え、(2) 2 行目から 5 行目までのコードのみを選択し、(3) 選択を実行します。
クエリ結果には、5 つの列で構成される完全なテーブルが表示されます。まず、Etherscan.io でコントラクト アドレスを確認します。
0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2
このスマート コントラクトは、イーサリアム ブロックチェーン上の WETH 資産を制御します。これで、以前のスクリーンショットから、WETH 価格を USD で表示するテーブルのソースがわかります。 前の表を確認しましょう。
ここでは、1分ごとのETH to USDの値を追跡する「minute」というカラムがあります。クエリを「interval - '1 hours'」に制限したので、利用可能なデータの最新1時間分のみを取得します。私たちの目的では、実際には最新のデータ入力だけが必要なので、このクエリを最後の1時間に制限すると、クエリの速度が大幅に向上します。これを例えば「1 days」, 「3days」or 「1 weeks」などに変更すれば、より多くの履歴データを取得することができます。
ここで重要なのは、カラムの名前が「minute」であることです。したがって、このクエリは「minute」カラムを参照しており、時間に関するコマンドと誤解されないようにする必要があります。
コードをこのセクションの最初に変更したものに戻し、クエリを実行してみましょう。
@0xPhillan / https://dune.com/queries/883725
その結果、Pooly NFTと引き換えにPooly1、Pooly2、Pooly3のスマートコントラクトに転送されたETHの現在のUSDが表示されます。
このために、再びカウンターを使用します。スクロールダウンして、（1）前回のクエリからフォークされたカウンターをクリックし、（2）データソースを調整し、（3）ラベルを変更します。
@0xPhillan / https://dune.com/queries/883725
完了したら、忘れずに保存してダッシュボードに追加してください
追加すると、下のスクリーンショットのようになります。全体のデザインはこのガイドの最後で行います。見た目は気にせず続けてください。
パート2,34 / クエリ 2b: 購入時ETH値で米ドルで調達された資金
このクエリは、2つのテーブルにクエリを発行し、結果を組み合わせる必要があるため、少し複雑になります。具体的には、個々の取引を取得し、取引時のETHの価格を使用して各取引のETH値を変換する必要があります。
繰り返しますが、次のクエリに備え、まず前回のクエリをフォークしましょう。
@0xPhillan via https://dune.com/queries/883725
フォークされたコードから、次のようにします。
with poolyTransactions as
(
select
block_time,
value/1e18 as value_eth
from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
)
select
sum(value_eth * price)
from poolyTransactions tx
left join
(select minute, price from prices.usd
where symbol = 'WETH' and minute > '2022-05-01')
as prices on date_trunc('minute', block_time) = minute
@0xPhillan 経由 https://dune.com/queries/884492
ここでこのコードを実行すると、1,400 万 USD の「USD value」を受け取ることがわかります。このコードを 3 つのセクションに分けてみましょう。
セクション 1
参照する最初のテーブルを作成します。
ethereum.transactions テーブルから block_time と value_eth (wei の値を 10^18 で割って ETH に変換したもの、カスタム名を付けている) を保持する 「poolyTransactions」 と呼ぶ補助テーブルを作成することです。
このテーブルでは、私たちが知っている3つのPoolyアドレスをフィルタリングしています。以下、一行ずつ説明します。
・1行目：poolyTransaction as - 「poolyTransaction」という補助テーブルを以下のプロパティを持つように定義します。
・3～11行目：ethereum.transcationsテーブルに含めるカラムとフィルタを選択します。
・5行目: value/1e18 as value_eth - ここでは、別の計算を行う代わりにセクション 2 で直接参照できるように、列の名前を 「value_eth」 に変更します。
セクション2
ここでは、出力テーブルを作成します。セクション1で作成した補助テーブルである「poolyTransactions」からテーブルを作成していますが、まだ定義していない「price」というカラムも参照しています。
「price」はこの後の19行目で初めて定義する予定です
これは、poolyTransactionsをセクション3のprice.usdテーブルの特定の出力と結合しているためです。つまり、補助テーブルpoolyTransactionsと、次のセクションでprice.usdから作成したテーブルを使用して、テーブルを作成しているのです。
セクション 3
ここで、別のテーブルと結合するテーブルを定義します。 「left join」キーワードを使用すると、これを行うことができます。
・18行目：left join - キーワードは、最初のテーブル（左テーブル）と別のテーブル（右テーブル）を結合することを示すために使用します。つまり、セクション1で定義した最初のテーブルがベーステーブルとして機能することを意味します。
・19-20行目: price.usdから作成するテーブルを定義しています。20行目では、期間を「2022-05-01」に制限しています。Poolyスマートコントラクトは5月にのみ展開されたため、Ethereumのgenesisブロックまでデータを解析する代わりに、より小さなタイムフレームに制限すれば、テーブルのクエリを大幅に高速化できます。
・21行目：as prices - これは、今後の参照を容易にするために、19～20行目のテーブルに「price」という名前を付けています。
・22行目: on data_trunc('minute', block_time) = minute - これは、補助テーブル（セクション1）と価格テーブル（セクション3の19～20行目）を結合する行です。つまり、分以外のデータ（秒、ミリ秒など）をすべて削除するのです。
prices.usd テーブルはすでに分単位に切り捨てられているため、ここでさらに変換する必要はありません。そして、price.usd の分カラムが補助テーブルの分カラムとマッチングされ、正しい価格が prices.usd から poolyTransactions の対応する分タイムスタンプに割り振られるのです。
3 番目のセクションをよりよく視覚化するために、理解しやすいようにパーツを再編成しました。
(1) poolyTransactionsテーブルを作成し、(2)SQLに別のテーブルと結合するように指示します。このテーブルには、price.usdテーブルから分と価格のカラムを定義します。この作成した prices.usd テーブルを、左側のテーブル poolyTransactions に分単位の時間をマッピング変数として結合しています。
テーブルを結合するためには、両テーブルが全く同じ項目を持つ必要がありますが、変数 block_time を分単位で切り詰めると、両テーブル間で分単位が一致するように作成されます。こうすることで、(5) poolyTransactions テーブルは価格カラムを含むように更新され、価格値がそれぞれの日付と一致するようになります。
ここからは、結合された poolyTransactions テーブルに問い合わせ、各行の value_eth と ETH の価格を掛け合わせた結果を合計するだけである。
これでカウンターを追加し、保存してダッシュボードに追加しました。
パート2.35 / クエリ 3: サポーターの総数
次のクエリでは、Pooly NFTを購入したユニークなアドレスをカウントしたいと思います (つまり、supportの合計数)。つまり、あるアドレスが3つのPoolyタイプすべてにわたって複数のPoolyを購入したとしても、1回だけカウントすればよいということです。
このために、まず一番最初のクエリを開いてフォークし、保存も忘れないようにしましょう。
@0xPhillan https://dune.com/queries/882266
ここでは、最初の行を変更します。
select COUNT(DISTINCT "from") from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
@0xPhillan / https://dune.com/queries/887079
「COUNT」変数はすべてのトランザクションをカウントし、「DISTINCT」はすべてのユニークなエントリーが一度だけカウントされることを確認します。
その結果、4660人のユニークサポーターが得られました。これをPoolyのウェブサイトのユニークサポーターと比較すると、非常に近いことがわかります。
ソース: https://mint.pooltogether.com/
これは、Dune のデータベースの更新と最新のブロックチェーンの状態との間に少し遅れがあるため、クエリが正しいことを示しています。 最後に、カウンターの視覚化を変更して、ダッシュボードに再度追加します。
パート2.36 クエリ 4a / ERC-721を使用したリーダーボード。抽象化
次に、アドレス、アドレスごとに購入したNFTの数、費やしたETHの合計を降順に含むリーダーボードを構築してみましょう。
ここでも、フィルタリングされたアドレスを再入力する必要がないように、前のクエリをフォークしましょう。次に進む前に、この新しいクエリを保存することを忘れないでください。
リーダーボードを見ると、必要な情報が3つあります。まず、購入者のアドレス、次に購入したNFTの数、そして最後に購入したすべてのNFTに費やされたETHの量です。
ソース: https://mint.pooltogether.com/
ここでは、保有されているNFTではなく、購入されたNFTについて見ています。誰かがNFTを購入（minted）し、安全な財布に移動したり、後の時点で転売したりすることは十分にあり得ます。私たちは初めて購入（mints）されたものだけに注目しています。
以下のクエリを使用してこれを実現します。
with poolyTransactions as
(
select
"from",
hash,
value/1e18 as value_eth
from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
)
select
"from", nfts_purchased, value_eth
from poolyTransactions
left join
(Select evt_tx_hash, COUNT("tokenId") as nfts_purchased
From erc721."ERC721_evt_Transfer"
Where (contract_address = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or contract_address = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or contract_address = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523')
and "from" = '\x0000000000000000000000000000000000000000'
group by 1)
as nfts
on evt_tx_hash = hash
ORDER BY 3 desc
@0xPhillan 経由 https://dune.com/queries/887141
これは「購入時のETH値でUSDで調達した資金」のクエリと酷似していることに気づきますが、同じ手法を使用しているためです。
まず、poodyTransactionsテーブルに取引データを集め、その上に共通のマッピング値で2番目のテーブルを残しています。
ここで、2つ目のテーブルにはERC-721. 「ERC721_evt_Transfer」テーブルを使用しています。これは、Duneが管理している、Ethereum上のすべてのNFT転送を追跡する抽象化されたテーブルです。
データセットエクスプローラーを使用して、"ERC-721 "と入力してください。そして「ERC721_evt_Transfer」までスクロールすると、その特定のテーブルに含まれるすべてのものを見ることができます。
また、2番目のテーブルのコマンドのみをハイライトして、どのような出力になるかを確認することもできます。
@0xPhillan 経由 https://dune.com/queries/887141
スマートコントラクトによって新たにmintされたNFTのみが欲しいので、「from」アドレスはnullアドレスを指定する必要があります。Ethereumでは、すべてのNFTはnullアドレスから発行されます。
各トランザクションの「tokenId」の量をカウントすることで、各トランザクションで発行されたNFTの総数をカウントすることができます。
また、フィルタの定義方法について、最初の3つのフィルターは括弧で囲まれるようになり、最後のフィルターは括弧の外側にあります。
括弧は、SQL で計算コマンドを実行する場合と同様に、and/or フィルタコマンドが評価される順序を決定します。最初の3つのステートメントを囲まなかった場合、and 条件は最後のフィルター設定にのみ適用されます。
from null addressフィルターを、前のフィルターの結果すべてに適用させたいので、括弧を追加する必要があります。
最後に、「COUNT」コマンドを使うので、どの列でカウントするか（つまり、どの変数にカウントをロールアップするか）を指定する必要があります。ここでは、「group by」コマンドを使って、「tokenId」のカウントをテーブルの最初のカラムである「evt_tx_hash」にグループ化することを指定する。
先ほど、この2つ目のテーブルをトランザクション・テーブルにマッピングするために、共通のマッピング値が必要であると述べました。ここでは、トランザクションハッシュを使用して、トランザクションごとに購入された NFT の数を poolyTransactions テーブルにマッピングし、今回はトランザクションハッシュも要求しています。
つまり、最終的には ERC721. 「ERC721_evt_Transfer」 テーブルのトランザクションハッシュ（"nfts "と命名）と poolyTransactions テーブルのトランザクションハッシュ（poolys の購入に使われたトランザクションのみを含む）をマッピングしました。
出力は、購入者のアドレス、購入したNFTの合計数、および使用されたETHの合計値を含むテーブルです。
@0xPhillan 経由 https://dune.com/queries/887141
最後に、Dune に「ORDER BY 3 desc」を指示します。これは、出力テーブルの 3 番目の列を降順で並べ替える必要があることを意味します。
@0xPhillan / https://dune.com/queries/887141
素晴らしい！リーダーボードが完成しました。 Pooly NFT Web サイトのリーダーボードと比較してみましょう。
すべての数字が一致するわけではありませんが、この一覧から、いくつかのアドレス、購入したNFT、費やしたETHの合計の数字が一致することがわかります。これは、Duneとリアルタイムのブロックチェーンデータとの間の同期タイミングの問題です。
同時に、クエリを保存し、ダッシュボードに追加することを忘れないでください。
パート 2.37 / クエリ 4b: poolysupporterを使用したリーダーボード
ERC721."ERC721_evt_Transfer" テーブルを使用する代わりに、Dune チームがまとめた poolysupporter."PoolyNFT_call_mintNFT" デコード テーブルを使用することもできます。
with poolyTransactions as
(
select
"from",
hash,
value/1e18 as value_eth
from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
)
select
"from", nfts_purchased, value_eth
from poolyTransactions
left join
(Select call_tx_hash, "_numberOfTokens" as nfts_purchased
From poolysupporters."PoolyNFT_call_mintNFT"
where contract_address = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or contract_address = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or contract_address = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
)
as nfts
on call_tx_hash = hash
ORDER BY 3 desc方法は上記と同じですが、このテーブルでは、ERC721.ERC721テーブルからどのトランザクションが呼び出されたかを判断するのにNULLアドレスを使う代わりに、mintNFT関数を呼び出したすべてのトランザクションハッシュを直接返せるという点が異なります。"ERC721_evt_Transfer "テーブルはミント・トランザクションです。
@0xPhillan 経由 https://dune.com/queries/887280
poolysupporter データセットを使用すると、特定のコントラクトコールを参照できるため、より具体的で詳細なクエリを実行できます。 2 つのテーブルの結果を比較して、何も問題がないことを確認してみましょう。
出力は同じです。 クエリを保存してダッシュボードに追加することを忘れないでください。
パート2.38 / Query 5: NFTの最大供給量と残り供給量
クエリ4の代替バージョンでは、poolysupporter関数を使用しました。データセットエクスプローラーでpoolyを検索すると、"PoolyNFT_call_maxNFT "という関数も表示されます。
この関数呼び出しを使用して、作成された NFT の最大値を直接取得できます。
残念ながら、それは不可能です。この関数は「read」関数であり、この関数が呼び出されたときにオンチェーンレコードはありません。以下のイーサスキャンを参照してください。
maxNFT変数は、デプロイメント契約によってPooly Supporterスマートコントラクトが展開される際に設定されますが、残念ながら記事作成の時点では、デプロイメントスマートコントラクトは解読されていないため、オンチェーンデータから最大ミント数を得ることはできません
その代わりに、各スマートコントラクトのmaxNFTの数字を手動で入力する必要があります。
with poolyContracts as
(
Select contract_address,
COUNT("tokenId") as nfts_purchased
From erc721."ERC721_evt_Transfer"
Where (contract_address = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or contract_address = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or contract_address = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523')
and "from" = '\x0000000000000000000000000000000000000000'
group by 1
)
select
CASE contract_address
WHEN '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed' then 'Pooly_Supporter'
WHEN '\x3545192b340F50d77403DC0A64cf2b32F03d00A9' then 'Pooly_Lawyer'
WHEN '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523' then 'Pooly_Judge'
END as NFT_name,
nfts_purchased,
CASE maxNFT_Supply
WHEN '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed' then 10000
WHEN '\x3545192b340F50d77403DC0A64cf2b32F03d00A9' then 100
WHEN '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523' then 10
END as NFT_Supply,
CASE maxNFT_Supply
WHEN '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed' then 100-(nfts_purchased/10000.0*100)
WHEN '\x3545192b340F50d77403DC0A64cf2b32F03d00A9' then 100-(nfts_purchased/1000.0*100)
WHEN '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523' then 100-(nfts_purchased/10.0*100)
END as percent_supply_remaining
from poolyContracts
left join
(
Select contract_address as maxNFT_Supply
From erc721."ERC721_evt_Transfer"
Where (contract_address = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or contract_address = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or contract_address = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523')
and "from" = '\x0000000000000000000000000000000000000000'
group by 1
)
as maxNFT
on maxNFT_Supply = contract_address
ORDER BY 3 desc
0xPhillan / https://dune.com/queries/887355
ここで、少し工夫が必要です。SQLで特定のテーブルエントリに手動で数字を追加するのは難しい作業なので、これを読みやすいテーブルに残すには、いくつかの作業を適用する必要がありました。
ここでも結合を使って2つのテーブルを組み合わせますが、4つの列のうち3つにはCASE WHENステートメントを使って、表示したい特定の情報を出力するようにします。
まず基本テーブルを作成し、次に2番目のテーブルを作成し、2番目のテーブルと1番目のテーブルを左結合して、テーブルの入力を人間が読めるように変換し、簡単な演算を行うようにします。
2つ目のテーブルを作成する理由は、SQLでは1つのクエリで1つのカラムを2度呼び出せないからです。実際、1つのカラムを複数回呼び出す必要があり、それぞれのカラムの呼び出しを別々に変換する必要があります。
しかし、結合されたテーブルでは、2番目のテーブルからカラムを複数回呼び出すことができ、必要なカラムの特定の行に対して必要な出力を作成することができます。
このクエリを理解しやすいように、4つに分けて考えてみましょう。
セクションの順番に注意してください。
Section 1
このセクションでは、「poolyContracts 」というテーブルを定義し、3つのPoolyコントラクトアドレスからNULLアドレスに由来するすべての個々のtokenIdsをカウントします。そして、これらを最初の列でグループ化し、poolyスマートコントラクトごとにminted NFTsを返します。
Section 2
このコードブロックでは、3 つのコントラクトアドレスのそれぞれの 1 つだけをクエリに表示するようにします。これは、「group by 1」コマンドを使用して行います。つまり、最初の列の一意のエントリで結果をグループ化します。
group by コマンドを使用しない場合、クエリはこれらのコントラクトアドレスに関連するすべての転送イベントを返しますが、それぞれを 1 回表示するだけで済みます。その理由は、次のセクションで説明します。
2 番目のテーブル
さらに、contract_address カラムの名前を maxNFT_Supply に変更し、このテーブルと poolyContracts テーブルを結合するカラムを定義できるようにしました。
Section 3
このセクションでは、結合されたテーブルからカラムを呼び出すことができます。呼び出します。
1, contract_address
2, nfts_purchased
3, maxNFT_Supply
4, maxNFT_Supply
カラム1、3、4で3回同じデータを取得していること、そしてカラム3と4は同じ同一のカラムです。2つのテーブルを結合したためこのようなデータの抽出が可能になりました。もし、テーブルが結合される前にcontract_addressを2回呼び出すと、クエリエディタはエラーメッセージを返してしまいます。
次に、列1、3、4にはそれぞれCASE WHEN句が埋め込まれています。先に作成した2つのテーブルには、各スマートコントラクトについて一意の行しかないため、CASE WHEN文を使用して、特定のスマートコントラクトアドレスが現れたら（3つのオプションのうちの1つ）、代わりに他のものを返すように指定することはできません。
最初の列では、各スマートコントラクトのアドレスをそれぞれのNFTの名前に置き換えるようクエリエディタに指示しているのがわかります。
3列目では、Poolyのウェブサイトに記載されている既知の最大NFT量に置き換えています。
そして4列目では、残りのNFT供給量のパーセントを計算する数式を使用しています。
これらのステートメントでは、演算に使用される数値の少なくとも1つに小数点以下が含まれている必要があります。これがないと、SQLクエリは整数を返すと解釈され、これらの計算で小数を取得することができなくなります。
.0 "を含めることで、この計算で小数点を返すようにサーバーに指示することができます。
Section 4
そして最後に、3列目で出力を降順（大きいものから小さいもの）に並べることを指示します。
こちらの表も出来上がりました。クエリを保存し、テーブルに必要な変更を加えて、ダッシュボードに追加します。
パート2.39 クエリ 6: ETH調達額の時系列チャート
最後のクエリでは、NFT販売によって調達されたETHの量の時系列チャートを作成します。
select
block_time as time,
sum(value/1e18) over (order by date_trunc('minute', block_time) asc) as cumu_value_eth
from ethereum.transactions
where ("to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523')
and date_trunc('day', block_time) < '2022-06-25'
https://dune.com/queries/887727経由の0xPhillan
これは、より短いコードですが、累積値を集計するための重要なコマンドであるoverコマンドを含んでいます。
このクエリでは、まず block_timeを選択し、次に ETH 値 (value/1e18) を block_time の分単位で合計し、それを直接昇順にソートして、列名を cumu_value_eth としています。
sum(value/1e18) over (order by date_trunc('minute', block_time) asc) as cumu_value_eth です。
さらに、このクエリではブロックタイムが2022-06-25を超えてはいけないという別のフィルタを最後に追加しました。これは、Poolyのウェブサイトによると、資金調達キャンペーンがほぼ終了する時期です。
こうすることで、エリアチャートはキャンペーンのデータのみを表示し、時間が経つにつれて引きずられるような平坦な線を追加することはありません。
エリアチャートを作成するには、(1) 「New visualization」 をクリックし、(2) ドロップダウンメニューで 「Area chart」 を選択し、最後に (3) 「Add visualization」 をクリックします。
Dune によって事前に選択された関連設定で、面グラフが自動的に表示されます。
あらかじめ選択されていない場合は、チャートの下にある設定で、見栄えがよくなるまで遊べます。
最後に、クエリを保存して、もう一度「Add to dashboard」を押してください。
パート3：ダッシュボードをクリーンアップする
たくさんのクエリを作成し、それらを直接ダッシュボードに追加しました。さて、どのように見えるか見てみましょう。最後のチャートをダッシュボードに追加した後、ダッシュボード名をクリックするだけです。
見栄えを良くする前に、デザインを整理する必要があります。 ダッシュボード画面の右上にある「Edit」をクリックして編集を開始します。
ここから、個々の要素を背景の赤いボックスで表示されるグリッドにドラッグ アンドドロップして要素を移動できます。また、左下隅にあるアイコンをドラッグして各要素のサイズを変更できます。
ドラッグアンドドロップとドラップで変更できます
ダッシュボードにテキストや画像を追加するには、ダッシュボード編集画面の右上にある「Add text widget」を押します。
デザインを整理するときに、これら 2 つのクエリが同じように見え、両方を表示していても実際にはあまり意味がないです。
PoolTogether がスマート コントラクトで ETH をいつ、どのように引き出しているかがわからないため、Pooly Web サイトの方法に則って行います。正しいものを削除して、別のクエリに置き換えます。
デザイン整理後のダッシュボードのイメージです。
@0xPhillan / https://dune.com/phillan/pooly-nft-by-0xphillan
これは視認性が上がり、Pooly Web サイトと同じフォーマットにも準拠する形で完成しました。
最後に
Dune Analyticsは、適切に使いこなすことができれば、有用なブロックチェーンデータ機能を提供することができる強力なプラットフォームです。
一連のレクチャーで、私はあなたに基本を教えることができたと思います。ここから先は、より大きな課題に挑戦し、さらに優れたダッシュボードを作るのはあなた次第です。あなたが作ったダッシュボードをぜひ私とシェアしてください。
この記事が気に入ったら、ぜひTwitterで@0xPhillanをフォローしてください。
Dune Analyticsに関する質問や、今後のガイドのアイディアがあれば、遠慮なくご連絡ください。
私のコードをレビューし、バグを知らせてくれた@superamscomに感謝します。このバグは現在修正されています。
この記事に関して
3万字前後あって少し雑になってしまっている部分があるかもしれません。(ごめんなさい)翻訳の間違いや、感想などあれば、下記のTwitterアカウントまで、気軽にご連絡いただければと思います。