PostgreSQLで統計解析 PL/R 基礎編 : PostgreSQL Advent Calendar #6

PostgreSQL Advent Calendar 12/6 です。

ネタを決めるにあたり、analyticsをテーマに何かないものかと探してみたら、
PL/R(R手続き型言語)なるものがあるそうで。

Rは統計解析分野で人気のプログラミング言語。
オープンソースで、コミュニティの活動も活発なようです。
参考：R Advent Calendar 2011 : ATND

では、PL/Rを導入して、ちょこっと触ってみます。


Install
今回構築した環境は下記。
・Mac OS X 10.6 Snow Leopard
・gcc 4.2.1
・postgresql 9.0.4
・R 2.14.0
・plr-8.3.0.13

Windows,Linuxへのインストールは下記をご参考
R と PostgreSQL - RjpWiki

まずRをインストール。
(macのhomebrewを使用)

brew install r
Error: This formula requires a fortran compiler

失敗。fortranコンパイラをインストールして再チャレンジ。

brew install gfortran
brew install r

OK。インストール時に一般的なインストール先に
リンクさせた方がいいよとメッセージが出るので、

sudo ln -s "/usr/local/Cellar/r/2.14.0/R.framework" /Library/Frameworks

これでRのインストールは完了。
なお、パッケージインストールも可能。
参考：R for Mac OS X

次にPL/Rをインストール。
ソースは→ http://joeconway.com/plr/ から入手。

su - postgres
cd /path-to-postgres_source/contrib #ここにPL/Rのソースを配置
tar zxf plr-8.3.0.13.tar.gz
cd plr
export R_HOME=/Library/Frameworks/R.framework/Resources
make
make install

PL/Rのインストール完了。テスト用のDBを作成して言語を登録。

createdb r_test
cd /path-to-pgsql/contrib
psql r_test < plr.sql
psql -c "SELECT * FROM pg_language WHERE lanName = 'plr'" r_test #確認

これで導入完了。


Try working

PL/Rのドキュメントにある集約関数をテストしてみます。
(中央値を返す集約関数medianを作成)

create or replace function r_median(_float8) returns float as '
  median(arg1)
' language 'plr';
CREATE AGGREGATE median (
  sfunc = plr_array_accum,
  basetype = float8,
  stype = _float8,
  finalfunc = r_median
);

SELECT median(v)
FROM (values(1.0),(2.5),(5.7),(3.4),(-0.9)) AS t(v);
-- => select 2.5

集約関数はfloat8を配列に集約して最後にr_median(_float8)を呼び出し、
Rの関数medianを計算しています。

ポイントはデータ型の違い。
R言語はベクトル処理言語であり、データはベクトルで持ちます。
例えば、上記のmedianの計算は、Rでは下記のように行います。

x <- c(1.0, 2.5, 5.7, 3.4, -0.9) #ベクトルを作成してxに代入
median(x) # => [1] 2.5

postgresからRへの引数の受け渡しについては、
・スカラ　⇒　単一要素のベクトル（例外あり）
・ 一次元配列　⇒　複数要素のベクトル
・二次元配列　⇒　行列
・三次元配列　⇒　三次元配列
・三次元以上の配列　⇒　サポートせず
・ 複合型　⇒　データフレーム
となります。
参考：Passing Data Values

では最後に、統計解析の基礎として相関係数を計算する関数を作成してみます。
参考：R-Source

CREATE OR REPLACE FUNCTION correlation(_float8, _float8) RETURNS float8 AS '
 cor(arg1, arg2)
' LANGUAGE 'plr';

列xと列yの相関係数を計算する場合は下記で。

SELECT correlation(array_agg(x),array_agg(y))
FROM (values(0.7,1.9),(-1.6,0.8),(-0.2,1.1),(-1.2,0.1),(-0.1,-0.1)
　　　　　　,(3.4,4.4),(3.7,5.5),(0.8,1.6),(0.0,4.6),(2.0,3.4)) AS t(x,y);
-- => select 0.795102...

以上、簡単にさわってみました。
それほど敷居は高くないように感じますが、どういう用途に使ったものか．．．


Be continued
応用編に続く　．．．のか？


Next
明日はs87さんです。よろしくお願いします。

【PostgreSQL】2つの配列に共通する要素のインデックスを取得する関数

やりたいことは、配列A{1,3,4,5,8,10,11,12}と配列B{1,2,5,11,13}から共通する要素（1,5,11）を探し、
配列Aの要素番号(1,4,7)を取得すること。（配列Aの要素はユニークである仮定）


配列Bが数値1つでよければ、contribにidx関数がある。

idx(int[], int item)
戻り値の型：int
説明：itemに一致する要素番号（存在しなければ0）
例：idx(array[11,22,33,22,11], 22)
結果：2

intarray
http://www.postgresql.jp/document/current/html/intarray.html


これを利用して関数を作成(sort()とuniq()もintarrayモジュールに含まれる)

CREATE OR REPLACE FUNCTION idx_array(_int4, _int4) RETURNS _int4 AS
'SELECT
    uniq(sort(array_agg(idx($1, t.v))))
FROM
    unnest($2) AS t(v)
