PREPARATION
OSS-DB入門
第6回 SQL基礎 II
前回のSQL基礎Iでは、INSERT/SELECT/UPDATE/DELETEの使い方を中心に学びました。SQLを用いたデータ操作に関してはおよそ理解していただけたと思います。今回のSQL基礎IIでは、テーブル結合やビュー、ストアドプロシージャといったもう少し踏み込んだ機能を見ていきます。
この章だけでは紹介しきれないほど多くの便利な機能がありますが、ここでは前回までに作成したテーブルをもとに基本的な機能や動作の紹介に重点をおいて解説していきたいと思います。より詳しい機能に挑戦したい方はリファレンスマニュアルを参考にして下さい。
この章だけでは紹介しきれないほど多くの便利な機能がありますが、ここでは前回までに作成したテーブルをもとに基本的な機能や動作の紹介に重点をおいて解説していきたいと思います。より詳しい機能に挑戦したい方はリファレンスマニュアルを参考にして下さい。
1．テーブル結合
リレーショナルデータベースは、テーブル（表）とテーブル（表）を結びつけてデータを取り出すことができます。複数のテーブル（表）を結びつけることを結合といいます。結合を行うにはJOIN句で結合したい列を指定します。指定された列の値を比較して、同じ値のデータを結合します。
PostgreSQLは、クロス結合（CROSS JOIN）、内部結合（INNER JOIN）、外部結合（OUTER JOIN）をサポートしています。
クロス結合（CROSS JOIN）
- クロス結合は、テーブルt1の1行ごとに、テーブルt2の全行を結合して表示します。クロス結合で得られる結果の行数は、テーブルt1とテーブルt2の行数の積となります。
内部結合（INNER JOIN）
- 内部結合は、もっとも一般的な結合です。複数のテーブルから一致する要素がある行のみを結合して表示します。PostgreSQLは、NATURAL INNER JOIN 句、INNER JOIN句、およびINNER JOIN USING句が使用できます。
外部結合（OUTER JOIN）
- 外部結合は、複数のテーブルから一致する要素の有無にかかわらず結合して表示します。PostgreSQLは、LEFT OUTER JOIN句、RIGHT OUTER JOIN句、およびFULL OUTER JOIN句が使用できます。
それでは、前回のSQL基礎Iで使用したデータをもとに実際に幾つかの例を見ていきましょう。
PostgreSQLは、クロス結合（CROSS JOIN）、内部結合（INNER JOIN）、外部結合（OUTER JOIN）をサポートしています。
クロス結合（CROSS JOIN）
- クロス結合は、テーブルt1の1行ごとに、テーブルt2の全行を結合して表示します。クロス結合で得られる結果の行数は、テーブルt1とテーブルt2の行数の積となります。
内部結合（INNER JOIN）
- 内部結合は、もっとも一般的な結合です。複数のテーブルから一致する要素がある行のみを結合して表示します。PostgreSQLは、NATURAL INNER JOIN 句、INNER JOIN句、およびINNER JOIN USING句が使用できます。
外部結合（OUTER JOIN）
- 外部結合は、複数のテーブルから一致する要素の有無にかかわらず結合して表示します。PostgreSQLは、LEFT OUTER JOIN句、RIGHT OUTER JOIN句、およびFULL OUTER JOIN句が使用できます。
それでは、前回のSQL基礎Iで使用したデータをもとに実際に幾つかの例を見ていきましょう。
1-1．前準備
テーブル結合を紹介するに前に、注文データが存在しない場合の結果を確認する為に新しく商品レコードを追加しておきます。
以下のSQLを実行して商品テーブルにバナナを追加してください。
INSERT INTO products (name, price, etc) VALUES ('バナナ', 250, 'フィリピン産');
テーブル結合を紹介するに前に、注文データが存在しない場合の結果を確認する為に新しく商品レコードを追加しておきます。
以下のSQLを実行して商品テーブルにバナナを追加してください。
INSERT INTO products (name, price, etc) VALUES ('バナナ', 250, 'フィリピン産');
1-2．外部結合（OUTER JOIN）
外部結合（OUTER JOIN）の場合は、LEFT OUTER JOIN句の左側にあるテーブルproductsの行はすべて表示されます。
結合条件式（この場合は、p.name = o.name）を満たさない行についてはordersの値はNULLとなります。
それでは実際にどのような結果が返ってくるのかを以下のSQLを実行して確認してみます。
このSQLでは商品テーブルと注文テーブルを結合するだけでなく、商品テーブルの単価と注文テーブルの注文数の積も表示しています。
SELECT p.name, o.arrival_date, o.order_num, p.price, (o.order_num * p.price) total
FROM products p LEFT OUTER JOIN orders o ON (p.name = o.name) ORDER BY o.arrival_date;
外部結合（OUTER JOIN）の場合は、LEFT OUTER JOIN句の左側にあるテーブルproductsの行はすべて表示されます。
結合条件式（この場合は、p.name = o.name）を満たさない行についてはordersの値はNULLとなります。
それでは実際にどのような結果が返ってくるのかを以下のSQLを実行して確認してみます。
このSQLでは商品テーブルと注文テーブルを結合するだけでなく、商品テーブルの単価と注文テーブルの注文数の積も表示しています。
SELECT p.name, o.arrival_date, o.order_num, p.price, (o.order_num * p.price) total
FROM products p LEFT OUTER JOIN orders o ON (p.name = o.name) ORDER BY o.arrival_date;
各商品の日別の売り上げが確認できます。
外部結合（OUTER JOIN）のLEFT OUTER JOIN句を使用しているため注文が1件もないバナナの場合は値にはNULLが入ります。
外部結合（OUTER JOIN）のLEFT OUTER JOIN句を使用しているため注文が1件もないバナナの場合は値にはNULLが入ります。
1-3．内部結合（INNER JOIN）
内部結合（INNER JOIN）は、複数のテーブルから一致する要素がある行のみを結合して表示します。
以下のSQLを実行してみてください。
SELECT p.name, o.arrival_date, o.order_num, p.price, (o.order_num * p.price) total
FROM products p INNER JOIN orders o ON (p.name = o.name) ORDER BY o.arrival_date;
内部結合（INNER JOIN）は、複数のテーブルから一致する要素がある行のみを結合して表示します。
以下のSQLを実行してみてください。
SELECT p.name, o.arrival_date, o.order_num, p.price, (o.order_num * p.price) total
FROM products p INNER JOIN orders o ON (p.name = o.name) ORDER BY o.arrival_date;
先ほどの外部結合（OUTER JOIN）ではバナナは表示されていましたが、内部結合（INNER JOIN）の場合は一致する要素があるのみ表示するため、バナナは表示されていません。
1-4．クロス結合（CROSS JOIN）
クロス結合はCROSS JOIN句の左側のテーブルの1行ごとに右側のテーブルの全行を結合して表示します。
SELECT p.name, o.arrival_date, o.order_num, p.price, (o.order_num * p.price) total
FROM products p CROSS JOIN orders o ORDER BY o.arrival_date;
クロス結合はCROSS JOIN句の左側のテーブルの1行ごとに右側のテーブルの全行を結合して表示します。
SELECT p.name, o.arrival_date, o.order_num, p.price, (o.order_num * p.price) total
FROM products p CROSS JOIN orders o ORDER BY o.arrival_date;
商品テーブルのレコード数×注文テーブルのレコード数となる2166行の結果が表示されます。
1-5．応用編
SQL基礎Iで紹介した集約関数を利用して各商品の8月の売り上げの合計を求めてみましょう。
SELECT p.name, EXTRACT(MONTH FROM arrival_date) arrival_month, SUM(o.order_num) * p.price month_total
FROM products p INNER JOIN orders o ON (p.name = o.name)
AND o.arrival_date BETWEEN '2012-08-01' AND '2012-08-31'
GROUP BY p.name, arrival_month;
SQL基礎Iで紹介した集約関数を利用して各商品の8月の売り上げの合計を求めてみましょう。
SELECT p.name, EXTRACT(MONTH FROM arrival_date) arrival_month, SUM(o.order_num) * p.price month_total
FROM products p INNER JOIN orders o ON (p.name = o.name)
AND o.arrival_date BETWEEN '2012-08-01' AND '2012-08-31'
GROUP BY p.name, arrival_month;
各商品の8月の売り上げの合計が確認できます。
2. ビュー（VIEW）の定義と削除
ビューとは定義したSELECT文の処理結果の集合をあたかも通常のテーブルのように扱うことができる機能です。ただし、更新対象として使用する際には通常のテーブルのように扱うことができません。使用頻度の高いSQLや複雑なSQL、見せたい範囲を限定したい場合といったケースで、それらをビューとして定義しておくような用途が考えられます。
PostgreSQLではCREATE VIEW文、DROP VIEW文でビューの定義、削除を行います。
ビューの定義
- CREATE VIEW文でビューを定義します。ビューは読み取り専用で、ビューに対する操作（INSERT、UPDATE、DELETE）はできません。
ビューの削除
- DROP VIEW文でビューを削除します。
それでは、さっそくビューを使用していきましょう。
2-1．ビューの定義
先ほどのテーブル結合の最後に実行した、「各商品の8月の売り上げの合計を算出するSQL」を使用してビューを定義しましょう。
以下のSQLを実行してください。ビューの名前はtotal_august_salesとしています。
CREATE VIEW total_august_sales AS
SELECT p.name, EXTRACT(MONTH FROM arrival_date) arrival_month, SUM(o.order_num) * p.price month_total
FROM products p INNER JOIN orders o ON (p.name = o.name)
AND o.arrival_date BETWEEN '2012-08-01' AND '2012-08-31'
GROUP BY p.name, arrival_month;
PostgreSQLではCREATE VIEW文、DROP VIEW文でビューの定義、削除を行います。
ビューの定義
- CREATE VIEW文でビューを定義します。ビューは読み取り専用で、ビューに対する操作（INSERT、UPDATE、DELETE）はできません。
ビューの削除
- DROP VIEW文でビューを削除します。
それでは、さっそくビューを使用していきましょう。
2-1．ビューの定義
先ほどのテーブル結合の最後に実行した、「各商品の8月の売り上げの合計を算出するSQL」を使用してビューを定義しましょう。
以下のSQLを実行してください。ビューの名前はtotal_august_salesとしています。
CREATE VIEW total_august_sales AS
SELECT p.name, EXTRACT(MONTH FROM arrival_date) arrival_month, SUM(o.order_num) * p.price month_total
FROM products p INNER JOIN orders o ON (p.name = o.name)
AND o.arrival_date BETWEEN '2012-08-01' AND '2012-08-31'
GROUP BY p.name, arrival_month;
2-2．ビューの参照
作成したビューの名前をSELECT文のFROM句に記述します。
SELECT * FROM total_august_sales;
作成したビューの名前をSELECT文のFROM句に記述します。
SELECT * FROM total_august_sales;
複雑なSQLを使用することなく「1-5.応用編」と同じ結果を得られました。
このように、ビューを使用することでシンプルで見やすいSQLになります。
2-3．ビューの削除
ビューの削除はDROP VIEW文で削除します。以下のSQLを実行してください。
DROP VIEW total_august_sales;
このように、ビューを使用することでシンプルで見やすいSQLになります。
2-3．ビューの削除
ビューの削除はDROP VIEW文で削除します。以下のSQLを実行してください。
DROP VIEW total_august_sales;
3．ストアドプロシージャ
ストアドプロシージャとは、SQLで記述した一連の処理をひとつのプログラムにまとめて、データベース管理システム(RDBMS）に保存したものです。複雑なSQLなどの呼び出しを、ひとつの処理単位にまとめて、簡単に呼び出すことができます。例えば、ひとつのプロシージャには複数のSQLが含まれていたり、一般的なプログラムにあるように繰り返しや条件分岐などの制御構造を持つこともできます。また、引数をとって処理をしたり、処理結果を返すこともできます。
PostgreSQLではストアドプロシージャのことをユーザー定義関数と呼んでいます。PostgreSQLのユーザー定義関数は複数の記述言語を使うことができ、独自の記述言語を使用することが多い商用RDBMSよりも選択肢が広いことが特徴です。標準で入っているものだけでも単純なSQLとC言語、PL/Tcl(Tcl言語)、 PL/Perl(Perl言語)、PL/pgSQLがサポートされています。PostgreSQLのPL/pgSQLは、OracleのPL/SQLと完全互換ではありませんが、PL/SQLと同じようにSQLに様々な拡張仕様を追加したプログラミング言語です。ここではこれらの中でも最もドキュメントが豊富で、多くのユーザーに支持されているPL/pgSQLについて紹介したいと思います。
まずはPL/pgSQLを利用するデータベースに対して、createlangコマンドを使ってPL/pgSQLを登録します。この操作はデータベースに対して一度だけ実行します。
データベースossdbにPL/pgSQLを登録する場合、次のようなコマンドを実行します。
$createlang plpgsql -d ossdb;
※createlangコマンドの解説については、以下のリファレンスマニュアルを参照して下さい。
http://www.postgresql.jp/document/9.1/html/app-createlang.html
次にCREATE FUNCTION文で関数を定義します。
構文は以下の通りです。
CREATE [OR REPLACE] FUNCTION 関数名（引数） RETURNS [SETOF] 戻り値 AS $$
関数の記述
$$ LANGUAGE 使用言語;
OR REPLACEを指定すると、既存の関数を上書きします。「戻り値」には、integerやtextなどのデータ型を指定できます、また、テーブル名を指定することで、そのテーブルの行と同じデータを戻り値とすることができます。複数の戻り値を取得する場合は、SETOFを指定します。「使用言語」には、関数を記述する言語を指定します。関数定義の詳しい内容については、リファレンスマニュアルを参考にして下さい。
3‐1．ユーザー定義関数の作成
例1
int型の引数に対して1を加算して返却するだけの関数get_count_numを作成します。
以下のSQLを実行してください。
CREATE FUNCTION get_count_num(num integer) RETURNS integer AS
$$
DECLARE
count_num integer;
BEGIN
count_num = num + 1;
RETURN count_num;
END;
$$
LANGUAGE plpgsql;
作成した関数get_count_numを実行します。以下のSQLを実行してください。
SELECT get_count_num(2);
※SELECT文でget_count_num関数を指定します。
SELECT get_count_num(2);
※SELECT文でget_count_num関数を指定します。
get_count_num関数にint型の引数に1を足した値が返却されます。
例2
指定した日の売り上げの合計を取得する関数を作成してみましょう。
日付型の引数を1つ取るget_sales_data関数を作成します。以下のSQLを実行してください。
CREATE FUNCTION get_sales_data(order_date date) RETURNS integer AS
$$
DECLARE
total_sales integer;
BEGIN
SELECT SUM(o.order_num * p.price) total_sales INTO total_sales
FROM products p INNER JOIN orders o ON (p.name = o.name)
AND o.arrival_date = order_date;
RETURN total_sales;
END;
$$
LANGUAGE plpgsql;
例2
指定した日の売り上げの合計を取得する関数を作成してみましょう。
日付型の引数を1つ取るget_sales_data関数を作成します。以下のSQLを実行してください。
CREATE FUNCTION get_sales_data(order_date date) RETURNS integer AS
$$
DECLARE
total_sales integer;
BEGIN
SELECT SUM(o.order_num * p.price) total_sales INTO total_sales
FROM products p INNER JOIN orders o ON (p.name = o.name)
AND o.arrival_date = order_date;
RETURN total_sales;
END;
$$
LANGUAGE plpgsql;
作成した関数get_sales_dataを実行します。以下のSQLを実行してください。
SELECT get_sales_data('2012-08-20');
SELECT get_sales_data('2012-08-25');
※SELECT文でget_sales_data関数を指定します。
SELECT get_sales_data('2012-08-20');
SELECT get_sales_data('2012-08-25');
※SELECT文でget_sales_data関数を指定します。
get_sales_data関数に日付型の引数を指定し、その日の売り上げの合計が返却されます
まとめ
SQL編ではSQL基礎I、SQL基礎IIと2回に渡って紹介してきました。いかがでしたでしょうか。
PostgreSQLは商用のRDBMSと比較しても遜色ないほど多くの機能を備えています。さらに理解を深める上でもリファレンスマニュアルなどを参考にして色々と挑戦していただくことをお勧めします。
次回はトランザクションについて紹介していきます。お楽しみに！
PostgreSQLは商用のRDBMSと比較しても遜色ないほど多くの機能を備えています。さらに理解を深める上でもリファレンスマニュアルなどを参考にして色々と挑戦していただくことをお勧めします。
次回はトランザクションについて紹介していきます。お楽しみに！
●SQL基礎IIで紹介したテーブル結合、ビュー（VIEW）定義と削除及びストアドプロシージャについては、以下のPostgreSQL 日本語ドキュメントのリファレンスも合わせてご覧ください。
1．第7章 問い合わせ
http://www.postgresql.jp/document/9.1/html/queries-table-expressions.html
2．第 3章 高度な諸機能
http://www.postgresql.jp/document/9.1/html/tutorial-views.html
3．CREATE VIEW
http://www.postgresql.jp/document/9.1/html/sql-createview.html
4．DROP VIEW
http://www.postgresql.jp/document/9.1/html/sql-dropview.html
5．第39章 PL/pgSQL-SQL手続き言語
http://www.postgresql.jp/document/9.1/html/plpgsql.html
執筆：
神谷 貴広 氏（OSS-DB Silver認定者）株式会社オークニー ジオソリューション事業部
PostGISを含めたFOSS4G(Free Open Source Software for Geospatial)ソフトウェアを活用して、地図、位置情報を組み合わせたアプリケーションの設計・開発に従事。一方で、クラウド、スマートフォン、ソーシャルメディアと言った新しい価値を世の中が求める中、位置情報、地図を絡めた新しいサービスを模索中。
アドバイザー：
森 亮 氏 株式会社オークニー代表取締役
2002年にオークニーを設立し、代表取締役に就任。マッピング・ GIS・LBS・ITS関連プロジェクトのビジネスコンサルティングを経て、現在は会社経営の傍ら、『入門Webマッピング』（Tyler Mitchell原著；オライリー・ジャパン発行）の翻訳、Open Source Geospatial財団日本支部の代表者を務めるなど、FOSS4G（オープンソース地理空間ソフトウェア）の普及活動に取り組んでいる。
監修：
松田 神一
LPI-Japan
Platinum Sponsors
© EDUCO All Rights Reserved.