ジョンとヨーコのイマジン日記

想像してください。「あなたはぼくをプラグマティストだと言うかもしれない」と歌う、逆イマジンです。

SQLiteのためのメモ(Rユーザーであり、かつMacユーザーである人向け)

SQLiteは小規模なデータに適したデータベースとされる。

  • 長所:始めるのが簡単
  • 短所:大規模なデータに向かない

どの程度までが小規模なデータなのかはよくわからないが、SQLiteでなにか困ることがあれば、そのとき代替(MySQL, PostgreSQL, Google BigQuery,...?)を検討することにする。

SQLite3のインストール

Macには最初からインストールされている。
ターミナルで "sqlite3 -version" とタイプすると次のように表示されるはずだ。

$ sqlite3 -version
3.28.0 2019-04-15 14:49:49

バージョンが古い場合は素直に新しいものを入れ直したほうが無難である。
現時点で 2021-06-18 (3.36.0) が最新。
https://www.sqlite.org/changes.html

brew install sqlite3

パスを通す。bashを使っている場合、

echo 'export PATH="/usr/local/opt/sqlite/bin:$PATH"' >> .bash_profile

zshを使っている場合、

echo 'export PATH="/usr/local/opt/sqlite/bin:$PATH"' >> .zprofile

確認。

$ sqlite3 -version
3.36.0 2021-06-18 18:36:39 

SQLite3を起動する

データベースのファイル "products.sqlite" を指定して起動する。ファイルの置き場はどこでもいい。"products.sqlite" が存在しない場合、これでカレントディレクトリに新規作成される。

$ sqlite3 products.sqlite
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.

table を作る

SQLite のデータ型には次のものがある:

  • NULL. The value is a NULL value.
  • INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
  • REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
  • TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
  • BLOB. The value is a blob of data, stored exactly as it was input.

Datatypes In SQLite Version 3

Boolean 型はない。

データ型を指定しなくても table を作ることはできる。その場合、型は適当に推測されるようである(ここでの「適当」は必ずしも「妥当」とか「適切」という意味ではない)。

create table Tokyo(
    AA text,
    BB text,
    CC integer);

CSVファイルを読み込む

CSVファイルを読み込み場合、mode を csv にしておく(試したことはないが JSON などの mode もあるようだ)。

.mode csv

import する。

.import --skip 1 dat1.csv Tokyo

確認。

PRAGMA table_info(Tokyo);

確認。

select * from Tokyo limit 6;

確認。

select count(*) from Tokyo;

SQLiteを終了する

.quit または .exit

失敗した場合

Tokyo という table を削除する場合は次のようにする。

drop table Tokyo;

ついでに vacuum; もしとくといいかも。VACUUM

SQLiteではデータベースは一個のファイル(ここでは "products.sqlite")のみで管理されているようである。そのため、どんなに派手に失敗した最悪の場合でもファイル("products.sqlite")を削除してやり直せばよい。

バッチ処理

「このフォルダのファイルを全部インポート」というような形で、複数のCSVファイルを読み込みたい。しかし、うまいやりかたがわからなかった。素直に以下のようにする。

Command Line Shell For SQLite

"create_table.sql" などのファイル名でファイルを作ってコマンドをまとめて書く。上述のコマンドの .import するファイルと table の部分を置き換えて何度もコピー・アンド・ペーストすればよい。コピー・アンド・ペーストに関しては適当なプログラムを使えば手作業をなくせる。ターミナルから次のように実行。

sqlite3 products.sqlite ".read create_table.sql"

以下Rの話

RからSQLiteを呼ぶ。クエリを直に書くならこれでいい。

library(DBI)
library(RSQLite)
con <- dbConnect(SQLite(),"products.sqlite")
test <- dbGetQuery(con, "select * from Tokyo limit 10")
test2 <- dbGetQuery(con, "select * from Osaka limit 10") 
dbDisconnect(con)

