BTC の連続時間陽線を SQL (Window 関数) で分析する
しろがみことの発端はこのツイート、
「14 時間連続陽線すげ〜〜」と思いつつも「BTC の歴史のどっかにはこれ以上の連続陽線があるんじゃないか？」と疑問になった
あと会社で window 関数の勉強会を開催したばっかだったので、「連続陽線を SQL の window 関数を分析するって応用にいいじゃん‼️」ということで SQL で分析する
しろがみデータセット
binance の 2017-08 ~ 2024-12 のデータセットを使わせてもらいます。
2009-01-03 からのデータセットは見つからなかった...
ただ月ごとに別れている & zip 化されていて使いづらいので、データをまとめて csv にしたものを google drive にあげておいた。
分析基盤
ClickHouse でやります。このスクラップで使うと思うので勉強がてらです。
環境構築簡単なのでクラウドサービス ClickHouse Cloud を使います。300 ドルの無料クレジットがあります。
多分 google cloud でも aws でもいいはず、自分は google cloud でやってます
しろがみデータセットの import
ダウンロードしたデータセットを Data sources からインポート
補足として
- テーブル名は
btc_prices - パーティションは
toYear(toDateTime(open_time / 1000))
としました
確認
select toDateTime(open_time / 1000), Open from btc_prices limit 10
こういうグラフ表示も clickhouse で出来る
しろがみデータが出来たので SQL 実行する。
使ってる window 関数は SUM による累積と、RANK() による連続上昇時間のランキングぐらい
連続陽線を計算するテクで SUM による累積で陽線が途切れるたびに streak_group を増加させている、
そして streak_group が同じ値のときは価格が連続して上昇していることを意味するので group by streak_group して select(*) で連続陽線の時間を算出
WITH price_changes AS (
SELECT
open_time,
open,
close,
IF(close >= open, 1, 0) AS is_bullish -- 陽線の定義
FROM btc_prices
),
streaks AS (
SELECT
open_time,
open,
close,
is_bullish,
SUM(IF(is_bullish = 0, 1, 0)) OVER (ORDER BY open_time ASC) AS streak_group -- 途切れたらグループ化
FROM price_changes
)
SELECT
RANK() OVER (ORDER BY COUNT(*) DESC) AS "連続時間ランキング",
COUNT(*) AS "連続陽線時間",
toDateTime(min(open_time / 1000)) AS "連続陽線の最初の時間",
toDateTime(max(open_time / 1000)) AS "連続陽線の最後の時間",
min(open) AS "連続陽線の始値",
max(close) AS "連続陽線の終値",
max(close) - min(open) AS "上昇値"
FROM streaks
WHERE is_bullish = 1
GROUP BY streak_group
ORDER BY "連続陽線時間" DESC;
2017-08 ~ 2024-12 の区間で最大は 12 時間 だった、
2025-01-10 の 14 時間連続は本当に珍しいんだな〜
2025-01 のデータが公開されたらもっかいやりたい
あと、めちゃくちゃ 2009 ~ 2017 の区間が気になる
いつか時間あったらやりたいな
しろがみまとめ
- 2025-01-10 の 14 時間連続陽線はほんとに珍しい
- 2017-08 ~ 2024-12 の区間で最大は 12 時間
- window 関数使えば連続陽線とかも分析できるよ