时序数据库 TDengine SQL 查询语法规则汇总,官方教程奉上!

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
简介: TDengine 采用 SQL 作为查询语言,本文将就部分查询细则做分析。

小 T 导读:尽管时序数据处理的特点是以写操作为主,读操作为辅,但查询需求也不容忽视。为方便用户上手,时序数据库Time Series DatabaseTDengine 采用 SQL 作为查询语言,主要查询功能包括单列及多列数据查询、数值列及聚合结果的四则运算、时间戳对齐的连接查询操作等,本文将就部分查询细则做分析。

《查询性能:TDengine 最高达到了 InfluxDB 的 37 倍、 TimescaleDB 的 28.6 倍》中,我们了解到了 TDengine 在查询方面展现出的具体实力。但想要更好地发挥出 TDengine 的查询性能,在实际操作上,也还有一些事项需要注意,部分内容汇总如下:

TDengine SQL 查询语句可以指定部分或全部列作为返回结果。数据列和标签列都可以出现在列表中。

通配符和标签列

通配符 * 可以用于代指全部列。对于普通表和子表,结果中只有普通列。对于超级表,还包含了 Tag 列。

  1. SELECT * FROM d1001;

通配符支持表名前缀,以下两个 SQL 语句均为返回全部的列:

  1. SELECT * FROM d1001;
  2. SELECT d1001.* FROM d1001;

在 JOIN 查询中,带表名前缀的*和不带前缀*返回的结果有差别, *返回全部表的所有列数据(不包含标签),而带表名前缀的通配符,则只返回该表的列数据。

  1. SELECT * FROM d1001, d1003 WHERE d1001.ts=d1003.ts;
  2. SELECT d1001.* FROM d1001,d1003 WHERE d1001.ts = d1003.ts;

上面的查询语句中,前者返回 d1001 和 d1003 的全部列,而后者仅返回 d1001 的全部列。

在使用 SQL 函数来进行查询的过程中,部分 SQL 函数支持通配符操作。其中的区别在于: count(*)函数只返回一列。firstlastlast_row函数则是返回全部列。

此外,在超级表和子表的查询中我们也可以指定标签列,且标签列的值会与普通列的数据一起返回。

  1. SELECT location, groupid, current FROM d1001 LIMIT 2;

结果去重

DISTINCT 关键字可以对结果集中的一列或多列进行去重,去除的列既可以是标签列也可以是数据列。

对标签列去重:

  1. SELECT DISTINCT tag_name [, tag_name ...] FROM stb_name;

对数据列去重:

  1. SELECT DISTINCT col_name [, col_name ...] FROM tb_name;

需要注意:

  1. cfg 文件中的配置参数 maxNumOfDistinctRes 将对 DISTINCT 能够输出的数据行数进行限制。其最小值是 100000,最大值是 100000000,默认值是 10000000。如果实际计算结果超出了这个限制,那么会仅输出这个数量范围内的部分。
  2. 由于浮点数天然的精度机制原因,在特定情况下,对 FLOAT 和 DOUBLE 列使用 DISTINCT 并不能保证输出值的完全唯一性。

特殊功能

部分特殊的查询功能可以不使用 FROM 子句执行。

下面的命令可以获取当前所在的数据库 database(),如果登录的时候没有指定默认数据库,且没有使用USE命令切换数据,则返回 NULL。

  1. SELECT DATABASE();

获取服务器和客户端版本号:

  1. SELECT CLIENT_VERSION();
  2. SELECT SERVER_VERSION();

服务器状态检测语句。如果服务器正常,返回一个数字(例如 1)。如果服务器异常,返回 error code。该 SQL 语法能兼容连接池对于 TDengine 状态的检查及第三方工具对于数据库服务器状态的检查。并可以避免出现使用了错误的心跳检测 SQL 语句导致的连接池连接丢失的问题。

  1. SELECT SERVER_STATUS();

我们可以使用SELECT NOW();来获取当前时间,使用SELECT TODAY();来获取当前日期,使用SELECT TIMEZONE();获取当前时区。

正则表达式过滤

语法

  1. WHERE (column|tbname) match/MATCH/nmatch/NMATCH _regex_

正则表达式规范

确保使用的正则表达式符合 POSIX 的规范,具体规范内容可参见Regular Expressions

使用限制

不仅能针对表名(即 tbname 筛选)、binary/nchar 类型标签值进行正则表达式过滤,也支持普通列的过滤。

