« SQL Client Applications Functions and Operators »

› ›

SQL 语言

edit

SQL 语言

edit

本章描述了支持的SQL语法和语义，即：

Lexical Structure: 词法结构
SQL Commands: 命令
Data Types: 数据类型
Index patterns: 索引模式

词法结构

edit

本节涵盖了SQL的主要词法结构，它在很大程度上与ANSI SQL本身相似，因此低级细节没有深入讨论。

Elasticsearch SQL 目前一次只接受一个命令。命令是由一系列标记组成的，以输入流的结束为终止。

一个标记可以是关键字、一个标识符（带引号的或不带引号的）、一个字面量（或常量）或一个特殊字符符号（通常是一个分隔符）。标记通常由空白字符（无论是空格、制表符）分隔，尽管在某些情况下，由于没有歧义（通常是由于字符符号），这并不是必需的——然而，为了可读性，应避免这种情况。

关键字

edit

以下是一个例子：

SELECT * FROM table

这个查询有四个标记：SELECT、*、FROM 和 table。前三个，即 SELECT、* 和 FROM 是 关键词，表示在 SQL 中具有固定含义的词。标记 table 是一个 标识符，表示它通过名称标识 SQL 中的实体，例如表（在这种情况下）、列等……

如您所见，关键字和标识符具有相同的词法结构，因此如果不了解SQL语言，就无法知道一个标记是关键字还是标识符；关键字的完整列表可以在保留附录中找到。请注意，关键字是不区分大小写的，这意味着前面的示例可以写成：

select * fRoM table;

然而，标识符不是这样的 - 因为 Elasticsearch 是区分大小写的，Elasticsearch SQL 会原封不动地使用接收到的值。

为了帮助区分这两者，在整个文档中，SQL关键字都使用了大写，我们发现这种约定可以提高可读性，因此推荐给其他人。

标识符

edit

标识符可以是两种类型：带引号的和不带引号的：

SELECT ip_address FROM "hosts-*"

这个查询有两个标识符，ip_address 和 hosts-*（一个索引模式）。由于 ip_address 不与任何关键字冲突，因此可以直接使用，而 hosts-* 则不能，因为它与 -（减法操作）和 * 冲突，因此需要使用双引号。

另一个例子：

SELECT "from" FROM "<logstash-{now/d}>"

第一个标识符需要加上引号，否则它会与FROM关键字冲突（该关键字不区分大小写，因此可以写为from），而第二个标识符使用Elasticsearch的索引和索引别名中的日期数学支持可能会使解析器混淆。

因此，一般来说，特别是在处理用户输入时，强烈建议为标识符使用引号。它对查询的增加最小，但提供了清晰性和消除歧义。

字面量 (常量)

edit

Elasticsearch SQL 支持两种隐式类型的文字：字符串和数字。

字符串字面量

edit

字符串字面量是由单引号 ' 包围的任意数量的字符：'Giant Robot'。要在字符串中包含单引号，请使用另一个单引号进行转义：'Captain EO''s Voyage'。

