★PostgreSQLカンファレンス2024 12月6日開催/チケット販売中★
他のバージョンの文書 16 | 15 | 14 | 13 | 12 | 11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

7.2. テーブル式

テーブル式はテーブルを計算するためのものです。 テーブル式にはFROM句が含まれており、その後ろにオプションとしてWHERE句、GROUP BY句、HAVING句を付けることができます。 単純なテーブル式は、単にディスク上のいわゆる基本テーブルと呼ばれるテーブルを参照するだけです。 しかし複雑な式では、様々な方法で基本テーブルを修正したり、結合させて使用することができます。

テーブル式のオプションWHERE句、GROUP BY句、およびHAVING句は、FROM句で派生したテーブル上に対して次々に変換を実行するパイプラインを指定します。 これらの変換によって仮想テーブルが1つ生成されます。 そしてこの仮想テーブルの行が選択リストに渡され、問い合わせの出力行が計算されます。

7.2.1. FROM

FROM句は、カンマで分けられたテーブル参照リストで与えられる1つ以上のテーブルから、1つのテーブルを派生します。

FROM table_reference [, table_reference [, ...]]

テーブル参照は、テーブル名(スキーマで修飾することもできます)、あるいは、副問い合わせ、JOINによる結合、これらの複雑な組み合わせなどの派生テーブルとすることができます。 FROM句に複数のテーブル参照がある場合、クロス結合されます(テーブルの行のデカルト積が形成されます。下記を参照)。 FROMリストの結果はWHERE句、GROUP BY句、およびHAVING句での変換対象となる中間的な仮想テーブルになり、最終的にはテーブル式全体の結果となります。

テーブル参照で、テーブルの継承階層の親テーブルの名前を指定すると、テーブル名の前にONLYキーワードがない場合は、テーブル参照はそのテーブルだけでなくその子テーブルに継承されたすべての行を生成します。 しかし、この参照は名前を指定したテーブルに現れる列のみを生成し、子テーブルで追加された列は無視されます。

テーブル名の前にONLYを記述する代わりに、テーブル名の後に*を記述して、子テーブルが含まれることを明示的に指定することができます。 子テーブルを検索するのが今は常にデフォルトの振る舞いですので、この文法を使う本当の理由はもうありません。 しかし、古いリリースとの互換性のためにサポートされています。

7.2.1.1. 結合テーブル

結合テーブルは、2つの(実または派生)テーブルから、指定した結合種類の規則に従って派生したテーブルです。 内部結合、外部結合、およびクロス結合が使用可能です。 テーブル結合の一般的な構文は次のとおりです

T1 join_type T2 [ join_condition ]

すべての結合は、互いに結び付けたり、あるいは入れ子にしたりすることができます。 T1T2のどちらか、あるいは両方が、結合テーブルになることがあります。 括弧でJOIN句を括ることで結合の順序を制御することができます。 括弧がない場合、JOIN句は左から右に入れ子にします。

結合の種類

クロス結合
T1 CROSS JOIN T2

T1およびT2からのすべての可能な行の組み合わせ(つまりデカルト積)に対し、結合されたテーブルはT1のすべての列の後にT2のすべての列が続く行を含みます。 テーブルがそれぞれN行とM行で構成されているとすると、結合されたテーブルの行数は N * M 行となります。

FROM T1 CROSS JOIN T2FROM T1 INNER JOIN T2 ON TRUE と同じです(下記を参照)。 また FROM T1, T2 とも同じです。

注記

3つ以上のテーブルが現れた場合、この後者の等価性は厳密には保たれてはいません。 なぜなら、JOINはカンマより強固に結合するためです。 例えば FROM T1 CROSS JOIN T2 INNER JOIN T3 ON conditionFROM T1, T2 INNER JOIN T3 ON condition と同じではありません。 なぜなら最初のケースではconditionT1を参照できますが、2番目ではできないからです。

限定的な結合
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2

INNEROUTERは省略可能です。 INNERがデフォルトとなります。 LEFTRIGHTFULLは外部結合を意味します。

結合条件は、ON句かUSING句で指定するか、またはNATURAL記述で暗黙的に指定します。 結合条件は、以下で詳しく説明するように、2つの元となるテーブルのどの行が一致するかを決めます。

限定的な結合には次のものがあります。

INNER JOIN(内部結合)

