企业官网建设流程全解析

如何用 TDengine 的 JOIN 关联查询组合多张表的数据

【免费下载链接】TDengineHigh-performance, scalable time-series database designed for Industrial IoT (IIoT) scenarios项目地址: https://gitcode.com/GitHub_Trending/tde/TDengine

当一张表的数据不够用——例如需要把不同电表、不同设备同一时刻的电压放在一起分析，或在两张表时间戳略有偏差时按"最接近时刻"配对——就需要用 JOIN 关联查询把多张表组合成一个结果集。本文以 TDengine 文档中的智能电表模型为例，在taosshell 中完成一条完整路径：建库建表、写入示例数据，然后分别用INNER JOIN、LEFT JOIN、ASOF JOIN和WINDOW JOIN组合两张表的数据，并说明如何判断结果是否符合预期。

前提：TDengine 服务已启动并能通过 shell 连接（可用 Docker 快速体验 启动服务后进入容器），完整语法参考见 关联查询。

准备条件与写入测试数据

在 shell 中执行下面的 SQL，创建power数据库、meters超级表和三张子表。meters的时间戳列ts是主键列，后续所有 JOIN 都围绕它做连接条件：

CREATE DATABASE IF NOT EXISTS power PRECISION 'ms' KEEP 3650 DURATION 10 BUFFER 16; USE power; CREATE STABLE IF NOT EXISTS meters ( ts timestamp, current float, voltage int, phase float ) TAGS ( location varchar(64), group_id int ); CREATE TABLE IF NOT EXISTS d1001 USING meters TAGS ("California.SanFrancisco", 2); CREATE TABLE IF NOT EXISTS d1002 USING meters TAGS ("California.SanFrancisco", 3); CREATE TABLE IF NOT EXISTS d1003 USING meters TAGS ("California.LosAngeles", 2);

再写入 数据写入文档中的示例数据，一条INSERT同时写入三张子表，共 9 条记录：

INSERT INTO d1001 VALUES ("2018-10-03 14:38:05", 10.2, 220, 0.23), ("2018-10-03 14:38:15", 12.6, 218, 0.33), ("2018-10-03 14:38:25", 12.3, 221, 0.31) d1002 VALUES ("2018-10-03 14:38:04", 10.2, 220, 0.23), ("2018-10-03 14:38:14", 10.3, 218, 0.25), ("2018-10-03 14:38:24", 10.1, 220, 0.22) d1003 VALUES ("2018-10-03 14:38:06", 11.5, 221, 0.35), ("2018-10-03 14:38:16", 10.4, 220, 0.36), ("2018-10-03 14:38:26", 10.3, 220, 0.33);

写入后可用SELECT * FROM d1001;确认返回 3 行。注意这组示例数据的特点：d1001的时间戳是:05/:15/:25，d1002是:04/:14/:24，两表时刻整体错开 1 秒——后面正好用它区分等值连接和按最接近时刻连接的行为。

TDengine JOIN 的连接条件规则

写 SQL 前先明确三条规则，否则查询会直接报错或行为与预期不符（以下均出自关联查询文档）：

1. 除ASOF JOIN/WINDOW JOIN外，连接条件中必须包含主键时间戳列的等值主连接条件，例如ON a.ts = b.ts。主连接条件与追加的其他条件之间只支持AND；INNER JOIN的条件可写在ON和/或WHERE中，二者至少其一。
2. 主连接条件中的主键列只支持TIMETRUNCATE运算，不支持其他函数和标量运算。两张表时间戳精度不一致但需要按秒对齐时，可写ON TIMETRUNCATE(a.ts, 1s) = TIMETRUNCATE(b.ts, 1s)。
3. 所有 Join 都要求输入数据含有效的主键时间线。直接查表通常满足；用子查询参与 JOIN 时，需要确认子查询结果中有序且首次出现的主键列可作为主键时间线。

