› › ›

SELECT

Синопсис:

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

Описание: Извлекает строки из нуля или более таблиц.

Общая последовательность выполнения SELECT такова:

Все элементы в списке FROM вычисляются (каждый элемент может быть базовой или алиасной таблицей). В настоящее время FROM поддерживает ровно одну таблицу. Однако обратите внимание, что имя таблицы может быть шаблоном (см. оператор FROM ниже).
Если указан оператор WHERE, все строки, которые не удовлетворяют условию, исключаются из результата. (См. оператор WHERE ниже.)
Если указан оператор GROUP BY или присутствуют вызовы агрегатных функций, результат объединяется в группы строк, соответствующих одному или нескольким значениям, и вычисляются результаты агрегатных функций. Если присутствует оператор HAVING, он исключает группы, не удовлетворяющие заданному условию. (См. оператор GROUP BY и оператор HAVING ниже.)
Фактические выходные строки вычисляются с помощью выходных выражений SELECT для каждой выбранной строки или группы строк.
Если указан оператор ORDER BY, возвращаемые строки сортируются в указанном порядке. Если ORDER BY не указан, строки возвращаются в порядке, который система считает наиболее быстрым для получения. (См. оператор ORDER BY ниже.)
Если указаны операторы LIMIT или TOP (нельзя использовать оба в одном запросе), оператор SELECT возвращает только подмножество строк результата. (См. оператор LIMIT и оператор TOP ниже.)

Список `SELECT`

Список SELECT, а именно выражения между SELECT и FROM, представляют собой выходные строки оператора SELECT.

Как и в случае с таблицей, каждый выходной столбец оператора SELECT имеет имя, которое может быть указано для каждого столбца с помощью ключевого слова AS:

SELECT 1 + 1 AS result;

    result
---------------
2

Примечание: ключевое слово AS является необязательным, однако оно улучшает читаемость и в некоторых случаях устраняет неоднозначность запроса, поэтому его рекомендуется указывать.

присваивается Elasticsearch SQL, если имя не указано:

SELECT 1 + 1;

    1 + 1
--------------
2

или, если это просто ссылка на столбец, используйте его имя как имя столбца:

SELECT emp_no FROM emp LIMIT 1;

    emp_no
---------------
10001

Подстановка

Для выбора всех столбцов в источнике можно использовать *:

SELECT * FROM emp LIMIT 1;

     birth_date     |    emp_no     |  first_name   |    gender     |       hire_date        |   languages   |   last_name   |     name      |    salary
--------------------+---------------+---------------+---------------+------------------------+---------------+---------------+---------------+---------------
1953-09-02T00:00:00Z|10001          |Georgi         |M              |1986-06-26T00:00:00.000Z|2              |Facello        |Georgi Facello |57305

что по сути возвращает все столбцы (включая вложенные поля).

TOP

Оператор TOP может быть использован перед списком SELECT или wildcard, чтобы ограничить (ограничить) количество возвращаемых строк в формате:

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

где

count: — целое положительное число или ноль, указывающее максимальное возможное количество возвращаемых результатов (так как может быть меньше совпадений, чем ограничение). Если 0 указан, результаты не возвращаются.

SELECT TOP 2 first_name, last_name, emp_no FROM emp;

  first_name   |   last_name   |    emp_no
---------------+---------------+---------------
Georgi         |Facello        |10001
Bezalel        |Simmel         |10002

TOP и LIMIT нельзя использовать вместе в одном запросе, в противном случае возвращается ошибка.

Оператор FROM

Оператор FROM указывает одну таблицу для оператора SELECT и имеет следующий синтаксис:

FROM table_name [ [ AS ] alias ]

где:

table_name: Представляет собой имя (при необходимости с квалификатором) существующей таблицы, либо конкретной, либо базовой (фактический индекс) или алиас.

Если имя таблицы содержит специальные символы SQL (такие как .,-,* и т. д…), используйте двойные кавычки для экранирования:

SELECT * FROM "emp" LIMIT 1;

     birth_date     |    emp_no     |  first_name   |    gender     |       hire_date        |   languages   |   last_name   |     name      |    salary
--------------------+---------------+---------------+---------------+------------------------+---------------+---------------+---------------+---------------
1953-09-02T00:00:00Z|10001          |Georgi         |M              |1986-06-26T00:00:00.000Z|2              |Facello        |Georgi Facello |57305

