OpenSearch SQL 查询完整指南

OpenSearch SQL 查询完整指南

目录

1. 基础查询
2. 字符串查询
3. 数值查询
4. 日期时间查询
5. 数组和嵌套查询
6. 聚合查询
7. 地理空间查询
8. 全文搜索
9. 复杂查询
10. 性能优化

---

🔍 基础查询

基本 SELECT

-- 查询所有字段
SELECT * FROM index_name;-- 查询特定字段
SELECT name, age, email FROM users;-- 使用别名
SELECT name AS user_name, age AS user_age FROM users;

WHERE 条件

-- 等于
SELECT * FROM users WHERE age = 25;-- 不等于
SELECT * FROM users WHERE age != 25;-- 大于/小于
SELECT * FROM users WHERE age > 25 AND age < 50;-- 范围查询
SELECT * FROM users WHERE age BETWEEN 25 AND 50;-- IN 查询
SELECT * FROM users WHERE status IN ('active', 'pending');

---

📝 字符串查询

大小写处理

-- 不区分大小写
SELECT * FROM users WHERE LOWER(name) = 'john';-- 转换为大写
SELECT * FROM users WHERE UPPER(status) = 'ACTIVE';-- 大小写混合
SELECT * FROM users WHERE name IN ('John', 'john', 'JOHN');

模式匹配

-- LIKE 查询
SELECT * FROM users WHERE name LIKE '%john%';
SELECT * FROM users WHERE email LIKE 'john%@gmail.com';-- 正则表达式
SELECT * FROM users WHERE name REGEXP '^[A-Z][a-z]+$';-- 通配符
SELECT * FROM users WHERE name LIKE 'J_n%';

字符串函数

-- 字符串长度
SELECT name, LENGTH(name) AS name_length FROM users;-- 子字符串
SELECT name, SUBSTRING(name, 1, 3) AS name_prefix FROM users;-- 字符串连接
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;-- 字符串替换
SELECT name, REPLACE(email, '@gmail.com', '@company.com') AS new_email FROM users;

---

数值查询

数学运算

-- 基本运算
SELECT price, price * 1.1 AS price_with_tax FROM products;-- 数学函数
SELECT price, ROUND(price, 2) AS rounded_price FROM products;
SELECT price, CEIL(price) AS ceiling_price FROM products;
SELECT price, FLOOR(price) AS floor_price FROM products;-- 绝对值
SELECT price, ABS(price) AS absolute_price FROM products;

统计函数

-- 平均值
SELECT AVG(price) AS avg_price FROM products;-- 总和
SELECT SUM(price) AS total_price FROM products;-- 最大值/最小值
SELECT MAX(price) AS max_price, MIN(price) AS min_price FROM products;-- 计数
SELECT COUNT(*) AS total_products FROM products;

---

📅 日期时间查询

日期比较

-- 基本日期查询
SELECT * FROM orders WHERE order_date > '2024-01-01';-- 日期范围
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';-- 相对日期
SELECT * FROM orders WHERE order_date > DATE_SUB(NOW(), INTERVAL 30 DAY);

日期函数

-- 当前日期时间
SELECT NOW() AS current_time;-- 日期提取
SELECT order_date, YEAR(order_date) AS year,MONTH(order_date) AS month,DAY(order_date) AS day
FROM orders;-- 日期格式化
SELECT order_date, DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date FROM orders;-- 日期计算
SELECT order_date, DATE_ADD(order_date, INTERVAL 7 DAY) AS delivery_date FROM orders;

---

数组和嵌套查询

数组查询

-- 数组包含
SELECT * FROM products WHERE 'red' IN tags;-- 数组长度
SELECT name, CARDINALITY(tags) AS tag_count FROM products;-- 数组元素
SELECT name, tags[0] AS first_tag FROM products;-- 数组展开
SELECT name, tag FROM products, UNNEST(tags) AS tag;

嵌套对象查询

-- 嵌套字段访问
SELECT name, address.city, address.street FROM users;-- 嵌套条件
SELECT * FROM users WHERE address.city = 'New York';-- 嵌套数组
SELECT name, phone FROM users, UNNEST(phones) AS phone;

---

聚合查询

基本聚合

-- 分组聚合
SELECT category, COUNT(*) AS count, AVG(price) AS avg_price 
FROM products 
GROUP BY category;-- 多字段分组
SELECT category, brand, COUNT(*) AS count 
FROM products 
GROUP BY category, brand;

高级聚合

-- HAVING 条件
SELECT category, AVG(price) AS avg_price 
FROM products 
GROUP BY category 
HAVING AVG(price) > 100;-- 排序聚合结果
SELECT category, COUNT(*) AS count 
FROM products 
GROUP BY category 
ORDER BY count DESC;

窗口函数

-- 排名
SELECT name, price, ROW_NUMBER() OVER (ORDER BY price DESC) AS rank
FROM products;-- 分组排名
SELECT name, category, price,ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS category_rank
FROM products;