一个转义的单引号是不一个双引号 (")，而是一个单引号 ' 重复 ('')。

数值字面量

edit

数值字面量可以接受十进制和科学记数法，带有指数标记（e 或 E），可以以数字或小数点 . 开头：

1969    -- integer notation
3.14    -- decimal notation
.1234   -- decimal notation starting with decimal point
4E5     -- scientific notation (with exponent marker)
1.2e-3  -- scientific notation with decimal point

包含小数点的数值字面量始终被解释为double类型。不包含小数点的数值字面量如果符合条件则被视为integer类型，否则它们的类型为long（或ANSI SQL类型中的BIGINT）。

通用字面量

edit

当处理任意类型字面量时，通常通过将字符串表示形式转换为所需类型来创建对象。这可以通过专门的转换操作符和函数来实现：

123::LONG                                   -- cast 123 to a LONG
CAST('1969-05-13T12:34:56' AS TIMESTAMP)    -- cast the given string to datetime
CONVERT('10.0.0.1', IP)                     -- cast '10.0.0.1' to an IP

请注意，Elasticsearch SQL 提供了一些函数，这些函数可以直接返回常用的字面量（如 E()）或为某些字符串提供专门的解析。

单引号与双引号

edit

值得指出的是，在SQL中，单引号 ' 和双引号 " 具有不同的含义，并且不能互换使用。单引号用于声明一个字符串字面量，而双引号用于标识符。

即：

SELECT "first_name" 
  FROM "musicians"  
 WHERE "last_name"  
     = 'Carroll'

	双引号 `"` 用于列和表标识符
	单引号 `'` 用于字符串字面量

要转义单引号或双引号，需要再使用一次该特定引号。例如，字面量 John's 可以转义为 SELECT 'John''s' AS name。对于双引号的转义也是如此 - SELECT 123 AS "test""number" 将显示为一个名为 test"number 的列。

特殊字符

edit

一些非字母数字的字符具有不同于运算符的专门含义。为了完整性，这些字符如下所示：

字符	描述
`*`	星号（或通配符）在某些上下文中用于表示表中的所有字段。也可以作为某些聚合函数的参数使用。
`,`	逗号用于枚举列表的元素。
`.`	用于数值常量中或分隔标识符限定符（目录、表、列名等）。
`()`	括号用于特定的SQL命令、函数声明或强制执行优先级。

运算符

edit

Elasticsearch SQL 中的大多数运算符具有相同的优先级并且是左结合的。由于这是在解析时完成的，因此需要使用括号来强制执行不同的优先级。

下表列出了支持的运算符及其优先级（从高到低）；

操作符/元素	结合性	描述
`.`	左	限定符分隔符
`::`	左	PostgreSQL 风格的类型转换
`+ -`	正确	一元加和减（数值字面量符号）
`* / %`	左	乘法、除法、取模
`+ -`	左	加法, 减法
`BETWEEN IN LIKE`		范围包含，字符串匹配
`< > <= >= = <=> <> !=`		比较
`NOT`	正确	逻辑非
`AND`	左	逻辑与
`或`	左	逻辑析取

注释

edit

Elasticsearch SQL 允许注释，这些注释是一系列被解析器忽略的字符。

支持两种样式：

Single Line: 注释以双破折号 -- 开头，并持续到行尾。
Multi line: 以/*开头并以*/结尾的注释（也称为C风格）。

-- single line comment
/* multi
   line
   comment
   that supports /* nested comments */
   */

SQL 命令

edit

本节包含Elasticsearch SQL支持的SQL命令列表及其语法：

DESCRIBE TABLE: 描述一个表格。
SELECT: 从零个或多个表中检索行。
SHOW CATALOGS: 列出可用的目录。
SHOW COLUMNS: 列出表中的列。
SHOW FUNCTIONS: 列出支持的函数。
SHOW TABLES: 列出可用的表。

描述表

edit

概要：

DESCRIBE | DESC
    [CATALOG identifier]? 
    [INCLUDE FROZEN]?     
    [table_identifier |   
     LIKE pattern]

	目录（集群）标识符。支持通配符（`*`）。
	是否包含冻结的索引。
	单表（索引或数据流）标识符或双引号的多目标模式。
	SQL LIKE 模式匹配表名。

描述: DESC 和 DESCRIBE 是 SHOW COLUMNS 的别名。

DESCRIBE emp;

       column       |     type      |    mapping
--------------------+---------------+---------------
birth_date          |TIMESTAMP      |datetime
dep                 |STRUCT         |nested
dep.dep_id          |VARCHAR        |keyword
dep.dep_name        |VARCHAR        |text
dep.dep_name.keyword|VARCHAR        |keyword
dep.from_date       |TIMESTAMP      |datetime
dep.to_date         |TIMESTAMP      |datetime
emp_no              |INTEGER        |integer
first_name          |VARCHAR        |text
first_name.keyword  |VARCHAR        |keyword
gender              |VARCHAR        |keyword
hire_date           |TIMESTAMP      |datetime
languages           |TINYINT        |byte
last_name           |VARCHAR        |text
last_name.keyword   |VARCHAR        |keyword
name                |VARCHAR        |keyword
salary              |INTEGER        |integer

选择

edit

概要：

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` 列表

edit

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

通配符

edit

要选择源中的所有列，可以使用 *：

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

它基本上返回所有（顶级字段、子字段，例如多字段被忽略）找到的列。

顶部

edit

可以使用TOP子句在SELECT列表或<通配符>之前，以使用以下格式限制（限制）返回的行数：

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 子句

edit

The 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 的约束。要运行跨集群搜索，请使用 : 语法指定集群名称，其中映射到 SQL 目录（集群），映射到表（索引或数据流）。支持通配符 (*)，并且可以是索引模式。

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 子句

edit

可选的 WHERE 子句用于从查询中过滤行，并具有以下语法：

WHERE condition

其中：

condition: 表示一个计算结果为布尔值的表达式。只有符合条件（结果为真）的行才会被返回。

SELECT last_name FROM emp WHERE emp_no = 10001;

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

GROUP BY

edit

The 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

如果需要自定义分桶，可以通过使用CASE来实现，如这里所示。

隐式分组

edit

当使用聚合而没有关联的 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

edit

可以使用HAVING子句仅与聚合函数（因此与GROUP BY）一起使用，以过滤保留或不保留哪些组，并且具有以下语法：

HAVING condition

其中：

condition: 表示一个计算结果为布尔值的表达式。只有满足条件（结果为真）的组才会被返回。

Both WHERE and 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

隐式分组

edit

如上所述，可以有一个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

edit

The ORDER BY 子句用于根据一个或多个表达式对 SELECT 的结果进行排序：

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

其中：

expression: 表示一个输入列、一个输出列或输出列的位置序号（从一开始）。此外，可以根据结果的分数进行排序。如果未指定方向，默认情况下为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

排序和分组

edit

对于执行分组的查询，可以对分组列（默认升序）或聚合函数应用排序。

使用 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 来减少结果的数量。

按分数排序

edit

在 WHERE 子句中进行全文查询时，结果可以根据其与给定查询的分数或相关性返回。

当在 WHERE 子句中进行多个文本查询时，它们的分数将按照与 Elasticsearch 的 bool 查询相同的规则进行组合。

要基于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将返回所有结果的相同值，因为所有结果的相关性是相同的。

限制

edit

The 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 不能在同一条查询中一起使用，否则会返回错误。

透视

edit

The PIVOT 子句对查询结果执行交叉表操作：它聚合结果并将行旋转为列。旋转是通过将表达式中某一列的唯一值（旋转列）转换为输出中的多个列来完成的。列值是对表达式中指定的其余列的聚合。

该子句可以分为三个部分：聚合、FOR子句和IN子句。

子句 aggregation_expr 指定一个包含聚合函数的表达式，该表达式将应用于源列之一。目前只能提供一个聚合。

The FOR-子句指定透视列：该列的不同值将成为要旋转的候选值集。

The IN-子句定义了一个过滤器：此处提供的集合与FOR-子句中的候选集合的交集将被旋转以成为附加到最终结果的列的标题。过滤器不能是子查询，必须在此处提供预先获取的文字值。

透视操作将对PIVOT子句中未指定的所有源列执行隐式的GROUP BY，以及通过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

查询执行可以逻辑上分解为以下步骤：

对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

显示目录

edit

概要：

SHOW CATALOGS

描述: 列出可用的目录及其类型。

SHOW CATALOGS;

     name         |     type
------------------+---------------
javaRestTest         |local
my_remote_cluster |remote

显示列

edit

概要：

SHOW COLUMNS
    [CATALOG identifier]? 
    [INCLUDE FROZEN]?     
    [FROM | IN]
    [table_identifier |   
     LIKE pattern]

	目录（集群）标识符。支持通配符（`*`）。
	是否包含冻结的索引。
	单表（索引或数据流）标识符或双引号的多目标模式。
	SQL LIKE 模式匹配表名。

有关模式的更多信息，请参见索引模式。

描述: 列出表中的列及其数据类型（和其他属性）。

SHOW COLUMNS IN emp;

       column       |     type      |    mapping
--------------------+---------------+---------------
birth_date          |TIMESTAMP      |datetime
dep                 |STRUCT         |nested
dep.dep_id          |VARCHAR        |keyword
dep.dep_name        |VARCHAR        |text
dep.dep_name.keyword|VARCHAR        |keyword
dep.from_date       |TIMESTAMP      |datetime
dep.to_date         |TIMESTAMP      |datetime
emp_no              |INTEGER        |integer
first_name          |VARCHAR        |text
first_name.keyword  |VARCHAR        |keyword
gender              |VARCHAR        |keyword
hire_date           |TIMESTAMP      |datetime
languages           |TINYINT        |byte
last_name           |VARCHAR        |text
last_name.keyword   |VARCHAR        |keyword
name                |VARCHAR        |keyword
salary              |INTEGER        |integer

显示函数

edit

概要：

SHOW FUNCTIONS [LIKE pattern]?

SQL匹配模式

描述: 列出所有SQL函数及其类型。可以使用LIKE子句将名称列表限制为给定的模式。

SHOW FUNCTIONS;

      name       |     type
-----------------+---------------
AVG              |AGGREGATE
COUNT            |AGGREGATE
FIRST            |AGGREGATE
FIRST_VALUE      |AGGREGATE
LAST             |AGGREGATE
LAST_VALUE       |AGGREGATE
MAX              |AGGREGATE
MIN              |AGGREGATE
SUM              |AGGREGATE
KURTOSIS         |AGGREGATE
MAD              |AGGREGATE
PERCENTILE       |AGGREGATE
PERCENTILE_RANK  |AGGREGATE
SKEWNESS         |AGGREGATE
STDDEV_POP       |AGGREGATE
STDDEV_SAMP      |AGGREGATE
SUM_OF_SQUARES   |AGGREGATE
VAR_POP          |AGGREGATE
VAR_SAMP         |AGGREGATE
HISTOGRAM        |GROUPING
CASE             |CONDITIONAL
COALESCE         |CONDITIONAL
GREATEST         |CONDITIONAL
IFNULL           |CONDITIONAL
IIF              |CONDITIONAL
ISNULL           |CONDITIONAL
LEAST            |CONDITIONAL
NULLIF           |CONDITIONAL
NVL              |CONDITIONAL
CURDATE          |SCALAR
CURRENT_DATE     |SCALAR
CURRENT_TIME     |SCALAR
CURRENT_TIMESTAMP|SCALAR
CURTIME          |SCALAR
DATEADD          |SCALAR
DATEDIFF         |SCALAR
DATEPART         |SCALAR
DATETIME_FORMAT  |SCALAR
DATETIME_PARSE   |SCALAR
DATETRUNC        |SCALAR
DATE_ADD         |SCALAR
DATE_DIFF        |SCALAR
DATE_FORMAT      |SCALAR
DATE_PARSE       |SCALAR
DATE_PART        |SCALAR
DATE_TRUNC       |SCALAR
DAY              |SCALAR
DAYNAME          |SCALAR
DAYOFMONTH       |SCALAR
DAYOFWEEK        |SCALAR
DAYOFYEAR        |SCALAR
DAY_NAME         |SCALAR
DAY_OF_MONTH     |SCALAR
DAY_OF_WEEK      |SCALAR
DAY_OF_YEAR      |SCALAR
DOM              |SCALAR
DOW              |SCALAR
DOY              |SCALAR
FORMAT           |SCALAR
HOUR             |SCALAR
HOUR_OF_DAY      |SCALAR
IDOW             |SCALAR
ISODAYOFWEEK     |SCALAR
ISODOW           |SCALAR
ISOWEEK          |SCALAR
ISOWEEKOFYEAR    |SCALAR
ISO_DAY_OF_WEEK  |SCALAR
ISO_WEEK_OF_YEAR |SCALAR
IW               |SCALAR
IWOY             |SCALAR
MINUTE           |SCALAR
MINUTE_OF_DAY    |SCALAR
MINUTE_OF_HOUR   |SCALAR
MONTH            |SCALAR
MONTHNAME        |SCALAR
MONTH_NAME       |SCALAR
MONTH_OF_YEAR    |SCALAR
NOW              |SCALAR
QUARTER          |SCALAR
SECOND           |SCALAR
SECOND_OF_MINUTE |SCALAR
TIMESTAMPADD     |SCALAR
TIMESTAMPDIFF    |SCALAR
TIMESTAMP_ADD    |SCALAR
TIMESTAMP_DIFF   |SCALAR
TIME_PARSE       |SCALAR
TODAY            |SCALAR
TO_CHAR          |SCALAR
WEEK             |SCALAR
WEEK_OF_YEAR     |SCALAR
YEAR             |SCALAR
ABS              |SCALAR
ACOS             |SCALAR
ASIN             |SCALAR
ATAN             |SCALAR
ATAN2            |SCALAR
CBRT             |SCALAR
CEIL             |SCALAR
CEILING          |SCALAR
COS              |SCALAR
COSH             |SCALAR
COT              |SCALAR
DEGREES          |SCALAR
E                |SCALAR
EXP              |SCALAR
EXPM1            |SCALAR
FLOOR            |SCALAR
LOG              |SCALAR
LOG10            |SCALAR
MOD              |SCALAR
PI               |SCALAR
POWER            |SCALAR
RADIANS          |SCALAR
RAND             |SCALAR
RANDOM           |SCALAR
ROUND            |SCALAR
SIGN             |SCALAR
SIGNUM           |SCALAR
SIN              |SCALAR
SINH             |SCALAR
SQRT             |SCALAR
TAN              |SCALAR
TRUNC            |SCALAR
TRUNCATE         |SCALAR
ASCII            |SCALAR
BIT_LENGTH       |SCALAR
CHAR             |SCALAR
CHARACTER_LENGTH |SCALAR
CHAR_LENGTH      |SCALAR
CONCAT           |SCALAR
INSERT           |SCALAR
LCASE            |SCALAR
LEFT             |SCALAR
LENGTH           |SCALAR
LOCATE           |SCALAR
LTRIM            |SCALAR
OCTET_LENGTH     |SCALAR
POSITION         |SCALAR
REPEAT           |SCALAR
REPLACE          |SCALAR
RIGHT            |SCALAR
RTRIM            |SCALAR
SPACE            |SCALAR
STARTS_WITH      |SCALAR
SUBSTRING        |SCALAR
TRIM             |SCALAR
UCASE            |SCALAR
CAST             |SCALAR
CONVERT          |SCALAR
DATABASE         |SCALAR
USER             |SCALAR
ST_ASTEXT        |SCALAR
ST_ASWKT         |SCALAR
ST_DISTANCE      |SCALAR
ST_GEOMETRYTYPE  |SCALAR
ST_GEOMFROMTEXT  |SCALAR
ST_WKTTOSQL      |SCALAR
ST_X             |SCALAR
ST_Y             |SCALAR
ST_Z             |SCALAR
SCORE            |SCORE

返回的函数列表可以根据模式进行自定义。

它可以是精确匹配：

SHOW FUNCTIONS LIKE 'ABS';

     name      |     type
---------------+---------------
ABS            |SCALAR

一个表示确切一个字符的通配符：

SHOW FUNCTIONS LIKE 'A__';

     name      |     type
---------------+---------------
AVG            |AGGREGATE
ABS            |SCALAR

匹配零个或多个字符的通配符：

SHOW FUNCTIONS LIKE 'A%';

     name      |     type
---------------+---------------
AVG            |AGGREGATE
ABS            |SCALAR
ACOS           |SCALAR
ASIN           |SCALAR
ATAN           |SCALAR
ATAN2          |SCALAR
ASCII          |SCALAR

当然，也可以是上述内容的变体：

SHOW FUNCTIONS LIKE '%DAY%';

     name      |     type
---------------+---------------
DAY            |SCALAR
DAYNAME        |SCALAR
DAYOFMONTH     |SCALAR
DAYOFWEEK      |SCALAR
DAYOFYEAR      |SCALAR
DAY_NAME       |SCALAR
DAY_OF_MONTH   |SCALAR
DAY_OF_WEEK    |SCALAR
DAY_OF_YEAR    |SCALAR
HOUR_OF_DAY    |SCALAR
ISODAYOFWEEK   |SCALAR
ISO_DAY_OF_WEEK|SCALAR
MINUTE_OF_DAY  |SCALAR
TODAY          |SCALAR

显示表

edit

概要：

SHOW TABLES
    [CATALOG [catalog_identifier | 
              LIKE pattern]]?      
    [INCLUDE FROZEN]?              
    [table_identifier |            
     LIKE pattern]?

	目录（集群）标识符。支持通配符（`*`）。
	SQL LIKE 模式匹配目录名称。
	是否包含冻结的索引。
	单表（索引或数据流）标识符或双引号的多目标模式。
	SQL LIKE 模式匹配表名。

有关模式的更多信息，请参见索引模式。

描述: 列出当前用户可用的表及其类型。

SHOW TABLES;

 catalog       |     name      | type     |     kind
---------------+---------------+----------+---------------
javaRestTest      |emp            |TABLE     |INDEX
javaRestTest      |employees      |VIEW      |ALIAS
javaRestTest      |library        |TABLE     |INDEX

通过使用Elasticsearch 多目标语法表示法来匹配多个索引：

SHOW TABLES "*,-l*";

 catalog       |     name      | type     |     kind
---------------+---------------+----------+---------------
javaRestTest      |emp            |TABLE     |INDEX
javaRestTest      |employees      |VIEW      |ALIAS

也可以使用 LIKE 子句将名称列表限制为给定的模式。

模式可以是精确匹配：

SHOW TABLES LIKE 'emp';

 catalog       |     name      | type     |     kind
---------------+---------------+----------+---------------
javaRestTest      |emp            |TABLE     |INDEX

多个字符：

SHOW TABLES LIKE 'emp%';

 catalog       |     name      | type     |     kind
---------------+---------------+----------+---------------
javaRestTest      |emp            |TABLE     |INDEX
javaRestTest      |employees      |VIEW      |ALIAS

一个字符：

SHOW TABLES LIKE 'em_';

 catalog       |     name      | type     |     kind
---------------+---------------+----------+---------------
javaRestTest      |emp            |TABLE     |INDEX

或单个和多个字符的混合：

SHOW TABLES LIKE '%em_';

 catalog       |     name      | type     |     kind
---------------+---------------+----------+---------------
javaRestTest      |emp            |TABLE     |INDEX

列出名称与通配符匹配的远程集群中的表：

SHOW TABLES CATALOG 'my_*' LIKE 'test_emp%';

     catalog     |     name      |     type      |     kind
-----------------+---------------+---------------+---------------
my_remote_cluster|test_emp       |TABLE          |INDEX
my_remote_cluster|test_emp_copy  |TABLE          |INDEX

数据类型

edit

Elasticsearch 类型	Elasticsearch SQL 类型	SQL 类型	SQL 精度
核心类型
`null`	`空值`	空值	0
`boolean`	`布尔值`	布尔值	1
`byte`	`字节`	微整型	3
`short`	`短整型`	SMALLINT	5
`integer`	`整数`	整数	10
`long`	`长整型`	BIGINT	19
`unsigned_long`	`[预览] 此功能处于技术预览阶段，可能会在未来的版本中更改或删除。Elastic 将努力修复任何问题，但技术预览中的功能不受官方 GA 功能支持 SLA 的约束。 unsigned_long`	BIGINT	20
`double`	`双精度`	双精度	15
`float`	`浮点数`	真实	7
`half_float`	`半浮点数`	浮点数	3
`scaled_float`	`scaled_float`	双精度	15
关键词类型家族	`关键词`	可变长度字符串	32,766
`text`	`文本`	可变长度字符串	2,147,483,647
`binary`	`二进制`	VARBINARY	2,147,483,647
`date`	`datetime`	时间戳	29
`ip`	`ip`	VARCHAR	39
`version`	`版本`	VARCHAR	32,766
复杂类型
`object`	`对象`	结构体	0
`nested`	`嵌套`	结构体	0
不支持的类型
未提及的类型	`不支持`	其他	0

大多数 Elasticsearch 数据类型在 Elasticsearch SQL 中都可用，如上所述。正如可以看到的，所有 Elasticsearch 数据类型都映射到 Elasticsearch SQL 中同名的数据类型，除了日期数据类型，它被映射到 Elasticsearch SQL 中的 日期时间。这是为了避免与 ANSI SQL 类型 DATE（仅日期）和 TIME（仅时间）混淆，这些类型在 Elasticsearch SQL 查询中也受支持（通过使用 CAST/CONVERT），但它们并不对应于 Elasticsearch 中的实际映射（见下表 table）。

显然，并非所有 Elasticsearch 中的类型在 SQL 中都有对应的类型，反之亦然，因此 Elasticsearch SQL 使用前者的特性而不是后者的特性，因为最终 Elasticsearch 是底层存储。

除了上述类型外，Elasticsearch SQL 还支持在 运行时 使用特定于 SQL 的类型，这些类型在 Elasticsearch 中没有等效类型。这些类型不能从 Elasticsearch 中加载（因为它不知道这些类型），但可以在 Elasticsearch SQL 的查询或其结果中使用。

下表列出了这些类型：

SQL 类型	SQL 精度
`日期`	29
`时间`	18
`interval_year`	7
`interval_month`	7
`interval_day`	23
`interval_hour`	23
`interval_minute`	23
`interval_second`	23
`interval_year_to_month`	7
`interval_day_to_hour`	23
`interval_day_to_minute`	23
`interval_day_to_second`	23
`interval_hour_to_minute`	23
`interval_hour_to_second`	23
`interval_minute_to_second`	23
`geo_point`	52
`geo_shape`	2,147,483,647
`形状`	2,147,483,647

SQL 和多字段

edit

Elasticsearch中的一个核心概念是analyzed字段，即一个全文值，为了有效地索引而进行解释。这些字段属于类型text，并且不用于排序或聚合，因为它们的实际值取决于所使用的analyzer，因此Elasticsearch还提供了keyword类型来存储精确值。

在大多数情况下，实际上默认情况下，是使用Elasticsearch通过多字段支持的两种类型来处理字符串，即以多种方式索引相同字符串的能力；例如，将其同时索引为text以进行搜索，但也索引为keyword以进行排序和聚合。

由于SQL需要精确值，当遇到text字段时，Elasticsearch SQL会搜索一个可以用于比较、排序和聚合的精确多字段。为此，它会搜索第一个keyword，该字段不是规范化的，并将其用作原始字段的精确值。

考虑以下字符串映射：

{
  "first_name": {
    "type": "text",
    "fields": {
      "raw": {
        "type": "keyword"
      }
    }
  }
}

以下SQL查询：

SELECT first_name FROM index WHERE first_name = 'John'

与以下内容相同：

SELECT first_name FROM index WHERE first_name.raw = 'John'

由于 Elasticsearch SQL 自动选择了 raw 多字段用于精确匹配。

索引模式

edit

Elasticsearch SQL 支持两种类型的模式来匹配多个索引或表：

Elasticsearch 多目标语法

edit

Elasticsearch 的枚举表示法，包括或排除多目标语法是支持的只要它被引用或作为表标识符转义。

例如：

SHOW TABLES "*,-l*";

 catalog       |     name      | type     |     kind
---------------+---------------+----------+---------------
javaRestTest      |emp            |TABLE     |INDEX
javaRestTest      |employees      |VIEW      |ALIAS

注意模式被双引号 " 包围。它枚举了 * 表示所有索引，但由于 - 排除了所有以 l 开头的索引。这种表示法非常方便且强大，因为它允许根据目标命名约定进行包含和排除。

同样类型的模式也可以用于查询多个索引或表。

例如：

SELECT emp_no FROM "e*p" LIMIT 1;

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

存在一个限制，即所有解析的具体表必须具有完全相同的映射。

[预览] 此功能处于技术预览阶段，可能会在未来的版本中进行更改或移除。Elastic 将努力修复任何问题，但技术预览中的功能不受官方 GA 功能支持 SLA 的约束。要运行跨集群搜索，请使用 : 语法指定集群名称，其中映射到 SQL 目录（集群），映射到表（索引或数据流）。支持通配符 (*)，并且可以是索引模式。

例如：

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

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

SQL `LIKE` 表示法

edit

常见的 LIKE 语句（包括如果需要转义）以匹配通配符模式，基于一个 _ 或多个 % 字符。

再次使用 SHOW TABLES 命令：

SHOW TABLES LIKE 'emp%';

 catalog       |     name      | type     |     kind
---------------+---------------+----------+---------------
javaRestTest      |emp            |TABLE     |INDEX
javaRestTest      |employees      |VIEW      |ALIAS

该模式匹配所有以emp开头的表。

此命令也支持转义，例如：

SHOW TABLES LIKE 'emp!%' ESCAPE '!';

 catalog       |     name      |     type      |     kind
---------------+---------------+---------------+---------------

注意现在 emp% 不匹配任何表，因为 % 表示匹配零个或多个字符，已经被 ! 转义，因此变成了一个普通字符。由于没有名为 emp% 的表，因此返回了一个空表。

简而言之，这两种模式之间的区别在于：

功能	多重索引	SQL `LIKE`
引用类型	`"`	`'`
包含	是	是
排除	是	否
枚举	是	否
单字符模式	否	`_`
多字符模式	`*`	`%`
转义	否	`ESCAPE`

使用哪一个取决于你，但在你的查询中尽量保持一致性。

由于两种模式之间的引用查询类型非常相似（" 与 '），Elasticsearch SQL 始终需要关键字 LIKE 用于 SQL LIKE 模式。

冻结索引

edit

默认情况下，Elasticsearch SQL 不会搜索冻结索引。要搜索冻结索引，请使用以下功能之一：

dedicated configuration parameter: 将属性 index_include_frozen 在 SQL search API 中或 index.include.frozen 在驱动程序中设置为 true，以包含冻结索引。
dedicated keyword: 通过在FROM子句中使用专门的FROZEN关键字或在SHOW命令中使用INCLUDE FROZEN来显式执行包含操作：

SHOW TABLES INCLUDE FROZEN;

 catalog       |     name      | type     |     kind
---------------+---------------+----------+---------------
javaRestTest      |archive        |TABLE     |FROZEN INDEX
javaRestTest      |emp            |TABLE     |INDEX
javaRestTest      |employees      |VIEW      |ALIAS
javaRestTest      |library        |TABLE     |INDEX

SELECT * FROM FROZEN archive LIMIT 1;

     author      |        name        |  page_count   |    release_date
-----------------+--------------------+---------------+--------------------
James S.A. Corey |Leviathan Wakes     |561            |2011-06-02T00:00:00Z

除非启用，冻结的索引会被完全忽略；它们就像不存在一样，因此针对它们的查询很可能会失败。

« SQL Client Applications Functions and Operators »

SQL 语言

SQL 语言

词法结构

关键字

标识符

字面量 (常量)

字符串字面量

数值字面量

通用字面量

单引号与双引号

特殊字符

运算符

注释

SQL 命令

描述表

选择

SELECT 列表

通配符

顶部

FROM 子句

WHERE 子句

GROUP BY

隐式分组

HAVING

隐式分组

ORDER BY

排序和分组

按分数排序

限制

透视

显示目录

显示列

显示函数

显示表

数据类型

SQL 和多字段

索引模式

Elasticsearch 多目标语法

SQL LIKE 表示法

冻结索引

`SELECT` 列表

SQL `LIKE` 表示法