しかし、dplyrを使ったほうが便利。Using dplyr with databases

例えば、

library(dplyr)
con <- DBI::dbConnect(RSQLite::SQLite(), "products.sqlite")
DBI::dbListTables(con)
tokyo_db <- tbl(con, "Tokyo")
osaka_db <- tbl(con, "Osaka")

print(colnames(tokyo_db))
print(colnames(osaka_db))

q1 <- dplyr::select(osaka_db, KEY, BB, CC) %>% 
  dplyr::filter(BB=="MOU4444") %>% 
  inner_join(tokyo_db, by=c("KEY" = "AA"))

S1 <- collect(q1) #collect()したときはじめてデータを呼びに行く

dbDisconnect(con) #閉じる

show_query(S1) #実際に実行されるクエリはこれ。

head(S1)

おしまい。

自由エネルギーもカルバック・ライブラー情報量に基づく情報量規準とみなせる

前置き

なんでこんなのを書こうと思ったんだっけ. たぶん「周辺尤度はカルバック・ライブラー情報量に基づく情報量規準とは考え方が異なる」みたいな文をどっかで見て, ちょっとつっこみたくなったからだった気がする.

用語

すべてのデータを X, すべての未知パラメータを wとまとめて置き, 尤度を p(X|w), 事前分布を \phi(w) とする.

このとき事後分布  \phi^\ast(w|X) は次の式で表される.

 \displaystyle \phi^\ast(w|X) = \frac{p(X|w) \phi(w)}{\int p(X|w) \phi(w) \, dw} .

右辺の分母である  \int p(x|w) \phi(w) \, dwエビデンスとか周辺尤度と呼ばれ, モデルの比較に用いられることがある. エビデンスというのはあまりいい術語とは思えないけど, まあそう呼ばれる. 値が大きいほうがいいモデルであるとされる.

周辺尤度の対数をとって符号を反転したもの

\displaystyle F = -\log \left\{ \int p(X|w) \phi(w) \, dw \right\}

は自由エネルギーと呼ばれる.

カルバック・ライブラー情報量から見た自由エネルギー

データがなんらかの確率分布  q(x) から生成されていると仮定し, その分布を  p(x) と推測したとする.

カルバック・ライブラー情報量( {\mathrm KL})を次のように定義する.

 \displaystyle {\mathrm KL} = \int q(x)\log \frac {q(x)}{p(x)} \,dx \\
\displaystyle =\int q(x)\log q(x) \,dx - \int q(x) \log p(x) \,dx.

第1項はデータを生成した真の分布 q(x) のみによって決まる量であり, 推測された  p(x) に依存しない.

第2項が小さいほどカルバック・ライブラー情報量は小さくなる.

つまり, 第2項が小さいほどよい推測である.

第2項を \mathrm GE と置き, 汎化損失と呼ぶことにする.

 \displaystyle {\mathrm GE} = -\int q(x) \log p(x) \,dx.

ところで, 尤度と事前分布が与えられれば, 次のように未知パラメータを事前分布の下で平均して消去することができる.

 \displaystyle p^\ast(X)=\int p(X|w)\phi(w) \, dw

 p^\ast(x) は言わば「データなしでモデルだけから作った予測分布」である.

データを生成した真の分布  q(x) p^\ast(x) と推測したときの汎化損失は

 {\mathrm GE}^\ast= -\displaystyle \int q(x) \log p^\ast(x) \,dx

で与えられる.

汎化損失そのものを知ることができればそれに越したことはないが, 無理な相談なので真の分布  q(x) による平均(積分)をデータ X による標本平均で近似することにすると次のようになる.

\displaystyle {\mathrm GE}^\ast \approx -\log p^\ast(X)

右辺は自由エネルギーである.

つまり自由エネルギーが小さいモデルを選ぶことは, カルバック・ライブラー情報量を小さくするモデル(尤度と事前分布のセット)を, 経験的に選んでいることになる.