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

F.36. tablefunc

tablefunc モジュールにはテーブル(つまり複数行)を返す各種関数があります。 これらの関数は、その独自の目的として、および、複数行を返すC関数の作成方法を示す例として、有用です。

F.36.1. 提供される関数

tablefunc モジュールにより提供される関数を 表F-28 に示します。

表 F-28. tablefunc の関数

関数 戻り値 説明
normal_rand(int numvals, float8 mean, float8 stddev) setof float8 正規分布乱数値の集合を生成します。
crosstab(text sql) setof record 行の名前と N 個の値列からなる "ピボット表" を生成します。 ここで N は呼出元の問い合わせで指定される行型で決定します。
crosstab N (text sql) setof table_crosstab_ N 行の名前と N 個の値列からなる "ピボット表" を生成します。 crosstab2 crosstab3 crosstab4 が定義されていますが、後述する手順で追加の crosstab N 関数を作成することが可能です。
crosstab(text source_sql, text category_sql) setof record 2番目の問い合わせで指定された値列を持つ "ピボット表" を生成します。
crosstab(text sql, int N) setof record

廃止予定の crosstab(text) です。 値列の数は呼び出す問い合わせで常に決まりますので、現在引数Nは無視されます。

connectby(text relname, text keyid_fld, text parent_keyid_fld [, text orderby_fld ], text start_with, int max_depth [, text branch_delim ]) setof record 階層ツリー構造表現を生成します。

F.36.1.1. normal_rand

normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8

normal_rand は正規乱数値の集合(ガウス分布)を生成します。

ここで numvals はこの関数が返す値の数です。 mean は正規分布の平均値、 stddev は正規分布値の標準偏差です。

例えば、以下の呼出しは、平均5、標準偏差3で1000個の値を要求します。

test=# SELECT * FROM normal_rand(1000, 5, 3);
     normal_rand
----------------------
     1.56556322244898
     9.10040991424657
     5.36957140345079
   -0.369151492880995
    0.283600703686639
       .
       .
       .
     4.82992125404908
     9.71308014517282
     2.49639286969028
(1000 rows)

F.36.1.2. crosstab(text)

crosstab(text sql)
crosstab(text sql, int N)

crosstab 関数は "ピボット" 表示を生成するために使用されます。 ここでは、データは下方向にではなくページ横方向に渡って列挙されます。 例えば、以下のようなデータがあるとします。

row1    val11
row1    val12
row1    val13
...
row2    val21
row2    val22
row2    val23
...

これを次のように表示したいとします。

row1    val11   val12   val13   ...
row2    val21   val22   val23   ...
...

crosstab 関数は、最初のような書式を持つ生データを生成するSQL問い合わせとなるテキストパラメータを取り、2番目のような書式を持つテーブルを生成します。

sql パラメータは元となるデータ集合を生成するSQL文です。 この文は row_name 列を1つ、 category 列を1つ、 value 列を1つ返さなければなりません。 N は廃れたパラメータであり、指定されたとしても無視されます。 (これまでは、これは出力値列の数と一致する必要がありました。しかし、現在これは呼び出し元の問い合わせにより決まります。)

例:指定したSQLは以下のような集合を生成しても構いません。

 row_name    cat    value
----------+-------+-------
  row1      cat1    val1
  row1      cat2    val2
  row1      cat3    val3
  row1      cat4    val4
  row2      cat1    val5
  row2      cat2    val6
  row2      cat3    val7
  row2      cat4    val8

crosstab 関数は setof record を返すものとして宣言されています。 このため、出力列の実際の名前と型を呼び出し元の SELECT 文の FROM 内で宣言しなければなりません。 以下に例を示します。

SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);

この例は以下のような集合を生成します。

           <== value  columns  ==>
 row_name   category_1   category_2
----------+------------+------------
  row1        val1         val2
  row2        val5         val6

