Node.jsでpostgres接続する

Node.jsでサーバPGをしようと思いpostgresへ接続

Node.js v0.10.26
postgres 9.3.2
上記バージョンにて確認

Node.jsはインストールされているとして以下のパッケージをnpmでインストールする必要がある。
https://github.com/brianc/node-postgres

$ npm install pg

使い方ですが、サーバで利用を想定して行うといかになります。

var pg = require('pg'); 
var http = require('http');

var conString = "postgres://postgres:@localhost:5432/postgres";
var server = http.createServer();
server.on('request', doRequest);
server.listen(1008);
function doRequest(request, response) {
    var client = new pg.Client(conString);
    client.connect(function(err) {
    if(err) {
        return console.error('could not connect to postgres', err);
    }
    client.query('SELECT NOW() AS "theTime"', function(err, result) {
        response.writeHead(200, {'Content-Type': 'text/html'});
        if(err) {
            return console.error('error running query', err);
        }
        console.log(result.rows[0].theTime);
        response.write(result.rows[0].theTime + "");
        response.end();
        client.end();
    });
});
};

コネクションの関係も見ながら確認してみたのですが。
pg.ClientはdoRequest内で行い、client.endがコネクションを閉じるメソッドのため
これが必須です。これを省くとサーバが動作する限りコネクション数が増えるので注意です。

postgresのバックアップを１ファイルずつgoogleドライブに保存する

自分用に作ったpostgresを１ファイルずつGoogleドライブに保存したいと考えシェルを作成。
pg_dumpを利用し対処１ファイルずつなのは除外したいDBもあるためです。
GoogleドライブはGoogleから出ているアプリを利用しフォルダに移動するだけなので保存すること自体は簡単です。
DropBoxとかもディレクトリを変更すれば対応できると思います。
※Macを利用しているのでLinux系では一部動作しない可能性もあります。
その際は一部改変が必要になるかと思います。


バックアップ詳細はDB名_yyyyMMdd.tar.gzで保存されています。
while read部分が少し戸惑いました。
パイプを利用してreadを行うないようです。

#/bin/sh

DATE=`date "+%Y%m%d"` # 日付のフォーマット
TMP_DIR="/tmp/pg_dump_$DATE" # 作業ディレクトリ
EXECLUDE_DB="template|postgres" # バックアップ除外対象
BACKUP_DIR="/Users/genya/Google ドライブ/backup" # バックアプディレクトリ
DB_USER=postgres # 動作ユーザー

# 作業ディレクトリ作成
echo "作業用ディレクトリを作成 $TMP_DIR"
mkdir $TMP_DIR

# DB一覧を取得する.
psql -c "select datname FROM pg_database;" -t -U postgres | while read db_name
do
 if [ -z "$db_name" ]; then
  continue
 fi
 # バックアップ対象外を除外
 DB_COUNT=`echo "$db_name" | grep  -E "($EXECLUDE_DB)" | wc -l`
 if [ "$DB_COUNT" -eq "1" ]; then
  echo "execlude database no backup $db_name"
  continue
 fi
 echo "backup database:$db_name"
 # バックアップ
 pg_dump -U $DB_USER $db_name > ${TMP_DIR}/${db_name}.sql
 # 圧縮
 tar zcvf ${TMP_DIR}/${db_name}_${DATE}.tar.gz ${TMP_DIR}/${db_name}.sql >/dev/null 2>&1
 # 元のファイルを削除
 rm ${TMP_DIR}/${db_name}.sql
 # googleドライブに移動
 mv ${TMP_DIR}/${db_name}_${DATE}.tar.gz "${BACKUP_DIR}/${db_name}_${DATE}.tar.gz"
done

# 作業ディレクトリ削除
rm -rf $TMP_DIR

postgresで区切り文字を集計する

備忘録のないようです。

以下のように「,」区切りの文字が連結されている状態でグループ化を行うための操作を記述

CREATE TABLE nodes(tags TEXT);
INSERT INTO nodes VALUES('tag1,tag2,tag3');
INSERT INTO nodes VALUES('tag1,tag3');
INSERT INTO nodes VALUES('tag1,tag3');
INSERT INTO nodes VALUES('tag1,tag4,tag5');

このデータを以下のようにしたい

tag   | count 
-------+-------
 tag1  |     4
 tag3  |     3
 tag2  |     1
 tag4  |     1
 tag5  |     1

