この記事は
PostgreSQL Advent Calendar 2013の21日目の記事です。
昨日は
sawada_masahikoさんの「
bgwokerで超簡易クラスタ管理」でした。
さて最近、自分の周りではMySQLからPostgreSQLに移行したと言う方が多いです。
AWSの
RDS For PostgreSQLも出たのでDBは得意じゃないけどPostgreSQLを使ってみようかなと言う方も多いと思います。
なので今日はそんな人向け（PostgreSQL初心者から中級者向け）です。
（他のアドベントカレンダーが濃いのでたまにはこんなのもいいですよね？＾＾；）
それでは早速紹介していきます。
■serial型なユニークキーがあるときに最新の情報を取ってくる
これですが最新情報を取ってくるのにdate型やtimestamp型をソートしてるのを見かけます。
これをユニークキーに変えるだけです。
ユニークキーは主キーの時もあるでしょうしサロゲートキーでもいいです。
つまりこれを
SELECT * FROM table_name ORDER BY created_at DESC limit 1
こうするだけ
SELECT * FROM table_name ORDER BY primary_key DESC limit 1
で更新順になっているので同じです。
例えば特定のユーザの最終コメントを取ってくる場合などは
SELECT * FROM comment WHERE user_id = :user_id ORDER BY primary_key DESC limit 1
となります。
ただしinsertが次々行われる前提でupdateによって最新のデータが変わる時には使えません。
応用技として自分以外の最新を取ってくるなどはこうです。
SELECT
*
FROM
comment
WHERE
comment_id in (
SELECT
max(comment_id) as comment_id
FROM
comment_id
WHERE user_id != :user_id
group by
user_id
)
自分以外の最新のcomment_idを取得しin句に投げてるだけですね。
PostgreSQLは表示させたいcolumnはGROUP BYで指定するか集約関数内でしか使えません。
MySQLからPostgreSQLに来た時によく躓くところですので注意が必要です。
（逆にこのMySQLの仕様はSQLアンチパターンのアンビギュアスグループの原因になりますね）
またMySQLではサブクエリはご法度とよく言われますがPostgreSQLのサブクエリは高速です。
それと先ほどGROUP BYの話をしましたが集約関数を直接呼ぶ場合は必要ありません。
つまり全体の最新レコードを取ってくる場合は
SELECT max(comment_id) from comment_id;
となります。
GROUP BYはコストが高いので使わなくて済む場合は使わない方がいいです。
■ランキングを取ってくる
ウインドウ関数をご紹介します。
リンク先で説明されているのですがそれだけではブログが寂しいのでこちらでもします。
ちょっと応用してPARTITION BY と ORDER BYをつかってグループ毎のランキングを出してみます。
次の画像のようなデータがあるとします。
この場合にcustomer_id毎のランキング（同率の場合は番号を飛ばす）は次のとおりです。
SELECT
SELECT
user_id
, customer_id
, score
, rank() OVER (PARTITION BY customer_id ORDER BY score)
FROM
"user"
※userが予約後だったのでダブルクォーテーションで囲いました
この結果はこちらです。
自分は同様な集計をMySQLで必要となった時非常に苦労しました。
その時はSQLがかなり複雑になってしまいました。
そのためメンテナンス考え、SQLだけで解決せずにPHPを使いました。
ですが上記のSQLならメンテナンスも十分耐えれますね！！
またLet's Postgresに面白い例が掲載されているのでご紹介します。
Let's Postgres Window関数
上記のリンクの先にありますが連番になっていない（歯抜け）のレコードも次のとおり取り出せます。
SELECT
id,
prev_id
FROM
(
SELECT
id,
lag(id, 1, 0) OVER (ORDER BY id) AS prev_id
FROM tbl
) AS t
WHERE id <> prev_id + 1;
となります。
本来は難しいSQLをこんなに簡単にしてくれます。
ウインドウ関数の強力さを感じていただけましたでしょうか。
■対象だけをユニーク制約（一意インデックス）
要望として次の例があると思います。
- ユーザのマスターはメールアドレスでユニークにしたい
- 削除は論理削除としたい
- 削除された場合はユニークの対象としない
普通にユニーク制約をメールアドレスにすると削除フラグを立てたあとも新しく同メールアドレスで追加できません。
なので複数列を使ったユニーク制約が必要になります。
例えばRailsっぽくするとメールアドレスとdeleted_atですね。
defaultはdeleted_atを空白として削除された際はdeleted_atに削除日を入れます。
※defaultをNULLとした場合、NULLは値では無いのでユニーク制約にかかりません。
（NULLとNULLを比較した場合はNULLが返る=一緒ではないため）
これによってdeleted_atが空白=有効と判断できますしユニーク制約も生かせます。
ただし「削除日が同一（timestampの場合は全く同じタイミング）の時」にerrorが出ます。
なかなかあり得ないと言えますがスマートではありません。
そこで一意インデックスを使います。
INDEXにはWHERE句で対象を絞ることができます。
つまりユニークである対象を指定することができるのです。
しかもこの場合はdeleted_atはNULLでも構いません。
例えば
table名：user
ユニーク列：mail
削除フラグ：deleted_at
の場合は
CREATE UNIQUE INDEX user_idx ON user (mail) WHERE deleted_at IS NULL;
こうです。
これはdeleted_atがNULLのuserが対象になります。
この応用は他にもたくさん使うことができますので是非研究してみてください。
※ただし一意INDEXが作れるINDEXはB-treeのみです
（配列型等には向いていませんね）
他にも色々と注意点があるので
公式ドキュメント（日本語）を一読するのをオススメします。
■知ってると便利な型
さて最後はPostgreSQLの多種多様な型の話をしようと思います。
方については
choplinさんが
1日目と
7日目にもお話してくださっています。
なので今日はそれとは別の角度で型を紹介していきます。
●
連番型
MySQLでいうところのオートインクリメントな列をつくります。
正確にはinteger(整数データ型)のデフォルトにsequenceのnext valueが指定されています。
つまり連番なKEYの列を作るときにシーケンス作成→列の作成とデフォルトを指定、といった手間を省くことが出来ます。
地味にtableを作るときに面倒な作業が一気に解決します。
●
ネットワークアドレスデータ型
最近使いました。
こいつ凄いです！
まずIPアドレスを表現するinetとネットワークを表現するcidrがあります。
両方共 IP/サブネットマスク で指定できます。
つまり
192.168.0.1/32
のような形で指定できいます。
また不正なIPアドレスは当然弾いてくれます。
例 192.168.0.300/24
これだけならサブネットマスクとIPアドレスを別の列にしてテキスト型でも表現出来ると思いますね？
(IPアドレスの整合性はバリデーションするとして)
本当に凄いのはデータを入れたあとです。
まずIPアドレスの最初の鬼門はソートです。
テキスト型だとソートの際に次のようになると思います。
192.168.0.1/24
192.168.0.111/24
192.168.0.12/24
ほんとは192.168.0.111は一番下ですよね？
これもちゃんと対応してくれます。
次にすごいのは検索です。
ネットワークアドレス関数と演算子
ちゃんとネットワークに合わせて比較して抽出等が出来ます。
「192.168.10.0/24のネットワークってあと何個IP余ってるの？」
みたいな要望にもこの型でIPを管理していればすぐ答えることができます。
業務管理系のアプリ作成やApacheのログの整形時に役立つ事まちがいなしです！
しかもコレ、IPv6も対応してます！！！！
●
配列型
私のお気に入りです。
ホントはこれを今回のメインに据えようと思ってたのですが初日で
choplinさんが紹介してます。
さらに大垣さんが１３日目にまさに説明したかった内容を書いてくれてます
タグ検索するならPostgreSQLで決まり
交差tableを必要とする場面で有効なことが多いです。
特にWebアプリケーションでは助けてくれる場面が多いのではないでしょうか。
（アンケートのチェックボックスの列など）
ただし外部キー制約が必要な場合やデータの重複を避ける際などは素直に正規化しましょう。
強力な武器ですが使い過ぎは毒になる典型的な例です。
●
範囲型
次は悩んだ人も多いのではないでしょうか？範囲指定の強い味方です。
範囲型は数値以外にも日付型を使うことができます。
よく要望のあがる
「公開日付の開始日と終了日」
を入れることができます。
当然、両方とも指定しないことも出来ます。
こいつの威力を発揮するのは「範囲と範囲の比較」をする時です。
例えば公開期間に対して
１月１日～２月１日うちで公開しているリストを出す
などの場合です。
特に重複(共通点を持つ)をプログラムで表現するときは発想の転換が必要です。
（純粋に重複するか判断するのは大変なので重複以外で考えた方がいい）
そんな煩わしさも簡単な演算子で比較することで解決できます。
範囲関数と演算子
また範囲の最大値、最小値も求めることができますし開始日を指定しない場合は即日反映、終了日を指定しない場合はずっと公開などもそのまま対応することができます。
また単位を独自に指定することもできます。
例 10刻みのintなど（10 20 30 40)
カレンダーの処理や公開期間の処理はよく対応する処理です。
アプリケーションが非常にシンプルになりますので是非ご活用ください！！
PostgreSQL Advent Calendar 2013の18日目の
yancyaさんがもっと詳しく書いています。
範囲型の良さがすごく分かりやすく書いてありますので是非一読してみてください。
PostgreSQL の範囲型について
●
幾何データ
最後の幾何データ型は地図上の検索を簡単にしてくれます。
実は二年前のアドベントカレンダーで紹介しました。
postgresqlを使った位置情報の計算について
postgresqlを使った位置情報の計算について その２
JavaScriptがイケてないし色々と知らいないことも多かった時期のエントリーなので改めてここで説明したい…と思ったのですが随分長くなったので次の機会にｗ
ただ緯度経度に対する範囲検索などは需要があるようで未だに上記のエントリーがそこそこViewがあります。
PostgreSQLの幾何データ型は非常に強力で他のDBの追随を許さないレベルです。
例で近似値を使ってますがより精度が必要な場合はPostGISを使うことで対応することが出来ます。
geometry型はGoogle Mapsなどと相性は抜群です。
Let's PostgreSQLに農研機構の寺元さんの記事があるので参考にどうぞ。
PostGISとは？
さて色々とご紹介しましたが一つでもみなさんの新しい知識になっていただけましたでしょうか。
冒頭でもご紹介しましたとおり
AWSでもPostgreSQLを使えるようになりました。
また
HerokuなどもPostgreSQLを使うことが出来ます。
今までWebと言えばMySQLでしたがPostgreSQLで便利になるシーンもあると思います。
MySQLのシンプルで高速さも非常に強力ですがPostgreSQLの高機能と堅牢性も非常に強力です。
今ではオートバキュームもあり運用も楽になっていますし検索はMySQLに負けていないほど高速です。
レプリケーションやパーテーションも対応していますしKVSなtableも作れます。
まだまだ奥が深いPostgreSQLをこの機会に興味を持って頂けたらと思います。
それと最後に宣伝。
2月1日に広島で以下のイベントが行われます。
オープンセミナー2014@広島
◆ 2014年2月1日(土)
場所 : 広島市中区大手町 1 丁目 5-3 サテライトキャンパスひろしま 大講義室 502
参加費 : 無料（懇親会：3500円)
主催：PostgreSQLユーザ会
オープンセミナー2014＠広島実行委員会
受付開始 9:30-
勉強会 10:00-17:30
懇親会 18:00-20:00
告知サイト：http://osh-2014.github.com/
申込サイト：http://osh-web.doorkeeper.jp/events/7534
自分は
AWSのRDS For PostgreSQLの話をします。
近場で興味がある方は是非ご参加ください！！
PostgreSQL Advent Calendar 2013もついに最終週です。
明日はいつも一風変わったネタを紹介してくれる
nuko_yokohamaさんです！
どんなネタが飛び出すか楽しみですね！！
それでは引き続き、
PostgreSQL Advent Calendar 2013をお楽しみください。