T1の各行R1に対して、T2において行R1との結合条件を満たしている各行が、結合されたテーブルに含まれます。

LEFT OUTER JOIN(左外部結合)

まず、内部結合が行われます。 その後、T2のどの行との結合条件も満たさないT1の各行については、T2の列をNULL値として結合行が追加されます。 したがって、連結されたテーブルは常にT1の行それぞれに少なくとも1つの行があります。

RIGHT OUTER JOIN(右外部結合)

まず、内部結合が行われます。 その後、T1のどの行の結合条件も満たさないT2の各行については、T1の列をNULL値として結合行が追加されます。 これは左結合の反対です。 結果のテーブルは、T2の行が常に入ります。

FULL OUTER JOIN(完全外部結合)

まず、内部結合が行われます。 その後、T2のどの行の結合条件も満たさないT1の各行については、T2の列をNULL値として結合行が追加されます。 さらに、T1のどの行でも結合条件を満たさないT2の各行に対して、T1の列をNULL値として結合行が追加されます。

ON句は最も汎用的な結合条件であり、WHERE句で使われるものと同じ論理値評価式となります。 ON式の評価が真となる場合、T1およびT2の対応する行が一致します。

USING句は、結合の両側で結合列に同じ名前を使っているという特別な状況の利点を活かすことができる省略形です。 それは、結合テーブルが共通で持つ列名をカンマで区切ったリストから、それぞれの列の等価性を結合条件として生成します。 例えば, T1T2USING (a, b)を使用して結合する場合は、ON T1.a = T2.a AND T1.b = T2.bという結合条件を生成します。

さらに、JOIN USINGの出力は、冗長列を抑制します。マッチした列は両方が同じ値を待つので両方を出力する必要がありません。 JOIN ONT1 からのすべての列と、それに続く T2 からのすべての列を生成します。 JOIN USINGは指定された列のペアのそれぞれについて1つの出力(結合リストでの指定順)、続いてT1の残りの列、その後にT2の残りの列を出力します。

最後に、NATURALUSINGの略記形式で、2つの入力テーブルの両方に含まれているすべての列名で構成されるUSINGリストを形成します。 USINGと同様、これらの列は出力テーブルに一度だけ現れます。 共通する列が存在しない場合、NATURAL JOINJOIN ... ON TRUEと同様に動作し、クロス積結合を生成します。

注記

USINGは、リストされている列のみ結合するのでリレーションの列の変更から適度に安全です。 NATURALは、USINGよりもかなり危険です。 いずれかのリレーションのスキーマ変更により新しくマッチする列名が作られると、結合にその新しい列も使われるようになってしまうからです。

まとめとして、 以下のテーブルt1

 num | name
-----+------
   1 | a
   2 | b
   3 | c

および、テーブルt2

 num | value
-----+-------
   1 | xxx
   3 | yyy
   5 | zzz

を想定すると、以下のように様々な結合に関する結果が得られます。

=> SELECT * FROM t1 CROSS JOIN t2;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   1 | a    |   3 | yyy
   1 | a    |   5 | zzz
   2 | b    |   1 | xxx
   2 | b    |   3 | yyy
   2 | b    |   5 | zzz
   3 | c    |   1 | xxx
   3 | c    |   3 | yyy
   3 | c    |   5 | zzz
(9 rows)

=> SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
(2 rows)

=> SELECT * FROM t1 INNER JOIN t2 USING (num);
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

=> SELECT * FROM t1 NATURAL INNER JOIN t2;
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
(3 rows)

=> SELECT * FROM t1 LEFT JOIN t2 USING (num);
 num | name | value
-----+------+-------
   1 | a    | xxx
   2 | b    |
   3 | c    | yyy
(3 rows)

=> SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
     |      |   5 | zzz
(3 rows)

=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
     |      |   5 | zzz
(4 rows)

ONで指定される結合条件には、結合に直接関係しない条件も含めることができます。 これは一部の問い合わせにおいては便利ですが、使用の際には注意が必要です。 例を示します。

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |     |
(3 rows)

WHERE句の中に制約を記述すると異なる結果になることに注意してください。

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
(1 row)

この理由はON句の中の制約は結合のに処理され、一方WHERE句の中の制約は結合のに処理されることによります。 これは内部結合には影響がありませんが、外部結合には大きな影響があります。

7.2.1.2. テーブルと列の別名

