趣味のクライミング記録をSQLで管理 (1) 脱スプレッドシートへの道

Last updated at 2026-01-11Posted at 2026-01-04

はじめに

リレーショナルデータベース（RDB）を使って面白いことができないかと思い、趣味で行っているフリークライミングの記録に応用できないか探ってみました。なお、フリークライミングとは何かを説明しようとすると長くなるので、ここでは割愛いたします。

目標

これまでに登ったクライミングルートを集約して、

どの岩場で
どのような時期に
どのような特徴のルートを
どれだけ登ったのか

が分かるようにしたいと思います。

データベース設計

スプレッドシートの例

まず、リレーショナルデータベースを使わない、Excelに代表されるスプレッドシートでの整理例を見てみます。整理しやすい表をつくるに「1セル1情報」「1行が1つの記録」という原則を守ることです。クライミングの記録を1つのスプレッドシートに記録していくと、下表のようになります。1行が1つのクライミング記録となっています。

大抵のスプレッドシートにはフィルタ機能が着いているので、特定の条件で絞り込むことは可能です。例えば、ルート名の列で1つのルートのみにすれば、そのルートをいつ登ったのか、結果はどうだったのかが分かります。

しかし、1つのスプレッドシートで整理する方法の難点は、同じ情報が何度も登場することです。情報を登録する過程で入力ミスする可能性もありますし、同じ情報を何度も記載するのはおっくうです。ルートのグレード（難易度）が改訂されてしまえば（簡単すぎたり難しすぎたりホールドが欠けたりといった理由で、しばしばルートのグレードは改訂されます）、当該箇所をすべて検索して修正しなければなりません。

これらの「データの重複と管理の煩雑さ」を解決する方法の一つが、リレーショナルデータベースの導入です。

エンティティの抽出

リレーショナルデータベース作成の準備として、まず、エンティティの抽出を行います。エンティティ（Entity）は「実体」や「存在」と訳されますが、ここでは「リレーショナルデータベース化したい現実世界の対象」くらいのイメージです。

僕の経験を振り返り、今回の目標に対応するエンティティは、以下の4つになると考えました。

岩場
ルート
登りに行った日の情報
個別のトライ記録

これらが、リレーショナルデータベースにおけるテーブルとなります。

つぎに、テーブルの中身であるカラム（列）も考えていきます。プログラミングで扱いやすくするため、表記は英語にします。

areasテーブル (クライミングエリア)
- id
- name (エリア名)
- rock_type (岩質)
- location (場所)
climb_routesテーブル (クライミングルート)
- id
- area_id (外部キー -> areasテーブル)
- name (クライミングルート名)
- grade (グレード)
- angle (斜度)
- type (種類)
- feature (特徴)
visitsテーブル (訪問日)
- id
- area_id (外部キー -> areasテーブル)
- date (日付)
- condition (その日のコンディション)
- memo (その日のメモ)
attemptsテーブル (個別のトライ記録)
- id
- route_id (外部キー -> climb_routesテーブル)
- visit_id (外部キー -> visitsテーブル)
- progress_pct (どこまで登れたかの割合)
- result (RP、OSなど)
- comment

エンティティ同士の関係

テーブルの中身は決まりましたが、これらがどう関係しているか（リレーションを持っているか）が、まだ定義できていません。それぞれの関係を整理します。

1つの岩場には、複数のルートがある → [岩場 1 : 多ルート]
1つの岩場には、何度も訪問する → [岩場 1 : 多訪問日]
1回の訪問（1日）で、何度もトライする → [訪問日 1 : 多トライ]
1つのルートには、何度もトライする → [ルート 1 : 多トライ]

これらの関係をER図（Entity Relationship Diagram）として線でつなぐと、下図のようになります。

このように各テーブルをIDでつなぎ合わせることで、1つの情報はただ1箇所にのみ存在することになり、情報の重複がなくなります。

データベースの実装

完全ではないような気がしますが、とりあえず設計はできましたので、実際に使える状態にしていきます。リレーショナルデータベースマネジメントシステム（RDBMS）には多くの種類があります。今回は、ファイル単位で扱うことのできるSQLiteを使います。また、操作を簡単にするためにGUIツールである DB Browser for SQLite を使用します。

上記リンク先のDownloadから、OSに適したものを入手して実行します。New Databaseを選んで適当な名前を付けて開きます。直後に「Edit Table Definition」というテーブル作成のダイアログが開きますが、今回はSQLで一括作成するため、これはキャンセルして閉じます。

タブの中から「Execute SQL」を選択します。

入力欄に以下のSQL文を貼り付け、再生ボタン（右向きの△）を押すとテーブルが作成されます。

-- 外部キー制約を有効化
PRAGMA foreign_keys = ON;

-- 岩場 (Area) テーブル
CREATE TABLE areas (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    rock_type TEXT,
    location TEXT
);