このような処理はどのようにと呼ぶのか分からないがニュアンス的には
・区切り文字配列化
・パース配列化
・文字列分割結合
などか？

正式名称が知りたい・・・・
とりあえずSQLだけ記載

SELECT tag, COUNT(tag) FROM (SELECT UNNEST(STRING_TO_ARRAY(tags, ',')) FROM nodes) AS T1 GROUP BY tag ORDER BY COUNT(tag) DESC, tag;

UNNESTとSTRING_TO_ARRAYを利用することで対応が可能である。

UNNESTで検索を行うといろいろ応用が出てくるので参考にしてみたい。
とりあえず自分の目的は上記で解決です。

mavenとiciqlのインストール手順

Javaを利用する機会があり、せっかくなので新しいO/Rマッパーを試そうと思い探していて見つけたのが

iciqlとよばれるO/Rマッパーです。

以前Seasar2のS2JDBCを利用していましたが
軽量ということで使い心地はわからないがとりあえず試してみようと思います。

以下のソフトのインストール手順となります。
Databaseはpostgresを利用します.
・maven
・iciql

OSはmac OS X 10.9.2で行っています。
Postgresは9.3.2を利用しています。

mavenのインストール手順

# mavenをダウンロード
cd ~/Downloads
curl -O http://ftp.yz.yamagata-u.ac.jp/pub/network/apache/maven/maven-3/3.2.1/binaries/apache-maven-3.2.1-bin.tar.gz
# 展開
tar xvzf apache-maven-3.2.1-bin.tar.gz
mv apache-maven-3.2.1 apache-maven
mv apache-maven /Applications/
# パスを追加
echo "M2_HOME=/Applications/apache-maven" >> ~/.bash_profile
echo "PATH=$PATH:$M2_HOME/bin" >> ~/.bash_profile
echo "export M2_HOME" >> ~/.bash_profile
echo "export PATH" >> ~/.bash_profile
source ~/.bash_profile

ここまではおなじみの手順です。
mavenのインストールですので特に解説なくいきます。

iciqlインストール

# iciqlのダウンロードとmvnの追加
curl -o https://iciql.googlecode.com/files/iciql-1.2.0.zip
unzip iciql-1.2.0.zip
# mavenリポジトリに追加
mvn install:install-file -Dfile=iciql-1.2.0.jar -DgroupId=com.iciql -DartifactId=iciql -Dversion=1.2.0 -Dpackaging=jar

iciqlはmavenの保存場所が直接見当たらなかったので
自分の環境にDLしローカル内で保存

pom.xmlの作成

  4.0.0
  net.rule.selenium
  roadbike
  0.0.1-SNAPSHOT
  war
  
   
    com.iciql
    iciql
    1.2.0
   
   
  postgresql
  postgresql
  9.1-901.jdbc4
   
  

postgresと一緒にりようするのでiciqlとpostgresqlでpom.xmlを作成
これで「mvn eclipse:eclipse」で環境が整います。

またこちらを利用したentityクラスの自動生成もありますコマンドは以下となります
postgreテーブルの内容をentityとしたjavaファイルに出力します。

cp ~/.m2/repository/com/iciql/iciql/1.2.0/iciql-1.2.0.jar ./iciql-1.2.0.jar 
cp ~/.m2/repository/postgresql/postgresql/9.1-901.jdbc4/postgresql-9.1-901.jdbc4.jar ./postgresql-9.1-901.jdbc4.jar
java -cp iciql-1.2.0.jar:postgresql-9.1-901.jdbc4.jar com.iciql.util.GenerateModels -url "jdbc:postgresql://localhost:5432/postgres" -user postgres -password {PASSWORD}

iciqlを利用し、entityを自動生成が可能です。
ある程度はマッピングしてくれるので、こちらをベースに対応を行うと便利です。

postgres template1削除方法

今後のメモです。

postgresを利用する際に初期にtemplateを指定できます
こちらがtemplate1と呼ばれるDBが標準で適用されるのですがこちら通常の
フローではdropdb(DROP DATABASE template1)が利用できません
こちらを初期化し再構築する手順をまとめます。

