SELECT

概要:

Sql

  1. SELECT [TOP [ count ] ] select_expr [, ...]
  2. [ FROM table_name ]
  3. [ WHERE condition ]
  4. [ GROUP BY grouping_element [, ...] ]
  5. [ HAVING condition]
  6. [ ORDER BY expression [ ASC | DESC ] [, ...] ]
  7. [ LIMIT [ count ] ]
  8. [ PIVOT ( aggregation_expr FOR column IN ( value [ [ AS ] alias ] [, ...] ) ) ]

説明: 0個以上のテーブルから行を取得します。

SELECT の一般的な実行は次のようになります:

  • 1. FROM リスト内のすべての要素が計算されます（各要素は基本テーブルまたはエイリアステーブルである可能性があります）。 現在、FROM は正確に1つのテーブルをサポートしています。ただし、テーブル名はパターンである可能性があることに注意してください（以下の FROM 句 を参照）。
  • 2. WHERE 句が指定されている場合、条件を満たさないすべての行が出力から除外されます。（以下の WHERE 句 を参照。）
  • 3. GROUP BY 句が指定されている場合、または集約関数の呼び出しがある場合、出力は1つ以上の値で一致する行のグループに結合され、集約関数の結果が計算されます。 HAVING 句が存在する場合、指定された条件を満たさないグループが除外されます。（以下の GROUP BY 句 と HAVING 句 を参照。）
  • 4. 実際の出力行は、選択された各行または行グループの SELECT 出力式を使用して計算されます。
  • 5. ORDER BY 句が指定されている場合、返される行は指定された順序でソートされます。 ORDER BY が指定されていない場合、行はシステムが生成するのに最も速い順序で返されます。（以下の ORDER BY 句 を参照。）
  • 6. LIMIT または TOP が指定されている場合（同じクエリで両方を使用することはできません）、SELECT ステートメントは結果行のサブセットのみを返します。（以下の LIMIT 句 と TOP 句 を参照。）

SELECT List

SELECT リスト、すなわち SELECT と FROM の間の式は、SELECT ステートメントの出力行を表します。

テーブルと同様に、SELECT のすべての出力列には名前があり、AS キーワードを介して列ごとに指定できます:

Sql

  1. SELECT 1 + 1 AS result;
  3.    result
  4. ---------------
  5. 2

注意: AS はオプションのキーワードですが、クエリの可読性や場合によっては曖昧さを助けるため、指定することが推奨されます。

名前が指定されていない場合、Elasticsearch SQL によって割り当てられます:

Sql

  1. SELECT 1 + 1;
  3.    1 + 1
  4. --------------
  5. 2

または、単純な列参照の場合、その名前を列名として使用します:

Sql

  1. SELECT emp_no FROM emp LIMIT 1;
  3.    emp_no
  4. ---------------
  5. 10001

Wildcard

ソース内のすべての列を選択するには、* を使用できます:

Sql

  1. SELECT * FROM emp LIMIT 1;
  3.    birth_date     |    emp_no     |  first_name   |    gender     |       hire_date        |   languages   |   last_name   |     name      |    salary
  4. --------------------+---------------+---------------+---------------+------------------------+---------------+---------------+---------------+---------------
  5. 1953-09-02T00:00:00Z|10001          |Georgi         |M              |1986-06-26T00:00:00.000Z|2              |Facello        |Georgi Facello |57305

これは、実質的に見つかったすべての（トップレベルフィールド、サブフィールド、マルチフィールドなどは無視されます）列を返します。

TOP

TOP 句は、SELECT リスト または <> の前に使用して、返される行の数を制限（制約）するために使用できます。形式は次のとおりです:

Sql

  1. SELECT TOP <count> <select list> ...

どこで

  • count
  • 正の整数またはゼロで、返される結果の最大可能数を示します（制限よりも少ない一致がある場合があります）。 0 が指定されている場合、結果は返されません。

Sql

  1. SELECT TOP 2 first_name, last_name, emp_no FROM emp;
  3.   first_name   |   last_name   |    emp_no
  4. ---------------+---------------+---------------
  5. Georgi         |Facello        |10001
  6. Bezalel        |Simmel         |10002

