LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@y_haruki(山下 はるき)

ブロックチェーンデータ,イーサリアムデータを使ってSQLを覚える（その３）

Posted at

■目的

・ウインドウ関数を理解する。
・イーサリアムデータを抽出操作をする。

■利用サービス

ブロックチェーン分析サービス
DUNE

■利用テーブル

イーサリアムの引き出しデータのあるテーブル
ethereum.transactions
を使います。

簡単に使うデータを把握します。
■クエリ

SELECT block_time, block_number, amount, address
FROM ethereum.withdrawals
LIMIT 10;

■実行結果
1_sql_window_func.png

| block_time          | block_number | amount     | address                                    |
|---------------------|--------------|------------|--------------------------------------------|
| 2023-04-12 22:46:00 | 17034945     | 3092489829 | 0xc9b290e993cc0da3a046ec9f0dac333ce9e09d53 |
| 2023-04-12 22:46:00 | 17034945     | 3155288341 | 0xc9b290e993cc0da3a046ec9f0dac333ce9e09d53 |
| 2023-04-12 22:46:00 | 17034945     | 2941183617 | 0xc9b290e993cc0da3a046ec9f0dac333ce9e09d53 |
| 2023-04-12 22:46:00 | 17034945     | 3007547336 | 0xc9b290e993cc0da3a046ec9f0dac333ce9e09d53 |
| 2023-04-12 22:46:00 | 17034945     | 3001014368 | 0xc9b290e993cc0da3a046ec9f0dac333ce9e09d53 |
| 2023-04-12 22:46:00 | 17034945     | 3044980833 | 0xc9b290e993cc0da3a046ec9f0dac333ce9e09d53 |
| 2023-04-12 22:46:00 | 17034945     | 3123361948 | 0xc9b290e993cc0da3a046ec9f0dac333ce9e09d53 |
| 2023-04-12 22:46:00 | 17034945     | 3230004330 | 0xc9b290e993cc0da3a046ec9f0dac333ce9e09d53 |
| 2023-04-12 22:46:00 | 17034945     | 2968796186 | 0xc9b290e993cc0da3a046ec9f0dac333ce9e09d53 |
| 2023-04-12 22:46:00 | 17034945     | 2989992521 | 0xc9b290e993cc0da3a046ec9f0dac333ce9e09d53 |

同じアドレスが、同じ時間に多くの引き出し処理をしているようです。

ウインドウ関数

ウインドウ関数の意味

ウインドウ関数は、リアルタイムでデータ分析を行う処理のときに利用できます。
例えば、財務諸表、市場分析などで利用できます。

ウインドウ関数の「ウインドウ」という呼び方は、ある操作（PARTITION BY）によって区切られたレコードの集合の「範囲」を表しています。

SQLゼロから始めるデータベース操作の著者、ミックさんによると、「ウインドウ」よりも「グループ」という方がわかりやすいかもしれない。でも、SQLの「グループ」というと、GROUP BY　と混合するので避ける意味で「ウインドウ」と呼んでいるでしょう。
と考えているようです。

ウインドウ関数の構文

<ウインドウ関数>　OVER ([PARTITION BY <列リスト>])
                            ORDER BY <ソート用列リスト>)
                            ※[ ]は省略可能

RANK関数を利用する

取引アドレスごとにamountの量が多い順に1位から順に表示させます。
■クエリ

SELECT address, amount, 
RANK() OVER (PARTITION BY address
            ORDER BY amount DESC) AS RANKING
FROM ethereum.withdrawals
LIMIT 10;

■実行結果
2_sql_window_func.png

