Apache IoTDB 深度解析时序数据聚合的 GROUP BY 与 HAVING 子句

Apache IoTDB 深度解析时序数据聚合的 GROUP BY 与 HAVING 子句 | 极客日志

Apache IoTDB 深度解析时序数据聚合的 GROUP BY 与 HAVING 子句

引言

在工业物联网场景中，某设备监控系统每秒产生超过 2 万条包含温度、压力、振动幅度的多维时序数据。若直接存储原始数据，单日存储量将突破 200GB。通过 IoTDB 的分组聚合（GROUP BY）与聚合结果过滤（HAVING）子句的协同使用，保证分析结果的精准性。

不同于传统数据库的聚合操作，IoTDB 针对时序数据的特性进行了深度优化，支持时间窗口、设备层级、标签等多维分组方式，并可通过 HAVING 子句实现聚合结果的精准过滤。

一、分组聚合（GROUP BY）

1.1 路径层级分组聚合

在时间序列层级结构中，路径层级分组聚合查询用于对某一层级下同名的序列进行聚合查询。

使用 GROUP BY LEVEL = INT 来指定需要聚合的层级，并约定 ROOT 为第 0 层。若统计 "root.ln" 下所有序列则需指定 level 为 1。路径层次分组聚合查询支持使用所有内置聚合函数。对于 sum, avg, min_value, max_value, extreme 五种聚合函数，需保证所有聚合的时间序列数据类型相同。其他聚合函数没有此限制。

练习 1：不同 database 下均存在名为 status 的序列 如 "root.ln.wf01.wt01.status", "root.ln.wf02.wt02.status", 以及 "root.sgcc.wf03.wt01.status", 如果需要统计不同 database 下 status 序列的数据点个数，使用以下查询：

SELECT COUNT(status) FROM root.** GROUP BY LEVEL = 1;

练习 2：统计不同设备下 status 序列的数据点个数 可以规定 level = 3：

SELECT COUNT(status) FROM root.** GROUP BY LEVEL = 3;

注意：这时会将 database ln 和 sgcc 下名为 wt01 的设备视为同名设备聚合在一起。

练习 3：统计不同 database 下的不同设备中 status 序列的数据点个数

SELECT COUNT(status) FROM root.** GROUP BY LEVEL = 1, 3;

练习 4：查询所有序列下温度传感器 temperature 的最大值 可以使用下列查询语句：

SELECT MAX_VALUE(temperature) FROM root.** GROUP BY LEVEL = 0;

练习 5：查询某一层级下所有传感器拥有的总数据点数 则需要显式规定测点为 *：

SELECT COUNT(*) FROM root.ln.** GROUP BY LEVEL = 2;

1.2 与时间区间分段聚合混合使用

通过定义 LEVEL 来统计指定层级下的数据点个数。

练习 1：统计降采样后的数据点个数

SELECT COUNT(status) FROM root.ln.wf01.wt01 GROUP BY ((2017-11-01T00:00:00, 2017-11-07T23:00:00], 1d), LEVEL = 1;

练习 2：加上滑动 Step 的降采样后的结果也可以汇总

SELECT COUNT(status) FROM root.ln.wf01.wt01 GROUP BY ([2017-11-01 00:00:00, 2017-11-07 23:00:00), 3h, 1d), LEVEL = 1;

1.3 标签分组聚合

IoTDB 支持通过 GROUP BY TAGS 语句根据时间序列中定义的标签的键值做分组聚合查询。

我们先在 IoTDB 中写入如下示例数据，稍后会以这些数据为例介绍标签聚合查询。

这些是某工厂 factory1 在多个城市的多个车间的设备温度数据，时间范围为 [1000, 10000)。

时间序列路径中的设备一级是设备唯一标识。城市信息 city 和车间信息 workshop 则被建模在该设备时间序列的标签中。其中，设备 d1、d2 在 Beijing 的 w1 车间，d3、d4 在 Beijing 的 w2 车间，d5、d6 在 Shanghai 的 w1 车间，d7 在 Shanghai 的 w2 车间。 d8 和 d9 设备目前处于调试阶段，还未被分配到具体的城市和车间，所以其相应的标签值为空值。

