上一篇【第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_addressFROMhosts-*;SELECTfromFROMlogstash-{now/d};标识符使用双引号字符串常量使用单引号两者不可互换-- 正确SELECTfirst_nameFROMmusiciansWHERElast_nameChen;-- 错误不能用单引号引用列名-- SELECT first_name FROM musicians;1.3 直接常量字符串常量字符串用单引号包裹转义使用两个单引号SELECTHello World;SELECTCaptain EOs Voyage;-- 包含单引号的字符串数值常量支持十进制和科学记数法SELECT1969;SELECT3.14;SELECT.1234;SELECT4E5;SELECT1.2e-3;类型推断规则包含小数点的为Double类型否则优先判断为Integer超出范围则为Long。1.4 注释ES SQL支持两种注释风格-- 单行注释查询所有书籍SELECT*FROMlibrary;/* 多行注释 这是第一行注释 这是第二行注释 */SELECTnameFROMlibraryWHEREpage_count500;二、SQL命令详解2.1 SHOW TABLES查看可用索引SHOW TABLES命令列出当前用户可访问的索引表SHOWTABLES;使用多索引模式通配符匹配-- 查看所有以emp开头的索引SHOWTABLESLIKEemp%;-- 查看所有索引排除以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;-- 通配符匹配多个索引FROMlogs-*;-- 特殊模式FROMlogstash-{now/d};WHERE子句WHERE子句用于行级过滤-- 等值查询SELECT*FROMlibraryWHEREauthorFrank Herbert;-- 范围查询SELECT*FROMlibraryWHEREpage_countBETWEEN400AND600;-- IN查询SELECT*FROMlibraryWHEREauthorIN(Frank Herbert,Dan Simmons);-- LIKE模糊查询SELECT*FROMlibraryWHEREnameLIKE%eow%;-- IS NULL / IS NOT NULLSELECT*FROMlibraryWHERErelease_dateISNOTNULL;-- 组合条件SELECT*FROMlibraryWHEREpage_count500ANDauthorFrank 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 HAVINGWHERE在分组前过滤行处理单行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(*)计数含NULLCOUNT(*)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)5CEIL(x)向上取整CEIL(4.3)5FLOOR(x)向下取整FLOOR(4.7)4ROUND(x)四舍五入ROUND(4.56)5ROUND(x, n)保留n位小数ROUND(4.567, 2)4.57SQRT(x)平方根SQRT(16)4.0EXP(x)e的x次方EXP(1)2.718…LN(x)自然对数LN(2.718)0.999…LOG10(x)以10为底对数LOG10(100)2.0POWER(x, y)x的y次方POWER(2, 3)8MOD(x, y)取余MOD(10, 3)1PI()圆周率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)abSUBSTRING(s, start, len)截取子串SUBSTRING(Hello, 1, 3)HelLENGTH(s)字符串长度LENGTH(Hello)5CHAR_LENGTH(s)字符数CHAR_LENGTH(你好)2UPPER(s)/UCASE(s)转大写UPPER(hello)HELLOLOWER(s)/LCASE(s)转小写LOWER(HELLO)helloLTRIM(s)去左空格LTRIM( hi)hiRTRIM(s)去右空格RTRIM(hi )hiTRIM(s)去两端空格TRIM( hi )hiREPLACE(s, old, new)替换REPLACE(abc, b, x)axcPOSITION(sub IN s)查找子串位置POSITION(ll IN Hello)3LEFT(s, n)左取n个字符LEFT(Hello, 2)HeRIGHT(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)相等则返回NULLNULLIF(status, deleted)ISNULL(expr)是否为NULLISNULL(phone)CASE WHEN示例SELECTname,page_count,CASEWHENpage_count600THEN超长篇WHENpage_count400THEN长篇WHENpage_count200THEN中篇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?formatjson{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的一个精简子集实现以下特性不被支持限制类别不支持的特性替代方案DMLINSERT、UPDATE、DELETE使用ES Index/Bulk APIDDLCREATE TABLE、ALTER TABLE使用ES Mapping APIJOININNER/LEFT/RIGHT JOIN使用ES父子文档或嵌套子查询SELECT中的嵌套查询拆分为多次查询UNIONUNION / UNION ALL使用ES多索引查询事务BEGIN / COMMIT / ROLLBACKES不支持事务视图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 ShellsqlSELECT*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|561sqlDESCRIBElibrary;column|type-----------------|-----------------author|KEYWORD name|TEXTpage_count|LONG release_date|DATEsqlSHOWTABLES;name---------------library6.3 CLI常用选项# 指定用户名和密码./elasticsearch-sql-cli http://user:passwordlocalhost:9200# 执行单条SQL./elasticsearch-sql-cli http://localhost:9200-qSELECT COUNT(*) FROM library# 指定输出格式./elasticsearch-sql-cli http://localhost:9200--formatcsv七、总结与最佳实践核心要点词法结构兼容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篇】为什么需要搜索引擎——关系数据库的搜索困境明日更新敬请期待
【Elasticsearch从入门到精通】第40篇：Elasticsearch SQL语法详解——从DDL到复杂查询
发布时间：2026/5/26 0:08:07
上一篇【第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_addressFROMhosts-*;SELECTfromFROMlogstash-{now/d};标识符使用双引号字符串常量使用单引号两者不可互换-- 正确SELECTfirst_nameFROMmusiciansWHERElast_nameChen;-- 错误不能用单引号引用列名-- SELECT first_name FROM musicians;1.3 直接常量字符串常量字符串用单引号包裹转义使用两个单引号SELECTHello World;SELECTCaptain EOs Voyage;-- 包含单引号的字符串数值常量支持十进制和科学记数法SELECT1969;SELECT3.14;SELECT.1234;SELECT4E5;SELECT1.2e-3;类型推断规则包含小数点的为Double类型否则优先判断为Integer超出范围则为Long。1.4 注释ES SQL支持两种注释风格-- 单行注释查询所有书籍SELECT*FROMlibrary;/* 多行注释 这是第一行注释 这是第二行注释 */SELECTnameFROMlibraryWHEREpage_count500;二、SQL命令详解2.1 SHOW TABLES查看可用索引SHOW TABLES命令列出当前用户可访问的索引表SHOWTABLES;使用多索引模式通配符匹配-- 查看所有以emp开头的索引SHOWTABLESLIKEemp%;-- 查看所有索引排除以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;-- 通配符匹配多个索引FROMlogs-*;-- 特殊模式FROMlogstash-{now/d};WHERE子句WHERE子句用于行级过滤-- 等值查询SELECT*FROMlibraryWHEREauthorFrank Herbert;-- 范围查询SELECT*FROMlibraryWHEREpage_countBETWEEN400AND600;-- IN查询SELECT*FROMlibraryWHEREauthorIN(Frank Herbert,Dan Simmons);-- LIKE模糊查询SELECT*FROMlibraryWHEREnameLIKE%eow%;-- IS NULL / IS NOT NULLSELECT*FROMlibraryWHERErelease_dateISNOTNULL;-- 组合条件SELECT*FROMlibraryWHEREpage_count500ANDauthorFrank 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 HAVINGWHERE在分组前过滤行处理单行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(*)计数含NULLCOUNT(*)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)5CEIL(x)向上取整CEIL(4.3)5FLOOR(x)向下取整FLOOR(4.7)4ROUND(x)四舍五入ROUND(4.56)5ROUND(x, n)保留n位小数ROUND(4.567, 2)4.57SQRT(x)平方根SQRT(16)4.0EXP(x)e的x次方EXP(1)2.718…LN(x)自然对数LN(2.718)0.999…LOG10(x)以10为底对数LOG10(100)2.0POWER(x, y)x的y次方POWER(2, 3)8MOD(x, y)取余MOD(10, 3)1PI()圆周率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)abSUBSTRING(s, start, len)截取子串SUBSTRING(Hello, 1, 3)HelLENGTH(s)字符串长度LENGTH(Hello)5CHAR_LENGTH(s)字符数CHAR_LENGTH(你好)2UPPER(s)/UCASE(s)转大写UPPER(hello)HELLOLOWER(s)/LCASE(s)转小写LOWER(HELLO)helloLTRIM(s)去左空格LTRIM( hi)hiRTRIM(s)去右空格RTRIM(hi )hiTRIM(s)去两端空格TRIM( hi )hiREPLACE(s, old, new)替换REPLACE(abc, b, x)axcPOSITION(sub IN s)查找子串位置POSITION(ll IN Hello)3LEFT(s, n)左取n个字符LEFT(Hello, 2)HeRIGHT(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)相等则返回NULLNULLIF(status, deleted)ISNULL(expr)是否为NULLISNULL(phone)CASE WHEN示例SELECTname,page_count,CASEWHENpage_count600THEN超长篇WHENpage_count400THEN长篇WHENpage_count200THEN中篇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?formatjson{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的一个精简子集实现以下特性不被支持限制类别不支持的特性替代方案DMLINSERT、UPDATE、DELETE使用ES Index/Bulk APIDDLCREATE TABLE、ALTER TABLE使用ES Mapping APIJOININNER/LEFT/RIGHT JOIN使用ES父子文档或嵌套子查询SELECT中的嵌套查询拆分为多次查询UNIONUNION / UNION ALL使用ES多索引查询事务BEGIN / COMMIT / ROLLBACKES不支持事务视图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 ShellsqlSELECT*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|561sqlDESCRIBElibrary;column|type-----------------|-----------------author|KEYWORD name|TEXTpage_count|LONG release_date|DATEsqlSHOWTABLES;name---------------library6.3 CLI常用选项# 指定用户名和密码./elasticsearch-sql-cli http://user:passwordlocalhost:9200# 执行单条SQL./elasticsearch-sql-cli http://localhost:9200-qSELECT COUNT(*) FROM library# 指定输出格式./elasticsearch-sql-cli http://localhost:9200--formatcsv七、总结与最佳实践核心要点词法结构兼容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篇】为什么需要搜索引擎——关系数据库的搜索困境明日更新敬请期待
