[8.21] 形で覚えるDune使い方講座<中級++>

あと一歩でDuneマスター、一緒に頑張ろう！

ta1suke

Aug 21, 2023

みなさんこんばんは、5日目のDune講座もそろそろ終盤に差し掛かりました。前回もかなり長くなりましたが、急いで書いたのでもう一回中級を補足する<中級++>の時間をください。

前回までの講座をまだみていない方はこちらから↓

[8.17] 形で覚えるDune使い方講座<初級>

ta1suke

August 17, 2023

Read full story

[8.18] 形で覚えるDune使い方講座<初級+>

ta1suke

August 18, 2023

Read full story

[8.19] 形で覚えるDune使い方講座<中級>

ta1suke

August 19, 2023

Read full story

[8.20] 形で覚えるDune使い方講座<中級+>

ta1suke

August 20, 2023

Read full story

前回の<中級+>の最後にでデータ型について触れたので、今回はそこの解説から始めます。その次に最大値、最小値の取得、グループ化後に条件指定、指定範囲の取得を解説しています。それでは早速本文に入りましょう。

Special thanks to @Guss3.eth

※コードブロックを使用して説明しているため、なるべくパソコンからのアクセスをお勧めします。またこの講座を受けるためにはパソコンが必要です。

データ型について

ここまでの講座であまりデータ型について気にする必要はありませんでしたが、ここからはこの「型」を意識したコーディングが要所で必要になってきます。

まず普通の生活をしていたら意識することは全くないと思いますが、プログラムの世界では基本的にデータには「型」という概念が存在しています。例えば「’ ’」で囲わないといけなかった「文字列」などが型の種類です。厳密には文字列や数字の中にも言語毎にいくつかの分類があるのでそれぞれを確認してみましょう。

SQLの中にもいろんな種類があり、サービス毎に対応しているSQLの種類が違うので、今回はDuneで採用されているDune SQLに存在する一般的な型を説明しています。またDune SQL限定の型には「🏜」マークを付けています。

理論型、Boolean型
- BOOLEAN: TUREかFALSEの論理演算の審議を返す
整数型、Integer（int）型
- TINYINT: 1byte（8bit）までの整数を格納できる（-2^7~2^7-1）
  - 文字数制限はできるが10進数ではなくbitの数（2進数）
- SMALLINT: 2byte（16bit）までの整数を格納できる（-2^15~2^15-1）
- INTEGER: 4byte（32bit）までの整数を格納できる（-2^31~2^31-1）
- BIGINT: 8byte（64bit）までの整数を格納できる（-2^63~2^63-1）
- 🏜 UINT256: 256bitの符号なし整数を格納できる（0~2^256-1）
- 🏜 INT256: 256bitまでの整数を格納できる（-2^255~2^255-1）
  - UINT256とINT256は残高やその他の数値を表すためにEVM系スマートコントラクトで一般的に利用されているデータ型
不動小数点型、Floating-point型
- REAL: IEEE754の32bitの
- DOUBLE: IEEE754の64bitの
数値データ型、Fixed-precision型
- DECIMAL: 2つのパラメーターからなるなる10進数の数値
  - DECIMAL(x, y)でxが合計桁数、yは小数点数（デフォルトは0）
文字列型、String型
- VARCHAR: 長さ指定のないデータを扱う文字列
  - CHARのように文字列指定も可能
- CHAR: 固定長のデータを扱う文字列
  - CHAR(x)でxに文字数を入れる（デフォルトは1）
- VARBINARY: 可変長のバイナリデータを扱う文字列
  - Duneではaddressやhashをこの型にしている
- JSON: JSONのデータ型をした文字列
日付型、Date型
- DATE: ‘YYYY-MM-DD’の形で日付を扱う型
時刻型、Time型
- TIME: ‘hh-mm-ss-aaa’の形で時刻を扱う型
  - 12ピコ秒単位での小数点の秒を持つ

最小値と最大値を取得〈MIN, MAX〉

カラムの最小値と最大値を取得するためには「MIN」と「MAX」を利用します。それぞれ引数にカラムを渡すだけで結果を返してくれます。

MIN(<何を（カラム名）>) /* 最小値 */
MAX(<何を（カラム名）>) /* 最大値 */

[8.20] 形で覚えるDune使い方講座<中級+>

ta1suke

August 20, 2023

Read full story

昨日の前半使用したdoodlesのホルダーリストの中から、（）の名前をつけて以下を取得してみましょう。

