PolarDB:テーブル式

概要

テーブル式はテーブルを計算します。 テーブル式にはFROM句が含まれ、オプションでWHERE、GROUP by、HAVING句が続きます。 トリビアルテーブル式は、単にディスク上のテーブル、いわゆるベーステーブルを指しますが、より複雑な式を使用して、さまざまな方法でベーステーブルを変更または結合できます。

テーブル式のオプションのWHERE、GROUP BY、およびHAVING句は、FROM句で派生したテーブルに対して実行される一連の変換のパイプラインを指定します。 これらの変換はすべて、クエリの出力行を計算するために選択リストに渡される行を提供する仮想テーブルを生成します。

句から

FROM句は、カンマ区切りのテーブル参照リストで指定された1つ以上の他のテーブルからテーブルを派生します。

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

テーブル参照には、テーブル名 (スキーマ修飾された可能性があります) 、またはサブクエリ、JOIN構造、またはこれらの複雑な組み合わせなどの派生テーブルを使用できます。 FROM句に複数のテーブル参照がリストされている場合、テーブルは相互結合されます (つまり、行のデカルト積が形成されます。以下を参照) 。 FROMリストの結果は、WHERE、GROUP by、およびHAVING句による変換を受けることができる中間仮想テーブルであり、最終的にテーブル式全体の結果となります。

テーブル参照がテーブル継承階層の親であるテーブルに名前を付けると、キーワードがテーブル名の前にのみある場合を除き、テーブル参照はそのテーブルだけでなくその子孫テーブルのすべての行を生成します。 ただし、参照は名前付きテーブルに表示される列のみを生成します。サブテーブルに追加された列は無視されます。

テーブル名の前にのみを記述する代わりに、テーブル名の後に * を記述して、子孫テーブルを含めることを明示的に指定できます。 子孫テーブルの検索が常にデフォルトの動作になるため、この構文を使用する実際の理由はもうありません。 ただし、古いリリースとの互換性のためにサポートされています。

参加テーブル

結合テーブルは、特定の結合タイプの規則に従って、2つの他の (実または派生) テーブルから派生したテーブルです。 インナー、アウター、およびクロスジョイントが利用可能です。 結合テーブルの一般的な構文は

T1 join_type T2 [ join_condition ]

すべてのタイプの結合は、チェーン接続または入れ子にすることができます。T1とT2のいずれかまたは両方を結合テーブルにすることができます。 JOIN句の周りで括弧を使用して、結合順序を制御できます。 括弧がない場合、JOIN句は左から右に入れ子になります。

タイプの結合

クロス参加

T1クロス参加T2

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

FROM T1 CROSS JOIN T2は、FROM T1 INNER JOIN T2 ON TRUEと同等です (以下を参照) 。 FROM、T1、T2にも相当します。

資格のある参加

T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING (参加列リスト)
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 

INNERとOUTERという単語は、すべての形式でオプションです。INNERはデフォルトです。LEFT、RIGHT、FULLは外部結合を意味します。

join条件は、ONまたはUSING句で指定されるか、暗黙的にNATURALという単語で指定されます。 結合条件は、以下に詳細に説明するように、2つのソーステーブルからのどの行が「一致する」と見なされるかを決定する。

修飾結合の可能なタイプは次のとおりです。

INNER JOIN: T1の各行R1について、結合されたテーブルは、R1との結合条件を満たすT2の各行の行を持っています。

LEFT OUTER JOIN: まず、内部結合が行われる。 次に、T2のいずれの行とも結合条件を満たさないT1の各行について、T2の列にヌル値を有する結合行が追加される。 したがって、結合されたテーブルは、常にT1内の各行に対して少なくとも1つの行を有する。

RIGHT OUTER JOIN: まず、内部結合が行われる。 次に、T1内のどの行とも結合条件を満たさないT2内の各行について、T1の列にヌル値を有する結合行が追加される。 これは左結合の逆です。結果テーブルは常にT2の各行に行を持ちます。

