当前位置： 首页 > web >正文

OpenSearch SQL 查询完整指南

web 2025/7/17 17:09:15

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()
http://www.xdnf.cn/news/15650.html

相关文章：

  • STM32-DMA
  • 数字魔方--玩转魔方的助手
  • oracle2kingbase的字段长度问题
  • 50天50个小项目 (Vue3 + Tailwindcss V4) ✨ | AutoTextEffect（自动打字机）
  • 尚庭公寓-------图片上传接口
  • 【c++深入系列】：万字详解list（附模拟实现的list源码）
  • 【unitrix】 6.4 类型化数特征(t_number.rs)
  • JavaScript进阶篇——第六章 内置构造函数与内置方法
  • 21、鸿蒙Harmony Next开发：组件导航(Navigation)
  • 主机安全---开源wazuh安装
  • 【Unity】IL2CPP相关理论知识学习
  • Spring MVC中@PathVariable的用法详解
  • AR智能巡检：电力行业数字化转型的“加速器”
  • Java 8新特性 函数式编程实践
  • python基础②-数据结构
  • 第二十一 篇 PDF文档自动化：Python一键合并、分割、水印、提取与加密解密！你的PDF全能管家！
  • 《通信原理》学习笔记——第二章
  • 【前端】Vue3 前端项目实现动态显示当前系统时间
  • 【TVM 教程】FAQ
  • 筑牢网络安全防线：DDoS/CC 攻击全链路防护技术解析
  • Hadoop（三）
  • PyTorch 损失函数详解：从理论到实践
  • Qt小组件 - 7 SQL Thread Qt访问数据库ORM
  • Uniapp中双弹窗为什么无法显示?
  • 玩转Docker | 使用Docker部署bender个人导航页工具
  • 利用Java自定义格式，循环导出数据、图片到excel
  • 【论文阅读 | CVPR 2023 |CDDFuse：基于相关性驱动的双分支特征分解的多模态图像融合】
  • lua（xlua）基础知识点记录
  • 【前端】在Vue3中绘制多系列柱状图与曲线图
  • 量子比特耦合与系统集成：量子计算硬件的核心突破