テーブルや複雑なテーブル参照に一時的な名前を付与し、問い合わせの以降の部分では、その名前を使ってテーブルや複雑なテーブル参照を利用することができます。 これをテーブルの別名と呼びます。

テーブルの別名を作成するには以下のようにします。

FROM table_reference AS alias

もしくは

FROM table_reference alias

ASキーワードはなくても構わないノイズです。 aliasは任意の識別子になります。

テーブルの別名の一般的な適用法は、長いテーブル名に短縮した識別子を割り当てて結合句を読みやすくすることです。 例を示します。

SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;

現在の問い合わせに関しては、別名がテーブル参照をする時の新しい名前になります。 問い合わせの他の場所で元々の名前でテーブルを参照することはできなくなります。 よって、次の例は有効ではありません。


SELECT * FROM my_table AS m WHERE my_table.a > 5;    -- 間違い

テーブルの別名は主に表記を簡単にするためにあります。 しかし次のように、1つのテーブルが自分自身と結合する場合は、必須となります。

SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;

さらに、テーブル参照が副問い合わせ(7.2.1.3を参照)の場合に別名が必要になります。

括弧は曖昧さをなくすために使われます。 次の例では、最初の文で2つ目のmy_tableのインスタンスにbという別名を付与し、一方、2つ目の文では結合結果に対して別名を付与しています。

SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...

次のような形式でテーブル別名を付けて、テーブル自身と同様にテーブルの列に一時的な名前を付けることができます。

FROM table_reference [AS] alias ( column1 [, column2 [, ...]] )

もし、実際のテーブルが持つ列よりも少ない数の列の別名が与えられる場合、残りの列は改名されません。 この構文は、自己結合あるいは副問い合わせで特に役立ちます。

別名がJOIN句の結果に適用される場合、別名はJOIN内で参照される元々の名を隠します。 以下に例を示します。

SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...

は有効なSQLですが、

SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c

は有効ではありません。 テーブルの別名aは、別名cの外側では参照することができません。

7.2.1.3. 副問い合わせ

派生テーブルを指定する副問い合わせは括弧で囲む必要があります。 また、(7.2.1.2にあるように)必ずテーブル別名が割り当てられている必要があります。 例を示します。

FROM (SELECT * FROM table1) AS alias_name

この例はFROM table1 AS alias_nameと同じです。 副問い合わせがグループ化や集約を含んでいる場合は、単純結合にまとめることはできない、より重要な例が発生します。

また、副問い合わせをVALUESリストとすることもできます。

FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
     AS names(first, last)

繰り返しますが、テーブルの別名が必要です。 VALUESリストの列に別名を付与することは省略することもできますが、付与することを勧めます。 7.7を参照してください。

7.2.1.4. テーブル関数

テーブル関数は、基本データ型(スカラ型)、もしくは複合データ型(テーブル行)からなる行の集合を生成する関数です。 これらは、問い合わせのFROM句内でテーブル、ビュー、副問い合わせのように使用されます。 テーブル関数から返される列は、テーブル、ビュー、副問い合わせの列と同様の手順で、SELECTJOINWHEREの中に含めることができます。

テーブル関数はROWS FROM構文を使用することで、それらの返却列を一緒に組み合わせることもできます。 このときの結果の行数は、行数が最大となる関数の結果と同じになり、少ない結果側は多い結果に合わせてnull値で埋められます。

function_call [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]
ROWS FROM( function_call [, ... ] ) [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]

WITH ORDINALITY句が指定されている場合、関数の結果の列にbigint型の列が追加されます。 この列は関数の結果の行を1から数えます。 (これは標準SQLの構文UNNEST ... WITH ORDINALITYの一般化です。) デフォルトでは、この序数(ordinal)の列はordinalityになります。しかし別の名前をAS句を使用して別名を割り当てることができます。

特別なテーブル関数UNNESTは、任意の数の配列パラメータで呼ぶことができます。 そしてそれは、対応する数の列を返し、あたかもUNNEST(9.19)が各パラメータ毎にROWS FROM構文を使用して結合されているかのようになります。

UNNEST( array_expression [, ... ] ) [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]

table_aliasが指定されない場合、テーブル名として関数名が使用されます。 ROWS FROM()の場合は最初の関数名が使用されます。