$ psql -U postgres
postgres=# SELECT * FROM pg_database where datname = 'template1';
postgres=# UPDATE pg_database SET datistemplate = 'f' where datname = 'template1';
postgres=# SELECT * FROM pg_database where datname = 'template1';
postgres=# \q
$ dropdb template1
$ createdb -T template0 template1
$ psql -U postgres
postgres=# SELECT * FROM pg_database where datname = 'template1';
postgres=# UPDATE pg_database SET datistemplate = 't' where datname = 'template1';
postgres=# SELECT * FROM pg_database where datname = 'template1';

pg_databaseの中身を書き換えることで実現しています。
この方法が一番らくだと思いますのでメモです。

postgresで重複レコードを削除しよう

毎回忘れるのでメモ対応

CREATE TABLE new_table as select distinct * from old_table;

これだと毎回面倒なのでシェル化してみました

#/bin/sh
DATABASE=$1
TABLE=$2
TMP_TABLE="$2_tmp"
BK_TABLE="$2_bk"
psql -c "CREATE TABLE $TMP_TABLE as select distinct * from $TABLE;" -U postgres $DATABASE
psql -c "CREATE TABLE $BK_TABLE as select * from $TABLE;" -U postgres $DATABASE
psql -c "DROP TABLE $TABLE" -U postgres $DATABASE
psql -c "ALTER TABLE $TMP_TABLE RENAME TO $TABLE" -U postgres $DATABASE

方法としては、distinctを利用し結果がユニークになることを利用し

テーブルのコピーを行い再度戻す対応となります

念のためバックアップも作成していますが、こちらは不要なら消すべきかと
PKが存在する系では、難しいかも知れませんが中間テーブルなどでは便利な手法です。

mac mavericksでpostgresをPHP PDO経由で使う方法

こんにちは、今回は今更ですが自分でMacのmountain lionからmavericksにアップデートしたときに
PHPとPDO経由でpostgresを利用できなくなったのでそちらに対するメモを記載.

ステップは以下のステップになります。
mac内にpostgresのサーバとなります。

1.Xcodeのインストール
2.postgres9.3のインストール
3.pgsqlとpdo_pgsqlのコンパイル
4.PHPのインストールとapacheの設定

このような構成になります。
早速実行

---

1.Xcodeのインストール

こちらのアプリはApp Storeから「Xcode」と検索を行っていただければダウンロードが行えます。
2G程度の要領が必要ですので注意です。

---

2.postgres9.3のインストール

まずはこちらからpostgres9.3系をDLします。
インストールは、よくあるもの名での割愛いたします。
通常のインストールなら、「/Library/PostgreSQL/9.3/」こちらに各種コマンド等がインストールされます。

---

3.pgsql・pdo_pgsqlのコンパイル

こちらからがメインになります。
まずはコンパイルを行うためにPHPのソースをダウンロードします.
以下一連の手順です。後ほど解説.

# brewが使えない場合は以下のコマンドでインストール
ruby -e "$(curl -fsSL https://raw.github.com/mxcl/homebrew/go/install)"
brew install autoconf
# PHP4.x系だった場合は、パスを修正してください.
curl -O http://museum.php.net/php5/php-`php -v | grep 5. | cut -d " " -f 2`.tar.gz
tar -xzvf php-`php -v | grep 5. | cut -d " " -f 2`.tar.gz 
cd php-`php -v | grep 5. | cut -d " " -f 2`/ext/pgsql/
sudo phpize
# postgresのバージョンにより差があるので各自書き換えてください.
sudo ./configure --with-pgsql=/Library/PostgreSQL/9.3/
sudo make
sudo make install

cd ../pdo_pgsql/
sudo phpize
sudo ./configure --with-pdo-pgsql=/Library/PostgreSQL/9.3/
sudo make
sudo make install

mavericksでは初期では、pdo_pgsqlなどが組み込まれていないため
自分でソースからビルドする流れになっています。
上記が手順となります。

4.PHPのインストールとapacheの設定

インストールしたモジュールなどを読み取るため設定を変更します

sudo sh -c 'cat /etc/php.ini.default | sed -e "s/; extension_dir = \".\/\"/extension_dir=\/usr\/lib\/php\/extensions\/no-debug-non-zts-20100525/g" -e "s/;extension=php_pdo_pgsql.dll/extension=pdo_pgsql.so/g" -e "s/;extension=php_pgsql.dll/extension=pgsql.so/g" > /etc/php.ini'
sudo apachectl restart

こちらでインストールができたはずです。
extension_dirはもしかすると自分でカスタマイズする必要があるかもしれません。

---

