Hologres:SELECT

コマンドの概要

コマンド構文

[ WITH with_query [, ...] ]
SELECT [ALL | DISTINCT [ON (expression [, ...])]]
  * | expression [[AS] output_name] [, ...]
  [FROM from_item [, ...]]
  [WHERE condition]
  [GROUP BY grouping_element [, ...]]
  [HAVING condition [, ...]]
  [{UNION | INTERSECT | EXCEPT} [ALL] select]
  [ORDER BY expression [ASC | DESC | USING operator] [, ...]]
  [LIMIT {count | ALL}]

コマンドの使用方法

一般的な SELECT の使用方法は次のとおりです。

1. FROM リスト内のすべての要素（実テーブルまたは仮想テーブル）が評価されます。リストに複数の要素が含まれる場合、結果はそれらの和集合になります。

2. WHERE 句が指定されている場合、指定された条件を満たさないすべての行がフィルターで除外されます。

3. GROUP BY 句が指定されている場合、または集計関数が使用されている場合、一致する値に基づいて行がグループにまとめられます。その後、各グループに対して集計関数が計算されます。HAVING 句が存在する場合、指定された条件を満たさないグループがフィルターで除外されます。

4. SELECT 出力式を使用して、選択された行またはグループから実際の出力行が計算されます。

5. UNION、INTERSECT、EXCEPT の集合演算子を使用して、複数の SELECT 文を単一の結果セットに結合できます。UNION 演算子は、いずれか一方または両方の結果セットに含まれるすべての行を返します。INTERSECT 演算子は、両方の結果セットに含まれるすべての行を返します。EXCEPT 演算子は、最初の結果セットに含まれて 2 番目の結果セットに含まれない行を返します。これら 3 つの演算子すべてにおいて、ALL を指定しない限り重複する行は最終結果セットから削除されます。デフォルトでは、これらの演算子は重複を削除します（これは DISTINCT を指定することと同等です）。この動作は、重複を含むすべての行を返す標準的な SELECT 文のデフォルト動作とは異なります。

6. ORDER BY 句を指定すると、返される行は指定された順序でソートされます。ORDER BY 句を指定しない場合、行はシステムが最も高速に生成できる順序（任意の順序）で返されます。

7. LIMIT（または FETCH FIRST）句または OFFSET 句が指定されている場合、SELECT 文は結果行のサブセットのみを返します。

パラメーターの説明

• WITH 句

  • コマンドの概要

    WITH 句を使用すると、メインクエリ内で名前によって参照できる 1 つ以上のサブクエリを定義できます。これらの名前付きサブクエリは、共通テーブル式（CTE）として知られています。WITH 句は通常、主な SELECT 文の前に配置されます。構文は次のとおりです。

    with query_name [ ( column_name [, ...] ) ] AS ( select ) // WITH句、共通テーブル式(CTE)を定義、クエリ名とカラム名を指定
    

  • パラメーターの説明

    パラメーター	説明
    query_name	現在の CTE の名前を指定します。有効な識別子であれば、どのようなものでも使用できます。
    column_name	サブクエリの戻り値に対応する列名をリストします。これは SELECT 句の AS セマンティクスと同様です。サブクエリは通常の SELECT クエリです。

    CTE はカンマで区切られます。後の CTE 定義では、前の CTE を参照できます。再帰的 CTE は現時点でサポートされていません。後続のクエリでは、query_name をビューとして直接使用できます。column_name リストが指定されていない場合、ビューの column_name はサブクエリが返す列名がデフォルトになります。column_name リストが指定されている場合、定義された column_name を使用する必要があり、column_name のエントリー数は SELECT 文が返す列数と一致している必要があります。

