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
在 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
其中
-
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 ] [, ...]
其中
-
表达式 - 表示输入列、输出列或输出列位置的序数（从 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()
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
注意：尝试从非全文查询返回 分数 将为所有结果返回相同的值，因为所有结果的相关性相同。
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
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
查询执行在逻辑上可以分解为以下步骤
- 在
FOR- 子句中的列上进行 GROUP BY：languages； - 结果值通过
IN- 子句中提供的集合进行过滤； - 现在过滤的列被旋转以形成附加到结果的两列的标题：
1和2； - 在源表
test_emp的所有列上进行 GROUP BY，但salary（聚合子句的一部分）和languages（FOR- 子句的一部分）除外； - 这些附加列中的值是
salary的SUM聚合，按相应的语言分组。
要交叉制表的表值表达式也可以是子查询的结果
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