| address                                    | amount      | RANKING |
|--------------------------------------------|-------------|---------|
| 0xd8deb0729f58a895c3d04f613430b95fec55f39d | 32107832842 | 1       |
| 0xd8deb0729f58a895c3d04f613430b95fec55f39d | 32062165633 | 2       |
| 0xd8deb0729f58a895c3d04f613430b95fec55f39d | 32016351634 | 3       |
| 0xd8deb0729f58a895c3d04f613430b95fec55f39d | 32016337260 | 4       |
| 0xd8deb0729f58a895c3d04f613430b95fec55f39d | 32016335276 | 5       |
| 0xd8deb0729f58a895c3d04f613430b95fec55f39d | 32016332837 | 6       |
| 0xd8deb0729f58a895c3d04f613430b95fec55f39d | 32016326036 | 7       |
| 0xd8deb0729f58a895c3d04f613430b95fec55f39d | 32016323735 | 8       |
| 0xd8deb0729f58a895c3d04f613430b95fec55f39d | 32016319312 | 9       |
| 0xd8deb0729f58a895c3d04f613430b95fec55f39d | 32016313738 | 10      |

少なくとも、上位１０位は
adress
0x99d850313d63ca4efc6233900f4b96ee0c91376d
が占めています。
同じくらいのamount量で引き出し処理をしているようです。

GROUPING演算子

小計、合計を同時に求めるときに利用します。

今回、GROUPING演算子の一つ、ROLLUPを使った例を紹介します。

合計行を求める

■クエリ

SELECT address, SUM(amount) AS sum_address_amt
FROM ethereum.withdrawals
GROUP BY ROLLUP(address)
ORDER BY sum_address_amt DESC
LIMIT 20;

アドレスごとに小計を計算し、その合計(address欄の空欄)を計算しています。
また、sum_address_amtと別名を付けた、SUM(amount)の数字を
数字の大きい順にソートして、上位20位を表示しています。

■実行結果

4_sql_grouping_func.png

| address                                    | sum_address_amt       |
|--------------------------------------------|-----------------------|
|                                            | 29348988232255188     |
| 0xb9d7934878b5fb9610b3fe8a5e441e8fad7e293f | 4085030229490617      |
| 0x210b3cb99fa1de0a64085fa80e18c22fe4722a1b | 2102751460080892      |
| 0x8e609ac80f4324e499a6efd24f221a2caa868224 | 1185000484525974      |
| 0xe839a3e9efb32c6a56ab7128e51056585275506c | 531415280927379       |
| 0x1cedc0f3af8f9841b0a1f5c1a4ddc6e1a1629074 | 469103602210643       |
| 0xeee27662c2b8eba3cd936a23f039f3189633e4c8 | 374252949673693       |
| 0xd4e11c28e04c0c2bf370b7a9989498b7ea02493f | 330927392710202       |
| 0xd4018ce9a041a9c110a9d0383d2b5e1c66ae1513 | 320124220014440       |
| 0x2641c2ded63a0c640629f5edf1189e0f53c06561 | 313072038236723       |
| 0x093f6c270ac22ec240f0c6fd7414ea774ca8d3e5 | 305396363553559       |
| 0x7e2a2fa2a064f693f0a55c5639476d913ff12d05 | 286038338986589       |
| 0xb3d9cf8e163bbc840195a97e81f8a34e295b8f39 | 211455398742364       |
| 0xa8c62111e4652b07110a0fc81816303c42632f64 | 207186514829196       |
| 0x6357e4bdaff733dfe8f50d12d07c03b3bed0884b | 164977927282477       |
| 0xd007058e9b58e74c33c6bf6fbcd38baab813cbb6 | 160540864692093       |
| 0x8306300ffd616049fd7e4b0354a64da835c1a81c | 156119330045539       |
| 0x35cb1491dcf4c0ab6b413afc42298e32f13ff524 | 154051927418109       |
| 0xb23c002bc65c6bb539aad4c11d606ef4f5502c93 | 146116069711984       |
| 0xbc5a4a09450b4106be9a4df3d85da3f4617e819f | 144099868261335       |

ORDER BY sum_address_amt DESC の処理をして、
sum_address_amtの大きい順にソートされています。
addressの空欄のaomountが各アドレスの合計の値になります。

集約キーに「block_time」を追加してみる

■クエリ

SELECT address, block_time, SUM(amount) AS sum_amt
FROM ethereum.withdrawals
GROUP BY ROLLUP(address, block_time)
ORDER BY address, sum_amt DESC;