另外两点与结果形态直接相关：除FULL JOIN外，INNER/LEFT/RIGHT等结果保留主键时间线；FULL JOIN无法产生有效主键时间线，后续依赖时间线的运算无法在其结果上进行。对普通表、子表、子查询，且无分组条件、无排序时，结果按驱动表主键列顺序输出；超级表查询、FULL JOIN或带分组条件时没有固定输出顺序，需要排序时应显式写ORDER BY。

用 INNER JOIN 组合两表同时满足条件的数据

INNER JOIN只返回左右表中同时符合连接条件的行，可视为两表符合条件数据的交集。两种等价语法：

SELECT ... FROM table_name1 [INNER] JOIN table_name2 [ON ...] [WHERE ...] [...] 或 SELECT ... FROM table_name1, table_name2 WHERE ... [...]

文档示例：找出d1001与d1002中同时出现电压大于 220V 的时刻及各自电压值：

SELECT a.ts, a.voltage, b.voltage FROM d1001 a JOIN d1002 b ON a.ts = b.ts AND a.voltage > 220 AND b.voltage > 220;

在上述示例数据上执行这条语句，两表没有任何相同的时间戳，所以返回 0 行——这恰好验证了"只有a.ts = b.ts同时成立才返回"的等值语义。换成你自己的数据时，返回的就是两侧条件都命中的行。

用 LEFT JOIN 保留左表全部行

左外连接在INNER JOIN结果之外，还包含左表中不符合连接条件的行（右表对应位置为NULL）。OUTER关键字可选：

SELECT a.ts, a.voltage, b.voltage FROM d1001 a LEFT JOIN d1002 b ON a.ts = b.ts AND a.voltage > 220 AND b.voltage > 220;

在示例数据上，d1001的 3 行全部保留，b.voltage均为NULL（因为没有相同时间戳的行可匹配）。RIGHT JOIN语义对称，只是保留右表全部行。适合"以某张表为基准、有匹配就带出、没匹配留空"的场景。

同类还有LEFT/RIGHT SEMI JOIN（对应IN/EXISTS语义）和LEFT/RIGHT ANTI JOIN（对应NOT IN/NOT EXISTS语义），例如只取d1001中"其他电表同一时刻电压也大于 220V"的时刻：

SELECT a.ts FROM d1001 a LEFT SEMI JOIN meters b ON a.ts = b.ts AND a.voltage > 220 AND b.voltage > 220 AND b.tbname != 'd1001';

时间戳不完全一致时：ASOF JOIN 与 WINDOW JOIN

时序数据里两表采集时刻很难严格对齐，这是INNER JOIN经常查不到东西的原因。TDengine 提供两种面向时序的连接方式，注意两者都只支持表（超级表、普通表、子表）之间的连接，不支持子查询参与。

ASOF JOIN：按主键时间戳最接近匹配

ASOF JOIN允许不完全匹配：左表每一行与右表中符合连接条件、按主键列排序后时间戳最接近的行配对。ON子句可指定主键列（或主键列经TIMETRUNCATE运算）的单个匹配规则，支持以下运算符（Left ASOF 的含义）：

Right ASOF 时运算符含义相反。不写ON或ON中未指定主键匹配规则时，默认按>=处理。ON中还可追加标签、普通列的等值条件作为分组条件（不支持标量函数及运算），所有ON条件之间只支持AND。

文档示例：找d1001电压大于 220V 且d1002在同一时刻或稍早前最后时刻也出现电压大于 220V 的行：

SELECT a.ts, a.voltage, b.ts, b.voltage FROM d1001 a LEFT ASOF JOIN d1002 b ON a.ts >= b.ts WHERE a.voltage > 220 AND b.voltage > 220;

去掉过滤条件后在示例数据上执行，可以直观看到按最接近时刻配对的效果：14:38:05配14:38:04、14:38:15配14:38:14、14:38:25配14:38:24。

JLIMIT jlimit_num可选，用于限制单行匹配结果的最大行数，未指定时默认1（每行最多获得一行匹配结果），取值范围[0, 1024]。