TOP と LIMIT は同じクエリで一緒に使用することはできず、そうでない場合はエラーが返されます。

FROM Clause

FROM 句は、SELECT のために1つのテーブルを指定し、次の構文を持ちます:

Sql

  1. FROM table_name [ [ AS ] alias ]

どこで:

  • table_name
  • 既存のテーブルの名前（オプションで修飾）を表し、具体的または基本的なもの（実際のインデックス）またはエイリアスです。

テーブル名に特殊なSQL文字（.、-、* など）が含まれている場合は、ダブルクォーテーションを使用してエスケープします:

Sql

  1. SELECT * FROM "emp" LIMIT 1;
  3.    birth_date     |    emp_no     |  first_name   |    gender     |       hire_date        |   languages   |   last_name   |     name      |    salary
  4. --------------------+---------------+---------------+---------------+------------------------+---------------+---------------+---------------+---------------
  5. 1953-09-02T00:00:00Z|10001          |Georgi         |M              |1986-06-26T00:00:00.000Z|2              |Facello        |Georgi Facello |57305

名前は、複数のインデックスを指す パターン である可能性があり（上記のように引用が必要な場合があります）、すべての解決された具体的なテーブルが正確なマッピングを持つ必要があります。

Sql

  1. SELECT emp_no FROM "e*p" LIMIT 1;
  3.    emp_no
  4. ---------------
  5. 10001

[プレビュー] この機能は技術プレビュー中であり、将来のリリースで変更または削除される可能性があります。Elastic は問題を修正するために作業しますが、技術プレビューの機能は公式の GA 機能のサポート SLA の対象ではありません。 クロスクラスタ検索 を実行するには、<remote_cluster>:<target> 構文を使用してクラスタ名を指定します。ここで、<remote_cluster> は SQL カタログ（クラスタ）にマッピングされ、<target> はテーブル（インデックスまたはデータストリーム）にマッピングされます。 <remote_cluster> はワイルドカード（*）をサポートし、<target> は インデックスパターン である可能性があります。

Sql

  1. SELECT emp_no FROM "my*cluster:*emp" LIMIT 1;
  3.    emp_no
  4. ---------------
  5. 10001
  • alias
  • エイリアスを含む FROM アイテムの代替名。エイリアスは簡潔さのため、または曖昧さを排除するために使用されます。エイリアスが提供されると、テーブルの実際の名前は完全に隠され、その代わりに使用する必要があります。

Sql

  1. SELECT e.emp_no FROM emp AS e LIMIT 1;
  3.    emp_no
  4. -------------
  5. 10001

WHERE Clause

オプションの WHERE 句は、クエリから行をフィルタリングするために使用され、次の構文を持ちます:

Sql

  1. WHERE condition

どこで:

  • condition
  • boolean に評価される式を表します。条件に一致する行のみが返されます（true へ）。

Sql

  1. SELECT last_name FROM emp WHERE emp_no = 10001;
  3.    last_name
  4. ---------------
  5. Facello

GROUP BY

GROUP BY 句は、指定された列からの一致する値に基づいて結果を行のグループに分割するために使用されます。次の構文を持ちます:

Sql

  1. GROUP BY grouping_element [, ...]

どこで:

  • grouping_element
  • 行がグループ化される式を表します。列名、エイリアス、列の順序番号、または列値の任意の式である可能性があります。

一般的な、グループ化する列名:

Sql

  1. SELECT gender AS g FROM emp GROUP BY gender;
  3.    g
  4. ---------------
  5. null
  6. F
  7. M

出力の順序によるグループ化:

Sql

  1. SELECT gender FROM emp GROUP BY 1;
  3.    gender
  4. ---------------
  5. null
  6. F
  7. M

エイリアスによるグループ化:

Sql

  1. SELECT gender AS g FROM emp GROUP BY g;
  3.    g
  4. ---------------
  5. null
  6. F
  7. M