■実行結果
-先頭行
5_sql_grouping_func_head.png

| address                                    | block_time          | sum_amt     |
|--------------------------------------------|---------------------|-------------|
| 0xfffffffff0cee49cff5550d64455d52e1e20824d | 2023-04-13 09:02:00 | 33939170920 |
| 0xfffffffff0cee49cff5550d64455d52e1e20824d |                     | 33939170920 |
| 0xffff826fbcdb6a95bb38bb8e3ab21724a93538b7 |                     | 32405962995 |
| 0xffff826fbcdb6a95bb38bb8e3ab21724a93538b7 | 2024-09-17 13:23:00 | 7729273     |
| 0xffff826fbcdb6a95bb38bb8e3ab21724a93538b7 | 2025-01-07 06:20:00 | 19131191    |
| 0xffff826fbcdb6a95bb38bb8e3ab21724a93538b7 | 2025-01-16 11:28:00 | 19160868    |
| 0xffff826fbcdb6a95bb38bb8e3ab21724a93538b7 | 2024-10-06 02:31:00 | 19207058    |
| 0xffff826fbcdb6a95bb38bb8e3ab21724a93538b7 | 2024-12-29 01:31:00 | 19211697    |
| 0xffff826fbcdb6a95bb38bb8e3ab21724a93538b7 | 2024-12-10 11:48:00 | 19213157    |
| 0xffff826fbcdb6a95bb38bb8e3ab21724a93538b7 | 2024-09-26 19:58:00 | 19223910    |
| 0xffff826fbcdb6a95bb38bb8e3ab21724a93538b7 | 2024-11-12 10:39:00 | 19258921    |
| 0xffff826fbcdb6a95bb38bb8e3ab21724a93538b7 | 2024-12-19 19:30:00 | 19312502    |
| 0xffff826fbcdb6a95bb38bb8e3ab21724a93538b7 | 2024-12-01 04:11:00 | 19327650    |
| 0xffff826fbcdb6a95bb38bb8e3ab21724a93538b7 | 2024-10-15 10:59:00 | 19374471    |
| 0xffff826fbcdb6a95bb38bb8e3ab21724a93538b7 | 2024-11-03 01:51:00 | 65416148    |
| 0xffff826fbcdb6a95bb38bb8e3ab21724a93538b7 | 2024-10-24 18:11:00 | 65512080    |
| 0xffff826fbcdb6a95bb38bb8e3ab21724a93538b7 | 2024-11-21 19:55:00 | 65759896    |
| 0xffff826fbcdb6a95bb38bb8e3ab21724a93538b7 | 2025-01-25 16:17:00 | 32009124173 |

最終行
5_sql_grouping_func_tail.png

| address                                    | block_time          | sum_amt           |
|--------------------------------------------|---------------------|-------------------|
| 0x00000000006267dd559d14f5adef3f777ae7bb0e | 2023-08-25 05:28:00 | 15452189          |
|                                            |                     | 29349354527872044 |

最後はすべてのsum_amtの合計、あるaddressの小計、addressの各block_timeごとの合計
と表示をさせる予定だった。

SELECT address, block_time, SUM(amount) AS sum_amt
FROM ethereum.withdrawals
GROUP BY ROLLUP(address, block_time);

とクエリを実行すると、合計、小計の順に表示されなかったので、
ORDER BY address, sum_amt DESC;
を加えて、調整しようとしたが最終行に、全アドレスのamountが表示されているのを見つけた。

また、今まで無料利用枠の制限により、２分を超えて処理がされる場合は、処理ができなかった。
そのため、LIMITをつけて計算量を抑えていた。
しかし、試しに、LIMITを外してみたら、正常に処理が完了した。
14,696,677 rowsだった。

もしかしたら、サーバーへの負荷がたまたま空いていたからできた可能性もある。
LIMITを使った場合、合計の計算の増減の変化がみられた。
今回は、クエリを利用することにフォーカスしているが、
計算を正しく行うために利用する場合は、LIMITを外して分析する必要がある。

■参考

・SQLゼロから始めるデータベース操作
・Udemy SQLコース

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?