Имя может быть шаблоном, указывающим на несколько индексов (вероятно, требующим экранирования, как упоминалось выше), с ограничением, что все разрешенные конкретные таблицы должны иметь точное соответствие.

SELECT emp_no FROM "e*p" LIMIT 1;

    emp_no
---------------
10001

[предварительный просмотр] Эта функция находится на стадии технического предварительного просмотра и может быть изменена или удалена в будущих выпусках. Elastic будет работать над устранением любых проблем, но функции, представленные в техническом предварительном просмотре, не подпадают под SLA поддержки официальных функций GA. Для запуска поиска по нескольким кластерам укажите имя кластера, используя синтаксис <remote_cluster>:<target>, где <remote_cluster> отображается на каталог SQL (кластер), а <target> — на таблицу (индекс или набор данных). <remote_cluster> поддерживает подстановки (*) и <target> может быть шаблоном индексов.

SELECT emp_no FROM "my*cluster:*emp" LIMIT 1;

    emp_no
---------------
10001

alias: Заместительное имя для элемента FROM, содержащего алиас. Алиас используется для краткости или для устранения неоднозначности. Когда указан алиас, фактическое имя таблицы полностью скрывается и должно использоваться вместо него.

SELECT e.emp_no FROM emp AS e LIMIT 1;

    emp_no
-------------
10001

Оператор WHERE

Необязательный оператор WHERE используется для фильтрации строк в запросе и имеет следующий синтаксис:

WHERE condition

где:

condition: Представляет выражение, которое вычисляется в логическое значение. Возвращаются только строки, удовлетворяющие условию (true).

SELECT last_name FROM emp WHERE emp_no = 10001;

   last_name
---------------
Facello

GROUP BY

Оператор GROUP BY используется для разделения результатов на группы строк по соответствующим значениям из указанных столбцов. Он имеет следующий синтаксис:

GROUP BY grouping_element [, ...]

где:

grouping_element: Представляет выражение, по которому группируются строки. Это может быть имя столбца, алиас или порядковый номер столбца или произвольное выражение значений столбцов.

Пример группировки по имени столбца:

SELECT gender AS g FROM emp GROUP BY gender;

       g
---------------
null
F
M

Группировка по порядковому номеру столбца:

SELECT gender FROM emp GROUP BY 1;

    gender
---------------
null
F
M

Группировка по алиасу:

SELECT gender AS g FROM emp GROUP BY g;

       g
---------------
null
F
M

И группировка по выражению столбца (обычно используется вместе с алиасом):

SELECT languages + 1 AS l FROM emp GROUP BY l;

       l
---------------
null
2
3
4
5
6

Или комбинация вышеперечисленного:

SELECT gender g, languages l, COUNT(*) c FROM "emp" GROUP BY g, l ORDER BY languages ASC, gender DESC;

       g       |       l       |       c
---------------+---------------+---------------
M              |null           |7
F              |null           |3
M              |1              |9
F              |1              |4
null           |1              |2
M              |2              |11
F              |2              |5
null           |2              |3
M              |3              |11
F              |3              |6
M              |4              |11
F              |4              |6
null           |4              |1
M              |5              |8
F              |5              |9
null           |5              |4

Когда оператор GROUP BY используется в операторе SELECT, все выражения вывода должны быть либо агрегатными функциями, либо выражениями, используемыми для группировки, или производными от них (иначе было бы несколько возможных значений для возврата для каждого столбца без группировки).

Например:

SELECT gender AS g, COUNT(*) AS c FROM emp GROUP BY gender;

       g       |       c
---------------+---------------
null           |10
F              |33
M              |57

Выражения над агрегатами, используемые в выводе:

SELECT gender AS g, ROUND((MIN(salary) / 100)) AS salary FROM emp GROUP BY gender;

       g       |    salary
---------------+---------------
null           |253
F              |259
M              |259

Несколько агрегатов, используемых:

SELECT gender AS g, KURTOSIS(salary) AS k, SKEWNESS(salary) AS s FROM emp GROUP BY gender;

       g       |        k         |         s
---------------+------------------+-------------------
null           |2.2215791166941923|-0.03373126000214023
F              |1.7873117044424276|0.05504995122217512
M              |2.280646181070106 |0.44302407229580243

Если требуется настройка группировки, это можно сделать с помощью CASE, как показано здесь.