FULL OUTER JOIN: まず、内部結合が実行される。 次に、T2のいずれの行とも結合条件を満たさないT1の各行について、T2の列にヌル値を有する結合行が追加される。 また、T1内のいずれの行とも結合条件を満たさないT2の各行に対して、T1の列にヌル値を有する結合行が追加される。

ON句は最も一般的な種類の結合条件です。WHERE句で使用されるのと同じ種類のブール値式を受け取ります。 ON式がtrueと評価された場合、T1とT2の行のペアが一致します。

USING句は、結合の両側が結合列に同じ名前を使用するという特定の状況を利用できるようにする省略形です。 これは、共有列名のコンマ区切りのリストを取り、それぞれの等号比較を含む結合条件を形成します。 例えば、T1およびT2をUSING (a, b) と結合すると、結合条件ON.a =.a and. b =.bが生成される。

さらに、JOIN USINGの出力は冗長列を抑制します。一致した列の両方を印刷する必要はありません。 JOIN ONがT1からのすべての列を生成し、その後T2からのすべての列が生成されますが、JOIN USINGはリストされた列ペアのそれぞれに対して1つの出力列を生成し (リストされた順序で) 、その後にT1からの残りの列、T2からの残りの列が生成されます。

最後に、NATURALはUSINGの省略形であり、両方の入力テーブルに表示されるすべての列名で構成されるUSINGリストを形成します。 USINGと同様に、これらの列は出力テーブルに1回だけ表示されます。 一般的な列名がない場合、NATURAL JOINはJOINのように動作します... ON TRUE、クロスプロダクト結合を生成します。

これをまとめるには、テーブルt1があるとします。

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

およびt2:

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

次に、さまざまな結合に対して次の結果が得られます。

=> SELECT * FROM t1クロスJOIN t2;
     num | name | num | 値
    -----+------+-----+-------
       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 | 値
    -----+------+-----+-------
       1 | a | 1 | xxx
       3 | c | 3 | yyy
    （2行）

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

    => SELECT * からt1自然な内部参加者参加t2;
     num | name | value
    -----+------+-------
       1 | a | xxx
       3 | c | yyy
    （2行）

    => SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
     num | name | num | 値
    -----+------+-----+-------
       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 | 値
    -----+------+-----+-------
       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 | 値
    -----+------+-----+-------
       1 | a | 1 | xxx
       2 | b | |
       3 | c | 3 | yyy
         | | 5 | zzz
    (4行) 

ONで指定された結合条件には、結合に直接関連しない条件を含めることもできます。 これはいくつかのクエリに役立ちますが、慎重に検討する必要があります。 設定例：

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

WHERE句に制限を配置すると、別の結果が得られることに注意してください。

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

これは、ON句の制限は結合の前に処理され、WHERE句の制限は結合の後に処理されるためです。 それは内側の結合では重要ではありませんが、外側の結合では非常に重要です。

表と列のエイリアス

クエリの残りの部分で派生テーブルへの参照に使用されるテーブルと複雑なテーブル参照に一時的な名前を付けることができます。 これはテーブルエイリアスと呼ばれます。

テーブルエイリアスを作成するには、

FROM table_reference ASエイリアス

または

FROM table_referenceエイリアス

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どこmy_table.a > 5; -間違っている

テーブルのエイリアスは主に表記の便宜のためですが、テーブルをそれ自体に結合するときに使用する必要があります。

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

さらに、テーブル参照がサブクエリの場合はエイリアスが必要です。

括弧はあいまいさを解決するために使用されます。 次の例では、最初のステートメントはmy_tableの2番目のインスタンスにエイリアスbを割り当てますが、2番目のステートメントは結合の結果にエイリアスを割り当てます。

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

別の形式のテーブルエイリアシングは、テーブルの列とテーブル自体に一時的な名前を与えます。