FROM 句は出力を1つの row_name 列(SQL問い合わせの最初の結果列と同一データ型)と続くN個の value 列(SQL問い合わせの3番目の結果列とすべて同じデータ型)を持つものとして定義しなければなりません。 必要なだけの個数の値列を出力するように設定することができます。 出力列の名前は使用者に任されています。

crosstab 関数は、同じ row_name 値を持つ入力行の各連続的なグループに対して、1つの出力行を生成します。 左から右へこれらの行の value フィールドで出力 value 列を埋めていきます。 もしグループ内の行が存在する出力 value 列より少なければ、余った出力列はNULLになります。 もし行が多ければ、余った入力行は無視されます。

実際のところ、入力行の順序が適切になるように、つまり、同じ row_name を持つ値がまとまり、行内で正しく順序付けられるように、SQL問い合わせは常に ORDER BY 1,2 を指定しなければなりません。 crosstab 自体が問い合わせ結果の2番目の列に注意を払わないことに注意してください。 これは順序付けのため、3番目の列の値がページに渡って現れる順序を制御するためだけに存在します。

以下に複雑な例を示します。

CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');

SELECT *
FROM crosstab(
  'select rowid, attribute, value
   from ct
   where attribute = ''att2'' or attribute = ''att3''
   order by 1,2')
AS ct(row_name text, category_1 text, category_2 text, category_3 text);

 row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
 test1    | val2       | val3       |
 test2    | val6       | val7       |
(2 rows)

必要な出力行型をその定義に反映した独自のcrosstab関数を構築することで、常に出力列を定義するための FROM 句を書く必要性をなくすことができます。 これは次節で説明します。 他にも必要な FROM 句をビュー定義に埋め込むことでも実現可能です。

F.36.1.3. crosstab N (text)

crosstab

N

(text sql)

crosstab N 関数は、呼び出し元の SELECT 問い合わせで列名と型を書き出す必要性をなくすことができるように、一般的な crosstab 関数に対する独自のラッパを構築する方法の例です。 tablefunc モジュールには、次のように出力行型が定義された crosstab2 crosstab3 crosstab4 が含まれています。

CREATE TYPE tablefunc_crosstab_N AS (
    row_name TEXT,
    category_1 TEXT,
    category_2 TEXT,
        .
        .
        .
    category_N TEXT
);

このように、入力問い合わせが text 型の row_name 列と value 列を生成し、かつ、2、3、または4個の出力値列を持つ場合、これらの関数を直接使用することができます。 この他の点はすべて、上述の一般的な crosstab 関数で説明した通りの動作をします。

例えば、上で挙げた例は下のように動作します。

SELECT *
FROM crosstab3(
  'select rowid, attribute, value
   from ct
   where attribute = ''att2'' or attribute = ''att3''
   order by 1,2');

これらの関数はほぼ説明を目的として提供されたものです。 背後の crosstab() 関数に基いた独自の戻り型と関数を作成することができます 独自のcrosstab関数を構築する方法は2つあります。

  • contrib/tablefunc/tablefunc--1.0.sql の例と同様にして、必要な出力列を記述する複合型を作成します。 そして、 text 型のパラメータを1つ取り、 setof your_type_name を返す一意な名前の関数を、同じ背後の crosstab C関数をリンクさせて定義します。 例えば、元データが行名として text 型を、値としてFLOAT8を生成し、5つの値列を希望する場合、以下のようになります。

    CREATE TYPE my_crosstab_float8_5_cols AS (
        my_row_name text,
        my_category_1 float8,
        my_category_2 float8,
        my_category_3 float8,
        my_category_4 float8,
        my_category_5 float8
    );
    
    CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(text)
        RETURNS setof my_crosstab_float8_5_cols
        AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT;

  • 暗黙的に戻り値の型を定義する場合は OUT パラメータを使用してください。 同じ例を以下のように書くこともできます。

    CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(
        IN text,
        OUT my_row_name text,
        OUT my_category_1 float8,
        OUT my_category_2 float8,
        OUT my_category_3 float8,
        OUT my_category_4 float8,
        OUT my_category_5 float8)
      RETURNS setof record
      AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT;

F.36.1.4. crosstab(text, text)

crosstab(text source_sql, text category_sql)

単一パラメータの crosstab 構文の大きな制限は、各値を最初の利用可能な列に挿入して、すべての値をグループのように扱う点です。 値列を特定のデータカテゴリに対応させ、グループの一部はカテゴリの一部のデータを持たない可能性がある場合は、うまく動作しません。 2パラメータを取る crosstab 構文は、出力列に対応するカテゴリのリストを明示的に提供することで、こうした状況を扱います。

source_sql は元となるデータ集合を生成するSQL文です。 このSQL文は row_name 列を1つ category 列を1つ、 value 列を1つ返さなければなりません。 また1つ以上の "追加" の列を持つこともできます。 row_name 列が先頭でなければなりません。 category value 列は、この順番で最後の2列でなければなりません。 row_name category との間の列はすべて "追加" の列とみなされます。 "追加" の列は同じ row_name 値を持つ行すべてで同一であるということが前提です。

例えば、 source_sql は以下のような集合を生成しなければなりません。

SELECT row_name, extra_col, cat, value FROM foo ORDER BY 1;

 row_name    extra_col   cat    value
----------+------------+-----+---------
  row1         extra1    cat1    val1
  row1         extra1    cat2    val2
  row1         extra1    cat4    val4
  row2         extra2    cat1    val5
  row2         extra2    cat2    val6
  row2         extra2    cat3    val7
  row2         extra2    cat4    val8

category_sql はカテゴリの集合を生成するSQL文でなければなりません。 このSQL文は1つの列のみを返さなければなりません。 また、少なくとも1つの結果行を生成しなければならず、さもないと、エラーになります。 さらに重複するカテゴリを生成してはなりません。 さもないとエラーとなります。 category_sql は以下のようなものになります。

SELECT DISTINCT cat FROM foo ORDER BY 1;
    cat
  -------
    cat1
    cat2
    cat3
    cat4

crosstab 関数は setof record を返すものとして宣言されていますので、出力列の実際の名前と型を、以下の例のように、呼出元の SELECT FROM 句で定義しなければなりません。

SELECT * FROM crosstab('...', '...')
    AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 text);

これは以下のような集合を生成します。

                  <==  value  columns   ==>
row_name   extra   cat1   cat2   cat3   cat4
---------+-------+------+------+------+------
  row1     extra1  val1   val2          val4
  row2     extra2  val5   val6   val7   val8

FROM 句は、出力列の適切な個数、およびその適切なデータ型を定義しなければなりません。 source_sql 問い合わせ結果に N 個の列がある場合、最初の N -2は最初の N -2出力列と一致しなければなりません。 残りの出力列は source_sql 問い合わせ結果の最後の列の型を持たなければならず、かつ、 category_sql 問い合わせ結果内の行と同じ個数でなければなりません。

crosstab 関数は、同一 row_name 値を持つ入力行の連続したグループ毎に1つの出力行を生成します。 row_name 出力列と任意の "追加" 列はグループの最初の行からコピーされます。 value 出力列は、 category 値と一致する行の value で埋められます。 行の category category_sql 問い合わせの出力とまったく一致しなかった場合、その value は無視されます。 グループの入力行内にまったくカテゴリに一致する出力列が存在しない場合、NULLで埋められます。

実際は、同じ row_name を持つ値をまとめられるように、 source_sql 問い合わせでは常に ORDER BY 1 を指定すべきです。 しかし、グループ内のカテゴリの順序は重要ではありません。 また、 category_sql 問い合わせの出力順序が指定された出力列の順序と一致することを確実にすることが重要です。

以下に複雑な例を2つ示します。

create table sales(year int, month int, qty int);
insert into sales values(2007, 1, 1000);
insert into sales values(2007, 2, 1500);
insert into sales values(2007, 7, 500);
insert into sales values(2007, 11, 1500);
insert into sales values(2007, 12, 2000);
insert into sales values(2008, 1, 1000);

select * from crosstab(
  'select year, month, qty from sales order by 1',
  'select m from generate_series(1,12) m'
) as (
  year int,
  "Jan" int,
  "Feb" int,
  "Mar" int,
  "Apr" int,
  "May" int,
  "Jun" int,
  "Jul" int,
  "Aug" int,
  "Sep" int,
  "Oct" int,
  "Nov" int,
  "Dec" int
);
 year | Jan  | Feb  | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov  | Dec
------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------
 2007 | 1000 | 1500 |     |     |     |     | 500 |     |     |     | 1500 | 2000
 2008 | 1000 |      |     |     |     |     |     |     |     |     |      |
(2 rows)

CREATE TABLE cth(rowid text, rowdt timestamp, attribute text, val text);
INSERT INTO cth VALUES('test1','01 March 2003','temperature','42');
INSERT INTO cth VALUES('test1','01 March 2003','test_result','PASS');
INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987');
INSERT INTO cth VALUES('test2','02 March 2003','temperature','53');
INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL');
INSERT INTO cth VALUES('test2','02 March 2003','test_startdate','01 March 2003');
INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234');

SELECT * FROM crosstab
(
  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
  'SELECT DISTINCT attribute FROM cth ORDER BY 1'
)
AS
(
       rowid text,
       rowdt timestamp,
       temperature int4,
       test_result text,
       test_startdate timestamp,
       volts float8
);
 rowid |          rowdt           | temperature | test_result |      test_startdate      | volts
-------+--------------------------+-------------+-------------+--------------------------+--------
 test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          | 2.6987
 test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 | 3.1234
(2 rows)

各問い合わせで結果列の名前と型を記述する必要性をなくすために、事前定義した関数を作成することができます。 前節の例を参照してください。 この crosstab 構文用の背後のC関数は crosstab_hash という名前です。

F.36.1.5. connectby

connectby(text relname, text keyid_fld, text parent_keyid_fld
          [, text orderby_fld ], text start_with, int max_depth
          [, text branch_delim ])

connectby 関数はテーブル内に格納された階層データ表示を生成します。 テーブルは行を一意に識別するキーフィールドと各行の親(もしあれば)を参照する親キーフィールドを持たなければなりません。 connectby は任意の行から辿った部分ツリーを表示することができます。

表F-29 ではパラメータを解説します。

表 F-29. connectby パラメータ

パラメータ 説明
relname 元となるリレーション名
keyid_fld キーフィールドの名前
parent_keyid_fld 親のキーフィールドの名前
orderby_fld 兄弟の順序付け用のフィールド名(省略可能)
start_with 開始行のキー値
max_depth 辿る深さに対する制限。無制限の場合はゼロ
branch_delim キーと分岐出力で区切る文字列(省略可能)

キーおよび親キーフィールドは任意のデータ型を取ることができますが、これらは同じデータ型でなければなりません。 キーフィールドのデータ型に関係なく、 start_with はテキスト文字列として入力されなければならないことに注意してください。

connectby 関数は setof record を返すものとして宣言されていますので、以下の例のように、出力列の実際の名前と型を呼出し元の SELECT 文の FROM 句で定義しなければなりません。

SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
    AS t(keyid text, parent_keyid text, level int, branch text, pos int);

先頭から2つの出力列は、現在の行のキーおよび親行のキーとして使用されます。 これらはテーブルのキーフィールドのデータ型と一致する必要があります。 3番目の出力列はツリーの深さであり、 integer 型である必要があります。 branch_delim パラメータが与えられた場合、次の出力列は分岐表示であり、 text 型である必要があります。 最後に、 orderby_fld パラメータが与えられた場合、最後の出力列は連番であり、 integer 型である必要があります。

"分岐" 出力列は現在の行まで達するために取られるキーの経路を示します。 キーは指定された branch_delim 文字列で区切られます。 分岐表示が不要ならば、 branch_delim パラメータと出力列リスト内の分岐列を省略してください。

同じ親を持つ兄弟の順序が重要な場合、どのフィールドで兄弟の順序付けを行うかを指定する orderby_fld パラメータを含めてください。 このフィールドは任意のソート可能なデータ型を取ることができます。 orderby_fld が指定された場合のみ、出力列リストには、最終整数型連番列を含めなければなりません。

テーブルおよびフィールド名を表すパラメータはそのまま connectby が内部的に生成するSQL問い合わせにコピーされます。 したがって、大文字小文字が混在した名前または特殊文字を含む名前の場合は二重引用符で括ってください。 またテーブル名をスキーマで修飾する必要があるかもしれません。

大規模なテーブルでは、親キーフィールド上にインデックスがないと性能が劣化します。

branch_delim 文字列がキー値内にまったく出現しないことが重要です。 さもないと、 connectby は無限再帰エラーを間違って報告するかもしれません。 branch_delim が提供されていない場合、再帰を検知するためにデフォルト値 ~ が使用されます。

以下に例を示します。

CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);