列に別名が提供されない場合、基本データ型を返す関数に対しては、列名も関数名と同じになります。 複合型を返す関数の場合は、結果の列は型の個々の属性の名前を取得します。

以下に数例示します。

CREATE TABLE foo (fooid int, foosubid int, fooname text);

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

SELECT * FROM foo
    WHERE foosubid IN (
                        SELECT foosubid
                        FROM getfoo(foo.fooid) z
                        WHERE z.fooid = foo.fooid
                      );

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);

SELECT * FROM vw_getfoo;

呼び出し方法に応じて異なる列集合を返すテーブル関数を定義することが役に立つ場合があります。 これをサポートするために、テーブル関数はOUTパラメータを持たないrecord擬似型を返すものと宣言することができます。 こうした関数を問い合わせで使用する場合、システムがその問い合わせをどのように解析し計画を作成すればよいのかが判断できるように、想定した行構造を問い合わせ自身内に指定しなければなりません。 この構文は次のようになります。

function_call [AS] alias (column_definition [, ... ])
function_call AS [alias] (column_definition [, ... ])
ROWS FROM( ... function_call AS (column_definition [, ... ]) [, ... ] )

ROWS FROM()構文を使用しない場合は、column_definitionのリストがFROM項目に取り付けることができる列の別名の代わりとなります。 列の定義内の名前は、列の別名として機能します。 ROWS FROM()構文を使用する場合は、column_definitionリストを個別に各メンバー関数に添付することができます。 またはメンバ関数が1つだけしかなく、かつWITH ORDINALITY句がない場合は、column_definitionリストを、ROWS FROM()の後ろの列別名のリストの場所に書くことができます。

以下の例を考えます。

SELECT *
    FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
      AS t1(proname name, prosrc text)
    WHERE proname LIKE 'bytea%';

dblink関数(dblinkモジュールの一部)は遠隔問い合わせを実行します。 これは任意の問い合わせで使用できるように、recordを返すものと宣言されています。 実際の列集合は、パーサが例えば*がどのように展開されるかを理解できるように、呼び出した問い合わせ内で指定されなければなりません。

ROWS FROMを使用した例:

SELECT *
FROM ROWS FROM
    (
        json_to_recordset('[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]')
            AS (a INTEGER, b TEXT),
        generate_series(1, 3)
    ) AS x (p, q, s)
ORDER BY p;

  p  |  q  | s
-----+-----+---
  40 | foo | 1
 100 | bar | 2
     |     | 3

2つの関数を結合して1つのFROMターゲットにします。 json_to_recordset()は、2つの列(最初のintegerと2番目のtext)を返すように指示されます。 generate_series()の結果は直接使用されます。 ORDER BY句では、列値が整数として並べ替えられます。

7.2.1.5. LATERAL 副問い合わせ

FROMに現れる副問い合わせの前にキーワードLATERALを置くことができます。 こうすると、副問い合わせは先行するFROM項目によって提供される列を参照できます。 (LATERALがない場合、それぞれの副問い合わせは個別に評価され、従ってその他のFROM項目を相互参照できません。)

FROMに現れるテーブル関数の前にもキーワードLATERALを置くことが可能ですが、関数に対してこのキーワードは省略可能です。 どんな場合であっても、関数の引数は先行する FROM項目により提供される列の参照を含むことができます。

LATERAL項目はFROMリストの最上層、またはJOIN木の中で表示することができます。 後者の場合、右側にあるJOINの左側のすべての項目を参照することが可能です。

FROM項目がLATERAL相互参照を含む場合の評価は以下のようになります。 相互参照される列(複数可)を提供するFROM項目のそれぞれの行、もしくは列を提供する複数のFROM項目の行一式に対し、LATERAL項目は列の行または複数行の一式の値により評価されます。 結果行(複数可)は通常のように演算された行と結合されます。 元となるテーブル(複数可)の列からそれぞれの行、または行の一式に対し反復されます。

LATERALの些細な例としては以下があげられます。

SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;

上記は以下のより伝統的なやり方と全く同じ結果をもたらしますので特別に有用ではありません。

SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;

LATERALは、結合される行を計算するために相互参照する列を必須とする場合、第一義的に有用です。 一般的な利用方法は、集合を返す関数に対して引数の値を提供することです。 例えば、vertices(polygon)が多角形の頂点の組みを返す関数だとして、以下のようにしてテーブルに格納されている多角形の互いに近接する頂点を特定できます。

