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> ...
其中
- 计数
- 是一个正整数或零，表示返回的可能最大结果数（因为匹配项可能少于限制）。如果指定了
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 ]
其中
-
表名 - 表示现有表的名称（可选限定），可以是具体表或基本表（实际索引）或别名。
如果表名包含特殊 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
-
别名 - 包含别名的
FROM项的替代名称。别名用于简洁或消除歧义。提供别名时，它会完全隐藏表的实际名称，并且必须使用它来代替实际名称。
SELECT e.emp_no FROM emp AS e LIMIT 1;
emp_no
-------------
10001
WHERE 子句
编辑可选的 WHERE 子句用于从查询中过滤行，并具有以下语法
WHERE condition
其中
-
条件 - 表示计算结果为
布尔值的表达式。仅返回与条件匹配（为true）的行。
SELECT last_name FROM emp WHERE emp_no = 10001; last_name --------------- Facello
GROUP BY
编辑GROUP BY 子句用于根据指定列中的匹配值将结果划分为行组。它具有以下语法
GROUP BY 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
当在 SELECT 中使用 GROUP BY 子句时，所有输出表达式必须是聚合函数或用于分组的表达式或其派生表达式（否则，对于每个未分组列，将有多个可能的返回值）。
也就是说
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
其中
-
条件 - 表示计算结果为
布尔值的表达式。仅返回与条件匹配（为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
此外，即使在输出（SELECT）中未使用，也可以在 HAVING 中使用多个聚合表达式
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 ] [, ...]
其中
-
表达式 - 表示输入列、输出列或输出列位置（从 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
Order By 和分组
编辑对于执行分组的查询，可以对分组列（默认情况下为升序）或聚合函数应用排序。
使用 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子句限制返回的行数
LIMIT ( <count> | ALL )
其中
- 计数
- 是一个正整数或零，表示返回的可能最大结果数（因为匹配项可能少于限制）。如果指定了
0，则不返回任何结果。 - ALL
- 表示没有限制，因此返回所有结果。
SELECT first_name, last_name, emp_no FROM emp LIMIT 1; first_name | last_name | emp_no ---------------+---------------+--------------- Georgi |Facello |10001
PIVOT
编辑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