CREATE DATABASE root.factory1;
CREATE TIMESERIES root.factory1.d1.temperature WITH DATATYPE=FLOAT TAGS(city='Beijing', workshop='w1');
CREATE TIMESERIES root.factory1.d2.temperature WITH DATATYPE=FLOAT TAGS(city='Beijing', workshop='w1');
CREATE TIMESERIES root.factory1.d3.temperature WITH DATATYPE=FLOAT TAGS(city='Beijing', workshop='w2');
CREATE TIMESERIES root.factory1.d4.temperature WITH DATATYPE=FLOAT TAGS(city='Beijing', workshop='w2');
CREATE TIMESERIES root.factory1.d5.temperature WITH DATATYPE=FLOAT TAGS(city='Shanghai', workshop='w1');
CREATE TIMESERIES root.factory1.d6.temperature WITH DATATYPE=FLOAT TAGS(city='Shanghai', workshop='w1');
CREATE TIMESERIES root.factory1.d7.temperature WITH DATATYPE= TAGS(city, workshop);
 TIMESERIES root.factory1.d8.temperature  DATATYPE;
 TIMESERIES root.factory1.d9.temperature  DATATYPE;
 root.factory1.d1(, temperature)  (, ), (, ), (, ), (, );
 root.factory1.d2(, temperature)  (, ), (, ), (, ), (, );
 root.factory1.d3(, temperature)  (, ), (, ), (, ), (, );
 root.factory1.d4(, temperature)  (, ), (, ), (, );
 root.factory1.d5(, temperature)  (, ), (, );
 root.factory1.d6(, temperature)  (, ), (, ), (, ), (, );
 root.factory1.d7(, temperature)  (, ), (, ), (, ), (, );
 root.factory1.d8(, temperature)  (, ), (, ), (, ), (, );
 root.factory1.d9(, temperature)  (, ), (, );

1.4 单标签聚合查询

统计该工厂每个地区的设备的温度的平均值，可以使用如下查询语句：

SELECT AVG(temperature) FROM root.factory1.** GROUP BY TAGS(city);

该查询会将具有同一个 city 标签值的时间序列的所有满足查询条件的点做平均值计算。

从结果集中可以看到，和分段聚合、按层次分组聚合相比，标签聚合的查询结果的不同点是：

标签聚合查询的聚合结果不会再做去星号展开，而是将多个时间序列的数据作为一个整体进行聚合计算。
标签聚合查询除了输出聚合结果列，还会输出聚合标签的键值列。该列的列名为聚合指定的标签键，列的值则为所有查询的时间序列中出现的该标签的值。如果某些时间序列未设置该标签，则在键值列中有一行单独的 NULL，代表未设置标签的所有时间序列数据的聚合结果。

1.5 多标签分组聚合查询

除了基本的单标签聚合查询外，还可以按顺序指定多个标签进行聚合计算。

例如，统计每个城市的每个车间内设备的平均温度。但因为各个城市的车间名称有可能相同，所以不能直接按照 workshop 做标签聚合。必须要先按照城市，再按照车间处理。

SELECT AVG(temperature) FROM root.factory1.** GROUP BY TAGS(city, workshop);

从结果集中可以看到，和单标签聚合相比，多标签聚合的查询结果会根据指定的标签顺序，输出相应标签的键值列。

1.6 基于时间区间的标签聚合查询

按照时间区间聚合是时序数据库中最常用的查询需求之一。IoTDB 在基于时间区间的聚合基础上，支持进一步按照标签进行聚合查询。

例如，统计时间 [1000, 10000) 范围内，每个城市每个车间中的设备每 5 秒内的平均温度。

SELECT AVG(temperature) FROM root.factory1.** GROUP BY ([1000, 10000), 5s), TAGS(city, workshop);

