yuu_nkjm blog

2014-07-01(Tue) [長年日記] 編集

[H2][SQL][RDBMS] H2 Databaseいろいろ

なぜh2 データベースを使ってみたか，なぜ気に入ったか

Javaが動けば，1MBぐらいのjarを落としてくるだけで動く．インストールが簡単，アプリに組み込みやすそう．sqliteと同じ様な手軽なイメージ．
Javaで書かれているのでJavaとの親和性が高そう．ストアドプロシージャがないみたいだが，ややこしいことやるならJavaで書けば良さそう．
WebUIがついてくるのが意外と便利．
インメモリモードがあるのがなんか面白そう．
dbのデータファイルがシンプルなので，バックアップが簡単そう．

h2の起動

こんな感じで起動．-baseDirというオプションもある．*.dbや.h2.server.propertiesの出来る位置がイマイチ分からん．

@start javaw  -cp "h2-1.4.179.jar;postgresql-8.3-607.jdbc4.jar;%H2DRIVERS%;%CLASSPATH%" org.h2.tools.Console -webAllowOthers -tcpAllowOthers -pgAllowOthers %*
@if errorlevel 1 pause

便利な関数

CSVREAD, CSVWRITE

これで読める．

SELECT * FROM CSVREAD('test.csv');

属性名の指定(無指定の時は1行目が属性名として用いられる)，文字コード，区切り文字，引用符文字(カンマからカンマまでを囲む文字)，エスケープ文字を指定しても読み込める．特に指定したく無いときはnullを渡せば良い．

 CSVREAD('game_logs.txt','ID,SESSION_ID,MESSAGE, ROUND_NUM,','utf-8',',','"','\');

Excelからcsv作った時の定番はこの変かな．char(9)はタブ区切りということ．

SELECT * FROM CSVREAD('HumanSubjects.csv', null, null, char(9));
INSERT INTO TESTTABLE SELECT * FROM CSVREAD('HumanSubjects.csv', null, null, char(9));

SQL

UNIONで和集合が取れる．同じクエリを複数のテーブルに投げて結果をまとめるのに便利．
順番を並び替えたいならUNIONしたあとにORDER BYすればOK．H2に限らないけど，ORDER BY A, Bとすれば，複数の属性を使ってソート出来る．

CREATE VIEW FP AS
SELECT * FROM
(SELECT WASEDA01_DECISION_LOGS.CREATE_AT, SESSION_ID, WASEDA01_USERS.USER_ID, ROUND, VALUE FROM WASEDA01_DECISION_LOGS JOIN WASEDA01_USERS  ON WASEDA01_DECISION_LOGS.USER_ID=WASEDA01_USERS.ID  WHERE NAME='proposition')
UNION
(SELECT WASEDA02_DECISION_LOGS.CREATE_AT, SESSION_ID, WASEDA02_USERS.USER_ID, ROUND, VALUE FROM WASEDA02_DECISION_LOGS JOIN WASEDA02_USERS  ON WASEDA02_DECISION_LOGS.USER_ID=WASEDA02_USERS.ID  WHERE NAME='proposition')
UNION
(SELECT WASEDA03_DECISION_LOGS.CREATE_AT, SESSION_ID, WASEDA03_USERS.USER_ID, ROUND, VALUE FROM WASEDA03_DECISION_LOGS JOIN WASEDA03_USERS  ON WASEDA03_DECISION_LOGS.USER_ID=WASEDA03_USERS.ID  WHERE NAME='proposition')
 ORDER BY SESSION_ID, ROUND;

CASEWHENという関数が使える．一つ目の引数が条件(述語)，二つ目の引数が条件が真の時の式，三つ目の引数は条件が偽の時の式．
CAST関数が使える．CAST(val AS type)という書式で使う．

DROP VIEW  FP_SP;
CREATE VIEW FP_SP AS 
SELECT FP.CREATE_AT, FP.SESSION_ID, FP.ROUND, FP.USER_ID AS FP, CAST(FP.VALUE AS INT) AS PROPOSITION,  CASEWHEN(SP.VALUE='no', 0, 100000-FP.VALUE * CASEWHEN(SP.VALUE='no', 0, 1))  AS PROFIT_OF_FP, SP.USER_ID AS SP, SP.VALUE AS YES_OR_NO, FP.VALUE * CASEWHEN(SP.VALUE='no', 0, 1) AS PROFIT_OF_SP, CASEWHEN(SP.SESSION_ID<='proc-20140519-165442-428', '非匿名', '匿名') AS ANONYMITY FROM FP JOIN SP ON (FP.SESSION_ID=SP.SESSION_ID AND FP.ROUND=SP.ROUND)

CASEWHEN関数の結果を他の関数の引数として渡すことも出来る．

SELECT ANONYMITY, AVG(PROPOSITION), AVG(PROFIT_OF_FP), AVG(PROFIT_OF_SP), SUM(CASEWHEN(YES_OR_NO='no', 0, 1))  AS SUM_OF_YES FROM FP_SP  GROUP BY ANONYMITY;