■目的
ブロックチェーンのデータを抽出しながら、
SQLの操作方法の基本を身につける。
■テーブルを集約して検索する
テーブルの列の合計値や、平均、最大値などの集計操作をする関数を紹介します。
集約とは、「複数行のデータを１行にまとめる」という意味で、
実行結果は、１行に出力されます。
データはイーサリアムのトランザクション使います。
COUNT関数：テーブルの行数を数える。
COUNT（*）は、NULLを含む、全行数をカウントします。
COUNT(列名)は、NULLを除く行数をカウントします。
SELECT COUNT(*) AS AL_COUNT,COUNT(block_number) AS BLK_COUNT
FROM ethereum.transactions;
実行結果：
| AL_COUNT | BLK_COUNT |
|------------|------------|
| 2781309981 | 2781309981 |
今回は、たまたま同じ数字になりました。
NULLはないようです。
合計値を求める
SUM関数
今回合計する列は、gas_usedとします。
SELECT SUM(gas_used) AS sum_gas_used
FROM ethereum.transactions;
実行結果
| sum_gas_used |
|-----------------|
| 223199152614906 |
最大値・最小値を求める
MAX関数、MIN関数を使います。
MAX関数はmaximamの略語で、最大値です。
MIN関数はminimamの略語で、最小値です。
gas_priceの列の最大値、最小値を求めたいと思います。
SELECT MAX(gas_price) AS max_gas_price,MIN(gas_price)AS min_gas_price
FROM ethereum.transactions;
実行結果
| max_gas_price | min_gas_price |
|--------------------|---------------|
| 508034850000000000 | 0 |
gas_priceが0になっているのは気になりますが、
ここでは、原因究明せずに先へ進めます。
また、MAX関数、MIN関数は数値データ以外に、日付データにも利用できます。
SELECT MAX(block_time) AS max_block_time,MIN(block_time)AS min_block_time
FROM ethereum.transactions;
実行結果：
| max_block_time | min_block_time |
|------------------|------------------|
| 2025-04-26 08:21 | 2015-08-07 03:30 |
最小値がblock_time列の2015-08-07 03:30が最初のデータ、
最大値がblock_time列の2025-04-26 08:21が最後のデータ（2025-04-26 17:36時点）
のようです。もしかしたら、データ取得の際のタイムラグもある可能性があるので
ツールによっては多少前後する可能性があります。
重複を除外して集約関数を利用する
(DISTINCT キーワード)
DISTINCTは重複を除いて処理するときに使います。
例えば、重複を除いて、行数を数える場合は
COUNT(DISTINCT 列名)と記述します。
データは"from"(送信元アドレス)を使います。
SELECT COUNT("from") AS all_from_cnt ,COUNT(DISTINCT "from") AS dit_from_cnt
FROM ethereum.transactions;
列名が、句と同じ場合、""(ダブルクォーテーション)で列名を囲みます。今回は列名のfromがSQLのFROM句と同じなので""で囲っています。SQLの句は大文字、小文字を問わず、動作します。
実行結果：
| all_from_cnt | dit_from_cnt |
|--------------|--------------|
| 2781370080 | 229036004 |
from列の全データall_from_cnt の2781370080に対して、
重複なしのデータdit_from_cntは229036004です。
差は、2552334076です。
約25億の送信元アドレスが取引を行っています。
多いですね。
テーブルをグループごとに切り分ける
GROUP BY句
テーブルの列の値をグループごとに、カウントする、平均するなどの処理をしたいときに利用します。
例えば、1年間の商品売上データベースがある場合、商品ごとの売上数量を出したいときや、
月ごとの商品売上金額を出したいときに利用します。
今回は、送信元アドレスのデータ"from"列のアドレスが何回データが存在しているかカウントし、
カウントの数が大きい順にソートします。
データの量が多いので、上位5つ表示させます。
ORDER BY で指定している from_cnt は SELECT句で利用しているCOUNT(*)の別名を指定しています。
SELECT句でつけた別名を利用することができます。
SELECT "from" ,COUNT(*) AS from_cnt
FROM ethereum.transactions
GROUP BY "from"
ORDER BY from_cnt DESC
LIMIT 5;
実行結果：
| from | from_cnt |
|--------------------------------------------|----------|
| 0xea674fdde714fd979de3edf0f56aa9716b898ec8 | 45653594 |
| 0x52bc44d5378309ee2abf1539bf71de1b7d7be3b5 | 18384838 |
| 0x46340b20830761efd32832a74d7169b29feb9758 | 14336356 |
| 0x829bd824b016326a401d083b33d092293333a830 | 13347954 |
| 0x28c6c06298d514db089934071355e5743bf21d60 | 12198637 |