-- ルート (ClimbRoute) テーブル
CREATE TABLE climb_routes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    area_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    grade TEXT,
    angle INTEGER,
    type TEXT,
    feature TEXT,
    FOREIGN KEY (area_id) REFERENCES areas (id) ON DELETE CASCADE
);

-- 訪問記録 (Visit) テーブル
CREATE TABLE visits (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    area_id INTEGER NOT NULL,
    date TEXT NOT NULL,
    condition TEXT,
    memo TEXT,
    FOREIGN KEY (area_id) REFERENCES areas (id) ON DELETE CASCADE
);

-- トライ記録 (Attempt) テーブル
CREATE TABLE attempts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    route_id INTEGER NOT NULL,
    visit_id INTEGER NOT NULL,
    progress_pct INTEGER,
    result TEXT,
    comment TEXT,
    FOREIGN KEY (route_id) REFERENCES climb_routes (id) ON DELETE CASCADE,
    FOREIGN KEY (visit_id) REFERENCES visits (id) ON DELETE CASCADE
);

一番下の欄に「Execution finished without errors.」と表示されれば、問題なくテーブルが作成されています。

上の方にある「Write Changes」を押すと、作成したテーブルがデータベースファイルに保存（コミット）されます。

なお、僕の環境（macOS）では、このままだと「Database Structure」タブにテーブルが表示されませんでした。「Close Database」を押していったんファイルを閉じてから、データベースファイル（拡張子.db）を再度開くと、テーブルが作成されていることが確認できました。

データの登録

テーブルという枠組みができたので、その中にデータを追加していきます。スプレッドシートの内容をCSV形式で保存し、DB Browser for SQLiteからインポートする方法が手軽です。

設計した構造にしたがってスプレッドシートの内容を整形します。例えば、routesテーブルの内容は下図のようになります。ここで、route_idは1からの連番、crag_idはcragsテーブルのidに対応しています。このように、別のテーブルとidで紐づけることで、岩場情報を何度も文字で書く必要がなくなります。

このシートをCSVファイル（文字コードはUTF-8）として保存し、DB Browser for SQLiteのFileメニュー→Import→Table from CSV file...から選択します。

インポート設定のダイアログが表示されます。「Table name」を登録したいテーブル名（例: routes）にし、「Column names in first line」にチェックを入れると、CSVファイルの1行目がカラム名として扱われます。

この手順をテーブルの数だけ繰り返します。データのインポート後、Write Changesを押して、内容をデータベースに忘れないように反映しておきます。

リレーショナルデータベースの活用

やっと準備ができたので、SQLを使って、作成したデータベースからクライミング記録を抽出・分析してみます。

特定のルートを登った回数を調べる

特定のルートをこれまで何度トライし、どれだけ完登できたかを調べます。以下のSQL文を実行します（最終行の WHERE 句を調べたいルート名にします）。

SELECT 
    r.name AS route_name,
    COUNT(a.id) as total_attempts, -- 総トライ数
    SUM(CASE WHEN a.progress_pct = 100 THEN 1 ELSE 0 END) as success_count, -- 完登数
    -- 完登率（成功数 ÷ 総数 × 100）
    ROUND(CAST(SUM(CASE WHEN a.progress_pct = 100 THEN 1 ELSE 0 END) AS REAL) / COUNT(a.id) * 100, 1) as success_rate_pct
FROM 
    attempts a
JOIN 
    climb_routes r ON a.route_id = r.id
WHERE 
    r.name = '小川山レイバック'; -- 検索したいルート名

DB Browser for SQLiteのExcecute SQLから実行すると、次の結果が得られます。（今回登録したデータ範囲では）小川山レイバックは2回登っており、2回とも完登していることが分かります。

登ったルートの傾斜のランキング表示

今度は、自分が登ったルートの傾向を知るために、傾斜ごとのトライ数をランキング表示してみます。

SELECT 
    r.angle, 
    COUNT(a.id) as attempt_count -- その傾斜をトライした回数
FROM 
    attempts a
JOIN 
    climb_routes r ON a.route_id = r.id
WHERE 
    r.angle IS NOT NULL -- 傾斜が未入力のものは除外
GROUP BY 
    r.angle
ORDER BY 
    attempt_count DESC; -- 多い順に並べる

結果はこちらです。登録した傾斜の情報が厳密ではないので、目安にしかなりませんが、80°（薄被り）と100°（被り）のルートをよく登っていることが分かりました。

まとめ

リレーショナルデータベースを使って、フリークライミングの記録を管理・分析する方法を探ってみました。テーブル構造さえ出来上がれば、SQLひとつで様々な分析ができる可能性を感じました。

ただし、今回の「スプレッドシート上でIDを手動連携させて、CSVインポートする」という方法は非常に面倒です。これでは継続できる気がしません。そもそもリレーショナルデータベースはシステムの一部として使われるものであり、人間が直接テーブルを編集するのは非効率です。

次回は、より快適に記録を行えるよう、Pythonなどを使って簡単な登録用アプリケーションの作成に挑戦したいと思います。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up