Неявная группировка

Когда агрегация используется без связанного оператора GROUP BY, применяется неявная группировка, что означает, что все выбранные строки рассматриваются как образующие одну группу по умолчанию или неявную группу. Таким образом, запрос выдает только одну строку (так как есть только одна группа).

Пример подсчёта количества записей:

SELECT COUNT(*) AS count FROM emp;

     count
---------------
100

Конечно, можно применять несколько агрегаций:

SELECT MIN(salary) AS min, MAX(salary) AS max, AVG(salary) AS avg, COUNT(*) AS count FROM emp;

      min:i    |      max:i    |      avg:d    |     count:l
---------------+---------------+---------------+---------------
25324          |74999          |48248.55       |100

HAVING

Оператор HAVING может использоваться только вместе с агрегационными функциями (и, следовательно, GROUP BY) для фильтрации групп, которые будут включены или исключены, и имеет следующий синтаксис:

HAVING condition

где:

condition: Представляет выражение, которое вычисляется в boolean. Возвращаются только группы, которые удовлетворяют условию (чтобы true).

Оба WHERE и HAVING используются для фильтрации, однако существуют некоторые значительные различия между ними:

WHERE работает с отдельными строками, а HAVING работает с группами, созданными оператором ``GROUP BY``
WHERE вычисляется до группировки, а HAVING вычисляется после группировки

SELECT languages AS l, COUNT(*) AS c FROM emp GROUP BY l HAVING c BETWEEN 15 AND 20;

       l       |       c
---------------+---------------
1              |15
2              |19
3              |17
4              |18

Кроме того, можно использовать несколько агрегационных выражений внутри HAVING, даже те, которые не используются в выводе (SELECT):

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;

      min      |      max      |     diff
---------------+---------------+---------------
28336          |74999          |46663
25976          |73717          |47741
29175          |73578          |44403
26436          |74970          |48534
27215          |74572          |47357
25324          |66817          |41493

Неявная группировка

Как указано выше, можно использовать оператор HAVING без оператора GROUP BY. В этом случае применяется так называемая неявная группировка, что означает, что все выбранные строки рассматриваются как образующие одну группу, и HAVING может применяться к любой из указанных агрегационных функций в этой группе. Таким образом, запрос генерирует только одну строку (так как существует только одна группа), и условие HAVING возвращает либо одну строку (группу), либо ноль, если условие не выполняется.

В этом примере, HAVING соответствует:

SELECT MIN(salary) AS min, MAX(salary) AS max FROM emp HAVING min > 25000;

      min      |      max
---------------+---------------
25324          |74999

ORDER BY

Оператор ORDER BY используется для сортировки результатов SELECT по одному или нескольким выражениям:

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

где:

expression: Представляет входной столбец, выходной столбец или порядковый номер позиции (начиная с единицы) выходного столбца. Кроме того, сортировка может быть выполнена на основе результатов оценки. Если направление не указано, по умолчанию используется ASC (возрастание). Независимо от указанной сортировки, значения NULL упорядочиваются последними (в конце).

Когда используется вместе с GROUP BY выражение может ссылаться только на столбцы, используемые для группировки или агрегационных функций.

Например, следующий запрос сортирует по произвольному входному полю (page_count):

SELECT * FROM library ORDER BY page_count DESC LIMIT 5;

     author      |        name        |  page_count   |    release_date
-----------------+--------------------+---------------+--------------------
Peter F. Hamilton|Pandora's Star      |768            |2004-03-02T00:00:00Z
Vernor Vinge     |A Fire Upon the Deep|613            |1992-06-01T00:00:00Z
Frank Herbert    |Dune                |604            |1965-06-01T00:00:00Z
Alastair Reynolds|Revelation Space    |585            |2000-03-15T00:00:00Z
James S.A. Corey |Leviathan Wakes     |561            |2011-06-02T00:00:00Z

Сортировка по группам и группировка

Для запросов, выполняющих группировку, сортировка может применяться либо к столбцам группировки (по умолчанию по возрастанию), либо к агрегационным функциям.

При использовании GROUP BY, убедитесь, что сортировка относится к результатам группировки. Применение к отдельным элементам внутри группы не повлияет на результаты, так как независимо от порядка значения внутри группы агрегируются.

Например, для сортировки групп просто укажите ключ группировки:

