無料スクリプト配布のPHP.TO   PHPの実用的なtips PHPマニュアル MySQLマニュアル Apacheマニュアル PostgreSQLマニュアル マニュアル検索    

F.17. intagg

intagg モジュールは整数型の集約子と列挙子を提供します。 その能力の上位集合を提供する組み込み関数が存在しますので、 intagg は現在使われません。 しかし、このモジュールは組み込み関数の互換ラッパとして今でもまだ提供されています。

F.17.1. 関数

集約子は、正確に提供する整数のみを含む整数型配列を生成する int_array_aggregate(integer) 集約関数です。 これは任意の配列型で同じことを行う array_agg のラッパです。

列挙子は、 setof integer を返す int_array_enum(integer[]) 関数です。 これは基本的に上記集約子の反対の操作を行います。 指定された整数型配列を行集合に拡張します。 これは任意の配列型で同じことを行う unnest のラッパです。

F.17.2. 使用例

多くのデータベースシステムは1対多のテーブルを持ちます。 こうしたテーブルは通常、以下のように2つのインデックス用のテーブルの間に存在します。

CREATE TABLE left (id INT PRIMARY KEY, ...);
CREATE TABLE right (id INT PRIMARY KEY, ...);
CREATE TABLE one_to_many(left INT REFERENCES left, right INT REFERENCES right);

通常以下のように使用されます。

SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right)
  WHERE one_to_many.left = 

item

;

これは、左辺のテーブル内にある項目に対応した、右辺のテーブル内のすべての項目を返します。 これはSQLで非常によく使用される式です。

さて、この方法論は one_to_many テーブル内に非常に多数の項目がある場合に扱いにくくなることがあり得ます。 しばしばこうした結合は、インデックススキャンと特定された左辺の項目に対応した右辺のテーブル内の項目をそれぞれ取り出すことになります。 非常に動的なシステムでは、できることは多くありません。 しかし、ほぼ静的なデータが一部にある場合、集約子を使用して要約テーブルを作成することができます。

CREATE TABLE summary AS
  SELECT left, int_array_aggregate(right) AS right
  FROM one_to_many
  GROUP BY left;

これは左辺項目毎に1行を持ち、右辺の項目の配列をもつテーブルを作成します。 さて、これは配列を使用する何らかの方法がないとかなり使い勝手が悪くなります。 これが配列列挙子が存在する理由です。 以下を行うことができます。

SELECT left, int_array_enum(right) FROM summary WHERE left = 

item

;

上の int_array_enum を使用した問い合わせは、以下と同じ結果を生成します。

SELECT left, right FROM one_to_many WHERE left = 

item

;

違いは、要約テーブルに対する問い合わせはテーブルから1行だけを取り出す必要があるのに対し、直接 one_to_many に問い合わせる場合はインデックススキャンと各項目に対し行を取り出さなければならないという点です。

あるシステムでは EXPLAIN を行うと8488というコストを持つ問い合わせが329というコストまで減少しました。 元の問い合わせは one_to_many テーブルを含む結合でしたが、以下のように置き換えられました。

SELECT right, count(right) FROM
  ( SELECT left, int_array_enum(right) AS right
    FROM summary JOIN (SELECT left FROM left_table WHERE left = 

item

) AS lefts
         ON (summary.left = lefts.left)
  ) AS list
  GROUP BY right
  ORDER BY count DESC;


powered by SEO.CUG.NET