■目的

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

■利用サービス

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

■利用テーブル

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

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

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

■実行結果

| 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;

■実行結果

| 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位を表示しています。

■実行結果

| 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;

■実行結果
-先頭行

| 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 |

最終行

| 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コース