最大保有量（Max_balance）
最小保有量（Min_balance）
最大保有率（Max_supply_share）
最小保有率（Min_supply_share）

その時のコードは下記のようになります。

SELECT
    MAX(balance) AS Max_balance,
    MIN(balance) AS Min_balance,
    MAX(supply_share) AS Max_supply_share,
    MIN(supply_share) AS Min_supply_share
FROM nft_ethereum.top_erc721_holders
WHERE nft_contract_address = '0x8a90cab2b38dba80c64b7734e58ee1db38b8992e'

SUMやAVG、COUNTなどと同じ簡単な使い方ですね。今回のような数値のみならず時刻型にも利用可能なので、ぜひ活用して見てください。

グループ化後に条件付与〈HAVING〉

HAVINGはWHEREと同じくデータへの条件付与を行うことができます。しかしこの2つの間では実行タイミングに大きく差があります。

HAVING <条件文>

WHERE: GROUP BYより前に実行
HAVING: GROUP BYの後に実行

つまりWHEREはグループ化される前の元データに抽出条件の指定を行っていますが、HAVINGの場合がグループ化された情報に条件を指定しているということです。こちらは具体例がサンプルコードで登場したらその時に詳しく説明します。

指定範囲を取得〈BETWEEN〉

BETWEENを使うことで必要な範囲のみのデータを取得することが可能です。例えば何個目から何個目までのデータであったり、何日から何日までのデータなど。

SELECT <何を（カラム名、値）> BETWEEN <ここから> AND <ここまで>;
SELECT <何を（カラム名、値）> NOT BETWEEN <ここから> AND <ここまで>;

いくつかテストコードを書いてみました。特に以上（>=）以下（<=）を利用した範囲指定は、=を右におかないといけないため可読性がかなり下がります。こういうときはBETWEENを使うとかなりわかりやすく表記できます。

SELECT 3 BETWEEN 2 AND 7 -- TRUE
/*  SELECT 3 >= 2 AND 3 <= 7 と同じ意味 */

SELECT 1 BETWEEN 5 AND 11 -- FALSE
/*  SELECT 1 >= 5 AND 1 <= 11 と同じ意味 */

SELECT 3 NOT BETWEEN 2 AND 7; -- FALSE
/*  SELECT 3 < 2 OR 3 > 7 と同じ意味 */

SELECT 1 BETWEEN 5 AND 11 -- TRUE
/* SELECT 1 < 5 OR 11 > 5 と同じ意味 */

doodles保有ランキングで以下の条件を満たすqueryを作ってみましょう。

100枚から500枚を保有するのホルダーを取得
15枚から50枚を保有していないのホルダーを取得
全体の0.2から0.5%を保有するのホルダーを取得

それぞれのコードは下記のようになります。

100枚から500枚を保有するのホルダーを取得

SELECT
    ROW_NUMBER() OVER(ORDER BY balance DESC) AS "Rank",
    wallet_address AS Address,
    balance AS Quantity,
    FORMAT('%s%%', ROUND(supply_share * 100, 2)) AS Percentage
FROM nft_ethereum.top_erc721_holders
WHERE nft_contract_address = '0x8a90cab2b38dba80c64b7734e58ee1db38b8992e'
    AND balance BETWEEN 100 AND 500

このように2つのアドレスのみが取得できました。

15枚から50枚を保有していないのホルダーを取得

SELECT
    ROW_NUMBER() OVER(ORDER BY balance DESC) AS "Rank",
    wallet_address AS Address,
    balance AS Quantity,
    FORMAT('%s%%', ROUND(supply_share * 100, 2)) AS Percentage
FROM nft_ethereum.top_erc721_holders
WHERE nft_contract_address = '0x8a90cab2b38dba80c64b7734e58ee1db38b8992e'
    AND balance NOT BETWEEN 15 AND 50

15から50枚のホルダーが排除されていることがわかりますね。

全体の0.2から0.5%を保有するのホルダーを取得

SELECT
    ROW_NUMBER() OVER(ORDER BY balance DESC) AS "Rank",
    wallet_address AS Address,
    balance AS Quantity,
    FORMAT('%s%%', ROUND(supply_share * 100, 2)) AS Percentage
FROM nft_ethereum.top_erc721_holders
WHERE nft_contract_address = '0x8a90cab2b38dba80c64b7734e58ee1db38b8992e'
    AND ROUND(supply_share * 100, 2) BETWEEN 0.2 AND 0.5
    -- AND supply_share BETWEEN 0.002 AND 0.005

きちんと0.2から0.5%の保有率のホルダーのみが取得できました。