SELECT gender AS g, COUNT(*) AS c FROM emp GROUP BY gender ORDER BY g DESC;

       g       |       c
---------------+---------------
M              |57
F              |33
null           |10

Конечно, можно указать несколько ключей:

SELECT gender g, languages l, COUNT(*) c FROM "emp" GROUP BY g, l ORDER BY languages ASC, gender DESC;

       g       |       l       |       c
---------------+---------------+---------------
M              |null           |7
F              |null           |3
M              |1              |9
F              |1              |4
null           |1              |2
M              |2              |11
F              |2              |5
null           |2              |3
M              |3              |11
F              |3              |6
M              |4              |11
F              |4              |6
null           |4              |1
M              |5              |8
F              |5              |9
null           |5              |4

Кроме того, можно отсортировать группы на основе агрегации их значений:

SELECT gender AS g, MIN(salary) AS salary FROM emp GROUP BY gender ORDER BY salary DESC;

       g       |    salary
---------------+---------------
F              |25976
M              |25945
null           |25324

Сортировка по агрегации возможна для до 10000 записей из-за ограничений по потреблению памяти. В тех случаях, когда результаты превышают этот порог, используйте LIMIT или TOP для уменьшения числа результатов.

Сортировка по оценке

При выполнении полнотекстовых запросов в операторе WHERE результаты могут возвращаться на основе их оценки или релевантности заданному запросу.

При выполнении нескольких полнотекстовых запросов в операторе WHERE, их оценки будут объединены по тем же правилам, что и в bool query Elasticsearch.

Для сортировки по score, используйте специальную функцию SCORE():

SELECT SCORE(), * FROM library WHERE MATCH(name, 'dune') ORDER BY SCORE() DESC;

    SCORE()    |    author     |       name        |  page_count   |    release_date
---------------+---------------+-------------------+---------------+--------------------
2.2886353      |Frank Herbert  |Dune               |604            |1965-06-01T00:00:00Z
1.8893257      |Frank Herbert  |Dune Messiah       |331            |1969-10-15T00:00:00Z
1.6086556      |Frank Herbert  |Children of Dune   |408            |1976-04-21T00:00:00Z
1.4005898      |Frank Herbert  |God Emperor of Dune|454            |1981-05-28T00:00:00Z

Обратите внимание, что вы можете возвращать SCORE(), используя предикат полнотекстового поиска в операторе WHERE. Это возможно даже если SCORE() не используется для сортировки:

SELECT SCORE(), * FROM library WHERE MATCH(name, 'dune') ORDER BY page_count DESC;

    SCORE()    |    author     |       name        |  page_count   |    release_date
---------------+---------------+-------------------+---------------+--------------------
2.2886353      |Frank Herbert  |Dune               |604            |1965-06-01T00:00:00Z
1.4005898      |Frank Herbert  |God Emperor of Dune|454            |1981-05-28T00:00:00Z
1.6086556      |Frank Herbert  |Children of Dune   |408            |1976-04-21T00:00:00Z
1.8893257      |Frank Herbert  |Dune Messiah       |331            |1969-10-15T00:00:00Z

ПРИМЕЧАНИЕ: Попытка вернуть score из запроса, не использующего полнотекстовый поиск, вернет одно и то же значение для всех результатов, поскольку все результаты одинаково релевантны.

LIMIT

Оператор LIMIT ограничивает (ограничивает) количество возвращаемых строк с помощью формата:

LIMIT ( <count> | ALL )

где

count: — целое положительное число или ноль, указывающее максимальное возможное количество возвращаемых результатов (так как количество совпадений может быть меньше предела). Если 0 указан, результаты не возвращаются.
ALL: указывает, что нет ограничений, и поэтому возвращаются все результаты.

SELECT first_name, last_name, emp_no FROM emp LIMIT 1;

  first_name   |   last_name   |    emp_no
---------------+---------------+---------------
Georgi         |Facello        |10001

TOP и LIMIT не могут использоваться вместе в одном запросе, в противном случае возвращается ошибка.

PIVOT

Вставка PIVOT выполняет перекрёстную сводку результатов запроса: она агрегирует результаты и поворачивает строки в столбцы. Поворот выполняется путём превращения уникальных значений одного столбца в выражении — столбца поворота — в несколько столбцов в выводе. Значения столбцов являются агрегированными по остальным столбцам, указанным в выражении.

Вставка можно разбить на три части: агрегирование, FOR и IN подзапросы.

