缶詰.log: 4月 2012

PostgreSQLで、ARRAY関数を使って複数レコードを一つのカラムにまとめて取得する方法です。
この日本語だけだと、正直一体何を言っているの分からないので、実際の業務に絡めてみました。

まず、CDを管理するシステムを運用していると仮定します。
そして、各CDは必ずどこかのレーベルから発売されています。
さらに、各CDには複数のジャンルがあります。
各CDと各ジャンルを紐付けるために、それぞれのIDを格納したテーブルを使用します。

ある日、営業にこんなことを言われました。

「全CDデータのCSV作ってよ。」
「カラムは、["CDのID", "CD名", "レーベル名", "ジャンル名"]でお願いね。」
「あ、各CDのジャンルは一行に纏めてね。とりあえず＆区切りとかでお願い。」

よくありますね。こういうの。
まぁこれくらいなら簡単に取得できそうです。
さっそく全データをSELECTだ！

*見やすいようにSQL文に改行を入れています

ぱっと見て分かりますが、「I am Man part2」が2レコードありますね。
これはジャンルが2つ設定されているためです。
ということは、各CDを1行のCSV行にするためには、何らかの方法で各CDの情報をマージする必要があります。
ジャンルを省いた状態でプログラムでデータを取得して、再度全レコードに対してジャンル情報を問い合わせたり・・・
極端に難しい訳ではなさそうですが、面倒くさいですよね。
余計なSQLの発行にもなるし・・・

そこで、PostgreSQLのARRAY関数の出番です。
SQLを以下のようの書き換えて実行してみます。

*見やすいようにSQL文に改行を入れています

postgres=# SELECT c.cd_id, c.cd_name, l.label_name,
                          ARRAY (
                                  SELECT genre_name
                                  FROM genre g, genre_reference gr
                                  WHERE c.cd_id = gr.cd_id AND g.genre_id = gr.genre_id
                          ) as genre_name
                  FROM cd c, label l
                  WHERE c.label_id = l.label_id
                  ORDER BY cd_id;

cd_id | cd_name | label_name | genre_name
-------+-----------------+------------+--------------
1 | I am Man part 1 | label_A | {Rock}
2 | I am Man part 2 | label_A | {Rock,Blues}
3 | hoge the CD | label_B | {Blues}

genre_nameが{}で囲まれて一つのレコードにまとめられました。
これがARRAY関数の機能です。
ジャンルに関連する部分を、SELECT内でARRAY関数を使ってサブクエリっぽくまとめてあげるだけで実現できます。
これで、各CD情報は完全に1行にまとめられました。
あとはプログラムで取得してCSVにするなり、vi等でゴニョゴニョするなり自由自在です。

ちなみにJavaだとjava.sql.Array型として取得できます。

java.sql.Array workCategories = this.resultSet.getArray("genre_name");
String[] categories = (String[]) workCategories.getArray();

参考

*******************************************************
DDL
*******************************************************

-- CDマスタ
CREATE TABLE cd( cd_id int, label_id int, cd_name varchar(64) );

-- レーベルマスタ
CREATE TABLE label( label_id int, label_name varchar(64) );

-- ジャンルマスタ
CREATE TABLE genre( genre_id int, genre_name varchar(64) );

-- CDとジャンルの関連を管理するテーブル
CREATE TABLE genre_reference ( genre_id int, cd_id int );

*******************************************************
テストデータ投入
*******************************************************
-- レーベルマスタのデータ
INSERT INTO label (label_id, label_name) VALUES(1, 'label_A');
INSERT INTO label (label_id, label_name) VALUES(2, 'label_B');

-- CDマスタのデータ
INSERT INTO cd (cd_id, label_id, cd_name) VALUES(1, 1, 'I am Man part 1');
INSERT INTO cd (cd_id, label_id, cd_name) VALUES(2, 1, 'I am Man part 2');
INSERT INTO cd (cd_id, label_id, cd_name) VALUES(3, 2, 'hoge the CD');

-- ジャンルのマスタ
INSERT INTO genre(genre_id, genre_name) VALUES(1, 'Rock');
INSERT INTO genre(genre_id, genre_name) VALUES(2, 'Blues');

-- CDとジャンルの紐付け
INSERT INTO genre_reference(genre_id, cd_id) VALUES(1,1);
INSERT INTO genre_reference(genre_id, cd_id) VALUES(1,2);
INSERT INTO genre_reference(genre_id, cd_id) VALUES(2,2);
INSERT INTO genre_reference(genre_id, cd_id) VALUES(2,3);
INSERT INTO genre_reference(genre_id, cd_id) VALUES(1,4);

*******************************************************
以下テーブル構成
*******************************************************
Table "public.cd"
Column | Type | Modifiers
----------+-----------------------+-----------
cd_id | integer |
label_id | integer |
cd_name | character varying(64) |

Table "public.label"
Column | Type | Modifiers
------------+-----------------------+-----------
label_id | integer |
label_name | character varying(64) |

Table "public.genre"
Column | Type | Modifiers
------------+-----------------------+-----------
genre_id | integer |
genre_name | character varying(64) |

Table "public.genre_reference"
Column | Type | Modifiers
----------+---------+-----------
genre_id | integer |
cd_id | integer |

缶詰.log

2012年4月25日水曜日

PostgreSQLで複数レコードを一つのカラムにまとめる方法