一个有关聚合查询的问题（A problem about aggregation query）

[LIU-WEI-git]

为了对部分字段进行索引，我将索引的值存储为LayerName。比如下面这个profile_task_segment_snapshot表（见Figure 1），它有6个字段，分别是id, sequence, dump_time, stack_binary, task_id, segment_id。我将它的id的值作为path的一部分，以实现索引的效果。最终同一个表的数据被分散到多个device中。

当我需要查询sequence的最大值时，我必须同时使用聚合函数max_value和group by level，后者的作用是合并分散在多个device下的数据。比如select max_value(sequence) from root.skywalking.profile_task_segment_snapshot.* group by level = 2（见Figure 2）。查询结果没有问题。

当我需要查询在所有segment_id为某个值的数据中sequence的最大值时，我必须使用align by device，否则结果将为null（见Figure 3）。比如：select max_value(sequence) from root.skywalking.profile_task_segment_snapshot.* where segment_id = "segment_id_1" align by device。但这个查询结果是按照每个device分开统计的，没有合并device。

接着我尝试了select max_value(sequence) from root.skywalking.profile_task_segment_snapshot.* where segment_id = "segment_id_1" group by level = 2，但结果只有一个null。我觉得应该要加上align by device的语义。所以又尝试了select max_value(sequence) from root.skywalking.profile_task_segment_snapshot.* where segment_id = "segment_id_1" group by level = 2 align by device，但这样会引发IllegalPathException（见Figure 4）。我想是因为IoTDB不能同时支持group by level和align by device的语义。

请问还有别的查询可以满足我的需求的吗？不然我只能select * from root.skywalking.profile_task_segment_snapshot.* where segment_id = "segment_id_1" align by device后自行实现max_value的功能了

OS: Win10, Version: 0.12.2

---

In order to index some fields, I store the value of the index as LayerName. For example, there is a table called profile_task_segment_snapshot (See Figure 1). It has 6 fields: id, sequence, dump_time, stack_binary, task_id, segment_id. I make the value of id as part of the storage path to achieve the effect of index. Eventually the data from the same table is spread across multiple devices.

When I need to query the max value of sequence, I must use aggregation function max_value and group by level. The latter is used to merge data spread across multiple devices. For instance, select max_value(sequence) from root.skywalking.profile_task_segment_snapshot.* group by level = 2 (See Figure 2). It works well.

When I need to query the max value of sequence in all data whose segment_id is a certain value, I must use align by device, otherwise the result will be null (See Figure 3). For instance: select max_value(sequence) from root.skywalking.profile_task_segment_snapshot.* where segment_id = "segment_id_1" align by device. However, this query result is calculated separately by device, without merging devices.

Then I tried select max_value(sequence) from root.skywalking.profile_task_segment_snapshot.* where segment_id = "segment_id_1" group by level = 2, but the result is just a null. I think we should add the semantics of align by device. So I tried select max_value(sequence) from root.skywalking.profile_task_segment_snapshot.* where segment_id = "segment_id_1" group by level = 2 align by device, but it will throw IllegalPathException (See Figure 4). I think it's because IoTDB does not support the semantics of group by level and align by device at the same time.

Is there any other query that can meet my needs? Otherwise I can only use select * from root.skywalking.profile_task_segment_snapshot.* where segment_id = "segment_id_1" align by device, then implement the max_value function by my self.

OS: Win10, Version: 0.12.2

• Figure 1
  
• Figure 2
  
• Figure 3
  
• Figure 4
  

[jun0315]

Hello, thank you for your question. I have encountered this problem before and communicated with the people in the community. It is difficult to support align by device and group by level together. The result set of align by device is in the form of a relational table with device and time as the primary key, while group by level is suitable for the IoTDB tree structure data model. The two are not compatible in themselves. If such a situation is supported, the original align by device semantics will be violated, and the resulting set will have no device column. Therefore, they do not agree with the implementation of group by level in align by device. You can get the value of the aggregate function and manually traverse it again, which is also implemented inside IoTDB.

[jun0315]

At the same time, thank you very much for describing this problem in detail :D

[LIU-WEI-git]

Thanks for your explanation. : )

[jun0315]

This is mainly thanks to @Alima777 detailed answers～