最後に以下のコマンドで動作確認

php -i | grep pgsql

こちらでpgsqlが出てきたら動作できます。

postgres SQLチューニングpart2【WHERE句での検証】

postgresでSQLの記述で高速化を図ります。Part2
パラメータ調整で多々あるみたいですが効率の良いSQLについて検証してみます。
前回の検証の続きです。
検証内容は前回に似ています。
もっとより実務的なSQLのチューニングをはかります。

---

検証1

サイト毎の合計view数,conversion数を計算

SQL1	SELECT * FROM (SELECT site_id, site_name, SUM(view) AS view, SUM(conversion) AS conversion FROM site AS T1 INNER JOIN site_view AS T2 USING(site_id) WHERE date BETWEEN '2013/11/01' AND '2013/11/30' GROUP BY site_id, site_name ORDER BY site_id) AS S1 LIMIT 10 OFFSET 0;
SQL2	SELECT * FROM (SELECT site_id, site_name, view, conversion FROM site AS T1 INNER JOIN (SELECT site_id, SUM(view) AS view, SUM(conversion) AS conversion FROM site_view WHERE date BETWEEN '2013/11/01' AND '2013/11/30' GROUP BY site_id) AS T2 USING(site_id) ORDER BY site_id) AS S1 LIMIT 10 OFFSET 0;

上記のSQL1,SQL2を検証します.
前回とほぼ同一なのでシェルの実行等は省きます.


以下が検証結果です。

実行ファイル	1回目	2回目	3回目	4回目	5回目	6回目	7回目	8回目	9回目	10回目	最小	最大	平均
sql1.sql	417.611	376.637	420.722	350.816	316.651	322.067	335.734	307.371	385.674	370.905	307.371	420.722	360.418
sql2.sql	321.847	280.912	303.027	263.667	258.189	276.157	310.888	274.68	277.269	265.227	258.189	321.847	283.186

と検証結果はこのようになりました。
先ほどよりやや差は縮まりましたがやはりSQL２の方が早いです。


では、SQL1の記述は遅いのかを親のデータsiteにWHERE句をかけて試します

SQL1	SELECT * FROM (SELECT site_id, site_name, SUM(view) AS view, SUM(conversion) AS conversion FROM site AS T1 INNER JOIN site_view AS T2 USING(site_id) WHERE T1.site_name ~ '[0-5]$' GROUP BY site_id, site_name ORDER BY site_id) AS S1 LIMIT 10 OFFSET 0;
SQL2	SELECT * FROM (SELECT site_id, site_name, view, conversion FROM site AS T1 INNER JOIN (SELECT site_id, SUM(view) AS view, SUM(conversion) AS conversion FROM site_view GROUP BY site_id) AS T2 USING(site_id) WHERE T1.site_name ~ '[0-5]$' ORDER BY site_id) AS S1 LIMIT 10 OFFSET 0;

このようなSQLです、あえて遅くするために正規表現で記載

実行ファイル	1回目	2回目	3回目	4回目	5回目	6回目	7回目	8回目	9回目	10回目	最小	最大	平均
sql1.sql	1709.609	1729.138	1714.069	1862.493	1838.969	1752.116	1668.097	1701.067	1749.651	1714.535	1668.097	1862.493	1743.974
sql2.sql	1074.498	1056.323	1087.329	1060.741	1051.747	1133.521	1133.265	1143.876	1087.005	1065.277	1051.747	1143.876	1089.358

極端とまでは行かないが半分近くSQL2が早い良い書き方はこういったように
効率化できそうである。
と後もう１個検証したい・・・

HAVINGを使うかWHEREを使うかである
検証で早いであろうSQL２をベースに以下のないようで検証します

SQL1	SELECT * FROM (SELECT site_id, site_name, view, conversion FROM site AS T1 INNER JOIN (SELECT site_id, SUM(view) AS view, SUM(conversion) AS conversion FROM site_view GROUP BY site_id HAVING SUM(conversion) > 90000) AS T2 USING(site_id) WHERE T1.site_name ~ '[0-5]$' ORDER BY site_id) AS S1 LIMIT 10 OFFSET 0;
SQL2	SELECT * FROM (SELECT site_id, site_name, view, conversion FROM site AS T1 INNER JOIN (SELECT site_id, SUM(view) AS view, SUM(conversion) AS conversion FROM site_view GROUP BY site_id) AS T2 USING(site_id) WHERE T1.site_name ~ '[0-5]$' AND T2.conversion > 90000 ORDER BY site_id) AS S1 LIMIT 10 OFFSET 0;