SELECT p1.id, p2.id, v1, v2
FROM polygons p1, polygons p2,
     LATERAL vertices(p1.poly) v1,
     LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;

この問い合わせは以下のようにも書くことができます。

SELECT p1.id, p2.id, v1, v2
FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1,
     polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;

そのほか幾つかの同等の定式化が考えられます。 (既に言及したとおり、LATERALキーワードはこの例に於いて必要ではありませんが、明確に示すために使用しました。)

LATERAL副問い合わせはLEFT JOINの対象として、しばしば特に重宝します。 たとえLATERAL副問い合わせがそこから行を生成しない場合に於いても元となった行が結果に現れるからです。 たとえば、get_product_names()が製造者により生産された製品名を返すとして、テーブル内のいくつかの製造者が現在製品を製造していない場合、それらは何であるかを以下のようにして見つけることができます。

SELECT m.name
FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true
WHERE pname IS NULL;

7.2.2. WHERE

WHERE句の構文は以下の通りです。

WHERE search_condition

ここで、search_conditionにはboolean型を返すどのような評価式(4.2を参照)も指定できます。

FROM句の処理が終わった後、派生した仮想テーブルの各行は検索条件と照合されます。 条件の結果が真の場合、その行は出力されます。 そうでない(すなわち結果が偽またはNULLの)場合は、その行は捨てられます。 一般的に検索条件は、FROM句で生成されたテーブルの最低1列を参照します。 これは必須ではありませんが、そうしないとWHERE句はまったく意味がなくなります。

注記

内部結合の結合条件は、WHERE句でもJOIN句でも記述することができます。 例えば、以下のテーブル式は等価です。

FROM a, b WHERE a.id = b.id AND b.val > 5

および

FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5

また、以下でも同じです。

FROM a NATURAL JOIN b WHERE b.val > 5

どれを使うかは、主にスタイルの問題です。 FROM句のJOIN構文はSQL標準であるにも関わらず、おそらく他のSQLデータベース管理システムへの移植性では劣るでしょう。 外部結合については、FROM句以外に選択の余地はありません。 外部結合のON句またはUSING句は、WHERE条件とは等しくありません。 なぜなら、最終結果での行を除去すると同様に、(一致しない入力行に対する)行の追加となるからです。

WHERE句の例を以下に示します。

SELECT ... FROM fdt WHERE c1 > 5

SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)

SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)

SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)

SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100

SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)

fdtFROM句で派生されたテーブルです。 WHERE句の検索条件を満たさなかった行は、fdtから削除されます。 評価式としてのスカラ副問い合わせの使い方に注目してください。 他の問い合わせのように、副問い合わせは複雑なテーブル式を使うことができます。 副問い合わせの中でどのようにfdtが参照されるかにも注意してください。 c1fdt.c1のように修飾することは、c1が副問い合わせの入力テーブルから派生した列名でもある時にだけ必要です。 列名の修飾は、必須の場合ではなくても、明確にするために役立ちます。 この例は、外側の問い合わせの列名の有効範囲を、どのようにして内側の問い合わせまで拡張するかを示します。

7.2.3. GROUP BY句とHAVING

WHEREフィルタを通した後、派生された入力テーブルをGROUP BY句でグループ化し、また、HAVING句を使用して不要なグループを取り除くことができます。

SELECT select_list
    FROM ...
    [WHERE ...]
    GROUP BY grouping_column_reference [, grouping_column_reference]...

GROUP BY句は、列挙されたすべての列で同じ値を所有する行をまとめてグループ化するために使用されます。 列の列挙順は関係ありません。 これは共通する値を持つそれぞれの行の集合をグループ内のすべての行を代表する1つのグループ行にまとめるものです。 これは、出力の冗長度を排除したり、それぞれのグループに適用される集約計算を行うためのものです。 以下に例を示します。

=> SELECT * FROM test1;
 x | y
---+---
 a | 3
 c | 2
 b | 5
 a | 1
(4 rows)

=> SELECT x FROM test1 GROUP BY x;
 x
---
 a
 b
 c
(3 rows)

2番目の問い合わせでは、SELECT * FROM test1 GROUP BY xと書くことはできません。 各グループに関連付けられる列yの単一の値がないからです。 GROUP BYで指定した列はグループごとに単一の値を持つので、選択リストで参照することができます。