INSERT INTO connectby_tree VALUES('row1',NULL, 0);
INSERT INTO connectby_tree VALUES('row2','row1', 0);
INSERT INTO connectby_tree VALUES('row3','row1', 0);
INSERT INTO connectby_tree VALUES('row4','row2', 1);
INSERT INTO connectby_tree VALUES('row5','row2', 0);
INSERT INTO connectby_tree VALUES('row6','row4', 0);
INSERT INTO connectby_tree VALUES('row7','row3', 0);
INSERT INTO connectby_tree VALUES('row8','row6', 0);
INSERT INTO connectby_tree VALUES('row9','row5', 0);

-- with branch, without orderby_fld (order of results is not guaranteed)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
 AS t(keyid text, parent_keyid text, level int, branch text);
 keyid | parent_keyid | level |       branch
-------+--------------+-------+---------------------
 row2  |              |     0 | row2
 row4  | row2         |     1 | row2~row4
 row6  | row4         |     2 | row2~row4~row6
 row8  | row6         |     3 | row2~row4~row6~row8
 row5  | row2         |     1 | row2~row5
 row9  | row5         |     2 | row2~row5~row9
(6 rows)

-- without branch, without orderby_fld (order of results is not guaranteed)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
 AS t(keyid text, parent_keyid text, level int);
 keyid | parent_keyid | level
