実は以前にも同じような要件について考えたことがあって、そのときは文字列を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内部に隠蔽される形での使用だったら、問題ないでしょう、と。自分で書いておきながらなんですが、配列にこういう使い方もあるんだなあ、といったところ。

それにしても、こういう要件のときの一般的なパターンはどんな感じなんだろう。やっぱりプログラムでゴリゴリやるのが正攻法なのかなあ。「こういう風にやると良いよ」とか「こんな感じでやってます」っていうのがあったら、教えてエロい人！

お疲れさまでした。