WCAデータベースたわむれ入門 [Speedcubing Advent Calendar 2014]

本記事は，Speedcubing Advent Calendar 2014 の16日目の記事です．
15日目はこうさんの「EP上下反転手順」でした．

Note: 本記事の内容はスピードキュービング競技の技術に直接的に結びつくテーマではありません．

---

---

はじめに

ハワイからの更新です．

さて，これまでのWCAデータベースに関する記事として，
1) BigDumpを用いてインポートする方法， 2) サーバ上のデータベースを自動的に定期更新する方法 を紹介してきました．

WCAデータベースを扱う操作の内，インポートという部分的な処理に焦点を当てて記述しましたが， 本記事ではデータベースの導入から実アプリケーションの作成までの一連流れ，全体像を説明します． WCAデータベースを使ってわちゃわちゃ遊ぶための基礎知識という位置付けです．
Web技術にちょっと興味があるけどまだ手を出せなかった方らへんを対象として想定しています．
これを機に，多くの人がWCAデータベースに触れて面白いデータやアプリケーションが増えたら楽しいなと思います．

本記事の構成は以下のようになっています．

1. 何が必要？ 環境構築
2. データの取り込み
3. 試してみる
4. 実アプリケーション紹介

Step 1. 環境構築

まずは環境構築です．データベースを扱うベースとなる基礎を整えます．
本記事では，MySQL と PHP を必要なアプリケーションとして想定します． もちろん，他のリレーショナルデータベースやそのデータベースを扱える言語なら何でも可能です． おそらくMySQL と PHP が一番一般的だと思います．

本記事での各アプリケーションのバージョンは，MySQL 5.1.73，PHP 5.3.3 です．

どこにデータベースを設置するか？ですが，2通り考えられます．
ローカル環境かサーバ環境です．それぞれにメリット・デメリットがあります．

ローカル環境
ローカル環境とは今あなたの手元にあるPCのことと定義します．Windows でも Mac OS でも Linux でも OK です．
メリット: 追加コストが発生しない．データの更新や操作が容易．
デメリット: 動的なデータを公開することは不可．静的データを作成して別の場所で公開することは可能．

サーバ環境
レンタルサーバやクラウドサービス（VPS等のIaaS / Heroku等のPaaS / SaaSもあるかも）と定義します．たいていは Linux だと思います．特殊な例ですが，物理的にローカルに存在していても固定IPが振られ常時起動のマシンであればこっちとして定義します．
メリット: Webサーバを立ち上げれば動的データを公開可能．アプリケーションベースのサービスの場合サーバ環境一択．
デメリット: たいていの場合ランニングコストが発生する．

ある日時のデータをもとに静的なデータを作成して表示/公開するのでしたらローカル環境だけで済みます．
日時やユーザの入力によって動的に変化するデータを表示/公開するにはWebサーバ上で動作させる環境が必要です．

各環境に応じたセットアップ方法はWeb上に膨大に存在するので，ここでは説明を省略します． “(Name-of-OS) php“，”(Name-of-OS) mysql” 等をキーワードに検索してください．

サーバ環境と整えるなら，VPSがおすすめです．VPSはサーバの一種ですが，一台の物理サーバコンピュータを仮想的に複数のサーバに見立てる技術です． そのため，VPSを契約するとあたかも物理サーバが一台存在してるように，root権限の使用，ソフトウェアのインストールに制限がない，システムファイルをすべて変更可能など，非常に豊富なカスタマイズ性が特徴です．
有名なVPSを以下に列挙します．それぞれスペックや料金の面で特徴がありますので検討してみてください．

• さくらインターネットのVPS
• ServersMan@VPS
• GMOクラウドのVPS
• 【ConoHa VPS】月額976円・メモリ1GB・CPU2コア

実はこの記事を書きながら見つけたのですが，Stefan Pochmann氏が最近開発した “WCA Data Tools” と呼ばれるWCAデータベースを扱うツールが公開されています．Pythonが使用できる環境であればこれを使用することで車輪の再開発（発明）をしなくて済みます．

Step 2. データを取り込む（インポート）

データベース環境が構築できたら，データを取り込みましょう．
WCA results export から “*.sql.zip” をダウンロードします．解凍します．WCA_export.sql というファイルが出てきます． サイズは100MBぐらいあります．これがWCAデータベースをダンプしたファイル，つまりデータベースの全ての中身です． このファイルを取り込むことで自分の環境にデータをインポートできます．

取り込み方法はいろいろありますが，主な方法を以下に挙げます．

• mysqlコマンドでを使う方法（ローカル環境かサーバ環境でssh接続できるならこれ）
• phpMyAdminを使う方法（遅いのであまり現実的ではない）
• BigDumpを使う方法

インポート方法については，冒頭でも書いたように専用の記事を書いています，ここでは説明を省略します．

Step 3. Try it!

以上で準備完了です．実際に操作してみましょう．