---

地理空间查询

地理坐标查询

-- 距离查询
SELECT name, GEO_DISTANCE(location, 'POINT(-74.006 40.7128)') AS distance
FROM stores
WHERE GEO_DISTANCE(location, 'POINT(-74.006 40.7128)') < 10000;-- 边界框查询
SELECT * FROM stores 
WHERE location IN (SELECT ST_GeomFromText('POLYGON((-74.1 40.7, -73.9 40.7, -73.9 40.9, -74.1 40.9, -74.1 40.7))')
);

地理函数

-- 计算距离
SELECT name, ST_Distance(location, 'POINT(-74.006 40.7128)') AS distance
FROM stores;-- 点在多边形内
SELECT * FROM stores 
WHERE ST_Contains(ST_GeomFromText('POLYGON((-74.1 40.7, -73.9 40.7, -73.9 40.9, -74.1 40.9, -74.1 40.7))'),location
);

---

全文搜索

MATCH 查询

-- 全文搜索
SELECT * FROM articles WHERE MATCH(content, 'open search tutorial');-- 多字段搜索
SELECT * FROM articles WHERE MATCH('title,content', 'machine learning');-- 短语搜索
SELECT * FROM articles WHERE MATCH_PHRASE(content, 'artificial intelligence');

相关性评分

-- 带评分的搜索
SELECT title, SCORE() AS relevance 
FROM articles 
WHERE MATCH(content, 'machine learning') 
ORDER BY relevance DESC;

---

🧩 复杂查询

子查询

-- IN 子查询
SELECT * FROM orders 
WHERE user_id IN (SELECT id FROM users WHERE status = 'active');-- 相关子查询
SELECT name, price, (SELECT AVG(price) FROM products p2 WHERE p2.category = p1.category) AS category_avg
FROM products p1;

UNION 查询

-- 合并查询结果
SELECT name, 'user' AS type FROM users
UNION ALL
SELECT name, 'product' AS type FROM products;

CASE 语句

-- 条件逻辑
SELECT name, price,CASE WHEN price < 50 THEN 'Low'WHEN price < 100 THEN 'Medium'ELSE 'High'END AS price_category
FROM products;

复杂条件

-- 多条件组合
SELECT * FROM orders 
WHERE (status = 'pending' AND total_amount > 100)OR (status = 'completed' AND created_date > DATE_SUB(NOW(), INTERVAL 7 DAY))OR (user_id IN (SELECT id FROM users WHERE vip = true));

---

⚡ 性能优化

索引优化

-- 使用索引字段
SELECT * FROM users WHERE indexed_field = 'value';-- 避免全表扫描
SELECT * FROM users WHERE status = 'active' LIMIT 1000;

查询优化技巧

-- 使用 LIMIT 限制结果
SELECT * FROM large_table LIMIT 1000;-- 只选择需要的字段
SELECT id, name, email FROM users WHERE status = 'active';-- 使用 EXISTS 代替 IN（大数据集）
SELECT * FROM orders o 
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.status = 'active');

分页查询

-- 基本分页
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40;-- 使用游标分页
SELECT * FROM products 
WHERE id > 'last_id' 
ORDER BY id 
LIMIT 20;

---

🎯 实用查询示例

用户分析

-- 活跃用户统计
SELECT DATE_FORMAT(created_date, '%Y-%m') AS month,COUNT(*) AS new_users,COUNT(CASE WHEN last_login > DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) AS active_users
FROM users 
GROUP BY DATE_FORMAT(created_date, '%Y-%m')
ORDER BY month;

销售分析

-- 销售趋势
SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS date,COUNT(*) AS orders,SUM(total_amount) AS revenue,AVG(total_amount) AS avg_order_value
FROM orders 
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE_FORMAT(order_date, '%Y-%m-%d')
ORDER BY date;

产品分析

-- 热门产品
SELECT p.name,COUNT(o.id) AS order_count,SUM(o.quantity) AS total_quantity,AVG(p.price) AS avg_price
FROM products p
JOIN order_items o ON p.id = o.product_id
GROUP BY p.id, p.name
ORDER BY order_count DESC
LIMIT 10;

---

�� 常用函数速查

字符串函数

• LENGTH(), UPPER(), LOWER()
• SUBSTRING(), CONCAT(), REPLACE()
• TRIM(), LPAD(), RPAD()

数值函数

• ROUND(), CEIL(), FLOOR()
• ABS(), POWER(), SQRT()
• RAND(), MOD()

日期函数

• NOW(), CURDATE(), CURTIME()
• YEAR(), MONTH(), DAY()
• DATE_ADD(), DATE_SUB(), DATEDIFF()

聚合函数

• COUNT(), SUM(), AVG()
• MAX(), MIN(), STDDEV()
• PERCENTILE(), CARDINALITY()