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

注意：这时会将 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=FLOAT TAGS(city=Shanghai, workshop=w2);
CREATE TIMESERIES root.factory1.d8.temperature WITH DATATYPE=FLOAT;
CREATE TIMESERIES root.factory1.d9.temperature WITH DATATYPE=FLOAT;
INSERT INTO root.factory1.d1(time, temperature) VALUES (1000, 104.0), (3000, 104.2), (5000, 103.3), (7000, 104.1);
INSERT INTO root.factory1.d2(time, temperature) VALUES (1000, 104.4), (3000, 103.7), (5000, 103.3), (7000, 102.9);
INSERT INTO root.factory1.d3(time, temperature) VALUES (1000, 103.9), (3000, 103.8), (5000, 102.7), (7000, 106.9);
INSERT INTO root.factory1.d4(time, temperature) VALUES (1000, 103.9), (5000, 102.7), (7000, 106.9);
INSERT INTO root.factory1.d5(time, temperature) VALUES (1000, 112.9), (7000, 113.0);
INSERT INTO root.factory1.d6(time, temperature) VALUES (1000, 113.9), (3000, 113.3), (5000, 112.7), (7000, 112.3);
INSERT INTO root.factory1.d7(time, temperature) VALUES (1000, 101.2), (3000, 99.3), (5000, 100.1), (7000, 99.8);
INSERT INTO root.factory1.d8(time, temperature) VALUES (1000, 50.0), (3000, 52.1), (5000, 50.1), (7000, 50.5);
INSERT INTO root.factory1.d9(time, temperature) VALUES (1000, 50.3), (3000, 52.1);

1.4 单标签聚合查询

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

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

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

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

1. 标签聚合查询的聚合结果不会再做去星号展开，而是将多个时间序列的数据作为一个整体进行聚合计算
2. 标签聚合查询除了输出聚合结果列，还会输出聚合标签的键值列。该列的列名为聚合指定的标签键，列的值则为所有查询的时间序列中出现的该标签的值。 如果某些时间序列未设置该标签，则在键值列中有一行单独的 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 的本质区别

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

2.2 注意事项

1. 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

2. 对 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 正确使用案例

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

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

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

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

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

3.1 需求分析

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

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

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 的强大功能，创造真正的业务价值。