DUNEでオンチェーン解析するためのSQL学習メモ

DUNE Analyticsでブロックチェーンのデータ解析する際にSQL勉強したときのメモ。
https://dune.com/

学習参考にしたもの

• ゼロからはじめるデータ分析のための実践的SQL入門〜最短経路で現場で使えるSQLを習得〜
  • https://www.udemy.com/course/sql_for_data_analytics/
• 【コピペで学べる】Dune Analytics: 初心者向けガイド
  • https://note.com/0xyoshi/n/n82ce78f34187

基本系

SELECT *
FROM ethereum.transactions
WHERE nonce = 1
ORDER BY block_time DESC
LIMIT 10

左部バーからテーブル構成見れる

演算子練習

SELECT *
FROM ethereum.transactions
WHERE nonce <> 1 AND gas_price <= 100
ORDER BY block_time DESC
LIMIT 10

演算子メモ

• AND
• OR
• BETWEEN A and B
• IN(1,3,5)
• LIKE "%" 0文字以上
• LIKE "_" 任意の1文字

レコード数のカウント ： トランザクション総カウント

SELECT COUNT(*) AS cnt
FROM ethereum.transactions

→1,914,208,039個

ユニークカラム : ユニークな送信アドレス

SELECT DISTINCT "from"
FROM ethereum.transactions
LIMIT 1000

→fromは予約語なのでダブルクオーテーションで囲む

COUNTとDISTINCTの掛け合わせ : ユニークな送信アドレス数

SELECT COUNT(DISTINCT "from") AS cnt
FROM ethereum.transactions

→ 162,293,074個

最小値と最大値

SELECT 
    MAX(value) as max_value,
    MIN(value) as min_value
FROM ethereum.transactions

max:11901464239480000000000000
min:0

合計と平均

SELECT 
    SUM(value) as sum_value,
    AVG(value) as AVG_value
FROM ethereum.transactions

SUM:9757209533600456740228763592
AVG:5097248104737085556

場合分け×名付け

SELECT
    DISTINCT token_bought_symbol,
    CASE
        WHEN token_bought_symbol = 'WETH' THEN 'Wrapped Ether'
        WHEN token_bought_symbol = 'DAI' THEN 'Dai'
    ELSE 'etc'
    END AS TBS
FROM uniswap.trades
LIMIT 1000

グループ分け集計

SELECT 
    token_bought_symbol,
    SUM(amount_usd) as sum_amount_usd,
    AVG(amount_usd) as avg_amount_usd,
    MAX(amount_usd) as max_amount_usd,
    MIN(amount_usd) as min_amount_usd
FROM uniswap.trades
GROUP BY token_bought_symbol
ORDER BY sum_amount_usd DESC

SELECT blockchain, COUNT(*) AS num_events
FROM opensea.events
GROUP BY blockchain

IF文

SELECT 
    currency_symbol,
    IF(currency_symbol = 'SOL', 'Solana', 'etc') AS TS
FROM opensea.events
GROUP BY currency_symbol

Having 出力条件縛り

SELECT currency_symbol, COUNT(*) AS num_events
FROM opensea.events
GROUP BY currency_symbol
HAVING COUNT(*) > 100
ORDER BY COUNT(*) DESC

INNER JOIN

SELECT block_time, block_number, difficulty, value
FROM ethereum.transactions AS ethTX
INNER JOIN ethereum.blocks AS ethBL
ON ethTX.block_number = ethBL.number
ORDER BY difficulty DESC
LIMIT 50;

LEFT JOIN

SELECT block_time, block_number, difficulty, value
FROM ethereum.transactions AS ethTX
LEFT JOIN ethereum.blocks AS ethBL
ON ethTX.block_number = ethBL.number
ORDER BY difficulty DESC
LIMIT 50;