正则匹配字符串长度不能超过 128 字节。可以通过参数 maxRegexStringLen 设置和调整最大允许的正则匹配字符串,该参数是客户端配置参数,需要重启才能生效。

CASE 表达式

语法

  1. CASE value WHEN compare_value THEN result [WHEN compare_value THEN result ...][ELSE result]END
  2. CASE WHEN condition THEN result [WHEN condition THEN result ...][ELSE result]END

说明

TDengine 通过 CASE 表达式让用户可以在 SQL 语句中使用 IF … THEN … ELSE 逻辑。

第一种 CASE 语法返回第一个 value 等于 compare_value 的 result,如果没有 compare_value 符合,则返回 ELSE 之后的 result,如果没有 ELSE 部分,则返回 NULL。

第二种语法返回第一个 condition 为真的 result。 如果没有 condition 符合,则返回 ELSE 之后的 result,如果没有 ELSE 部分,则返回 NULL。

CASE 表达式的返回类型为第一个 WHEN THEN 部分的 result 类型,其余 WHEN THEN 部分和 ELSE 部分,result 类型都需要可以向其转换,否则 TDengine 会报错。

示例

某设备有三个状态码,显示其状态,语句如下:

  1. SELECT CASE dev_status WHEN 1 THEN 'Running' WHEN 2 THEN 'Warning' WHEN 3 THEN 'Downtime' ELSE 'Unknown'END FROM dev_table;

统计智能电表的电压平均值,当电压小于 200 或大于 250 时认为是统计有误,修正其值为 220,语句如下:

  1. SELECT AVG(CASE WHEN voltage <200or voltage >250 THEN 220 ELSE voltage END) FROM meters;

JOIN 子句

TDengine 支持基于时间戳主键的内连接,即 JOIN 条件必须包含时间戳主键。只要满足基于时间戳主键这个要求,普通表、子表、超级表和子查询之间可以随意的进行内连接,且对表个数没有限制。

普通表与普通表之间的 JOIN 操作:

  1. SELECT *
  2. FROM temp_tb_1 t1, pressure_tb_1 t2
  3. WHERE t1.ts = t2.ts

超级表与超级表之间的 JOIN 操作:

  1. SELECT *
  2. FROM temp_stable t1, temp_stable t2
  3. WHERE t1.ts = t2.ts AND t1.deviceid = t2.deviceid AND t1.status=0;

子表与超级表之间的 JOIN 操作:

  1. SELECT *
  2. FROM temp_ctable t1, temp_stable t2
  3. WHERE t1.ts = t2.ts AND t1.deviceid = t2.deviceid AND t1.status=0;

类似地,也可以对多个子查询的查询结果进行 JOIN 操作。

嵌套查询

“嵌套查询”又称为“子查询”,也即在一条 SQL 语句中,“内层查询”的计算结果可以作为“外层查询”的计算对象来使用。

从 2.2.0.0 版本开始,TDengine 的查询引擎开始支持在 FROM 子句中使用非关联子查询(“非关联”的意思是,子查询不会用到父查询中的参数)。也即在普通 SELECT 语句的 tb_name_list 位置,用一个独立的 SELECT 语句来代替(这一 SELECT 语句被包含在英文圆括号内),于是完整的嵌套查询 SQL 语句形如:

  1. SELECT ... FROM (SELECT ... FROM ...)...;

需要注意:

  • 内层查询的返回结果将作为“虚拟表”供外层查询使用,此虚拟表建议起别名,以便于外层查询中方便引用。
  • 在内层和外层查询中,都支持普通的表间/超级表间 JOIN。内层查询的计算结果也可以再参与数据子表的 JOIN 操作。
  • 内层查询支持的功能特性与非嵌套的查询语句能力是一致的。
  • 内层查询的 ORDER BY 子句一般没有意义,建议避免这样的写法以免无谓的资源消耗。
  • 与非嵌套的查询语句相比,外层查询所能支持的功能特性存在如下限制(计算函数部分):
  • 如果内层查询的结果数据未提供时间戳,那么计算过程隐式依赖时间戳的函数在外层会无法正常工作。例如:INTERP, DERIVATIVE, IRATE, LAST_ROW, FIRST, LAST, TWA, STATEDURATION, TAIL, UNIQUE。
  • 如果内层查询的结果数据不是按时间戳有序,那么计算过程依赖数据按时间有序的函数在外层会无法正常工作。例如:LEASTSQUARES, ELAPSED, INTERP, DERIVATIVE, IRATE, TWA, DIFF, STATECOUNT, STATEDURATION, CSUM, MAVG, TAIL, UNIQUE。
  • 计算过程需要两遍扫描的函数,在外层查询中无法正常工作。例如:此类函数包括:PERCENTILE。