データベースは「テーブル」と呼ばれる表の集合です．「テーブル」とは馴染み深いExcelやGoogleスプレッドシートと同じような2次元の表です．
これらのどのようなテーブルで構成されているかを知るには，例えばphpMyAdminでデータベースの構造をチェックしてください．
他には，roudaiさんがテーブル構造をまとめたものをコメント付きで公開しています．
WCA Database – roudai.net

特に重要な意味をもつテーブルは，”Competitions（大会）”，”Persons（競技者）”，”Results（競技結果）” の3つでしょうか．

それから，README.txt にも書いてありますが，競技結果のデータを数値として保存するフォーマットが3タイプ存在します．次の3つです．

1. time: 100ミリ秒単位でのタイム
2. number: 個数（Fewest moves用）
3. multi: 個数とタイム（Multiple BF用）

3個目のフォーマット “multi” は少し工夫されています．順位を整数としてソート可能なように設計されています．詳しくは README.txt を見てください．

さて，データベース（テーブル）内から必要なデータを取り出すためには，SQL文が必要です． 詳細は，こちらもWeb上に膨大な数の文献が転がってると思うので調べてみてください．
SELECT, FROM, WHERE, ORDER BY, COUNT, GROUP BY あたりを知っておくと良いと思います．
PHPの関数としては，mysql_connect, mysql_select_db, mysql_set_charset, mysql_query, mysql_fetch_assoc あたりが重要かと思います．
※ mysql系の関数は現在公式で非推奨となっていますので，mysqli系の関数かPDOを使用してください．

※ 2015年3月8日 追記: SQLに関して詳細な記事を作成しましたので，そちらもご覧ください．
WCAデータベースで遊ぶ実践編「自己ベスト記録のリストを作成する」

1つだけPHPでの例を挙げます． エラー処理は省いてあります．
SQLがよくわかんなくても，英語的に読めばそれなりに何をやっているのか理解できると思います．

簡単な例ですが，この例では，3x3x3目隠しの表彰台に上った3人の競技者全員が sub-X (ただし，X=90, 80, …, 30) を達成した大会数を表示します．
実行結果を以下の図に示します．

3x3x3目隠しの表彰台の3競技者全員が sub30 を達成した大会が1つだけありました．
その大会は，Polish Nationals 2013 です．ハイレベルです．すごいです．

こんな感じに，ちょっとコツをつかんでしまえば思うままにデータをいじくり回すことが可能です．

余談ですが，ある程度複雑なアルゴリズムを用いる場合，特に変則的なソートなどで自前でPHP等で変に$O(n^2)$以上のアルゴリズムを記述するより，MySQLに処理を任せてしまったほうが効率が良かったりします．けど，複雑な処理をSQL文で記述するのが大変というジレンマです．

ちなみに，今回は省きましたが，データベースにインデックスを張ることは重要です． アプリケーションによってはパフォーマンスを左右する重要なファクタですので適切に設定しましょう． 特に ORDER BY を含んだSQLを実行する場合，インデックスが重要になると思います．

Step 4. 実アプリケーション紹介

最後に，実際に公開してるページを実例として紹介します．

入賞実績
私が所属してる大学のキューブサークルのメンバーの入賞実績をリストにして表示しています．
あらかじめ，メンバーの WCA ID 一覧を適当なデータ構造（ここでは配列を用いています）で持っておいて `Results` テーブルから pos フィールドあたりを条件にして検索しています．

入賞実績 – 早稲田大学スピードキュービングサークル

早稲田記録
こちらも同様にメンバー一覧を用意しておいて，`Results` テーブルから各競技毎に記録を引っ張ってきます．記録を取り出したら，選択ソートのような感じで一番記録の良い行を探索して表示します．ORDER BY を使うのとどっちが性能良いのか実験してないんでわかりません．

早稲田記録 – 早稲田大学スピードキュービングサークル

日本語化について
データベースには名前のみローカル言語（日本語）表記が記録されています．一方，その他大会名等は入っていないため，英語で表示する or 手動で日本語に変換するためのマッピングを用意する必要があります．

おわりに

本記事では，WCAデータベースを使って遊ぶための基礎知識として，データベースの環境を構築することから実例までを紹介しました．

さあ，これで準備は整いました． 残すとこデータベースをどう活用するかはアイデア次第です．
例えば，今以下のアイデアを思いつきました．

• ある特定のコミュニティ内での入賞実績・最高記録の表示（今回の例）
• 任意の2競技間の相関係数・回帰直線を表示（次作りたい）
• 過去$n$か月の国籍別全競技者数に対する新規競技者の割合（もっともアツい国がわかる）
• 国籍別全競技者の各競技毎のタイムの(平均値|中央値|最頻値|その他のどれか)でランキング（その国が強い競技分野がわかる）
• 最近スクランブルもデータとして残るようになったので，模擬大会みたいなアプリケーション

面白いデータ/アプリケーション作ってみて公開してみましょう！

---