WHERE
    idx($1, t.v) > 0'
LANGUAGE SQL IMMUTABLE STRICT;

SELECT idx_array(ARRAY[1,3,4,5,8,10,11,12], ARRAY[1,2,5,11,13])
結果：{1,4,7}

やさしいJavaメモ

レッスン７　配列
・配列を宣言し、new演算子で配列を確保することができます。
・配列変数を使って、配列要素に値を代入することができます。
・{}内に値を指定して、配列を初期化することができます。
・.lengthをつけて、配列要素の数を知ることができます。
・配列変数には、別の配列変数を代入することができます。
・多次元配列を作成して扱うことができます。

レッスン８　クラスの基本
・クラスは、フィールドとメソッドをメンバとしてもちます。
・クラス宣言をもとにしてオブジェクトを作成することができます。
・オブジェクトを作成してメンバにアクセスすることができます。
・クラス内に変数を宣言してフィールドとすることができます。
・クラス内に一定の処理をまとめて、メソッドとして定義することができます。
・メソッドに引数を渡して処理させることができます。
・メソッドの呼び出し元は、戻り値を受けとることができます。

レッスン９　クラスの機能
・privateメンバには、クラスの外からアクセスすることはできません。
・publiメンバには、クラスの外からアクセスすることができます。
・カプセル化によって、誤りのおきにくいプログラムを作成することができます。
・メソッド名が同じで引数の型・数の異なる複数のメソッドをオーバーロードすることができます。
・コンストラクタは、オブジェクトを作成するときに呼び出されます。
・引数の型・数の異なる複数のコンストラクタをオーバーロードすることができます。
・個々のオブジェクトに関連づけられたメンバを、インスタンス変数・インスタンスメソッドと呼びます。
・クラスに関連づけられたメンバを、クラス変数・クラスメソッドと呼びます。

レッスン１０　クラスの利用
・クラスライブラリのクラスを使うと、コードを簡単に作成できます。
・クラス型の変数には、同じクラスの変数を代入することができます。
・クラス型の変数にnullを代入すると、その変数はオブジェクトをささなくなります。
・フィールドにクラス型の変数を使うことができます。
・メソッドの仮引数としてクラス型の変数を使うことができます。
・オブジェクトを配列で扱うことができます。

レッスン１１　新しいクラス
・スーパークラスからはサブクラスを拡張することができます。
・サブクラスは、スーパークラスのメンバを継承します。
・スーパークラスのprotectedメンバに、サブクラスからアクセスすることができます。
・スーパークラスと同じメソッド名・引数の型・数をもつメソッドをサブクラスで定義して、オーバーライドすることができます。
・スーパークラスを指定しないクラスは、Objectクラスのサブクラスとなります。

レッスン１２　インターフェイス
・抽象クラスを宣言することができます。
・抽象クラスは処理が定義されていない抽象メソッドをもちます。
・抽象クラスのオブジェクトを作成することはできません。
・インターフェイスを宣言してクラスで実装することができます。
・インターフェイスのフィールドは、定数となります。
・インターフェイスのメソッドは、処理を定義することができない抽象メソッドとなります。
・スーパーインターフェイスを拡張し、サブインターフェイスを宣言することができます。

レッスン１３　大きなプログラムの作成
・ファイルを分割してコンパイルすることができます。
・クラスをパッケージに含めるにはpackage文を使います。
・クラスを異なるパッケージから利用できるようにするには、publicを指定します。
・異なるパッケージのクラスを利用するには、「パッケージ名.クラス名」と記述します。
・異なるパッケージのクラスをimport文を使ってインポートすることができます。
・クラスライブラリのクラスは、パッケージ内に分類されています。
・同じパッケージのクラスをすべてインポートするには、import文で*を指定します。

レッスン１４　例外と入出力処理
・例外は、try、catch、finallyブロックを使って処理します。
・独自の例外クラスをつくるには、Throwableクラスのサブクラスを拡張します。
・例外を送出するにはthrow文を使います。
・例外を送出する可能性のあるメソッドにはthrowsをつけます。
・入出力を行うには、ストリームを利用します。
・コマンドライン引数を利用すると、プログラムに引数を渡すことができます。

レッスン１５　スレッド
・スレッドは、複数起動することができます。
・スレッドを起動するにはThreadクラスを拡張し、start()メソッドを呼び出します。
・スレッドの最初の処理として、run()メソッドが呼び出されます。
・スレッドを一時停止するには、sleepメソッドを呼び出します。
・スレッドの終了を待つには、join()メソッドを呼び出します。
・複数のスレッドが共有するフィールドなどを処理するときは、メソッドにsynchronizedを指定します。

レッスン１６　アプレット
・Webブラウザ上で動作するアプレットを作成することができます。
・paint()メソッドを定義して、画面に文字やグラフィカルな要素を表示できます。
・init()、start()、stop()、destroy()メソッドを定義して、Webブラウザの動作にあわせた処理を記述できます。
・ウィンドウ部品（AWT）をアプレット上で使うことができます。
・スレッドによるアニメーションをアプレット上で行うことができます。

やさしいCメモ

