基础查询

基础查询 #

概述 #

SELECT 语句是从 Easysearch 索引中检索数据的核心语法。完整语法如下：

SELECT [ALL | DISTINCT] (* | expression) [[AS] alias] [, ...]
FROM index_name
[WHERE predicates]
[GROUP BY expression [, ...]
 [HAVING predicates]]
[ORDER BY expression [ASC | DESC] [NULLS {FIRST | LAST}] [, ...]]
[LIMIT [offset, ] size]

语句末尾允许加分号 ;，这对 BI 工具和 Excel 生成的查询很有用。

执行顺序 #

SQL 各子句的实际执行顺序与书写顺序不同：

FROM index         -- ① 确定数据源
 → WHERE           -- ② 行级过滤
  → GROUP BY       -- ③ 分组
   → HAVING        -- ④ 组级过滤
    → SELECT       -- ⑤ 字段投影
     → ORDER BY    -- ⑥ 排序
      → LIMIT      -- ⑦ 分页

SELECT #

SELECT 子句指定要检索的字段。

查询所有字段 #

POST /_sql
{
  "query": "SELECT * FROM accounts"
}

查询指定字段 #

POST /_sql
{
  "query": "SELECT firstname, lastname, balance FROM accounts"
}

字段别名 #

使用 AS 为字段设置别名，提高可读性：

POST /_sql
{
  "query": "SELECT account_number AS num, balance AS bal FROM accounts"
}

DISTINCT 去重 #

返回字段的唯一值（底层翻译为 terms 聚合）：

POST /_sql
{
  "query": "SELECT DISTINCT age FROM accounts"
}

DISTINCT 也可以与表达式结合使用：

SELECT DISTINCT SUBSTRING(lastname, 1, 1) FROM accounts

FROM #

FROM 子句指定查询的数据源（索引）。

索引别名 #

SELECT acc.firstname, acc.lastname FROM accounts acc

索引模式匹配 #

通过通配符从多个名称相似的索引查询数据，适合时间序列索引（如 logs-2025.02.*）：

SELECT * FROM account*

子查询 #

FROM 子句支持子查询（详见 复杂查询）。

WHERE #

WHERE 子句对文档进行行级过滤。支持以下谓词：

运算符说明示例
=, <>, !=相等/不等WHERE age = 30
>, >=, <, <=比较WHERE balance >= 10000
AND, OR, NOT逻辑组合WHERE age > 30 AND gender = 'M'
IN集合匹配WHERE state IN ('CA', 'NY', 'TX')
BETWEEN ... AND范围WHERE age BETWEEN 25 AND 35
LIKE模式匹配WHERE lastname LIKE 'D%'
IS NULL / IS NOT NULL空值检测WHERE employer IS NULL

基本过滤 #

POST /_sql
{
  "query": "SELECT account_number, balance FROM accounts WHERE balance > 10000"
}

翻译为 DSL：

{
  "query": {
    "bool": {
      "filter": [{
        "range": {"balance": {"gt": 10000}}
      }]
    }
  }
}

组合条件 #

SELECT firstname, age, balance
FROM accounts
WHERE age > 30 AND balance > 5000 AND state IN ('IL', 'TN')

NULL 检测 #

Easysearch 允许灵活的文档结构，字段可能缺失。使用 IS NULL / IS NOT NULL 检测：

SELECT account_number, employer FROM accounts WHERE employer IS NULL

当前 SQL 插件不区分"字段缺失"和"字段值为 null"。

GROUP BY #

GROUP BY 子句将文档按字段值分组，通常与聚合函数配合使用。详细的聚合语法见 聚合查询。

GROUP BY 表达式支持三种形式：

-- 1. 按字段名
GROUP BY gender

-- 2. 按 SELECT 列表中的序号
GROUP BY 1

-- 3. 按表达式
GROUP BY ABS(age)

示例 #

SELECT gender, COUNT(*), AVG(age) FROM accounts GROUP BY gender

+--------+-----------+----------+
| gender | COUNT(*)  | AVG(age) |
+--------+-----------+----------+
| F      | 1         | 28.0     |
| M      | 3         | 33.67    |
+--------+-----------+----------+

按别名分组 #

SELECT account_number AS num FROM accounts GROUP BY num

按标量函数分组 #

SELECT ABS(age) AS a, COUNT(*) FROM accounts GROUP BY ABS(age)

HAVING #

HAVING 子句在 GROUP BY 之后对聚合结果进行过滤。

SELECT age, MAX(balance) FROM accounts
GROUP BY age
HAVING MIN(balance) > 10000

HAVING 中可以使用与 SELECT 不同的聚合函数，也可以引用 SELECT 中的别名：

SELECT gender, SUM(age) AS s FROM accounts GROUP BY gender HAVING s > 100

ORDER BY #

ORDER BY 子句指定结果排序方式。

-- 按字段名排序（ASC 升序为默认）
SELECT * FROM accounts ORDER BY balance DESC

-- 按序号排序
SELECT firstname, age FROM accounts ORDER BY 2 DESC

-- 按别名排序
SELECT balance AS b FROM accounts ORDER BY b

-- 空值排序控制
SELECT * FROM accounts ORDER BY employer NULLS LAST

-- 按聚合函数排序
SELECT gender, MAX(age) FROM accounts GROUP BY gender ORDER BY MAX(age) DESC

LIMIT #

LIMIT 子句控制返回结果的数量。建议与 ORDER BY 配合使用以获得确定性结果。

-- 返回前 10 条
SELECT * FROM accounts ORDER BY balance DESC LIMIT 10

-- 跳过 5 条后返回 10 条（分页）
SELECT * FROM accounts ORDER BY account_number LIMIT 5, 10

-- 使用 OFFSET 语法
SELECT * FROM accounts ORDER BY age LIMIT 10 OFFSET 5

对于大偏移量的分页，性能较低。大规模分页建议使用 游标分页。

©INFINI.LTD, All Rights Reserved.