PostgreSQLの配列を使って文字列を集約をする方法
1対N（OneToMany）の関係にある2つのエンティティについて、N個の文字列を集約する必要があって、方法を考えてみた。
次のような部署マスタと社員マスタを例にとって、説明します。
これに対して、次のような表を出すという要件。
| 部署 | 所属社員 |
|---|---|
| 営業1課 | 佐藤 山下 高山 |
| 営業2課 | 大野 池本 野崎 |
「2つのエンティティを JOIN すれば良いじゃん」という声もあるけど、単純に結合しちゃうと、結果セットから取り出すとき、もしくは表示時点でプログラムでごにょごにょやらないと、うまくいかない。所属社員の一覧を、改行区切りの文字列として、ひとつの変数に格納できれば、らくちん。要するに、DBから値を取得する時点で、表示するときの形まで持っていきたいという話。
ここの方法については賛否両論あるかもしれないけど、SQLはプログラムが最もシンプルで済むような結果が得られるように書くべきだと思ってます。
話が逸れたけど、じゃあ、それをどうやってやるか、という話ですよ。
実は以前にも同じような要件について考えたことがあって、そのときは文字列をSUMする関数を用意した。
SELECT d.department_name, sum(e.employee_name) FROM department d, employee e WHERE d.department_id=e.department_id GROUP BY d.department_name;
まあ、これでも要件は満たせるんだけど、上のような感じで集約関数を使ってしまうと、レコードの出現順に値を結合することになってしまうので、表示順を制御したい場合には、副問合せで ORDER BY して、それを結合してから GROUP BY するという、面倒くさいことになってしまう。今回のように単純なら良いけど、他にも 1対N の関係にあるエンティティを同じように集約して取得したい、となってくると、使い勝手がよろしくないんですYO。
そこで、今回考えたのが、配列を使った方法。
SELECT
department_name,
ARRAY_TO_STRING(
ARRAY(
SELECT employee_name FROM employee e
WHERE e.department_id=d.department_id
ORDER BY employee_name
), '\r\n') AS employee_name
FROM department d;
自己相関サブクエリで所属社員の一覧を取得して、それを一旦配列化する。んでもって、その配列を改行コード区切りの文字列に分解する、ということをしてます。これだと見ての通り、ORDER BY も気軽に書けるし、やたらめったら結合していくよりも、パフォーマンスも良好。
DBの配列って、ぶっちゃけ普通は使わない。配列型のカラムがあったら、それは多くの場合、正規化に失敗している。大体にして、配列型のカラムは検索のSQLを書くのが面倒くさい。それだったら、正規化して JOIN した方が良い。
でも、こういう風にSQL内部に隠蔽される形での使用だったら、問題ないでしょう、と。自分で書いておきながらなんですが、配列にこういう使い方もあるんだなあ、といったところ。
それにしても、こういう要件のときの一般的なパターンはどんな感じなんだろう。やっぱりプログラムでゴリゴリやるのが正攻法なのかなあ。「こういう風にやると良いよ」とか「こんな感じでやってます」っていうのがあったら、教えてエロい人！
お疲れさまでした。
Posted by dT by 23:30