• SELECT リスト

  • コマンドの概要

    SELECT リスト（SELECT キーワードと FROM キーワードの間の部分）は、SELECT 文の出力行を構成する式を指定します。これらの式は、FROM 句で計算された列を参照できます。

  • パラメーターの説明

    SELECT 文の各出力列には名前があります。シンプルな SELECT 文では、この名前が表示用の列ラベルとして使用されます。SELECT 文がサブクエリである場合、外部クエリはこの名前を使用して、サブクエリによって生成された仮想テーブルの列を参照します。出力列の名前を指定するには、列式の後に AS output_name を追加します。AS は省略できますが、目的の出力名が PostgreSQL のキーワードと一致しない場合に限ります。将来のキーワードとの潜在的な競合を避けるため、AS を使用するか、出力名を二重引用符で囲むことを推奨します。列名を指定しない場合、PostgreSQL は自動的に名前を割り当てます。列式がシンプルな列参照の場合、割り当てられた名前はその列の名前と同じになります。関数や型名を含むより複雑なケースでは、 ?column? のような汎用名がシステムによって生成される場合があります。

    出力列は、ORDER BY 句および GROUP BY 句でその名前を使用して参照できます。ただし、WHERE 句または HAVING 句では、出力列をその名前で参照することはできません。これらの句では、式自体を使用する必要があります。

    出力リストにアスタリスク（*）を使用して、選択された行のすべての列を省略形で指定できます。table_name.* を使用して、特定のテーブルからのすべての列を省略形で指定することもできます。これらの場合は、AS を使用して新しい名前を指定することはできません。出力列名は、元のテーブルの列名と同じになります。

• FROM 句

  • コマンドの概要

    FROM 句は、SELECT 文の 1 つ以上のソーステーブルを指定します。複数のソーステーブルを指定した場合、結果はすべてのソーステーブルの直積（クロス結合）になります。通常、WHERE 句に結合条件を追加して、直積の一部の小さなサブセットのみを返すように制限します。

  • パラメーターの説明

    FROM 句には次の要素を含めることができます。

    要素	説明
    table_name	既存のテーブルまたはビューの名前（スキーマで修飾することも可能）。
    alias	エイリアスを含む FROM 項目に対する代替名。エイリアスを使用すると、簡潔に記述できたり、自己結合（同じテーブルを複数回スキャンする場合）での曖昧さを解消したりできます。エイリアスが指定されている場合、実際のテーブル名または関数名は隠されます。たとえば、FROM foo AS f が指定されている場合、SELECT の残りの部分ではこの FROM 項目を foo ではなく f として参照する必要があります。
    select	FROM 句にサブ SELECT を記述できます。これは、SELECT コマンドの実行中に一時テーブルが作成されたかのように動作します。サブ SELECT は括弧で囲み、エイリアスを指定する必要があります。
    function_name	FROM 句に関数呼び出しを記述できます（特に結果セットを返す関数で有用ですが、任意の関数を使用できます）。これは、関数の出力が SELECT コマンドの実行中に一時テーブルとして作成されたかのように動作します。 テーブルと同様にエイリアスを指定できます。エイリアスを指定する場合、関数の複合戻り値型の 1 つ以上の属性（ORDINALITY が存在する場合はそれによって追加された新しい列を含む）に対して代替名を指定する列エイリアスのリストも指定できます。 複数の関数呼び出しを ROWS FROM（。） で囲むことで、単一の FROM 句項目に結合できます。このような項目の出力は、各関数の最初の行、次に各関数の 2 行目というように連結されます。一部の関数が他の関数よりも少ない行数を生成する場合、不足しているデータ位置には NULL が埋められるため、返される行の総数は常に最も多くの行を生成する関数の行数と一致します。
    join_type	次の 5 種類があります。 [ INNER ] JOIN INNER および OUTER 結合タイプでは、NATURAL, ON join_condition, or USING (join_column [, ...]) のいずれか 1 つの結合条件を指定する必要があります（以下を参照）。CROSS JOIN では、これらの句は使用できません。JOIN 句は 2 つの FROM 項目（便宜上「テーブル」と呼びますが、任意の FROM 項目タイプを指定できます）を結合します。必要に応じて括弧を使用してネストの順序を制御できます。括弧がない場合、JOIN は左から右にネストされます。いずれの場合も、JOIN の結合はコンマで区切られた FROM リスト項目よりも強くなります。 LEFT [ OUTER ] JOIN LEFT OUTER JOIN は、結合条件を満たすすべての組み合わせ（制限された直積）に加えて、左側のテーブルの一致しない各行のコピーを 1 つずつ返します。一致しない左側の行は、右側の列に NULL を挿入することで完全な結合テーブル行に拡張されます。一致する行を決定するのは JOIN 句自身の条件のみであることに注意してください。外部条件はその後に適用されます。 RIGHT [ OUTER ] JOIN RIGHT OUTER JOIN は、結合されたすべての行に加えて、一致しない右側の各行（左側に NULL を拡張）を返します。これは単なる表記上の便宜であり、左右のテーブルを入れ替えることで LEFT OUTER JOIN に変換できます。 FULL [ OUTER ] JOIN FULL OUTER JOIN は、結合されたすべての行に加えて、一致しない左側の各行（右側に NULL を拡張）および一致しない右側の各行（左側に NULL を拡張）を返します。 CROSS JOIN CROSS JOIN および INNER JOIN は、単純な直積（FROM に 2 つのテーブルをリストした場合と同じ結果）を生成しますが、結合条件（ある場合）によって制約されます。CROSS JOIN は INNER JOIN ON (TRUE) と同等であり、条件によって行が削除されないことを意味します。これらの結合タイプは単なる表記上の便宜であり、プレーンな FROM および WHERE で実現できないことはありません。
    join_condition	join_condition は、ブール値を返す式（WHERE 句と同様）で、結合時にどの行が一致するとみなされるかを指定します。
    USING ( a, b, ... )	USING (a, b...) 句は、ON left_table.a = right_table.a AND left_table.b = right_table.b ... の省略形です。さらに、USING は結合出力に各等価ペアから 1 つの列のみを含めるようにします。
    NATURAL	両方のテーブルで同一の名前を持つすべての列をリストする USING の省略形です。