-------+--------------+-------
 row2  |              |     0
 row4  | row2         |     1
 row6  | row4         |     2
 row8  | row6         |     3
 row5  | row2         |     1
 row9  | row5         |     2
(6 rows)

-- with branch, with orderby_fld (notice that row5 comes before row4)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
 AS t(keyid text, parent_keyid text, level int, branch text, pos int);
 keyid | parent_keyid | level |       branch        | pos
-------+--------------+-------+---------------------+-----
 row2  |              |     0 | row2                |   1
 row5  | row2         |     1 | row2~row5           |   2
 row9  | row5         |     2 | row2~row5~row9      |   3
 row4  | row2         |     1 | row2~row4           |   4
 row6  | row4         |     2 | row2~row4~row6      |   5
 row8  | row6         |     3 | row2~row4~row6~row8 |   6
(6 rows)

-- without branch, with orderby_fld (notice that row5 comes before row4)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)
 AS t(keyid text, parent_keyid text, level int, pos int);
 keyid | parent_keyid | level | pos
-------+--------------+-------+-----
 row2  |              |     0 |   1
 row5  | row2         |     1 |   2
 row9  | row5         |     2 |   3
 row4  | row2         |     1 |   4
 row6  | row4         |     2 |   5
 row8  | row6         |     3 |   6
(6 rows)

F.36.2. 作者

Joe Conway


powered by SEO.CUG.NET