または列式によるグループ化（通常はエイリアスと一緒に使用されます）:

Sql

  1. SELECT languages + 1 AS l FROM emp GROUP BY l;
  3.    l
  4. ---------------
  5. null
  6. 2
  7. 3
  8. 4
  9. 5
  10. 6

または上記の混合:

Sql

  1. SELECT gender g, languages l, COUNT(*) c FROM "emp" GROUP BY g, l ORDER BY languages ASC, gender DESC;
  3.    g       |       l       |       c
  4. ---------------+---------------+---------------
  5. M              |null           |7
  6. F              |null           |3
  7. M              |1              |9
  8. F              |1              |4
  9. null           |1              |2
  10. M              |2              |11
  11. F              |2              |5
  12. null           |2              |3
  13. M              |3              |11
  14. F              |3              |6
  15. M              |4              |11
  16. F              |4              |6
  17. null           |4              |1
  18. M              |5              |8
  19. F              |5              |9
  20. null           |5              |4

GROUP BY 句が SELECT で使用される場合、すべての出力式は集約関数またはグループ化に使用される式またはその導出でなければなりません（そうでない場合、未グループ化された列に対して返す可能性のある値が複数存在することになります）。

具体的には:

Sql

  1. SELECT gender AS g, COUNT(*) AS c FROM emp GROUP BY gender;
  3.    g       |       c
  4. ---------------+---------------
  5. null           |10
  6. F              |33
  7. M              |57

出力に使用される集約に関する式:

Sql

  1. SELECT gender AS g, ROUND((MIN(salary) / 100)) AS salary FROM emp GROUP BY gender;
  3.    g       |    salary
  4. ---------------+---------------
  5. null           |253
  6. F              |259
  7. M              |259

使用される複数の集約:

Sql

  1. SELECT gender AS g, KURTOSIS(salary) AS k, SKEWNESS(salary) AS s FROM emp GROUP BY gender;
  3.    g       |        k         |         s
  4. ---------------+------------------+-------------------
  5. null           |2.2215791166941923|-0.03373126000214023
  6. F              |1.7873117044424276|0.05504995122217512
  7. M              |2.280646181070106 |0.44302407229580243

カスタムバケットが必要な場合は、CASE を使用することで実現できます。詳細は こちら を参照してください。

Implicit Grouping

集約が関連する GROUP BY なしで使用される場合、暗黙のグループ化 が適用され、選択されたすべての行が単一のデフォルトまたは暗黙のグループを形成するものと見なされます。そのため、クエリは単一の行のみを出力します（単一のグループしかないため）。

一般的な例は、レコードの数をカウントすることです:

Sql

  1. SELECT COUNT(*) AS count FROM emp;
  3.    count
  4. ---------------
  5. 100

もちろん、複数の集約を適用できます:

Sql

  1. SELECT MIN(salary) AS min, MAX(salary) AS max, AVG(salary) AS avg, COUNT(*) AS count FROM emp;
  3.    min:i    |      max:i    |      avg:d    |     count:l
  4. ---------------+---------------+---------------+---------------
  5. 25324          |74999          |48248.55       |100

HAVING

HAVING 句は、集約関数（および GROUP BY）と共にのみ使用され、保持されるグループをフィルタリングするために使用され、次の構文を持ちます:

Sql

  1. HAVING condition

どこで:

  • condition
  • boolean に評価される式を表します。条件に一致するグループのみが返されます（true へ）。

WHERE と HAVING はフィルタリングに使用されますが、いくつかの重要な違いがあります:

  • 1. WHERE は個々の行に対して機能し、HAVING は GROUP BY によって作成されたグループに対して機能します。
  • 2. WHERE はグループ化の前に評価され、HAVING はグループ化の後に評価されます。

Sql

  1. SELECT languages AS l, COUNT(*) AS c FROM emp GROUP BY l HAVING c BETWEEN 15 AND 20;
  3.    l       |       c
  4. ---------------+---------------
  5. 1              |15
  6. 2              |19
  7. 3              |17
  8. 4              |18

