SELECT
编辑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 列表 或 <<sql-syntax-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
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 将努力解决任何问题，但技术预览版中的功能不受官方 GA 功能的支持 SLA 的约束。 要运行跨集群搜索，请使用 <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 - 表示求值为
boolean的表达式。仅返回与条件匹配（为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
隐式分组
编辑当在没有关联的 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
隐式分组
编辑如上所述，可以在没有 GROUP BY 的情况下使用 HAVING 子句。在这种情况下，将应用所谓的 隐式分组，这意味着所有选定的行都被认为形成一个组，并且 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 - 表示输入列、输出列或输出列位置的序号（从 1 开始）。此外，排序可以基于结果的分数进行。如果未指定方向，则默认方向为
ASC（升序）。无论指定的排序方式如何，空值都将排在最后（末尾）。
当与 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
按分数排序
编辑在 WHERE 子句中执行全文查询时，可以根据结果的 分数 或与给定查询的相关性返回结果。
如果在 WHERE 子句中执行多个文本查询，则它们的分数将使用与 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
请注意，您可以通过在 WHERE 子句中使用全文搜索谓词来返回 SCORE()。即使不使用 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 ( <count> | ALL )
其中
- count
- 是一个正整数或零，指示返回的最大可能结果数（因为可能存在少于限制的匹配项）。如果指定了
0，则不返回任何结果。 - 全部
- 表示没有限制，因此将返回所有结果。
SELECT first_name, last_name, emp_no FROM emp LIMIT 1; first_name | last_name | emp_no ---------------+---------------+--------------- Georgi |Facello |10001
透视
编辑PIVOT 子句对查询结果执行交叉制表：它聚合结果并将行旋转为列。旋转通过将表达式中一列（透视列）的唯一值转换为输出中的多个列来完成。列值是表达式中指定的其余列的聚合。
该子句可以分为三个部分：聚合、FOR- 和 IN-子句。
aggregation_expr 子句指定一个表达式，该表达式包含要应用于源列之一的 聚合函数。目前，只能提供一个聚合。
FOR-子句指定透视列：此列的不同值将成为要旋转的值的候选集。
IN-子句定义一个过滤器：此处提供的集合与 FOR-子句中的候选集之间的交集将旋转以成为附加到最终结果的列的标题。过滤器不能是子查询，必须在此处提供预先获得的文字值。
透视操作将对未在 PIVOT 子句中指定的所有源列以及通过 IN-子句过滤的值执行隐式 GROUP BY。考虑以下语句
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
查询执行可以逻辑地分解为以下步骤
要交叉制表的表值表达式也可以是子查询的结果
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