miau's blog?

カンマ結合を行う集約関数(PostgreSQL)

SQL をで行を横展開 - miau's blog?

このアイテム、blog の中でも参照率が高いやつなんですが。
・取得する列の数が限定されているので、本当の関連テーブルっぽいのには使いにくい
・sub_id みたいなのを持たないテーブルには使えない
・なんだかんだで、書くのが面倒
という欠点があります。

ただ単に見やすく表示したいだけであれば、PostgreSQL でもっと楽にできる方法もありますよ、って話。(多少難あり)




PostgreSQL では、集約関数を自分で定義できたりします。
これを作ってしまうと、

SELECT id, SUM(point) FROM Points GROUP BY id

みたいなノリで「カンマで文字列を結合する」集約処理

SELECT id, comma_join(point_name) FROM Points GROUP BY id

みたいなことができちゃいます。

では早速。

CREATE FUNCTION text_comma_join(text, text) RETURNS text AS '
SELECT $1 || '','' || $2
' LANGUAGE SQL;

CREATE FUNCTION skip_initial(text) RETURNS text AS '
SELECT substr($1, 2)
' LANGUAGE SQL;

CREATE AGGREGATE comma_join (
BASETYPE = text,
SFUNC = text_comma_join,
STYPE = text,
FINALFUNC = skip_initial,
INITCOND = ''
);

こんな感じで定義。

すると、

# select * from customerfamily;
cust_id | family_id | name
---------+-----------+--------
1 | 1 | 波平
1 | 2 | フネ
1 | 3 | カツオ
1 | 4 | ワカメ
2 | 1 | マスオ
2 | 2 | サザエ
2 | 3 | タラオ
(7 rows)

こんなテーブルのデータを・・・

# select cust_id, comma_join(name) from customerfamily group by cust_id;
cust_id | comma_join
---------+-------------------------
1 | ワカメ,フネ,カツオ,波平
2 | マスオ,サザエ,タラオ
(2 rows)

こんな感じで取得できますよ、と。


で、とまぁこんな感じで一見便利そうで、「なんで標準でこういう関数用意してないの?」と思えるくらいなんですが・・・当然のように問題もありまして。結果を見るとわかるように、表示順を保証する方法がありません。
そもそも集約関数内で交換不可能な演算子を使うのはルール違反という気がします。

ということで、あまりお薦めできる方法ではないですけど・・・軽くデータをチェックしたいような場合には便利なので一応紹介でした。
posted at 21:49:17 on 2006-12-11 by miau - Category: General No Trackbacks - Permalink

TrackBack

このエントリにトラックバックはありません
現在トラックバックは受け付けていません。

Comments

ma wrote:

そもそも DB にやらせることじゃないよね。多分。

今「列を縦展開」みたいなことをやってる Function がある DB をチューニング中。
2006-12-13 13:44:28

miau wrote:

列を縦展開って・・・それは正規化しろって話ですよね。たぶん。

でも正規化したテーブルを扱いやすくするためには、横展開って有効な気がします。
横展開しない場合は、プログラム側で「カーソルを回しながら、id が前回と同じ場合は○○、異なる場合は○○」みたいな処理が必要なわけですよね?それはプログラミングの本質ではないではないような。
前回の方法は強引すぎますが、今回の集約関数を使った方法は理想に近い形じゃないですか?

今回は文字列を単純に結合してしまいましたが、型としてオブジェクトが使えるような場合、集計結果を set(集合)みたいなオブジェクトとして持ってやれば、論理的にもすっきりするのでは。
2006-12-15 00:16:38

ma wrote:

たしかにそうか。処理的に SUM となんら変わらんもんね。
2006-12-16 14:10:09

ma wrote:

MySQL だと GROUP_CONCAT() ってあるね。
2006-12-19 14:43:38

miau wrote:

お。本当ですね。これは便利そう。
PostgreSQL でも標準で備えてくれると嬉しいんですけど。
2006-12-20 07:54:47

抹茶 wrote:

ほしいと思っていた情報がそのままありました〜
助かったっす
2007-11-06 13:36:11

miau wrote:

あ、どうも。お役に立てたようでなによりです。
2007-11-30 00:20:05

大沢ちゃん wrote:

私もとっても助かりましたー!ありがとうございますーーーー!!!
2010-10-09 16:47:05

てろん wrote:

色々考え探し回った挙句見つけました。
めちゃめちゃ助かりました。
ありがたやーーー(-人-)
2011-09-21 17:21:29

Add Comments

現在コメントは受け付けていません。
お手数ですが、 こちら のコメント欄にでも記載していただければと思います。