一般的に、テーブルがグループ化されている場合、GROUP BYでリストされていない列は集約式を除いて参照することはできません。 集約式の例は以下の通りです。

=> SELECT x, sum(y) FROM test1 GROUP BY x;
 x | sum
---+-----
 a |   4
 b |   5
 c |   2
(3 rows)

上記でsum() は、グループ全体について単一の値を計算する集約関数です。 使用可能な集約関数の詳細については、9.21を参照してください。

ヒント

集約式を使用しないグループ化は、列内の重複しない値の集合を効率良く計算します。 これはDISTINCT句(7.3.3を参照)の使用で同じように達成することができます。

別の例を示します。 これは各製品の総売上を計算します (全製品の総売上ではありません)。

SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
    FROM products p LEFT JOIN sales s USING (product_id)
    GROUP BY product_id, p.name, p.price;

この例では、product_id列、p.name列、p.price列は必ずGROUP BY句で指定する必要があります。 なぜなら、これらは問い合わせ選択リストの中で使われているからです(ただし、以下を参照)。 s.units列はGROUP BYで指定する必要はありません。 これは、製品ごとの売上計算の集約式(sum(...))の中だけで使われるためです。 この問い合わせは、各製品に対して製品の全販売に関する合計行が返されます。

productsテーブルが、例えば、product_idが主キーであるように設定されている場合、nameとprice列は製品ID(product_id)に関数依存しており、このため製品IDグループそれぞれに対してどのnameとpriceの値を返すかに関するあいまいさがありませんので、上の例ではproduct_idでグループ化することで十分です。

厳密なSQLでは、GROUP BYは、元となるテーブルの列によってのみグループ化できますが、PostgreSQLでは、選択リストの列によるグループ化もできるように拡張されています。 単純な列名の代わりに、評価式でグループ化することもできます。

GROUP BYを使ってグループ化されたテーブルで特定のグループのみ必要な場合、結果から不要なグループを除くのに、WHERE句のようにHAVING句を使うことができます。 構文は以下の通りです。

SELECT select_list FROM ... [WHERE ...] GROUP BY ... HAVING boolean_expression

HAVING句内の式は、グループ化された式とグループ化されてない式(この場合は集約関数が必要になります)の両方を参照することができます。

例を示します。

=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;
 x | sum
---+-----
 a |   4
 b |   5
(2 rows)

=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';
 x | sum
---+-----
 a |   4
 b |   5
(2 rows)

次に、より現実的な例を示します。

SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
    FROM products p LEFT JOIN sales s USING (product_id)
    WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
    GROUP BY product_id, p.name, p.price, p.cost
    HAVING sum(p.price * s.units) > 5000;

上の例で、WHERE句は、グループ化されていない列によって行を選択している(この式では最近の4週間の売上のみが真になります)のに対し、HAVING句は出力を売上高が5000を超えるグループに制限しています。 集約式が、問い合わせ内で常に同じである必要がないことに注意してください。

ある問い合わせが集約関数を含んでいるがGROUP BY句がない場合でも、グループ化は依然として行われます。 結果は単一グループ行(またはHAVINGで単一行が削除されれば、行が全くなくなるかもしれません)となります。 HAVING句を含んでいれば、集約関数呼び出しやGROUP BY句がまったく存在しなくても同じことが言えます。

7.2.4. GROUPING SETSCUBEROLLUP

上述のものよりも複雑なグループ化の操作は、グループ化セットの概念を用いることで可能です。 FROM句とWHERE句によって選択されたデータは、指定されたグループ化セットによってそれぞれグループ化され、単純なGROUP BY句と同じように集約計算され、その後結果が返されます。 例を示します。

=> SELECT * FROM items_sold;
 brand | size | sales
-------+------+-------
 Foo   | L    |  10
 Foo   | M    |  20
 Bar   | M    |  15
 Bar   | L    |  5
(4 rows)

=> SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());
 brand | size | sum
-------+------+-----
 Foo   |      |  30
 Bar   |      |  20
       | L    |  15
       | M    |  35
       |      |  50
(5 rows)

GROUPING SETSの各サブリストはゼロ個以上の列または式を指定することが出来ます。 そして、それが直接GROUP BY句で指定したのと同じように解釈されます。 空のグループ化セットは、全行が一つのグループにまで集約されることを意味します(何も入力行が存在しない場合でも出力されます)。 これは、上述したGROUP BY句がない集約関数の場合と同様です。