レッスン７　配列
・配列を宣言して、各要素に値を代入できます。
・{}内に初期化子を指定して、配列を初期化することができます。
・初期化子がたりない場合は、たりない数だけ0が格納されます。
・文字列に置き換えられるマクロを定義することができます。
・多次元配列を作成して扱うことができます。
・文字列配列の末尾には¥0を格納します。
・文字列配列は、""を使った文字列を指定して初期化できます。
・文字列を操作するときは、最後が¥0であることを利用します。

レッスン８　関数
・一定の処理をまとめて関数として定義し、呼び出すことができます。
・関数の本体に引数を渡して処理させることができます。
・関数の本体から戻り値を受け取ることができます。
・かんたんな処理について、関数形式マクロを定義できます。
・変数の宣言位置によって、ローカル変数とグローバル変数があります。
・関数プロトタイプ宣言によって、関数の仕様をコンパイラに知らせることができます。
・ファイルを分割してコンパイルすることができます。
・ヘッダファイルには関数プロトタイプ宣言を記述します。
・C言語の開発環境には、標準ライブラリ関数が添付されています。

レッスン９　ポインタ
・アドレスは、メモリ上の位置を直接あらわします。
・ポインタは、特定のアドレスを格納する変数です。
・アドレス演算子（&）を使うと、ポインタがさしている変数の値を得ることができます。
・ポインタに関数参照演算子（*）を使うと、ポインタがさしている変数の値を得ることができます。
・引数は原則として値渡しで関数に渡されます。
・関数の引数にポインタを使うと、呼び出し元の実引数を変更することができます。
・仮引数にconstを指定すると、実引数を変更することができなくなります。

レッスン１０　配列・ポインタの応用
・配列名は、配列の先頭要素のアドレスをあらわします。
・配列を引数にもつ関数は、配列の先頭要素のアドレスを渡します。
・文字列関数は、""を使った文字列を指定して初期化できます。
・文字列は、配列またはポインタで扱うことができます。
・標準ライブラリの文字列操作関数を利用することができます。
・プログラムの実行時にメモリを確保することができます。
・関数ポインタによって、関数を呼び出すことができます。

レッスン１１　いろいろな型
・基本型以外に新しい型を作成することができます。
・構造体は、異なる型の値をまとめることができる型です。
・構造体のメンバにアクセスするには、ドット演算子（.）を使います。
・typedefを使って、独自の型名をつけることができます。
・構造体に同じ方の構造体を代入することができます。
・構造体にビットフィールドをもたせることができます。
・構造体のポインタからメンバにアクセスするには、アロー演算子（->）を用いると便利です。
・構造体の配列を作成することができます。
・共用体の各メンバは、同じメモリを共有します。
・列挙は、識別子の値を格納することができます。

レッスン１２　ファイルの入出力
・入出力機能を使うには、標準ライブラリを利用します。
・scanf()関数・printf()関数を使って、標準入出力との書式付き入出力ができます。
・gets()関数・puts()関数を使って、標準入出力との1行入出力ができます。

・getchar()関数・putchar()関数を使って、標準入出力との1文字入出力ができます。
・fscanf()関数・fprintf()関数を使って、ストリームとの書式付き入出力ができます。
・fgets()関数・fputs()関数を使って、ストリームとの1行入出力ができます。

・fgetc()関数・fputc()関数を使って、ストリームとの1文字入出力ができます。

・fopen()関数・fclose()関数を使って、ファイルのオープン・クローズができます。

・fread()関数・fwrite()関数を使って、指定したデータサイズの入出力ができます。

・fseek()関数を使って、ファイルポジションを移動できます。

・コマンドライン引数を利用すると、プログラムに文字列を渡すことができます。
・条件付きコンパイルを行うことができます。
・組み込みマクロを使うことができます。

「誰かがやらなくちゃいけない」

明日からは社会人。大学生のときに印象に残った言葉の１つを記しておく。

大学・大学院の専攻に都市防災をテーマに扱う研究室がある。ざっくり言うと火災や地震に対応した都市づくりをどう進めるかが研究テーマだ。
自分はその研究室には所属しなかったが、その研究室の助教授と話をする機会があり、その時に聞いた言葉がずっと心のどこかでひっかかっていた。

大学４年生の夏、所属する研究室を選択する際、都市防災にするという選択肢は最初から自分にはなかった。
理由は、地味そうとか楽しくなさそうとか、そんな程度だった気がする。

都市防災に対するイメージはそんな風で、あまり魅力的な研究だとも思えなかったので、その助教授と話をしたときに聞いてみた。
「なぜ先生は都市防災をテーマに選んだのですか？」

この問いに助教授はこう答えた。
「誰かがやらなくちゃいけない」

この言葉を聞いたときはあまりピンとこなかった。そんなテーマの選び方は当時の自分にはあまり理解できなかった。
就職活動を経験することで少しずつ共感できるようになり、社会人になる前にブログに書こうと考えていたところ、3.11の震災が起きて、再度この言葉を反芻しているところだ。

明日から社会人になって、しばらくは興味・関心の赴くままに進もうと考えているが、いつかまたこの言葉を問い直すことになるのではないか、そんな気がしている。