• WHERE 句

  • コマンドの概要

    オプションの WHERE 句は次の形式です。

    WHERE 条件

  • パラメーターの説明

    パラメーター	説明
    condition	condition は、ブール結果を返す任意の式です。この条件を満たさない行はすべて出力から削除されます。変数参照に実際の値を代入したときに式が true を返す場合、その行は条件を満たします。

• GROUP BY 句

  • コマンドの概要

    オプションの GROUP BY 句は、グルーピング式の値が同じであるすべての選択行を 1 つの要約行にまとめます。GROUP BY 句の形式は次のとおりです。

    GROUP BY grouping_element [, ...] // GROUP BY句、グループ化要素を指定
    

  • パラメーターの説明

    grouping_element の式は、入力列名、SELECT リストの出力列名または序数、または入力列値から構成される式です。名前が曖昧な場合、GROUP BY 句はそれを出力列名ではなく入力列名として解釈します。

    グルーピング要素に GROUPING SETS, ROLLUP、または CUBE が含まれている場合、GROUP BY 句は複数の独立したグルーピングセットを定義します。これは、各サブクエリが 1 つのグルーピングセットに対応する独自の GROUP BY 句を持つサブクエリの UNION ALL と同等です。

    集計関数を使用する場合、各グループ内のすべての行に対して計算され、各グループに対して 1 つの値が生成されます。集計関数を使用するが GROUP BY 句を含めない場合、クエリはすべての選択行を 1 つのグループとして扱います。集計関数呼び出しに FILTER 句を追加することで、集計関数に渡される行をフィルターできます。FILTER 句が存在する場合、フィルター条件に一致する行のみがその集計関数の入力に含まれます。

    GROUP BY 句または任意の集計関数が存在する場合、SELECT リストの式は、集計関数内またはグルーピングされた列に機能的に依存するグルーピングされていない列を除き、グルーピングされていない列を参照できません。これは、グルーピングされていない列に対して返す可能性のある値が複数存在するためです。機能的依存性は、グルーピングされた列にグルーピングされていない列を含むテーブルのプライマリキーまたはそのサブセットが含まれている場合に存在します。

    すべての集計関数は、HAVING 句または SELECT リスト内のスカラー式が計算される前に計算されます。たとえば、CASE 式を使用して集計関数の計算をスキップすることはできません。