WINDOW JOIN：按窗口边界连接

WINDOW JOIN以左表（Left Window Join）每一行的主键时间戳加上WINDOW_OFFSET(start_offset, end_offset)构造窗口，窗口左右边界均为闭区间，窗口内的连接条件由WINDOW_OFFSET指定，ON子句可选且只用于追加分组用的等值条件。偏移量支持自带时间单位：b（纳秒）、u（微秒）、a（毫秒）、s（秒）、m（分）、h（小时）、d（天）、w（周）；不支持自然月（n）和自然年（y）；支持的最小时间单位为数据库精度，左右表所在数据库精度需一致。

文档示例：d1001电压大于 220V 时，取前后 1 秒区间内d1002的电压值：

SELECT a.ts, a.voltage, b.voltage FROM d1001 a LEFT WINDOW JOIN d1002 b WINDOW_OFFSET(-1s, 1s) WHERE a.voltage > 220;

也支持在窗口内做聚合后按HAVING过滤每个窗口的聚合结果：

SELECT a.ts, a.voltage, AVG(b.voltage) FROM d1001 a LEFT WINDOW JOIN d1002 b WINDOW_OFFSET(-1s, 1s) WHERE a.voltage > 220 HAVING (AVG(b.voltage) > 220);

JLIMIT在WINDOW JOIN中含义不同：未指定时默认获取窗口内全部匹配行，指定后每个窗口最多返回jlimit_num条（超出时优先返回主键时间戳最小的若干条），取值范围[0, 1024]。在示例数据上，d1001每行的 ±1 秒窗口都恰好覆盖d1002对应的那一行。

验证结果是否符合预期

判断查询结果是否"对"，依据文档给出的各类型结果集定义：

• INNER JOIN：只出现两侧都满足连接条件的行，行数 ≤ 任一侧命中行数；
• LEFT JOIN：左表行数不减，无匹配行右表列为NULL；
• ASOF JOIN：左表每行至多获得jlimit_num条最接近时刻的右表行（右表不足时行数可能更少）；
• WINDOW JOIN：左表每行获得窗口内至多jlimit_num条右表行，或其聚合结果。

对d1001、d1002这类普通子表查询且未加分组条件、排序时，结果按驱动表主键列顺序输出；如果改为对超级表meters做 JOIN 或出现了分组条件，结果没有固定顺序，需要稳定顺序时显式加ORDER BY。

限制与边界

• 嵌套与多表：目前除INNER JOIN支持嵌套与多表 Join 外，其他类型的 Join 暂不支持嵌套与多表 Join。需要连三张及以上表时，只能基于INNER JOIN组合。
• FULL JOIN无主键时间线：全外连接无法产生有效主键时间序列，其结果上不能进行依赖时间线的运算。
• ASOF JOIN/WINDOW JOIN不支持子查询参与，只支持表之间。
• WINDOW JOIN的其他限制：SQL 中不能再含其他GROUP BY/PARTITION BY/ 窗口查询；不支持SLIMIT；不支持各种窗口伪列；HAVING中只支持聚合函数过滤，不支持标量过滤。
• 连接条件限制：主连接条件与其他连接条件之间只支持AND；分组条件只支持除主键列外的标签、普通列等值条件，不支持标量运算。
• 对超级表做INNER JOIN时，与主连接条件为AND关系的标签列等值条件会作为类似分组条件使用，输出结果不能保持有序。

完整的类型定义、语法与更多示例见 关联查询；本文的建表与写数步骤分别来自 数据建模 与 数据写入与更新。如果你的场景是对 JOIN 结果再加工，可继续看 基础查询 中的子查询与JOIN子句说明，以及 执行计划 用EXPLAIN查看 JOIN 的执行方式。

【免费下载链接】TDengineHigh-performance, scalable time-series database designed for Industrial IoT (IIoT) scenarios项目地址: https://gitcode.com/GitHub_Trending/tde/TDengine