グループ化している列または式の参照は、その列が現われないグループ化セットの結果行ではNULL値に置き換えられます。 特定の出力行が、どのグループ化から生じたかを識別するには表 9.59を参照して下さい。

グループ化セットの中で一般的な2種類については、略記法での指定方法が提供されています。

ROLLUP ( e1, e2, e3, ... )

上の句は、式の指定されたリストと空のリストを含めたリストのすべてのプレフィックスを表します。 したがって、以下と同等です。

GROUPING SETS (
    ( e1, e2, e3, ... ),
    ...
    ( e1, e2 ),
    ( e1 ),
    ( )
)

これは一般に、階層データに対する分析のために使用されます。例えば、部署、部門、全社合計による総給与を出します。

CUBE ( e1, e2, ... )

上の句は、与えられたリストとその可能な部分集合(サブセット)のすべて(すなわち、べき集合)を表します。 したがって

CUBE ( a, b, c )

は以下と同等です。

GROUPING SETS (
    ( a, b, c ),
    ( a, b    ),
    ( a,    c ),
    ( a       ),
    (    b, c ),
    (    b    ),
    (       c ),
    (         )
)

CUBE句やROLLUP句の各要素は、個々の式、または括弧で囲まれた要素のサブリスト、どちらかに出来ます。 後者の場合には、サブリストは個々のグループ化セットを生成する目的において一つの単位として扱われます。 例えば

CUBE ( (a, b), (c, d) )

は以下と同等です。

GROUPING SETS (
    ( a, b, c, d ),
    ( a, b       ),
    (       c, d ),
    (            )
)

そして

ROLLUP ( a, (b, c), d )

は以下と同等です。

GROUPING SETS (
    ( a, b, c, d ),
    ( a, b, c    ),
    ( a          ),
    (            )
)

CUBEROLLUP構文は、GROUP BY句の中で直接使用、またはGROUPING SETS句の中で入れ子に出来ます。 GROUPING SETS句が別の内側に入れ子になっている場合、内側の句が外側の句に直接書かれている場合と効果は同じになります。

複数の集約項目がGROUP BY句一つで指定されている場合、グループ化セットの最終的なリストは、個々の項目の外積(クロス積)です。 例えば

GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e))

は以下と同等です。

GROUP BY GROUPING SETS (
    (a, b, c, d), (a, b, c, e),
    (a, b, d),    (a, b, e),
    (a, c, d),    (a, c, e),
    (a, d),       (a, e)
)

注記

(a, b)という構文は通常行コンストラクタとして式に認識されます。 GROUP BY句の中では、トップレベルの式の場合これは適用されず、(a, b)は上記のような式のリストとして解析されます。 何らかの理由で、グループ化式の中で行コンストラクタが必要になった場合は、ROW(a, b)を使用して下さい。

7.2.5. ウィンドウ関数処理

問い合わせがウィンドウ関数(3.59.224.2.8を参照)を含んでいれば、これらの関数はグループ化、集約およびHAVING条件検索が行われた後に評価されます。 つまり、問い合わせが何らかの集約、GROUP BYまたはHAVINGを使用していれば、ウィンドウ関数により見える行はFROM/WHEREでの本来のテーブル行ではなく、グループ行となります。

複数のウィンドウ関数が使用された場合、そのウィンドウ定義にある構文的に同等であるPARTITION BYおよびORDER BY句を持つすべてのウィンドウ関数は、データ全体に渡って単一の実行手順により評価されることが保証されています。 したがって、ORDER BYが一意に順序付けを決定しなくても同一の並べ替え順序付けを見ることができます。 しかし、異なるPARTITION BYまたはORDER BY仕様を持つ関数の評価については保証されません。 (このような場合、並べ替え手順がウィンドウ関数評価の諸手順間で一般的に必要となり、ORDER BYが等価と判断する行の順序付けを保存するような並べ替えは保証されません。)

現時点では、ウィンドウ関数は常に事前に並べ替えられたデータを必要とするので、問い合わせ出力はウィンドウ関数のPARTITION BY/ORDER BY句のどれか1つに従って順序付けされます。 とはいえ、これに依存することは薦められません。 確実に結果が特定の方法で並べ替えられるようにしたいのであれば、明示的な最上階層のORDER BYを使用します。