Studio ODIN

PostgreSQL で 集約関数を作成する

次のようなテーブルが データベースにあるとします。

group_name	food_name
fruits	orange
fruits	apple
fruits	pears
fish	sardines
fish	tuna
fish	bream

画面に表示する場合は、 次のようにグループ化して表示したくなることが あるんじゃないかと思います。 私はそうです。

group_name	food_name
fruits	orange
	apple
	pears
fish	sardines
	tuna
	bream

そのためには、グループごとに件数を取得するなど 追加で処理を作成する必要があるのですが、 今回は集約関数を作って擬似的に対応してみます。

集約関数とは max(*) や count(*) のように 複数のレコードに対して処理を行なう関数です。

まず定義です。 CREATE AGGREGATE 文を使用します。
詳しくは、以下を参照してください。

PostgreSQL 8.0.2 文書 - CREATE AGGREGATE

こんな感じです。

CREATE AGGREGATE array_accum
(
    BASETYPE = anyelement
  , SFUNC = array_append
  , STYPE = anyarray
  , INITCOND = '{}'
);

テーブルは次のようになっています。

db=# select group_name, food_name from foods;

 group_name | food_name
------------+-----------
 fruits     | orange
 fruits     | apple
 fruits     | pears
 fish       | sardines
 fish       | tuna
 fish       | bream
(6 rows)

作成した集約関数 array_accum を使ってみます。

db=# select group_name
db-#      , array_accum(food_name) as food
db-# from foods
db-# group by group_name;

 group_name |        food
------------+-----------------------
 fruits     | {orange,apple,pears}
 fish       | {sardines,tuna,bream}
(2 rows)

これでは値の並びが悪いので 副問合せでテーブルをソートします。

db=# select group_name
db-#      , array_accum(food_name) as food
db-# from (select * from foods order by food_name) as foods
db-# group by group_name;

 group_name |        food
------------+-----------------------
 fruits     | {apple,orange,pears}
 fish       | {bream,sardines,tuna}
(2 rows)

文字列として返すために array_to_string を使って 配列を結合します。 array_to_string(配列, '区切り文字') です。

db=# select group_name
db-#      , array_to_string(array_accum(food_name), '/') as food
db-# from (select * from foods order by food_name) as foods
db-# group by group_name;

 group_name |        food
------------+---------------------
 fruits     | apple/orange/pears
 fish       | bream/sardines/tuna
(2 rows)

値を受けたプログラムで処理をすることもできますが 元が改行を含まない値の場合、 次のように改行で区切って返すようにすると扱い易いです。

select group_name
     , array_to_string(array_accum(food_name), chr(10)) as food
from (select * from foods order by food_name) as foods
group by group_name;

改行を <br> に置き換えると 次のようになります。

group_name	food
fruits	apple orange pears
fish	bream sardines tuna

複数行のデータを１行で返すこの方法は 色々な応用ができるので 覚えておくと役に立つと思います。

積ん読 2009/11/29

• 工人舎、デュアルディスプレイを搭載したモバイルノート「DZシリーズ」を発売 - GIGAZINE (2009/11/27)
• ZIP/RAR/LZH/ISOなどあらゆるファイルの解凍・圧縮が一発でできるようになるフリーソフト「Explzh」 - GIGAZINE (2009/11/28)
• HTML5が拓く新しいWebHTML5が拓く新しいWeb 1.Google編 2.モジラ編 3.Opera編 - @IT (2009/11/27)