和标签聚合相比，基于时间区间的标签聚合的查询会首先按照时间区间划定聚合范围，在时间区间内部再根据指定的标签顺序，进行相应数据的聚合计算。在输出的结果集中，会包含一列时间列，该时间列值的含义和时间区间聚合查询的相同。

二、聚合结果过滤（HAVING）

如果想对聚合查询的结果进行过滤，可以在 GROUP BY 子句之后使用 HAVING 子句。

2.1 HAVING 与 WHERE 的本质区别

对比项	WHERE 子句	HAVING 子句
执行阶段	分组前执行（行级过滤）	分组后执行（组级过滤）
引用对象	原始列或常量	聚合函数结果
索引适用性	可利用 B+ 树索引加速	无法直接使用索引
典型场景	数据预筛选	聚合结果二次筛选

在工业质检场景中，通过 SELECT device_id, count(*) FROM production GROUP BY device_id HAVING count(*) > 1000 可快速定位产量异常设备。配合 WHERE 子句的 AND quality_status='failed' 实现缺陷设备的精准定位，使质检效率提升 50%。

2.2 注意事项

HAVING 子句中的过滤条件必须由聚合值构成，原始序列不能单独出现。

下列使用方式是不正确的：

-- 错误示例
SELECT COUNT(s1) FROM root.** GROUP BY ([1, 3), 1ms) HAVING SUM(s1) > s1;
SELECT COUNT(s1) FROM root.** GROUP BY ([1, 3), 1ms) HAVING s1 > 1;

对 GROUP BY LEVEL 结果进行过滤时，SELECT 和 HAVING 中出现的 PATH 只能有一级。

下列使用方式是不正确的：

-- 错误示例
SELECT COUNT(s1) FROM root.** GROUP BY ([1, 3), 1ms), LEVEL = 1 HAVING SUM(d1.s1) > 1;
SELECT COUNT(d1.s1) FROM root.** GROUP BY ([1, 3), 1ms), LEVEL = 1 HAVING SUM(s1) > 1;

2.3 正确使用案例

对于以下聚合结果进行过滤：

SELECT COUNT(s1) FROM root.** GROUP BY ([1, 11), 2ms), LEVEL = 1 HAVING COUNT(s2) > 2;

对于以下聚合结果进行过滤：

SELECT COUNT(s1), COUNT(s2) FROM root.** GROUP BY ([1, 11), 2ms) HAVING COUNT(s2) > 1 ALIGN BY DEVICE;

三、智慧城市交通流量分析实战案例

3.1 需求分析

某智慧城市项目需要实现：

每 5 分钟统计各路口交通流量
筛选出平均车速低于 30km/h 且拥堵指数超过 1.5 的路口
补全缺失的车流量数据以保证可视化效果

3.2 GROUP BY 与 HAVING 协同查询实现

SELECT intersection_id, AVG(speed) AS avg_speed, COUNT(*) FILTER (WHERE status='congestion') AS congestion_count 
FROM root.city.traffic 
WHERE time >= '2023-12-01 00:00:00' AND time <= '2023-12-07 23:59:59'
GROUP BY TIME(5m), intersection_id 
HAVING avg_speed < 30 AND congestion_count > 1.5;

3.3 结果可视化方案

通过 Grafana 集成 IoTDB 数据源。

实现：

交通流量热力图，实时显示各路口拥堵情况
车速趋势图，展示平均车速变化趋势
拥堵指数排行榜，快速定位高拥堵路口
实时告警看板，自动推送拥堵预警信息

四、总结

Apache IoTDB 的 GROUP BY 和 HAVING 子句构成了时序数据分析的完整工具链。通过合理配置和优化，实现了查询效率提升、存储空间减少、数据完整率提高、业务洞察能力的提升。本文详细讲述了 GROUP BY 和 HAVING 子句的具体使用和案例，能够帮助在实际项目中充分发挥 IoTDB 的强大功能，创造真正的业务价值。

Apache IoTDB 深度解析时序数据聚合的 GROUP BY 与 HAVING 子句