さらに、HAVING 内で複数の集約式を使用することができ、出力に使用されないもの（SELECT）も含まれます:

Sql

  1. SELECT MIN(salary) AS min, MAX(salary) AS max, MAX(salary) - MIN(salary) AS diff FROM emp GROUP BY languages HAVING diff - max % min > 0 AND AVG(salary) > 30000;
  3.    min      |      max      |     diff
  4. ---------------+---------------+---------------
  5. 28336          |74999          |46663
  6. 25976          |73717          |47741
  7. 29175          |73578          |44403
  8. 26436          |74970          |48534
  9. 27215          |74572          |47357
  10. 25324          |66817          |41493

Implicit Grouping

上記のように、HAVING 句が GROUP BY なしで存在することが可能です。この場合、いわゆる 暗黙のグループ化 が適用され、選択されたすべての行が単一のグループを形成するものと見なされ、HAVING はこのグループに指定された集約関数のいずれかに適用できます。そのため、クエリは単一の行のみを出力します（単一のグループしかないため）し、HAVING 条件は、条件が失敗した場合に1行（グループ）またはゼロを返します。

この例では、HAVING が一致します:

Sql

  1. SELECT MIN(salary) AS min, MAX(salary) AS max FROM emp HAVING min > 25000;
  3.    min      |      max
  4. ---------------+---------------
  5. 25324          |74999

ORDER BY

ORDER BY 句は、SELECT の結果を1つ以上の式でソートするために使用されます:

Sql

  1. ORDER BY expression [ ASC | DESC ] [, ...]

どこで:

  • expression
  • 入力列、出力列、または出力列の位置の順序番号（1から始まる）を表します。さらに、スコアに基づいて順序を付けることもできます。指定されていない場合、方向はデフォルトで ASC（昇順）です。指定された順序に関係なく、null 値は最後（最後尾）に配置されます。

一緒に使用される場合、GROUP BY 式はグループ化または集約関数に使用される列のみにポイントできます。

たとえば、次のクエリは任意の入力フィールド（page_count）でソートします:

Sql

  1. SELECT * FROM library ORDER BY page_count DESC LIMIT 5;
  3.    author      |        name        |  page_count   |    release_date
  4. -----------------+--------------------+---------------+--------------------
  5. Peter F. Hamilton|Pandora's Star      |768            |2004-03-02T00:00:00Z
  6. Vernor Vinge     |A Fire Upon the Deep|613            |1992-06-01T00:00:00Z
  7. Frank Herbert    |Dune                |604            |1965-06-01T00:00:00Z
  8. Alastair Reynolds|Revelation Space    |585            |2000-03-15T00:00:00Z
  9. James S.A. Corey |Leviathan Wakes     |561            |2011-06-02T00:00:00Z

Order By and Grouping

グループ化を行うクエリでは、グループ化列（デフォルトは昇順）または集約関数に基づいて順序を付けることができます。

GROUP BY を使用する場合、順序付けは結果のグループをターゲットにすることを確認してください - グループ内の個々の要素に適用しても結果には影響しません。なぜなら、順序に関係なく、グループ内の値は集約されるからです。

たとえば、グループを順序付けるには、単にグループ化キーを示します:

Sql

  1. SELECT gender AS g, COUNT(*) AS c FROM emp GROUP BY gender ORDER BY g DESC;
  3.    g       |       c
  4. ---------------+---------------
  5. M              |57
  6. F              |33
  7. null           |10

もちろん、複数のキーを指定できます:

Sql

  1. SELECT gender g, languages l, COUNT(*) c FROM "emp" GROUP BY g, l ORDER BY languages ASC, gender DESC;
  3.    g       |       l       |       c
  4. ---------------+---------------+---------------
  5. M              |null           |7
  6. F              |null           |3
  7. M              |1              |9
  8. F              |1              |4
  9. null           |1              |2
  10. M              |2              |11
  11. F              |2              |5
  12. null           |2              |3
  13. M              |3              |11
  14. F              |3              |6
  15. M              |4              |11
  16. F              |4              |6
  17. null           |4              |1
  18. M              |5              |8
  19. F              |5              |9
  20. null           |5              |4