HAVINGを利用したSQLがSQL1でWHERE句を利用したのがSQL2である
何方もどっちな気がしなくはないが検証

実行ファイル	1回目	2回目	3回目	4回目	5回目	6回目	7回目	8回目	9回目	10回目	最小	最大	平均
sql1.sql	1180.76	1383.684	1326.127	1376.931	1359.841	1411.559	1368.94	1324.281	1383.121	1211.007	1180.76	1411.559	1332.625
sql2.sql	1359.779	1306.331	1312.314	1350.094	1327.714	1377.518	1405.109	1362.456	1313.428	1146.364	1146.364	1405.109	1326.111

お〜結論何方もどっちであることが分かりました。


仮にPHPやJavaでWHERE句やHAVINGを動的生成するのならSQL2の方がやりやすそうな気がするのでSQL2に軍配あり？ とま〜長々と検証していきました。


結論としては、GROUP BYを先に行いJOINしましょうに限る
今度は、月別レポートの作成で検証したいと思います。

postgres SQLチューニングpart1【基本文での検証】

postgresでSQLの記述で高速化を図ります。
パラメータ調整で多々あるみたいですが効率の良いSQLについて検証してみます。
前回のテストテーブルpostgresで連番のテストデータを作成のデータをもとにします。
検証する条件は以下です。
1.思いつくパターンを複数SQLを記載.
2.検証には10回のデータを用います.
3.\timingの値を検証結果とします.
4.limit offsetを利用回数毎に移動させます.
5.WHERE句の有無での変化を確認.
上記条件が主なないようになります。

site,site_viewこちらの２テーブルの検索です。
\timingと呼ばれるコマンドを利用することで簡単に実行時間を
計ることができるのでこちらを利用.

---

検証1

サイト毎の合計view数,conversion数を計算

SQL1	SELECT site_id, site_name, SUM(view) AS view, SUM(conversion) AS conversion FROM site AS T1 INNER JOIN site_view AS T2 USING(site_id) GROUP BY site_id, site_name ORDER BY site_id LIMIT 10 OFFSET 0;
SQL2	SELECT site_id, site_name, view, conversion FROM site AS T1 INNER JOIN (SELECT site_id, SUM(view) AS view, SUM(conversion) AS conversion FROM site_view GROUP BY site_id) AS T2 USING(site_id) ORDER BY site_id LIMIT 10 OFFSET 0;

上記のSQL1,SQL2を検証します.
検証用のシェルを作成.

sql1.sql

---

\timing
SELECT site_id, site_name, SUM(view) AS view, SUM(conversion) AS conversion FROM site AS T1 INNER JOIN site_view AS T2 USING(site_id) GROUP BY site_id, site_name ORDER BY site_id LIMIT 10 OFFSET 0;
SELECT site_id, site_name, SUM(view) AS view, SUM(conversion) AS conversion FROM site AS T1 INNER JOIN site_view AS T2 USING(site_id) GROUP BY site_id, site_name ORDER BY site_id LIMIT 10 OFFSET 10;
・・・
SELECT site_id, site_name, SUM(view) AS view, SUM(conversion) AS conversion FROM site AS T1 INNER JOIN site_view AS T2 USING(site_id) GROUP BY site_id, site_name ORDER BY site_id LIMIT 10 OFFSET 90;

sql2.sql

---

\timing
SELECT site_id, site_name, view, conversion FROM site AS T1 INNER JOIN (SELECT site_id, SUM(view) AS view, SUM(conversion) AS conversion FROM site_view GROUP BY site_id) AS T2 USING(site_id) ORDER BY site_id LIMIT 10 OFFSET 0;
SELECT site_id, site_name, view, conversion FROM site AS T1 INNER JOIN (SELECT site_id, SUM(view) AS view, SUM(conversion) AS conversion FROM site_view GROUP BY site_id) AS T2 USING(site_id) ORDER BY site_id LIMIT 10 OFFSET 10;
・・・
SELECT site_id, site_name, view, conversion FROM site AS T1 INNER JOIN (SELECT site_id, SUM(view) AS view, SUM(conversion) AS conversion FROM site_view GROUP BY site_id) AS T2 USING(site_id) ORDER BY site_id LIMIT 10 OFFSET 90;