• CUBE 句

  • CUBE

    CUBE サブ句は、GROUP BY 句で指定されたグルーピング列のすべての組み合わせに対して自動的に小計を作成します。結果セットには、ディメンション列のすべての可能な値の組み合わせと、それらのディメンション組み合わせに一致するベース行からの集約値が含まれます。CUBE は各グループに対して 1 つの要約行を返します。CUBE を使用してクロス集計値を生成できます。たとえば、CUBE 句で 3 つの式 (n = 3) を指定すると、操作の結果は 2ⁿ = 2³ = 8 グループになります。すべての n 式の値でグルーピングされた行は通常の行です。他のすべての行はスーパーアグリゲート行です。構文は次のとおりです。

    CUBE ( { expression | ( expression [, …] ) } [, …] ) // CUBE句、指定した式のすべての組み合わせでグループ化
    

  • ROLLUP

    ROLLUP サブ句は GROUP BY 句の拡張です。統計情報やレポートを生成する際に、ROLLUP を使用して各グループの小計とすべてのグループの総計を返すことができます。構文は次のとおりです。

    ROLLUP ( { expression | ( expression [, …] ) } [, …] ) // ROLLUP句、階層的な集計を行う
    

  • GROUPING SETS

    GROUPING SETS サブ句は GROUP BY 句の拡張で、単一のクエリで複数の GROUP BY オプションを指定できます。これにより、必要なグルーピングセットのみを選択できるため効率が向上します。必要なデータグループのみを指定することで、データベースは CUBE や ROLLUP によって生成される完全な集約セットを計算する必要がなくなります。構文は次のとおりです。

    GROUPING SETS ( grouping_element [, …] ) // GROUPING SETS句、複数のグループ化セットを指定
    

• DISTINCT 句

  SELECT DISTINCT を指定すると、結果セットからすべての重複行が削除されます。重複行のグループごとに 1 行のみが保持されます。

  説明

  ARRAY データ型の列に対して SELECT DISTINCT を使用することはサポートされていません。

  SELECT DISTINCT accountid FROM table;

• COUNT DISTINCT 句

  • コマンドの概要

    COUNT DISTINCT は、列内の個別値の数をカウントします。複数回出現する値は 1 回のみカウントされます。COUNT 関数と同様に、列内の NULL 値はカウントに含まれません。

  • 構文の説明

    • 正確な計算の構文：

      SELECT c1, COUNT(DISTINCT c2) FROM table GROUP BY c1

    • 正確な COUNT DISTINCT 計算は大量のリソースを消費する可能性があります。このため、Hologres では近似 COUNT DISTINCT 計算もサポートしています。構文は次のとおりです。

      SELECT c1, approx_count_distinct(c2) FROM table GROUP BY c1 // COUNT DISTINCT句、近似カウント
      

• UNION 句

  • コマンドの概要

    UNION 句の構文は次のとおりです。

    select_statement UNION [ ALL | DISTINCT ] select_statement // UNION句、2つのSELECT結果を結合（全件または重複排除）
    

  • パラメーターの説明

    パラメーター	説明
    select_statement	select_statement は、ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE、または FOR KEY SHARE 句を含まない任意の SELECT 文です（サブ式が括弧で囲まれている場合、ORDER BY および LIMIT をそれに付加できます。括弧がない場合、これらの句は右側の式ではなく UNION の結果に適用されます）。
    UNION	UNION 演算子は、関与する SELECT 文によって返される行の和集合を計算します。少なくとも 2 つの結果セットのいずれかに含まれる行が和集合に現れます。UNION のオペランドとして使用される SELECT 文は、同じ数の列を生成する必要があり、対応する列は互換性のあるデータ型である必要があります。 UNION の結果には、ALL オプションが指定されていない限り重複行は含まれません。ALL を指定すると、重複の削除が行われないため（通常、UNION ALL は UNION よりも大幅に高速です）、可能な場合は常に ALL を使用してください。明示的に重複の削除を指定するには、DISTINCT と記述します。 同じ SELECT 文に複数の UNION 演算子が含まれる場合、括弧で異なる順序が指定されていない限り、左から右に評価されます。