写在最后

受篇幅所限,本文仅阐述了在进行 SQL 查询时部分细则及注意事项,关于结果集列名、伪列、查询对象、GROUP BY等子句的使用规则以及相关语法示例,大家可以进入官网文档——https://docs.taosdata.com/taos-sql/select/#group-by 进行查阅。在使用 TDengine 执行 SQL 查询时,以上实操手册会帮助你解决一系列基础问题。但如果你遇到的问题迟迟未能解决,也不要着急,可以联系我们,向 TDengine 技术人员寻求帮助。

目录
相关文章
|
17天前
|
SQL 存储 人工智能
Vanna:开源 AI 检索生成框架,自动生成精确的 SQL 查询
Vanna 是一个开源的 Python RAG(Retrieval-Augmented Generation)框架,能够基于大型语言模型(LLMs)为数据库生成精确的 SQL 查询。Vanna 支持多种 LLMs、向量数据库和 SQL 数据库,提供高准确性查询,同时确保数据库内容安全私密,不外泄。
84 7
Vanna:开源 AI 检索生成框架,自动生成精确的 SQL 查询
|
3天前
|
SQL Oracle 数据库
使用访问指导(SQL Access Advisor)优化数据库业务负载
本文介绍了Oracle的SQL访问指导(SQL Access Advisor)的应用场景及其使用方法。访问指导通过分析给定的工作负载,提供索引、物化视图和分区等方面的优化建议,帮助DBA提升数据库性能。具体步骤包括创建访问指导任务、创建工作负载、连接工作负载至访问指导、设置任务参数、运行访问指导、查看和应用优化建议。访问指导不仅针对单条SQL语句,还能综合考虑多条SQL语句的优化效果,为DBA提供全面的决策支持。
22 11
|
17天前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。
|
16天前
|
SQL 监控 安全
SQL Servers审核提高数据库安全性
SQL Server审核是一种追踪和审查SQL Server上所有活动的机制,旨在检测潜在威胁和漏洞,监控服务器设置的更改。审核日志记录安全问题和数据泄露的详细信息,帮助管理员追踪数据库中的特定活动,确保数据安全和合规性。SQL Server审核分为服务器级和数据库级,涵盖登录、配置变更和数据操作等事件。审核工具如EventLog Analyzer提供实时监控和即时告警,帮助快速响应安全事件。
|
24天前
|
SQL Java
使用java在未知表字段情况下通过sql查询信息
使用java在未知表字段情况下通过sql查询信息
35 8
|
1月前
|
SQL 缓存 Java
【详细实用のMyBatis教程】获取参数值和结果的各种情况、自定义映射、动态SQL、多级缓存、逆向工程、分页插件
本文详细介绍了MyBatis的各种常见用法MyBatis多级缓存、逆向工程、分页插件 包括获取参数值和结果的各种情况、自定义映射resultMap、动态SQL
【详细实用のMyBatis教程】获取参数值和结果的各种情况、自定义映射、动态SQL、多级缓存、逆向工程、分页插件
|
1月前
|
SQL 安全 PHP
PHP开发中防止SQL注入的方法,包括使用参数化查询、对用户输入进行过滤和验证、使用安全的框架和库等,旨在帮助开发者有效应对SQL注入这一常见安全威胁,保障应用安全
本文深入探讨了PHP开发中防止SQL注入的方法,包括使用参数化查询、对用户输入进行过滤和验证、使用安全的框架和库等,旨在帮助开发者有效应对SQL注入这一常见安全威胁,保障应用安全。
54 4
|
1月前
|
SQL 监控 关系型数据库
SQL语句当前及历史信息查询-performance schema的使用
本文介绍了如何使用MySQL的Performance Schema来获取SQL语句的当前和历史执行信息。Performance Schema默认在MySQL 8.0中启用,可以通过查询相关表来获取详细的SQL执行信息,包括当前执行的SQL、历史执行记录和统计汇总信息,从而快速定位和解决性能瓶颈。
|
1月前
|
SQL 存储 缓存
如何优化SQL查询性能?
【10月更文挑战第28天】如何优化SQL查询性能?
122 10
|
1月前
|
SQL 关系型数据库 MySQL
下一篇
DataWorks