さらに、グループの値の集約に基づいてグループを順序付けることも可能です:

Sql

  1. SELECT gender AS g, MIN(salary) AS salary FROM emp GROUP BY gender ORDER BY salary DESC;
  3.    g       |    salary
  4. ---------------+---------------
  5. F              |25976
  6. M              |25945
  7. null           |25324

集約による順序付けは、メモリ消費の理由から最大10000エントリまで可能です。この閾値を超える結果がある場合は、LIMIT または TOP を使用して結果の数を減らします。

Order By Score

WHERE 句で全文検索を行うと、結果は与えられたクエリに対するスコアまたは関連性に基づいて返されることがあります。

WHERE 句で複数のテキストクエリを行う場合、それらのスコアはElasticsearchのboolクエリと同じルールを使用して組み合わされます。

score に基づいてソートするには、特別な関数 SCORE() を使用します:

Sql

  1. SELECT SCORE(), * FROM library WHERE MATCH(name, 'dune') ORDER BY SCORE() DESC;
  3.    SCORE()    |    author     |       name        |  page_count   |    release_date
  4. ---------------+---------------+-------------------+---------------+--------------------
  5. 2.2886353      |Frank Herbert  |Dune               |604            |1965-06-01T00:00:00Z
  6. 1.8893257      |Frank Herbert  |Dune Messiah       |331            |1969-10-15T00:00:00Z
  7. 1.6086556      |Frank Herbert  |Children of Dune   |408            |1976-04-21T00:00:00Z
  8. 1.4005898      |Frank Herbert  |God Emperor of Dune|454            |1981-05-28T00:00:00Z

全文検索述語を WHERE 句で使用することで SCORE() を返すことができます。これは、SCORE() がソートに使用されていない場合でも可能です:

Sql

  1. SELECT SCORE(), * FROM library WHERE MATCH(name, 'dune') ORDER BY page_count DESC;
  3.    SCORE()    |    author     |       name        |  page_count   |    release_date
  4. ---------------+---------------+-------------------+---------------+--------------------
  5. 2.2886353      |Frank Herbert  |Dune               |604            |1965-06-01T00:00:00Z
  6. 1.4005898      |Frank Herbert  |God Emperor of Dune|454            |1981-05-28T00:00:00Z
  7. 1.6086556      |Frank Herbert  |Children of Dune   |408            |1976-04-21T00:00:00Z
  8. 1.8893257      |Frank Herbert  |Dune Messiah       |331            |1969-10-15T00:00:00Z

注意: 非全文クエリから score を返そうとすると、すべての結果に対して同じ値が返されます。すべてが同等に関連しているためです。

LIMIT

LIMIT 句は、次の形式を使用して返される行の数を制限（制約）します:

Sql

  1. LIMIT ( <count> | ALL )

どこで

  • count
  • 正の整数またはゼロで、返される結果の最大可能数を示します（制限よりも少ない一致がある場合があります）。 0 が指定されている場合、結果は返されません。
  • ALL
  • 制限がないことを示し、したがってすべての結果が返されます。

Sql

  1. SELECT first_name, last_name, emp_no FROM emp LIMIT 1;
  3.   first_name   |   last_name   |    emp_no
  4. ---------------+---------------+---------------
  5. Georgi         |Facello        |10001

TOP と LIMIT は同じクエリで一緒に使用することはできず、そうでない場合はエラーが返されます。

PIVOT

PIVOT 句は、クエリの結果に対してクロスタブを実行します: 結果を集約し、行を列に回転させます。回転は、式内の1つの列からのユニークな値を - ピボット列 - 複数の列に出力します。列の値は、式で指定された残りの列の集約です。

この句は、集約、FOR- および IN-サブ句の3つの部分に分けることができます。

aggregation_expr サブ句は、ソース列の1つに適用される集約関数を含む式を指定します。現在、1つの集約のみが提供できます。

FOR-サブ句は、ピボット列を指定します: この列の異なる値が回転される候補セットになります。

