PostgreSQL で 集約関数を作成する

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

group_namefood_name
fruitsorange
fruitsapple
fruitspears
fishsardines
fishtuna
fishbream

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

group_namefood_name
fruitsorange
apple
pears
fishsardines
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_namefood
fruitsapple
orange
pears
fishbream
sardines
tuna

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

Google サイト内検索

Amazonアソシエイト