Подзапрос aggregation_expr определяет выражение, содержащее функцию агрегирования, которая должна применяться к одному из столбцов источника. В настоящее время может быть указана только одна агрегация.

Подзапрос FOR определяет столбец поворота: различные значения этого столбца станут кандидатами для поворота.

Подзапрос IN определяет фильтр: пересечение набора, предоставленного здесь, и набора кандидатов из подзапроса FOR будет повернуто, чтобы стать заголовками столбцов, добавленных к конечному результату. Фильтр не может быть подзапросом, здесь должны быть указаны литеральные значения, полученные заранее.

Операция поворота выполнит неявное GROUP BY по всем столбцам источника, не указанным в подзапросе PIVOT, а также значения, отфильтрованные через подзапрос IN. Рассмотрим следующее утверждение:

SELECT * FROM test_emp PIVOT (SUM(salary) FOR languages IN (1, 2)) LIMIT 5;

       birth_date    |    emp_no     |  first_name   |    gender     |     hire_date       |   last_name   |       name       |       1       |       2
---------------------+---------------+---------------+---------------+---------------------+---------------+------------------+---------------+---------------
null                 |10041          |Uri            |F              |1989-11-12 00:00:00.0|Lenart         |Uri Lenart        |56415          |null
null                 |10043          |Yishay         |M              |1990-10-20 00:00:00.0|Tzvieli        |Yishay Tzvieli    |34341          |null
null                 |10044          |Mingsen        |F              |1994-05-21 00:00:00.0|Casley         |Mingsen Casley    |39728          |null
1952-04-19 00:00:00.0|10009          |Sumant         |F              |1985-02-18 00:00:00.0|Peac           |Sumant Peac       |66174          |null
1953-01-07 00:00:00.0|10067          |Claudi         |M              |1987-03-04 00:00:00.0|Stavenow       |Claudi Stavenow   |null           |52044

Выполнение запроса можно логически разбить на следующие шаги:

GROUP BY по столбцу в подзапросе FOR: languages;
полученные значения фильтруются через набор, предоставленный в подзапросе IN;
теперь отфильтрованный столбец поворачивается, образуя заголовки двух дополнительных столбцов, добавленных к результату: 1 и 2;
GROUP BY по всем столбцам исходной таблицы test_emp, за исключением salary (часть подзапроса агрегирования) и languages (часть подзапроса FOR);
значения в этих добавленных столбцах являются SUM агрегациями salary, сгруппированными по соответствующему языку.

Выражение с табличным значением для перекрёстной сводки также может быть результатом подзапроса:

SELECT * FROM (SELECT languages, gender, salary FROM test_emp) PIVOT (AVG(salary) FOR gender IN ('F'));

   languages   |       'F'
---------------+------------------
null           |62140.666666666664
1              |47073.25
2              |50684.4
3              |53660.0
4              |49291.5
5              |46705.555555555555

Повернутые столбцы могут быть переименованы (и требуется цитирование для учёта пробелов), с помощью или без поддержки токена AS:

SELECT * FROM (SELECT languages, gender, salary FROM test_emp) PIVOT (AVG(salary) FOR gender IN ('M' AS "XY", 'F' "XX"));

   languages   |        XY       |        XX
---------------+-----------------+------------------
null           |48396.28571428572|62140.666666666664
1              |49767.22222222222|47073.25
2              |44103.90909090909|50684.4
3              |51741.90909090909|53660.0
4              |47058.90909090909|49291.5
5              |39052.875        |46705.555555555555

Полученная перекрёстная сводка может иметь применённые вставки ORDER BY и LIMIT:

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

© 2023-2025 Elasticsearch
As of September 2024, Elasticsearch is available under a choice of three licenses: the Server Side Public License (SSPL), the Elastic License, or the AGPLv3 (OSI approved).
Elasticsearch and the Elasticsearch logo are trademarks of Elasticsearch B.V., registered in the U.S. and in other countries.
https://www.elastic.co/guide/en/elasticsearch/reference/8.17/sql-syntax-select.html

SELECT

Список SELECT

Подстановка

TOP

Оператор FROM

Оператор WHERE

GROUP BY

Неявная группировка

HAVING

Неявная группировка

ORDER BY

Сортировка по группам и группировка

Сортировка по оценке

LIMIT

PIVOT

Список `SELECT`