一个有关排序查询的问题（A problem about sort query）

[LIU-WEI-git]

社区的朋友们好，我在使用IoTDB的时候遇到这样一个需求，请问各位有好的解决方法吗？
为了对部分字段进行索引，我将索引的值存储为LayerName。比如下面这个instance_traffic表，它有5个字段，分别是id, service_id, last_ping, name, properties。我将它的id和service_id的值作为path的一部分，以实现索引的效果。

现在我需要实现类似select * from root.skywalking.instance_traffic order by last_ping的排序查询，我应该如何实现呢？
我尝试了select top_k(last_ping, "k"="1") from root.skywalking.instance_traffic.*.*，我希望获得last_ping=2的那一条数据，但由于两条数据在两个device下，查询结果还是两条，并没有实现过滤。

请问这个问题如何解决呢？
ps：我使用的IoTDB版本是0.12.2

---

Hi, I met such a demand when using IoTDB. Could you give me some suggestions?
In order to index some fields, I store the value of the index as LayerName. For example, there is a table called instance_traffic. It has 5 fields: id, service_id, last_ping, name, properties. I make the value of id and service_id as part of the storage path to achieve the effect of index.

Now I need to realize a sort query which similar to select * from root.skywalking.instance_traffic order by last_ping. How to realize it?
I try to use select top_k(last_ping, "k"="1") from root.skywalking.instance_traffic.*.* and hope to get only a piece of data whose last_ping=2. However, because the two data are in two devices, the query result is still two, and filtering is not implemented.

How to solve this problem?
ps: My IoTDB version is 0.12.2

[SteveYurongSu]

谢谢你的提问 :D

我尝试了select top_k(last_ping, "k"="1") from root.skywalking.instance_traffic..，我希望获得last_ping=2的那一条数据，但由于两条数据在两个device下，查询结果还是两条，并没有实现过滤。

top_k这个函数的输出结果本质还是一个时间序列。select 多个时间序列的时候，结果集是默认按照多列的时间戳归并对齐的。
在你的场景下，每一个 top_k 的输出列最多只有一个非 null 的数据点，目前可以通过判断非 null 值的位置来获取过滤情况。

我猜你是想要类似 align by device 这样的表结构作为返回结果集？

---

Thanks for your questions!

I try to use select top_k(last_ping, "k"="1") from root.skywalking.instance_traffic.. and hope to get only a piece of data whose last_ping=2. However, because the two data are in two devices, the query result is still two, and filtering is not implemented.

The output result of the top_k function is essentially a time series. When selecting multiple time series, the result set is merged and aligned according to the timestamps of multiple columns by default.

In your scenario, each top_k output column has at most one non-null data point. Currently, you can get the filtering status by judging the position of the non-null value.

I guess you want a table structure like align by device as the return result set?

[LIU-WEI-git]

SteveYurongSu你好，感谢你回复。

非常认同你说的在我的场景下，每一个top_k的输出列最多只有一个非null的数据点，这是这个问题的关键。但我不认为可以通过判断非null值得位置来获取过滤结果。当有3条数据时，top_k(last_ping, "k"="2")的查询结果有3列，但每一列只有一个有效值，我依然无法获得排名前2的结果，而且存储了多少条数据，IoTDB就会返回多少行，这样查询和处理结果的开销将会非常大。除非我将查询结果再一次进行排序，但这样就不如直接select *后再进行排序过滤。

我希望获得类似align by device那样的表结构作为返回结果集，但遗憾的是系统并不支持这样。

---

Hi, SteveYurongSu. Thanks for your relpy.

I agree with you that each top_k output column has at most one non-null data point in my scenario. This is the key to this problem. But I don't think you can get the filtering result by judging the position of the non-null value. There are 3 pieces of data. The result of top_k(last_ping, "k"="2") has 3 column, but each column only has one valid value. So I still can't get the top 2 results. What's more, IoTDB will return as many rows as it stores. In this way, the cost of query and processing results will be very large. Unless I sort the query results again, but then it is better to implement sort and filter directly after SELECT *.

I want to get a table structure like align by device as the return result set. Unfortunately, IoTDB doesn't support this.