FROM table_reference [AS] エイリアス (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の外には表示されません。

サブクエリ

派生テーブルを指定するサブクエリは、括弧で囲み、テーブルのエイリアス名を割り当てる必要があります。 設定例：

FROM (SELECT * FROM table1) AS alias_name

この例は、FROM table1 AS alias_nameに相当します。 単純な結合に減らすことができないより興味深いケースは、サブクエリがグループ化または集約を含む場合に発生します。

サブクエリはVALUESリストにすることもできます。

FROM (VALUES ('anne ', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
AS名 (最初、最後) 

ここでも、テーブルのエイリアスが必要です。 VALUESリストの列にエイリアス名を割り当てることはオプションですが、良い方法です。

テーブル関数

テーブル関数は、基本データ型 (スカラー型) または複合データ型 (テーブル行) で構成される行のセットを生成する関数です。 クエリのFROM句では、テーブル、ビュー、またはサブクエリのように使用されます。 テーブル関数によって返される列は、テーブル、ビュー、またはサブクエリの列と同じ方法で、SELECT、JOIN、またはWHERE句に含めることができます。

テーブル関数は、ROWS FROM構文を使用して組み合わせることもでき、結果は並列列で返されます。この場合の結果行の数は、最大の関数結果の行の数であり、より小さな結果は一致するようにnull値でパディングされます。

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

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

特殊テーブル関数UNNESTは、任意の数の配列パラメータで呼び出すことができ、UNNESTが各パラメータに対して別々に呼び出され、ROWS FROMコンストラクトを使用して結合されたかのように、対応する数の列を返します。

UNNEST( array_expression [, ... ] ) [通常] [[AS] table_alias [(column_alias [, ... ])]]

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

列エイリアスが指定されていない場合、基本データ型を返す関数の場合、列名も関数名と同じになります。 複合型を返す関数の場合、結果の列は型の個々の属性の名前を取得します。

いくつかの例:

CREATE TABLE foo (fooid int、foosubid int、foonameテキスト);

CREATE FUNCTION getfoo(int) RETURNS SETOF fooとして $$$
SELECT * fooからfooid = $1;
    $$言語SQL;

SELECT * FROM getfoo (1) AS t1;

SELECT * FROM foo
foosubidのどこ ()
                   SELECT foosubid
                   FROM getfoo(foo.fooid) z
                   WHERE z.fooid = foo.fooid
                          );

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

場合によっては、呼び出される方法に応じて異なる列セットを返すことができるテーブル関数を定義すると便利です。 これをサポートするために、テーブル関数は、OUTパラメーターなしで疑似型レコードを返すものとして宣言できます。 このような関数をクエリで使用する場合、期待される行構造をクエリ自体で指定する必要があります。これにより、システムはクエリを解析して計画する方法を知ることができます。 この構文は次のようになります。

function_call [AS] エイリアス (column_definition [, ... ])
function_call AS [エイリアス] (column_definition [, ... ])
ROWS FROM( ... function_call AS (column_definition [, ... ]) [, ... ] ) 

ROWS FROM() 構文を使用しない場合、colume_definitionリストは、FROMアイテムにアタッチされる可能性のある列エイリアスリストを置き換えます。列定義の名前は列エイリアスとして機能します。 ROWS FROM() 構文を使用する場合、columen_definitionリストを各メンバー関数に個別に添付できます。メンバー関数が1つしかなく、WITH ORDINALITY句がない場合は、ROWS FROM() に続く列別名リストの代わりにcolumen_definitionリストを記述できます。

この例を考えてみましょう:

SELECT *
dblinkから ('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc ')
AS t1 (代名詞名、prosrcテキスト)
どこの代名詞LIKE 'bytea % '; 

dblink機能 (dblinkモジュールの一部) は、リモートクエリを実行する。 あらゆる種類のクエリに使用される可能性があるため、レコードを返すように宣言されます。 実際の列セットは、呼び出し元のクエリで指定する必要があります。これにより、パーサーは、たとえば * の展開先を把握できます。

この例では、ROWS FROMを使用します。

SELECT *
からの行から
        (
            json_to_recordset('[{"a":40,"b":"foo" },{ "a":"100","b":"bar"}]')
                AS (指、bテキスト) 、
            generate_series(1, 3)
        ) AS x (p、q、s)
注文によってp;

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

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

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は、結合される行を計算するために相互参照された列が必要な場合に主に有用である。 一般的なアプリケーションは、セット戻し関数の引数値を提供することである。 たとえば、頂点 (ポリゴン) がポリゴンの頂点のセットを返すと仮定すると、次のようにテーブルに格納されているポリゴンの頂点が近接しているかを識別できます。

SELECT p1.id, p2.id, v1, v2
ポリゴンp1、ポリゴンp2、
         ラテラル頂点 (p1.poly) v1、
         ラテラル頂点 (p2.poly) v2
WHERE (v1 <-> v2) < 10およびp1.id! =p2.id; 

このクエリは、

SELECT p1.id, p2.id, v1, v2
ポリゴンからp1クロスJOIN LATERAL頂点 (p1.poly) v1、
         ポリゴンp2クロス参加ラテラル頂点 (p2.poly) v2
WHERE (v1 <-> v2) < 10およびp1.id! =p2.id; 

または他のいくつかの同等の製剤。 (すでに述べたように、この例ではLATERALキーワードは不要ですが、わかりやすくするために使用します。)

多くの場合、LATERALサブクエリへのLEFT JOINは特に便利です。これにより、LATERALサブクエリが行を生成しない場合でも、ソース行が結果に表示されます。 たとえば、get_product_names() がメーカーによって作成された製品の名前を返しているが、テーブル内の一部のメーカーが現在製品を生産していない場合、次のようなものを見つけることができます。

SELEC T m.name
メーカーからm LEFT JOIN LATERAL get_product_names(m.id) pname ON true
WHERE pnameはNULLです。

WHERE条項

WHERE句の構文は

WHERE search_condition

search_conditionは、boolean型の値を返す値式です。

FROM句の処理が完了した後、派生仮想テーブルの各行が検索条件と照合されます。 条件の結果が真である場合、行は出力テーブルに保持され、そうでない場合 (すなわち、結果が偽またはヌルである場合) 、それは破棄される。 検索条件は通常、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ここでb.val > 5

これらのどれを使用するかは、主にスタイルの問題です。 FROM句のJOIN構文は、SQL標準であるにもかかわらず、おそらく他のSQLデータベース管理システムに移植可能ではありません。 外部結合の場合、選択の余地はありません。それらはFROM句で行う必要があります。 外部結合のON句またはUSING句は、WHERE条件と同等ではありません。これは、行の追加 (一致しない入力行の場合) 、および最終結果の行の削除が発生するためです。

WHERE句の例を次に示します。

SELECT... fdt WHERE c1 > 5から

SELECT... fdt WHERE c1 IN (1、2、3) から

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

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

SELECT... FROM fdt WHERE c1 BETWEEEN (選択c3 FROM t2 WHERE c2 = fdt.c1 + 10) および100

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

fdtは、FROM句で派生したテーブルです。 WHERE句の検索条件を満たさない行はfdtから削除されます。 スカラーサブクエリを値式として使用することに注意してください。 他のクエリと同様に、サブクエリは複雑なテーブル式を使用できます。 サブクエリでfdtが参照される方法にも注意してください。 c1をfdt.c1として認定することは、c1がサブクエリの派生入力テーブルの列の名前でもある場合にのみ必要です。 ただし、列名を修飾すると、不要な場合でも明確になります。 この例では、外部クエリの列命名スコープが内部クエリにどのように拡張されるかを示します。

GROUP BYおよびHAVING条項

WHEREフィルターを通過した後、派生入力テーブルはGROUP BY句を使用してグループ化し、HAVING句を使用してグループ行を削除することができます。

SELECT select_list
        FROM ...
        [どこ...]
        GROUP BY grouping_column_reference [, grouping_column_reference]... 

GROUP BY句は、リストされているすべての列で同じ値を持つテーブル内の行をグループ化するために使用されます。 列がリストされる順序は重要ではありません。 その効果は、共通の値を有する行の各セットを、グループ内のすべての行を表す1つのグループ行に結合することである。 これは、出力の冗長性を排除し、および /またはこれらのグループに適用される集計を計算するために行われます。 例えば:

=> SELECT * からtest1;
     x | y
    ---+---
     a | 3
     c | 2
     b | 5
     a | 1
    （4行）

    => SELECT x FROM test1グループBY x;
     x
    ---
     a
     b
     c
    (3行) 

2番目のクエリでは、SELECT * FROM test1 GROUP BY xを記述できませんでした。これは、各グループに関連付けることができる列yの単一の値がないためです。 グループ化された列は、各グループに単一の値があるため、選択リストで参照できます。

一般に、テーブルがグループ化されている場合、GROUP BYにリストされていない列は、集計式以外では参照できません。 集計式の例は次のとおりです。

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

別の例を次に示します。(すべての製品の総売上ではなく) 各製品の総売上を計算します。

SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
        製品からp LEFT JOINセールスs USING (product_id)
        GROUP BY product_id、p。名前、p。価格; 

この例では、product_id、p.name、およびp.price列は、クエリ選択リストで参照されるため、GROUP BY句に含める必要があります (ただし、以下を参照) 。 s.units列は、製品の売上を表す集計式 (sum(...)) でのみ使用されるため、GROUP BYリストに含める必要はありません。 製品ごとに、クエリは製品のすべての売上に関する概要行を返します。

製品テーブルが、たとえばproduct_idが主キーであるように設定されている場合、上記の例ではproduct_idによってグループ化するだけで十分です。名前と価格は製品idに機能的に依存するため、製品idグループごとにどの名前と価格値が返されるかについてあいまいさはありません。

厳密SQLでは、GROUP BYはソーステーブルの列でのみグループ化できますが、PostgreSQLはこれを拡張して、group byが選択リストの列でグループ化できるようにします。 単純な列名ではなく、値式でグループ化することもできます。

テーブルがGROUP BYを使用してグループ化されているが、特定のグループのみが対象である場合、WHERE句のようにHAVING句を使用して、結果からグループを削除できます。 構文は次のとおりです。

SELECT select_list FROM... [どこ...] GROUP BY... boolean_式

を持っている

HAVING句の式は、グループ化された式とグループ化されていない式 (必然的に集計関数を含む) の両方を指すことができます。

例：

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

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

繰り返しますが、より現実的な例:

SELECT product_id, p.name, (合計 (s.units) * (p.price - p.cost)) AS利益
製品からp LEFT JOINセールスs USING (product_id)
WHERE s.date > CURRENT_DATE-インターバル「4週間」
GROUP BY product_id, p.name, p.price, p.cost
合計額 (p.price * s.units) > 5000; 

上記の例では、WHERE句はグループ化されていない列によって行を選択しています (この式は過去4週間の売上にのみ当てはまります) が、HAVING句は総売上高が5000を超えるグループに出力を制限しています。 集計式は、クエリのすべての部分で必ずしも同じである必要はないことに注意してください。

クエリに集計関数呼び出しが含まれているが、GROUP BY句が含まれていない場合でも、グループ化が発生します。結果は単一のグループ行になります (または、単一の行がHAVINGによって削除された場合は、行がまったくない可能性があります) 。 HAVING句が含まれている場合も、集計関数の呼び出しやGROUP BY句がなくても同じことが言えます。

グループセット、キューブ、およびROLLUP

上述したものよりも複雑なグループ化操作は、グループ化セットの概念を用いて可能である。 FROM句とWHERE句で選択されたデータは、指定された各グループ化セットごとに別々にグループ化され、単純なgroup by句の場合と同様に各グループに対して計算された結果が返されます。 設定例：

=> SELECT * からitems_sold;
     ブランド | サイズ | 販売
    -------+------+-------
     フー | L | 10
     フー | M | 20
     バー | M | 15
     バー | L | 5
    （4行）

    => SELECTブランド、サイズ、合計 (売上高) FROM items_sold GROUPING SETS ((ブランド) 、(サイズ) 、());
     ブランド | サイズ | 合計
    -------+------+-----
     フー | | 30
     バー | | 20
           | L | 15
           | M | 35
           | | 50
    (5行) 

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

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

2つの共通のタイプのグループ化セットを指定するための簡略表記が提供される。 フォームの句

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

は、指定された式のリストと、空のリストを含むリストのすべてのプレフィックスを表します。

GROUPINGセット (
        (e1, e2, e3, ... ),
        ...
        (e1, e2 ),
        (e1 ),
        ( )
    )

これは、一般的に、階層データ (例えば、部門、部門、および全社の合計による総給与) の分析に使用される。

フォームの句

CUBE ( e1, e2, ... )

は、所与のリストおよびその可能なサブセット (すなわち、電力セット) のすべてを表す。 したがって、

CUBE ( a, b, c )

に相当します。

GROUPINGセット (
        (a、b、c) 、
        (a、b) 、
        (a、c) 、
        (a ),
        (b、c) 、
        (b ),
        (c ),
        (         )
    )

CUBEまたはROLLUP句の個々の要素は、個々の式または括弧内の要素のサブリストのいずれかです。 後者の場合、サブリストは、個々のグループ化セットを生成するための単一のユニットとして扱われる。 設定例：

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

に相当します。

GROUPINGセット (
        (a、b、c、d) 、
        (a、b) 、
        (c、d) 、
        (            )
    )

および

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

に相当します。

GROUPINGセット (
        (a、b、c、d) 、
        (a、b、c) 、
        (a ),
        (            )
    )

CUBEおよびROLLUP構造は、GROUP BY句で直接使用することも、GROUPING SETS句内にネストすることもできます。 1つのGROUPING SETS句が別の句の中にネストされている場合、その効果は、内側句のすべての要素が外側句に直接書き込まれている場合と同じです。

1つのGROUP BY句で複数のグループ化アイテムが指定されている場合、グループ化セットの最終リストは個々のアイテムの外積となります。 設定例：

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

に相当します。

グループ化による

グループ (
        (a、b、c、d) 、(a、b、c、e) 、
        (a、b、d) 、(a、b、e) 、
        (a、c、d) 、(a、c、e) 、
        (a, d), (a, e)
    )

ウィンドウ関数処理

クエリにウィンドウ関数が含まれている場合、これらの関数は、グループ化、集計、およびHAVINGフィルタリングが実行された後に評価されます。 つまり、クエリでGROUP BYまたはHAVINGのいずれかの集計が使用されている場合、ウィンドウ関数によって表示される行は、from /WHEREの元のテーブル行ではなくグループ行になります。

複数のウィンドウ関数が使用される場合、ウィンドウ定義に構文的に同等のPARTITION BY句とORDER BY句を持つすべてのウィンドウ関数は、データ上の単一のパスで評価されることが保証されます。 したがって、ORDER BYが順序を一意に決定しない場合でも、同じソート順序が表示されます。 しかしながら、異なるPARTITION BYまたはORDER BY仕様を有する機能の評価については保証されていない。 (そのような場合、ソートステップは、通常、ウィンドウ関数評価のパスの間に必要とされ、ソートは、ORDER BYが等価とみなす行の順序を保持することは保証されない。)

現在、ウィンドウ関数は常に事前ソートされたデータを必要とするため、クエリ出力は、ウィンドウ関数のPARTITION BY /ORDER BY句のいずれかに従って順序付けられます。 ただし、これに頼ることはお勧めできません。 結果が特定の方法でソートされていることを確認する場合は、明示的なトップレベルのORDER BY句を使用します。