• INTERSECT 句

  • コマンドの概要

    INTERSECT 句の形式は次のとおりです。

    select_statement INTERSECT [ ALL | DISTINCT ] select_statement // INTERSECT句、2つのSELECT結果の共通部分を抽出（全件または重複排除）
    

  • パラメーターの説明

    パラメーター	説明
    select_statement	select_statement は、ORDER BY LIMIT 句を含まない任意の SELECT 文です。
    INTERSECT	INTERSECT 演算子は、関与する SELECT 文によって返される行の積集合を計算します。両方の結果セットに含まれる行のみが積集合に現れます。 INTERSECT の結果には、ALL オプションが指定されていない限り重複行は含まれません。ALL を指定すると、左側のテーブルに m 回、右側のテーブルに n 回現れる行は、結果に min(m, n) 回現れます。明示的に重複の削除を指定するには、DISTINCT と記述します。 同じ SELECT 文に複数の INTERSECT 演算子が含まれる場合、括弧で異なる順序が指定されていない限り、左から右に評価されます。INTERSECT は UNION よりも優先度が高いため、A UNION B INTERSECT C は A UNION (B INTERSECT C) として解釈されます。

• EXCEPT 句

  • コマンドの概要

    EXCEPT 句の形式は次のとおりです。

    select_statement EXCEPT [ ALL | DISTINCT ] select_statement // EXCEPT句、左のSELECT結果から右のSELECT結果に含まれる行を除外（全件または重複排除）
    

  • パラメーターの説明

    パラメーター	説明
    select_statement	select_statement は、ORDER BY LIMIT 句を含まない任意の SELECT 文です。
    EXCEPT	EXCEPT 演算子は、左側の SELECT 文の結果に含まれて右側の SELECT 文の結果に含まれない行のセットを計算します。 EXCEPT の結果には、ALL オプションが指定されていない限り重複行は含まれません。ALL を指定すると、左側のテーブルに m 回、右側のテーブルに n 回現れる行は、結果に max(m - n, 0) 回現れます。明示的に重複の削除を指定するには、DISTINCT と記述します。 同じ SELECT 文に複数の EXCEPT 演算子が含まれる場合、括弧で異なる順序が指定されていない限り、左から右に評価されます。EXCEPT は UNION と同じ優先度を持ちます。 現在、FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE および FOR KEY SHARE は、EXCEPT の結果または任意の EXCEPT 入力で使用できません。

• ORDER BY 句

  • コマンドの概要

    オプションの ORDER BY 句の形式は次のとおりです。

    ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] // ORDER BY句、式に基づいてソート（昇順/降順/演算子使用、NULL値の扱い）
    

  • パラメーターの説明

    ORDER BY 句は、指定された式に基づいて結果行をソートします。左端の式で 2 行が等しい場合、次の式に基づいて比較され、以降も同様に処理されます。すべての指定された式で行が等しい場合、実装依存の順序で返されます。

    各式は、SELECT リストの出力列名または序数、または入力列値から構成される式です。

    序数は、左から右への出力列の位置を指します。この機能により、一意でない名前を持つ列の順序を定義できます。通常、これは必要ありません。なぜなら、AS 句を使用して出力列に一意の名前を割り当てることができるためです。

    ORDER BY 句には、SELECT 出力リストに含まれていない列を含む任意の式も使用できます。たとえば、次の文は有効です。

    SELECT name FROM distributors ORDER BY code;

    この機能の制限として、UNION、INTERSECT、または EXCEPT 句の結果に ORDER BY 句を適用する場合、ORDER BY 句では出力列名または序数のみを指定でき、式は指定できません。

    ORDER BY 式がシンプルな名前で、出力列名と入力列名の両方に一致する場合、ORDER BY はそれを出力列名として解釈します。この動作は、曖昧な名前を入力列名として解釈する GROUP BY 句とは異なります。この不整合は、SQL 標準との互換性のためです。

    ORDER BY 句の任意の式の後に ASC（昇順）または DESC（降順）キーワードを追加できます。キーワードを指定しない場合、デフォルトで ASC が使用されます。または、USING 句でソート演算子名を指定することもできます。ソート演算子は、B ツリー演算子ファミリーの小なりまたは大なりのメンバーである必要があります。通常、ASC は USING < と同等であり、DESC は USING > と同等です。ただし、ユーザー定義データ型の作成者は特定のデフォルトソート順序を定義でき、これは他の名前の演算子に対応する場合があります。

    NULLS LAST を指定すると、NULL 値は非 NULL 値の後にソートされます。NULLS FIRST を指定すると、NULL 値は非 NULL 値の前にソートされます。どちらも指定しない場合、ASC のデフォルト動作は NULLS LAST、DESC のデフォルト動作は NULLS FIRST です。デフォルトでは、NULL は非 NULL よりも大きいとみなされます。USING 句を使用する場合、デフォルトの NULL 順序は、演算子が小なり演算子か大なり演算子かによって異なります。

    順序オプションは、それに続く式にのみ適用されることに注意してください。たとえば、ORDER BY x, y DESC は ORDER BY x DESC, y DESC とは異なります。