[SteveYurongSu]

了解你的使用场景了～

你可以画一下下面几个查询你期望的结果集长什么样吗？我们可以考虑作为一个新的feature来开发。

select *, top_k(last_ping, "k"="2") from root.skywalking.instance_traffic.*.* align by device

select top_k(*, "k"="2") from root.skywalking.instance_traffic.*.* align by device

select top_k(last_ping, "k"="2") from root.skywalking.instance_traffic.*.* align by device

---

Can you draw a picture of the dataset you expect for the following queries? We can consider developing it as a new feature.

select *, top_k(last_ping, "k"="2") from root.skywalking.instance_traffic.*.* align by device

select top_k(*, "k"="2") from root.skywalking.instance_traffic.*.* align by device

select top_k(last_ping, "k"="2") from root.skywalking.instance_traffic.*.* align by device

[LIU-WEI-git]

比如有一个具有temperature和status两个timeseries的device，如下所示：

当我单独使用top_k函数的时候，IoTDB会返回过滤后的结果，比如：select top_k(temperature, "k"="3") from root.sgcc.*.*

但当我想得到依照某一字段排名后的前k个数据点时，IoTDB不能返回过滤后的结果，它会查询返回所有数据点，并且多出一列”top_k“，其中不满足筛选条件的值为null，满足筛选条件的值则为非null。比如：select *, top_k(temperature, "k"="3") from root.sgcc.*.*（由于数据量太大，我仅截取了部分结果）。所以我要么采用select *, top_k后自行实现过滤，要么查询两次select top_k from XXX，select * from XXX where xxx in (...)，而且后者还会受到重复值的影响。

对于新feature的希望如下：

• 支持select *, top_k的过滤查询，可以参考上面的描述。
• 对于采用了索引（也就是将需要索引的值存储为LayerName的形式）的查询，比如select top_k(last_ping, "k"="2") from root.skywalking.instance_traffic.*.*，希望能自动合并星号（*）下的结果，最好是增加关键字来让用户决定是否要合并device来进行查询。
• 以上两点可以同时使用，比如select *, top_k(last_ping, "k"="2") from root.skywalking.instance_traffic.*.*，查询结果既合并了不同的device，又过滤了不需要的数据。
• 以上三点都支持align by device，这样用户对于查询的结果处理会更简单。

---

For example, there is a device with two timeseries, temperature and status, as shown below:

When I use the 'top_k' function alone,IoTDB returns the filtered result. For instance: select top_k(temperature, "k"="3") from root.sgcc.*.*

But when I want the top K data points ranked by a field, IoTDB cannot return filtered results. It will query and return all data points with one more column "top_k". The value that does not meet the filter conditions is null, and the value that does meet the filter conditions is non-null. For instance: select *, top_k(temperature, "k"="3") from root.sgcc.*.* (Due to the large amount of data, I only capture part of the results). Therefore, I either implement filtering by myself after using select *, top_k, or query twice, select top_k from XXX, select * from XXX where xxx in (...). In addition, the latter will be affected by duplicate values.

My hopes for the new feature are as follows

• support filter query like select *, top_k. Refer to the above description
• For query that adopts index (the value of index filed is stored as a LayerName), for example, select top_k(last_ping, "k"="2") from root.skywalking.instance_traffic.*.*, I hope IoTDB could automatically merge results under asterisks (*), preferably by adding keywords that allow the user to decide whether to merge devices for query.
• The above two points can be used at the same time. For example, select *, top_k(last_ping, "k"="2") from root.skywalking.instance_traffic.*.*. The query result not only merges different devices, but also filters unnecessary data.
• All three of the above support align by device. This will make it easier for users to process the query results.

[LIU-WEI-git]

我发现在使用聚合函数时可以使用group by level获得合并device的效果，希望top_k和bottom_k这两个选择函数也能支持group by level

---

I find we can use group by level to get the effect of merging devices when using aggregation function. Hope select function, top_k and bottom_k, could support group by level as well.

[SteveYurongSu]

可以帮忙建立一个 ISSUE，让感兴趣的同学一起来做这个 feature :D