---

それぞれの時間を以下のコマンドで検証します.
psql -U postgres -f sql1.sql analytics | grep Time: | cut -d " " -f 2
psql -U postgres -f sql2.sql analytics | grep Time: | cut -d " " -f 2

以下が検証結果です。

実行ファイル	1回目	2回目	3回目	4回目	5回目	6回目	7回目	8回目	9回目	10回目	最小	最大	平均
sql1.sql	2314.365	2247.267	2331.978	2235.310	2244.249	2457.474	2531.733	2445.966	2614.957	2483.380	2235.31	2614.957	2390.6679
sql2.sql	1080.565	1041.092	1062.615	1019.743	1037.495	1031.203	1020.304	1049.538	1021.847	1043.459	1019.743	1080.565	1040.7861

と検証結果はこのようになりました。
sql2のほうが圧倒的です。(ここまででるのかw
ここからさらにWHERE句を付けより検証していきたいと思います。
とりあえずこの記事はここまで次回part2を記事化していきます。

postgresで連番のテストデータを作成

SQLの検証をしたくてテストデータを作成しようと
思ったのですが良い方法がないか検討しまとめました。

今回は親のテーブルに対して、１日毎にレコードが付く構造です。
以下テーブル用のSQLになります

-- サイト情報
CREATE TABLE site (
 site_id SERIAL,
 site_name TEXT,
 PRIMARY KEY(site_id)
);

-- サイト履歴
CREATE TABLE site_view (
 site_id NUMERIC(19, 0),
 date TIMESTAMP,
 view NUMERIC(19, 0), -- 　view数
 conversion NUMERIC(19, 0), -- コンバージョン数
 PRIMARY KEY(site_id, date)
);

サイトとサイトに対するview数とコンバージョン数のデータです。
Googleアナリティクスのデータみたいなものを集計したいのでこのような名称ですw

今回はこちらの２テーブルに以下の条件でデータを入れます.
1.siteテーブルには１０００件のサイト情報を入れる.
2.siteテーブルには連番でサイト名を登録.
3.site_viewには過去１年間のレコードを入れる.
4.site_view#viewには必ず毎日１以上のレコードが存在する.
5.site_view#conversionには、view以下の数値が入る.
以上の条件で生成します。

-- 1000件のサイト情報を作成します。
INSERT INTO site(site_name) SELECT 'site' || generate_series(1, 1000);

-- １年分のテストレコードの作成
INSERT INTO site_view(site_id, date, view) SELECT site_id, (current_date - (generate_series(1, 365) || ' days')::INTERVAL) AS date, TRUNC(random() * 1000) AS view FROM site;

-- 1年分のconversionデータの作成
UPDATE site_view SET conversion = TRUNC(random() * view);

これで完了です。
generate_seriesを利用することで連番が作成できるのでこの機能を利用しました
またgenerate_seriesを利用し日付を１年間-1ずつすることで簡単に１年分の日付を１行で
記載が可能です。

postgresで一次テーブルをトランザクションで利用

postgresの一次テーブルについてのメモ
一次テーブルを利用しようとしたが仕様がいまいち
わからなかったのですこしまとめました。
大きく２点のまとめです。
1.一次テーブルをとりあえず作ったらどのタイミングで消えるのか
2.BEGIN,COMITのトランザクション間で一次テーブルを利用するにはどうすれば良いのか

この２つについて記載いたします。

環境

環境情報	構築日	ソフトウェア
CentOS 6.3	2013/07/06	postgresql8.4

1.一次テーブルをとりあえず作ったらどのタイミングで消えるのか
こちらについては、とりあえず一次テーブルを作る意味合いのメモです。

$ psql 
-- 以下SQL文を記載

-- 一次テーブルを作成
CREATE TEMP TABLE temptable1(id numeric(9,0));

-- この時点ではtemptable1は残っている
INSERT INTO temptable1 VALUES(1);
-- この時点ではtemptable1は残っている
BEGIN;
INSERT INTO temptable1 VALUES(2);
INSERT INTO temptable1 VALUES(3);
-- この時点ではtemptable1は残っている
SELECT * FROM temptable1;
COMMIT;
-- この時点ではtemptable1は残っている
SELECT * FROM temptable1;
-- この時点ではtemptable1は残っている
¥q
-- この時点でtemptable1は削除される
$ psql 

-- この時点でtemptable1は存在しない
¥d temptable1

と、上記のようになります。
特に意識せずに一次テーブルを作成すると、¥qで全体を抜けるまで
一次テーブルが有効です。ちなみにコンソールを２つ表示し
それぞれpsqlコマンドで同じDBに入っても作った方意外からは見えません。
これが一次テーブルです。


2.BEGIN,COMITのトランザクション間で一次テーブルを利用するにはどうすれば良いのか
こちらの使い方を自分で使いたかったのでまとめました。

$ psql 
-- 以下SQL文を記載

BEGIN;
-- 一次テーブルを作成 COMMIT DROPオプションがつきます
CREATE TEMP TABLE temptable1(a numeric(9,0)) ON COMMIT DROP;

-- テーブルが存在します。
¥d temptable1

COMMIT;

-- テーブルが存在しません。
¥d temptable1

このようにトランザクションを張る場合は、COMMIT DROPが有効です。
重たい動作をされる際にご利用ください。

postgresで累計グラフ用のデータを作成

postgresで累計グラフ(積み上げ折れ線グラフ)を作成するための集計を実施します。
使うようで使わない気がしますが、私では利用する頻度が多いのでメモ含め書き留めます。


今回は、あるシステムで６月の日付ごとにコストと集客人数を
入力したテーブルに対して６月の日毎のと累計を知りたいのが目的とします。

環境

環境情報	構築日	ソフトウェア
CentOS 5.6	2013/06/18	postgresql 8.4以降

今回利用するSQLではpostgresql 8.4以降でないと扱えない文法のため 8.4以上でお試しください。 まずは、お決まりのテーブルを作成

-- コスト、集客管理
CREATE TABLE report (
 date TIMESTAMP, -- 集計日
 cost NUMERIC(19, 0), -- コスト 
 person NUMERIC(19, 0) -- 集客人数
);

-- 日毎のデータ
INSERT INTO report VALUES
('2013/06/01', 100, 1),
('2013/06/02', 120, 3),
('2013/06/03', 150, 0),
('2013/06/04', 110, 6),
('2013/06/05', 130, 1),
('2013/06/06', 120, 4),
('2013/06/07', 160, 5),
('2013/06/08', 190, 7),
('2013/06/09', 100, 1),
('2013/06/10', 110, 9),
('2013/06/11', 100, 8),
('2013/06/12', 100, 5),
('2013/06/13', 130, 4),
('2013/06/14', 120, 3),
('2013/06/15', 100, 6),
('2013/06/16', 100, 1),
('2013/06/17', 110, 0),
('2013/06/18', 100, 0),
('2013/06/19', 170, 1),
('2013/06/20', 100, 2),
('2013/06/21', 100, 6),
('2013/06/22', 190, 7),
('2013/06/23', 100, 9),
('2013/06/24', 100, 1),
('2013/06/25', 190, 8),
('2013/06/26', 100, 6),
('2013/06/27', 120, 7),
('2013/06/28', 120, 4),
('2013/06/29', 150, 2),
('2013/06/30', 110, 1);

上記のデータが元データです。 集計には「over」と呼ばれるWindow関数を利用します。

-- 集計用SQL
SELECT date, SUM(cost) over (order by date) AS cost, SUM(person) over (order by date) AS person FROM report;
        date         | cost | person 
---------------------+------+--------
 2013-06-01 00:00:00 |  100 |      1
 2013-06-02 00:00:00 |  220 |      4
 2013-06-03 00:00:00 |  370 |      4
 2013-06-04 00:00:00 |  480 |     10
 2013-06-05 00:00:00 |  610 |     11
 2013-06-06 00:00:00 |  730 |     15
 2013-06-07 00:00:00 |  890 |     20
 2013-06-08 00:00:00 | 1080 |     27
 2013-06-09 00:00:00 | 1180 |     28
 2013-06-10 00:00:00 | 1290 |     37
 2013-06-11 00:00:00 | 1390 |     45
 2013-06-12 00:00:00 | 1490 |     50
 2013-06-13 00:00:00 | 1620 |     54
 2013-06-14 00:00:00 | 1740 |     57
 2013-06-15 00:00:00 | 1840 |     63
 2013-06-16 00:00:00 | 1940 |     64
 2013-06-17 00:00:00 | 2050 |     64
 2013-06-18 00:00:00 | 2150 |     64
 2013-06-19 00:00:00 | 2320 |     65
 2013-06-20 00:00:00 | 2420 |     67
 2013-06-21 00:00:00 | 2520 |     73
 2013-06-22 00:00:00 | 2710 |     80
 2013-06-23 00:00:00 | 2810 |     89
 2013-06-24 00:00:00 | 2910 |     90
 2013-06-25 00:00:00 | 3100 |     98
 2013-06-26 00:00:00 | 3200 |    104
 2013-06-27 00:00:00 | 3320 |    111
 2013-06-28 00:00:00 | 3440 |    115
 2013-06-29 00:00:00 | 3590 |    117
 2013-06-30 00:00:00 | 3700 |    118
(30 行)

苦労してSQLをくまないと行けないと考えていましたがWindow関数で簡単にできました。 EC売り上げ集計や、広告レポートを集計するのに役に立ちそうです。

最小二乗法をpostgresで説く

postgresにも数学用の関数がいくつか用意されています。
こちらを利用し、最小二乗法が実現可能です。
ただし、作成できるのは線形(y=ax+b)のみなので利用用とは初級では限界があるかもしれません。
でも出来るだけでもありがたい。

環境

環境情報	構築日	ソフトウェア
CentOS 5.6	2013/04/20	postgresql 9.2

まずは、テーブルを作成

-- データ格納用テーブル.
CREATE TABLE target(
date TIMESTAMP, --  日付
sales NUMERIC(19, 0) -- 値
);

続いてデータを作成

INSERT INTO target VALUES('2013/01/01', 200);
INSERT INTO target VALUES('2013/01/02', 300);
INSERT INTO target VALUES('2013/01/03', 400);
INSERT INTO target VALUES('2013/01/04', 500);
INSERT INTO target VALUES('2013/01/05', 600);
INSERT INTO target VALUES('2013/01/06', 700);
INSERT INTO target VALUES('2013/01/07', 800);
INSERT INTO target VALUES('2013/01/08', 900);
INSERT INTO target VALUES('2013/01/09', 1000);
INSERT INTO target VALUES('2013/01/10', 1100);
INSERT INTO target VALUES('2013/01/11', 1200);
INSERT INTO target VALUES('2013/01/12', 1300);
INSERT INTO target VALUES('2013/01/13', 1400);
INSERT INTO target VALUES('2013/01/14', 1500);
INSERT INTO target VALUES('2013/01/15', 1600);
INSERT INTO target VALUES('2013/01/16', 1700);
INSERT INTO target VALUES('2013/01/17', 1800);

これで利用準備が整いました
では今回解く式はy=ax+bです。
日付は、日にちと考え、年月を集約します
xを日付、yを値とすると
インサートしたデータを利用すると式は
y=100x+100ができるはずです。
SQLを作成します。

-- 傾き(a)にあたる部分を取得
SELECT regr_slope(sales, to_char(date, 'dd'):: integer) FROM target ;
-- 切片(b)にあたる部分を取得
SELECT regr_intercept(sales, to_char(date, 'dd'):: integer) FROM target ;

今回のデータは参考用のためきれいな値が求まりましたが もっと複雑なのも大丈夫です。 今回利用した集約関数(postgresマニュアル参照)では下記の説明がなされています

regr_intercept(Y, X)	(X, Y)の組み合わせで決まる、線型方程式に対する最小二乗法のY切片。
regr_slope(Y, X)	(X, Y)の組み合わせで決まる、最小二乗法に合う線型方程式の傾き。

postgresql 9.2のインストール

CentOS 5.6を使用しpostgresql 9.2をインストールする構築手順書

環境

環境情報	構築日	ソフトウェア	必要込コマンド
CentOS 5.6	2013/02/01	postgresql	yum

$ yum -y install ruby ruby-devel ruby-irb ruby-rdoc ruby-libs

$ su -
$ cd ~/
$ mkdir download
$ cd download

$ wget http://yum.postgresql.org/9.2/redhat/rhel-5-x86_64/pgdg-centos92-9.2-5.noarch.rpm
$ rpm -ivh pgdg-centos92-9.2-5.noarch.rpm
$ yum clean packages
$ yum install postgresql postgresql-server
$ /etc/init.d/postgresql-9.2 initdb
$ /etc/init.d/postgresql-9.2 start

とりあえずこれでインストールができる。