【Elasticsearch从入门到精通】第40篇：Elasticsearch SQL语法详解——从DDL到复杂查询
上一篇【第39篇】Elasticsearch SQL接口——用熟悉的SQL语法查询ES
下一篇【第41篇】为什么需要搜索引擎——关系数据库的搜索困境（明日更新，敬请期待）
摘要
在上一篇中我们了解了ES SQL接口的基本用法，本文将深入讲解ES SQL的完整语法体系。从词法结构（标识符、常量、运算符）入手，逐步展开SQL命令的详细语法，包括SHOW TABLES查看可用索引、SHOW COLUMNS和DESCRIBE查看字段结构、SELECT基本查询的完整流程。随后，我们将系统介绍ES SQL支持的函数体系——聚合函数（AVG/COUNT/MAX/MIN/SUM等）、数学函数（ABS/CEIL/FLOOR/ROUND）、字符串函数（CONCAT/SUBSTRING/LTRIM等）、日期函数（CURDATE/NOW/YEAR/MONTH等），并通过实战示例展示GROUP BY/HAVING/ORDER BY/LIMIT等子句的组合使用。最后，我们将总结ES SQL的已知限制，并介绍SQL CLI工具的使用方法。
一、词法结构
1.1 关键字
ES SQL中的关键字（Keyword）具有固定含义，不区分大小写：
-- 以下写法等价SELECT*FROMlibrary;select*fromlibrary;SeLeCt*FrOmlibrary;常用的SQL关键字包括：SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT、AND、OR、NOT、IN、BETWEEN、LIKE、IS NULL、IS NOT NULL、AS、DESC、ASC、JOIN、ON等。
最佳实践：关键字全部大写，标识符使用小写，提高代码可读性。
1.2 标识符
标识符用于标识表名（索引名）和列名（字段名），分为带引号和不带引号两种：
-- 不带引号的标识符（不与关键字冲突时可用）SELECTip_addressFROMhosts;-- 带双引号的标识符（包含特殊字符或与关键字冲突时必须使用）SELECTip_addressFROM"hosts-*";SELECT"from"FROM"<logstash-{now/d}>";标识符使用双引号，字符串常量使用单引号，两者不可互换：
-- 正确SELECT"first_name"FROMmusiciansWHERElast_name='Chen';-- 错误：不能用单引号引用列名-- SELECT 'first_name' FROM musicians;1.3 直接常量
字符串常量
字符串用单引号包裹，转义使用两个单引号：
SELECT'Hello World';SELECT'Captain EO''s Voyage';-- 包含单引号的字符串数值常量
支持十进制和科学记数法：
SELECT1969;SELECT3.14;SELECT.1234;SELECT4E5;SELECT1.2e-3;类型推断规则：包含小数点的为Double类型，否则优先判断为Integer，超出范围则为Long。
1.4 注释
ES SQL支持两种注释风格：
-- 单行注释：查询所有书籍SELECT*FROMlibrary;/* 多行注释： 这是第一行注释 这是第二行注释 */SELECTnameFROMlibraryWHEREpage_count>500;二、SQL命令详解
2.1 SHOW TABLES：查看可用索引
SHOW TABLES命令列出当前用户可访问的索引（表）：
SHOWTABLES;使用多索引模式（通配符匹配）：
-- 查看所有以emp开头的索引SHOWTABLESLIKE'emp%';-- 查看所有索引，排除以logstash-开头的SHOWTABLES"*,-logstash-*";多索引模式和LIKE模式的区别：
| 特性 | 多索引模式 | LIKE模式 |
|---|---|---|
| 引号 | 双引号 | 单引号 |
| 通配符 | *匹配任意字符 | %匹配任意字符序列 |
| 排除支持 | 支持（-前缀） | 不支持 |
| 示例 | "*,-l*" | 'emp%' |
| 转义 | 不需要 | ESCAPE子句 |
2.2 SHOW COLUMNS：查看列信息
SHOWCOLUMNSFROMlibrary;返回结果包含列名、数据类型、是否可为空等信息。
2.3 DESCRIBE：查看表结构
DESCRIBE（可缩写为DESC）用于查看索引的结构信息：
-- 两种写法等价DESCRIBElibrary;DESClibrary;2.4 SELECT基本语法
SELECT是ES SQL中最核心、功能最丰富的命令：
SELECTselect_expr[,...]FROMtable_name[WHEREcondition][GROUPBYgrouping_element[,...]][HAVINGcondition][ORDERBYexpression[ASC|DESC][,...]][LIMIT{count|ALL}]SELECT的执行流程：
- FROM：确定查询的数据源（索引）
- WHERE：过滤不满足条件的行
- GROUP BY：将结果按分组元素分组
- HAVING：过滤不满足条件的组
- SELECT：计算输出表达式
- ORDER BY：对结果排序
- LIMIT：限制返回的行数
SELECT列表
-- 查询指定列SELECTname,authorFROMlibrary;-- 使用AS指定列别名SELECTnameASbook_name,page_countASpagesFROMlibrary;-- 使用*查询所有列SELECT*FROMlibrary;-- 使用表达式SELECTname,page_count*0.1ASprice_estimateFROMlibrary;FROM子句
-- 指定索引名FROMlibrary;-- 使用别名FROMlibraryASlib;FROMlibrary lib;-- 通配符匹配多个索引FROM"logs-*";-- 特殊模式FROM"<logstash-{now/d}>";WHERE子句
WHERE子句用于行级过滤：
-- 等值查询SELECT*FROMlibraryWHEREauthor='Frank Herbert';-- 范围查询SELECT*FROMlibraryWHEREpage_countBETWEEN400AND600;-- IN查询SELECT*FROMlibraryWHEREauthorIN('Frank Herbert','Dan Simmons');-- LIKE模糊查询SELECT*FROMlibraryWHEREnameLIKE'%eow%';-- IS NULL / IS NOT NULLSELECT*FROMlibraryWHERErelease_dateISNOTNULL;-- 组合条件SELECT*FROMlibraryWHEREpage_count>500ANDauthor='Frank Herbert';GROUP BY子句
GROUP BY将结果按指定列分组，通常配合聚合函数使用：
-- 按作者分组，统计每人书籍数量和平均页数SELECTauthor,COUNT(*)ASbook_count,AVG(page_count)ASavg_pages,MAX(page_count)ASmax_pages,MIN(page_count)ASmin_pagesFROMlibraryGROUPBYauthor;HAVING子句
HAVING子句对GROUP BY产生的组进行过滤：
-- 查找平均页数超过500的作者SELECTauthor,AVG(page_count)ASavg_pagesFROMlibraryGROUPBYauthorHAVINGAVG(page_count)>500;WHERE vs HAVING：WHERE在分组前过滤行（处理单行），HAVING在分组后过滤组（处理组）。WHERE不能使用聚合函数，HAVING可以。
ORDER BY子句
ORDER BY对结果进行排序：
-- 按页数升序SELECT*FROMlibraryORDERBYpage_countASC;-- 按页数降序SELECT*FROMlibraryORDERBYpage_countDESC;-- 多字段排序SELECT*FROMlibraryORDERBYauthorASC,page_countDESC;-- 按列位置排序SELECTname,author,page_countFROMlibraryORDERBY3DESC;-- 按_score排序（ES特有）SELECT*FROMlibraryORDERBYSCORE()DESC;LIMIT子句
-- 返回前5条SELECT*FROMlibraryLIMIT5;-- 返回所有结果SELECT*FROMlibraryLIMITALL;警告：使用
LIMIT ALL时，如果数据量很大，可能导致内存溢出。建议使用Cursor分页替代。
三、函数体系
3.1 聚合函数
聚合函数对一组值进行计算，返回单一值。通常与GROUP BY配合使用。
| 函数 | 说明 | 示例 |
|---|---|---|
AVG(expr) | 平均值 | AVG(page_count) |
COUNT(*) | 计数（含NULL） | COUNT(*) |
COUNT(expr) | 非NULL值计数 | COUNT(author) |
MAX(expr) | 最大值 | MAX(page_count) |
MIN(expr) | 最小值 | MIN(page_count) |
SUM(expr) | 求和 | SUM(page_count) |
KURTOSIS(expr) | 峰度 | KURTOSIS(page_count) |
SKEWNESS(expr) | 偏度 | SKEWNESS(page_count) |
FIRST(expr) | 第一个值 | FIRST(name) |
LAST(expr) | 最后一个值 | LAST(name) |
VAR_POP(expr) | 总体方差 | VAR_POP(score) |
VAR_SAMP(expr) | 样本方差 | VAR_SAMP(score) |
STDDEV_POP(expr) | 总体标准差 | STDDEV_POP(score) |
STDDEV_SAMP(expr) | 样本标准差 | STDDEV_SAMP(score) |
综合聚合查询示例：
SELECTauthor,COUNT(*)AStotal_books,SUM(page_count)AStotal_pages,AVG(page_count)ASavg_pages,MIN(page_count)ASshortest,MAX(page_count)ASlongest,STDDEV_POP(page_count)ASpage_stddevFROMlibraryGROUPBYauthorORDERBYtotal_booksDESC;3.2 数学函数
| 函数 | 说明 | 示例 | 结果 |
|---|---|---|---|
ABS(x) | 绝对值 | ABS(-5) | 5 |
CEIL(x) | 向上取整 | CEIL(4.3) | 5 |
FLOOR(x) | 向下取整 | FLOOR(4.7) | 4 |
ROUND(x) | 四舍五入 | ROUND(4.56) | 5 |
ROUND(x, n) | 保留n位小数 | ROUND(4.567, 2) | 4.57 |
SQRT(x) | 平方根 | SQRT(16) | 4.0 |
EXP(x) | e的x次方 | EXP(1) | 2.718… |
LN(x) | 自然对数 | LN(2.718) | 0.999… |
LOG10(x) | 以10为底对数 | LOG10(100) | 2.0 |
POWER(x, y) | x的y次方 | POWER(2, 3) | 8 |
MOD(x, y) | 取余 | MOD(10, 3) | 1 |
PI() | 圆周率 | PI() | 3.14159… |
RAND() | 随机数 | RAND() | 0~1之间 |
数学函数使用示例：
SELECTname,page_count,CEIL(page_count/200.0)ASest_reading_days,SQRT(page_count)AScomplexity_score,ROUND(page_count*0.02,2)ASestimated_priceFROMlibraryORDERBYestimated_priceDESC;3.3 字符串函数
| 函数 | 说明 | 示例 | 结果 |
|---|---|---|---|
CONCAT(s1, s2) | 字符串拼接 | CONCAT('a', 'b') | "ab" |
SUBSTRING(s, start, len) | 截取子串 | SUBSTRING('Hello', 1, 3) | "Hel" |
LENGTH(s) | 字符串长度 | LENGTH('Hello') | 5 |
CHAR_LENGTH(s) | 字符数 | CHAR_LENGTH('你好') | 2 |
UPPER(s)/UCASE(s) | 转大写 | UPPER('hello') | "HELLO" |
LOWER(s)/LCASE(s) | 转小写 | LOWER('HELLO') | "hello" |
LTRIM(s) | 去左空格 | LTRIM(' hi') | "hi" |
RTRIM(s) | 去右空格 | RTRIM('hi ') | "hi" |
TRIM(s) | 去两端空格 | TRIM(' hi ') | "hi" |
REPLACE(s, old, new) | 替换 | REPLACE('abc', 'b', 'x') | "axc" |
POSITION(sub IN s) | 查找子串位置 | POSITION('ll' IN 'Hello') | 3 |
LEFT(s, n) | 左取n个字符 | LEFT('Hello', 2) | "He" |
RIGHT(s, n) | 右取n个字符 | RIGHT('Hello', 2) | "lo" |
字符串函数使用示例：
SELECTname,UPPER(author)ASauthor_upper,CONCAT(author,' - ',name)ASbook_info,LENGTH(name)ASname_length,SUBSTRING(name,1,3)ASshort_nameFROMlibrary;3.4 日期函数
| 函数 | 说明 | 示例 |
|---|---|---|
CURDATE() | 当前日期 | CURDATE() |
NOW() | 当前日期时间 | NOW() |
YEAR(date) | 提取年份 | YEAR(release_date) |
MONTH(date) | 提取月份 | MONTH(release_date) |
DAY(date) | 提取日 | DAY(release_date) |
HOUR(date) | 提取小时 | HOUR(release_date) |
MINUTE(date) | 提取分钟 | MINUTE(release_date) |
SECOND(date) | 提取秒 | SECOND(release_date) |
DAYOFWEEK(date) | 星期几(1-7) | DAYOFWEEK(NOW()) |
DAYOFYEAR(date) | 一年中第几天 | DAYOFYEAR(NOW()) |
QUARTER(date) | 季度(1-4) | QUARTER(release_date) |
DATE_FORMAT(date, fmt) | 格式化日期 | DATE_FORMAT(NOW(), '%Y-%m') |
DATE_TRUNC(unit, date) | 截断到指定单位 | DATE_TRUNC('month', NOW()) |
日期函数使用示例：
SELECTname,release_date,YEAR(release_date)ASrelease_year,MONTH(release_date)ASrelease_month,CONCAT(YEAR(release_date),'-Q',QUARTER(release_date))ASrelease_quarterFROMlibraryORDERBYrelease_dateDESC;3.5 条件函数
| 函数 | 说明 | 示例 |
|---|---|---|
CASE WHEN ... THEN ... ELSE ... END | 条件判断 | 见下方示例 |
IF(condition, true_val, false_val) | 条件选择 | IF(page_count > 500, 'long', 'short') |
COALESCE(v1, v2, ...) | 返回第一个非NULL值 | COALESCE(nickname, name) |
NULLIF(v1, v2) | 相等则返回NULL | NULLIF(status, 'deleted') |
ISNULL(expr) | 是否为NULL | ISNULL(phone) |
CASE WHEN示例：
SELECTname,page_count,CASEWHENpage_count>=600THEN'超长篇'WHENpage_count>=400THEN'长篇'WHENpage_count>=200THEN'中篇'ELSE'短篇'ENDASbook_lengthFROMlibrary;3.6 函数分类总览
| 分类 | 代表函数 | 说明 |
|---|---|---|
| 聚合函数 | AVG, COUNT, MAX, MIN, SUM | 对一组值计算，配合GROUP BY |
| 数学函数 | ABS, CEIL, FLOOR, ROUND, SQRT | 数值计算 |
| 字符串函数 | CONCAT, SUBSTRING, UPPER, LOWER | 字符串处理 |
| 日期函数 | NOW, YEAR, MONTH, DAY, HOUR | 日期时间处理 |
| 条件函数 | CASE WHEN, IF, COALESCE | 条件判断 |
| 类型转换 | CAST, TRY_CAST | 数据类型转换 |
| 信息函数 | DATABASE, SCHEMA | 返回环境信息 |
四、综合查询实战
4.1 带分组和排序的复杂查询
SELECTauthor,COUNT(*)ASbook_count,AVG(page_count)ASavg_pages,SUM(page_count)AStotal_pagesFROMlibraryGROUPBYauthorHAVINGCOUNT(*)>=1ORDERBYavg_pagesDESCLIMIT10;4.2 使用REST API执行
POST_sql?format=json{"query":"SELECT author, COUNT(*) AS book_count, AVG(page_count) AS avg_pages FROM library GROUP BY author ORDER BY avg_pages DESC"}响应示例：
{"columns":[{"name":"author","type":"text"},{"name":"book_count","type":"long"},{"name":"avg_pages","type":"double"}],"rows":[["Frank Herbert",1,604.0],["James S.A. Corey",1,561.0],["Dan Simmons",1,482.0]],"status":200}五、ES SQL的已知限制
5.1 不支持的SQL特性
ES SQL是对标准SQL的一个精简子集实现，以下特性不被支持：
| 限制类别 | 不支持的特性 | 替代方案 |
|---|---|---|
| DML | INSERT、UPDATE、DELETE | 使用ES Index/Bulk API |
| DDL | CREATE TABLE、ALTER TABLE | 使用ES Mapping API |
| JOIN | INNER/LEFT/RIGHT JOIN | 使用ES父子文档或嵌套 |
| 子查询 | SELECT中的嵌套查询 | 拆分为多次查询 |
| UNION | UNION / UNION ALL | 使用ES多索引查询 |
| 事务 | BEGIN / COMMIT / ROLLBACK | ES不支持事务 |
| 视图 | CREATE VIEW | 不适用 |
| 存储过程 | PROCEDURE / FUNCTION | 不适用 |
| 窗口函数 | ROW_NUMBER / RANK / OVER | 使用ES聚合 |
| 递归查询 | WITH RECURSIVE | 不适用 |
5.2 其他限制
- 不支持修改索引的mapping
- 不支持跨索引的JOIN操作
- GROUP BY不支持复杂表达式
- 部分SQL函数可能因底层ES版本不同而有所差异
- WHERE子句中的条件在底层转换为ES的bool query，某些复杂条件可能无法完美映射
六、SQL CLI工具
6.1 安装与配置
ES SQL CLI是一个命令行工具，提供交互式的SQL查询体验：
# 安装（需要Java环境）elasticsearch-sql-cli-7.x.x.zip# 解压后执行./elasticsearch-sql-cli http://localhost:92006.2 交互式使用
# 连接后进入交互式SQL Shellsql>SELECT*FROMlibraryLIMIT3;name|author|release_date|page_count---------------------|----------------------|-----------------------|------------Dune|Frank Herbert|1965-06-01T00:00:00.000Z|604Hyperion|Dan Simmons|1989-05-26T00:00:00.000Z|482Leviathan Wakes|James S.A.Corey|2011-06-02T00:00:00.000Z|561sql>DESCRIBElibrary;column|type-----------------|-----------------author|KEYWORD name|TEXTpage_count|LONG release_date|DATEsql>SHOWTABLES;name---------------library6.3 CLI常用选项
# 指定用户名和密码./elasticsearch-sql-cli http://user:password@localhost:9200# 执行单条SQL./elasticsearch-sql-cli http://localhost:9200-q"SELECT COUNT(*) FROM library"# 指定输出格式./elasticsearch-sql-cli http://localhost:9200--format=csv七、总结与最佳实践
核心要点
- 词法结构兼容ANSI SQL：关键字不区分大小写，标识符用双引号，字符串用单引号
- 函数体系丰富但有限：涵盖聚合、数学、字符串、日期、条件等常用函数
- 注意GROUP BY + HAVING组合：WHERE过滤行，HAVING过滤组，两者配合实现精细筛选
- 了解限制合理使用：ES SQL不支持JOIN和子查询，复杂场景需回到Query DSL
最佳实践清单
- 使用SQL CLI进行数据探索和快速验证
- 复杂查询先用Translate API查看翻译结果，确认语义正确
- 使用参数化查询（params）防止SQL注入
- 聚合查询务必设置LIMIT，避免结果集过大
- 对于ES SQL不支持的特性，使用原生Query DSL替代
- 定期使用DESCRIBE和SHOW COLUMNS了解索引结构变化
上一篇【第39篇】Elasticsearch SQL接口——用熟悉的SQL语法查询ES
下一篇【第41篇】为什么需要搜索引擎——关系数据库的搜索困境（明日更新，敬请期待）