• LIMIT 句

  • コマンドの概要

    LIMIT 句は、次の 2 つの独立したサブ句で構成されます。

    LIMIT { count | ALL } // LIMIT句、取得件数を制限
    OFFSET start // OFFSET句、開始位置を指定
    

  • パラメーターの説明

    count パラメーターは、返される行の最大数を指定します。start パラメーターは、システムが行を返し始める前にスキップする行数を指定します。両方のパラメーターを指定した場合、システムは返す行をカウントし始める前に指定された start 行数をスキップします。

    count 式が NULL に評価される場合、LIMIT ALL（つまり制限なし）として扱われます。start 式が NULL に評価される場合、OFFSET 0 として扱われます。

    LIMIT 句を使用する場合は、行を一意の順序に制約するために ORDER BY 句も含める必要があります。そうしないと、クエリの行の予測不可能なサブセットを取得することになります。たとえば、ORDER BY 句を指定せずに 10 行目から 20 行目を要求しても、どの行が返されるか保証されません。

    クエリプランナーは、クエリプランを生成する際に LIMIT 句を考慮します。したがって、異なる LIMIT および OFFSET 値では、おそらく異なるクエリプランおよび行の順序が生成されます。ORDER BY 句を使用して予測可能な順序を強制しない限り、異なる LIMIT および OFFSET 値を使用してクエリ結果の異なるサブセットを選択すると、結果が一貫しなくなる可能性があります。これはバグではありません。SQL では、ORDER BY 句を使用して順序を制約しない限り、クエリ結果の特定の順序は保証されないという事実の意図的な結果です。

    ORDER BY 句を使用して決定論的なサブセットを強制しない場合、同じ LIMIT クエリを複数回実行しても、テーブル行の異なるサブセットが返される可能性があります。これはバグではありません。このようなケースでは、決定論性は保 guarantee されません。

使用例

• 2 つのテーブルを結合

  SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM  // filmsテーブルとdistributorsテーブルを結合
  distributors d, films f WHERE f.did = d.did;
  

• WITH 句

  WITH distributor_name(name) AS (SELECT name from distributors) // distributorsテーブルから名前を取得するCTEを定義
  SELECT name FROM distributor_name ORDER BY name; // CTEを使用して名前をソート
  

• GROUP BY

  SELECT kind, sum(length) AS total FROM films GROUP BY kind;

• HAVING によるフィルタリング

  SELECT kind, sum(length) AS total FROM films GROUP BY kind  // kindでグループ化してlengthの合計を取得、合計が5時間未満のグループのみ出力
  HAVING sum(length) < interval '5 hours';
  

• GROUP BY CUBE

  SELECT  l_returnflag // l_returnflag、l_shipmodeのすべての組み合わせでグループ化してl_quantityの合計を取得
          ,l_shipmode
          ,SUM(l_quantity)
  FROM    public.lineitem
  GROUP BY cube((l_returnflag),(l_shipmode))
  ORDER BY l_returnflag
           ,l_shipmode;
  

• GROUP BY ROLLUP

  SELECT  l_returnflag
          ,l_shipmode
          ,SUM(l_quantity)
  FROM    public.lineitem
  GROUP BY ROLLUP ((l_returnflag),(l_shipmode))
  ORDER BY l_returnflag
           ,l_shipmode;

• GROUP BY GROUPING SETS

  SELECT  l_returnflag
          ,l_shipmode
          ,SUM(l_quantity)
  FROM    public.lineitem
  GROUP BY GROUPING SETS ((l_returnflag,l_shipmode),())
  ORDER BY l_returnflag
           ,l_shipmode;

• ORDER BY

  SELECT * FROM distributors ORDER BY name;