IN-サブ句はフィルターを定義します: ここで提供されたセットと FOR-サブ句からの候補セットの交差が回転され、結果の最後に追加される列のヘッダーになります。フィルターはサブクエリであってはならず、事前に取得したリテラル値を提供する必要があります。

ピボット操作は、PIVOT 句で指定されていないすべてのソース列に対して暗黙の GROUP BY を実行し、IN-句を通じてフィルタリングされた値とともに実行されます。次のステートメントを考えてみてください:

Sql

  1. SELECT * FROM test_emp PIVOT (SUM(salary) FOR languages IN (1, 2)) LIMIT 5;
  3.    birth_date    |    emp_no     |  first_name   |    gender     |     hire_date       |   last_name   |       name       |       1       |       2
  4. ---------------------+---------------+---------------+---------------+---------------------+---------------+------------------+---------------+---------------
  5. null                 |10041          |Uri            |F              |1989-11-12 00:00:00.0|Lenart         |Uri Lenart        |56415          |null
  6. null                 |10043          |Yishay         |M              |1990-10-20 00:00:00.0|Tzvieli        |Yishay Tzvieli    |34341          |null
  7. null                 |10044          |Mingsen        |F              |1994-05-21 00:00:00.0|Casley         |Mingsen Casley    |39728          |null
  8. 1952-04-19 00:00:00.0|10009          |Sumant         |F              |1985-02-18 00:00:00.0|Peac           |Sumant Peac       |66174          |null
  9. 1953-01-07 00:00:00.0|10067          |Claudi         |M              |1987-03-04 00:00:00.0|Stavenow       |Claudi Stavenow   |null           |52044

クエリの実行は、次のステップに論理的に分けることができます:

  • 1. FOR-句の列に対する GROUP BY: languages;
  • 2. 結果の値は IN-句で提供されたセットを通じてフィルタリングされます;
  • 3. 現在フィルタリングされた列は、結果に追加される2つの追加列のヘッダーを形成するためにピボットされます: 1 と 2;
  • 4. test_emp のソーステーブルのすべての列に対する GROUP BY を実行し、salary（集約サブ句の一部）と languages（FOR-句の一部）を除外します;
  • 5. これらの追加列の値は、SUM の salary の集約であり、それぞれの言語でグループ化されています。

クロスタブを実行するためのテーブル値式もサブクエリの結果である可能性があります:

Sql

  1. SELECT * FROM (SELECT languages, gender, salary FROM test_emp) PIVOT (AVG(salary) FOR gender IN ('F'));
  3.    languages   |       'F'
  4. ---------------+------------------
  5. null           |62140.666666666664
  6. 1              |47073.25
  7. 2              |50684.4
  8. 3              |53660.0
  9. 4              |49291.5
  10. 5              |46705.555555555555

ピボットされた列はエイリアスを付けることができ（空白を考慮するために引用が必要）、サポートする AS トークンがあってもなくても構いません:

Sql

  1. SELECT * FROM (SELECT languages, gender, salary FROM test_emp) PIVOT (AVG(salary) FOR gender IN ('M' AS "XY", 'F' "XX"));
  3.    languages   |        XY       |        XX
  4. ---------------+-----------------+------------------
  5. null           |48396.28571428572|62140.666666666664
  6. 1              |49767.22222222222|47073.25
  7. 2              |44103.90909090909|50684.4
  8. 3              |51741.90909090909|53660.0
  9. 4              |47058.90909090909|49291.5
  10. 5              |39052.875        |46705.555555555555

結果のクロスタブにはさらに ORDER BY および LIMIT 句を適用できます:

Sql

  1. SELECT * FROM (SELECT languages, gender, salary FROM test_emp) PIVOT (AVG(salary) FOR gender IN ('F')) ORDER BY languages DESC LIMIT 4;
  2.    languages   |       'F'
  3. ---------------+------------------
  4. 5              |46705.555555555555
  5. 4              |49291.5
  6. 3              |53660.0
  7. 2              |50684.4