2013年12月21日土曜日

Webアプリケーションを作るときに知ってると嬉しいPostgreSQLの使い方

この記事は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をお楽しみください。
投稿者 時刻:
ラベル: , ,

0 件のコメント:

コメントを投稿

登録: コメントの投稿 (Atom)