こんにちは。日商エレクトロニクスの友近です。

今回は EC2 や PHP(PDO) からPostgreSQL(RDS)にリモートアクセスするまでの覚え書きです。

DB系は業務と関係なく個人的な趣味で勉強しています。これまで検証環境で MySQL を使ってきましたが、ウインドウ関数など方言の違いで使えないコマンドがあります。真面目にやるならやはり複数環境があった方がいいと思いました。今回 PostgreSQL 環境を用意しました。

RDS

無料枠の PostgreSQL を選びました。インスタンスは"t2.micro"を使いました。下記の情報をどこかにメモしておくと便利です。

DBインスタンス識別子
マスタユーザ
パスワード
データベース名
エンジン：PostgreSQL 9.6.2
エンドポイント

インスタンスが起動したら、セキュリティグループのインバウンドでポート番号5432が許可されていることを確認します。

接続元ホスト(クライアント)のインストール

接続元ホストとなる EC2 上に Postgres のクライアントをインストールします。RDSにリモート接続するには、接続元のホストにクライアントをセットアップする必要があります。MySQLでいう"mysqlclient"に相当するものです。

クライアントインストール

$ sudo yum localinstall -y https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-6-x86_64/pgdg-ami201503-96-9.6-2.noarch.rpm
$ sudo yum install -y postgresql96

RDSにリモートアクセス

先ほど控えておいたRDSの情報を元に、下記のコマンドでリモートアクセスできることを確認します。

$ psql -h {エンドポイント} -U {マスターユーザ} {データベース名}
Password for user {マスターユーザ}:{パスワード}

{データベース名}=>

SQLコマンドを試す

ウインドウ関数を試行してみます。勉強中の「SQL実践入門（技術評論社）」から7章の一部引用させて頂きます。

データベース名：demotron
ユーザ名：dxpj

購入明細テーブル作成

CREATE TABLE Receipts
(cust_id   CHAR(1) NOT NULL, 
 seq   INTEGER NOT NULL, 
 price   INTEGER NOT NULL, 
     PRIMARY KEY (cust_id, seq));

テーブル構造

demotron=> \d Receipts
      Table "public.receipts"
 Column  |     Type     | Modifiers
---------+--------------+-----------
 cust_id | character(1) | not null
 seq     | integer      | not null
 price   | integer      | not null
Indexes:
    "receipts_pkey" PRIMARY KEY, btree (cust_id, seq)

demotron=>

データの中身

データは予め INSERT しておきます。

demotron=> SELECT * FROM Receipts;
 cust_id | seq | price
---------+-----+-------
 A       |   1 |   500
 A       |   2 |  1000
 A       |   3 |   700
 B       |   5 |   100
 B       |   6 |  5000
 B       |   7 |   300
 B       |   9 |   200
 B       |  12 |  1000
 C       |  10 |   600
 C       |  20 |   100
 C       |  45 |   200
 C       |  70 |    50
 D       |   3 |  2000
(13 rows)

demotron=>

相関サブクエリでデータを取り出す

SELECT cust_id, seq, price
 FROM Receipts R1
 WHERE seq = (SELECT MIN(seq)
                FROM Receipts R2
               WHERE R1.cust_id = R2.cust_id);
    
 cust_id | seq | price
---------+-----+-------
 A       |   1 |   500
 B       |   5 |   100
 C       |  10 |   600
 D       |   3 |  2000
(4 rows)

ウインドウ関数でデータを取り出す

SELECT cust_id, seq, price
 FROM (SELECT cust_id, seq, price,
               ROW_NUMBER()
                 OVER (PARTITION BY cust_id
                           ORDER BY seq) AS row_seq
          FROM Receipts ) WORK
 WHERE WORK.row_seq = 1;

 cust_id | seq | price
---------+-----+-------
 A       |   1 |   500
 B       |   5 |   100
 C       |  10 |   600
 D       |   3 |  2000
(4 rows)

おまけ

PHP から PDO で PostgreSQL にアクセスする方法です。手軽なテスト用。Apache 等のWebサーバや PHP が設定されていることが前提です。

php-pgsql のインストール

$ sudo yum -y install php-pgsql

サンプルプログラム

subquery.php

<?php

try
{

$dsn='pgsql:{エンドポイント}';
$user='{ユーザ名}';
$password='{パスワード}';
$dbh=new PDO($dsn, $user, $password);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql='
SELECT R1.cust_id, R1.seq, R1.price
  FROM Receipts R1
         INNER JOIN
           (SELECT cust_id, MIN(seq) AS min_seq
              FROM Receipts
             GROUP BY cust_id) R2
    ON R1.cust_id = R2.cust_id
    AND R1.seq = R2.min_seq;
';
$stmt=$dbh->prepare($sql);
$stmt->execute();

$dbh=null;

while(true)
{
        $rec=$stmt->fetch(PDO::FETCH_ASSOC);
        if($rec==false)
        {
                break;
        }
}

}
catch (Exception $e)
{
        print 'データーベース接続エラー発生';
        exit();
}

?>

参考

PostgreSQLコマンドチートシート

PostgreSql コマンドの覚え書き

「SQL実践入門」著.ミック技術評論社（第一版）

当社ではソフトウェア関連取組みの一環として、クラウド型パフォーマンスモニタサービス「New Relic」を提供しています
詳しくはこちら