sqliteにデータインポート&日付型データをランダムに抽出

sqliteを使用したiPhoneアプリを作成しているのですが、ある団体に所属する会員の中から、ある年齢の人をランダムに数人ずつselectしてくるという要件があり、あれこれ調べたり、試したりしたので、忘れないうちにメモしておきます。

Tableの作成とデータの用意

まず、selectしてくるテーブルの項目ですが、だいたい以下のようなものです。

シーケンス、名前、生年月日、出身地、備考

まず、sqliteに上記のテーブルを作成しました。私はmac環境で開発しているので、terminalを開いて、

sqlite3 sampledb.sqlite

のようにしてデータベースを作成します。

次に、そのデータベースにテーブル「user」を作成します。

create table user
(
key INTEGER NOT NULL PRIMARY KEY,
name TEXT,
birthday TEXT,
pref TEXT,
misc TEXT
);

ちなみに、

.tables

というコマンドを打つと、ちゃんとテーブルが作成されているかわかります。

さて、このテーブルにデータを投入したいのですが、一件一件insertしていくのも大変なので、csvからimportすることを考えます。用意したcsvは以下のようなものです。文字コードはUTF-8が扱いやすいでしょう。

sampledb.csvの中身

1,ユーザ１,1974-01-09,鹿児島,備考１
2,ユーザ２,1975-02-09,鹿児島,備考２
3,ユーザ３,1976-03-09,鹿児島,備考３
4,ユーザ４,1977-04-09,鹿児島,備考４
5,ユーザ５,1978-05-09,鹿児島,備考５

さて、これをコマンドラインからインポートします。あ、ちょっと待って。sqliteは、importのデフォルトのセパレータが「|」になっていますので、「,」に変更しておくことを忘れずに。

sqlite> .separator ','

では実際にインポートします。以下のコマンド打ちましょう。

sqlite> .import sampledb.csv user

※「sqlite>」はコマンドプロンプトなので、実際には「.」から後を入力してください。

select * from user;

と打つと、ちゃんとデータが入ったか確認できます。

日付型のデータをランダムに抽出する

さて、sqliteは日付型の項目を持つことができませんので、インポートした項目の生年月日はTEXT型で定義してあります。ここから、ある生年月日以上の人をselectするにはどうしたらいいでしょうか。これには、「date()」関数を使用します。例えば、生年月日が「1976-03-09」以降の人を抽出するには、

select date(birthday) from user where date(birthday) > date('1976-03-09');

のようなqueryになります。２件のレコードが返ってきましたか？

それでは、次の段階ですね。生年月日が「1975-02-09以上」の人をランダムに２人抽出します。これは、仕事で、なるべく同じ人が重ならないように、毎回違う人を抽出したいという要件があったためです。

select * from user where date(birthday) >= date('1975-02-09') ORDER BY RANDOM() limit 2;

いかがですか。同じクエリをもう一度投げると、今度は違った２人が返ってくると思います。

以上が、sqliteで、試行錯誤した結果です。日付型の扱いやランダム抽出など、一カ所にまとまって説明しているところが無かったので備忘録的にまとめてみました。ほとんど自分のためですが。。。誰かのお役にも立てれば幸いです。

cocos2dで物理エンジンを利用したゲームを作る際に参考になります。英語ですが、物理エンジンを解説した本は少ないため、貴重です。