聚合结果过滤

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

注意：

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

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

1. select count(s1) from root.** group by ([1,3),1ms) having sum(s1) > s1 2. select count(s1) from root.** group by ([1,3),1ms) having s1 > 1 2. 对GROUP BY LEVEL结果进行过滤时，SELECT和HAVING中出现的PATH只能有一级。

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

1. select count(s1) from root.** group by ([1,3),1ms), level=1 having sum(d1.s1) > 1 2. select count(d1.s1) from root.** group by ([1,3),1ms), level=1 having sum(s1) > 1

SQL 示例：

• 示例 1：

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

1. +-----------------------------+---------------------+---------------------+ 2. | Time|count(root.test.*.s1)|count(root.test.*.s2)| 3. +-----------------------------+---------------------+---------------------+ 4. |1970-01-01T08:00:00.001+08:00| 4| 4| 5. |1970-01-01T08:00:00.003+08:00| 1| 0| 6. |1970-01-01T08:00:00.005+08:00| 2| 4| 7. |1970-01-01T08:00:00.007+08:00| 3| 2| 8. |1970-01-01T08:00:00.009+08:00| 4| 4| 9. +-----------------------------+---------------------+---------------------+

1. select count(s1) from root.** group by ([1,11),2ms), level=1 having count(s2) > 2;

执行结果如下：

1. +-----------------------------+---------------------+ 2. | Time|count(root.test.*.s1)| 3. +-----------------------------+---------------------+ 4. |1970-01-01T08:00:00.001+08:00| 4| 5. |1970-01-01T08:00:00.005+08:00| 2| 6. |1970-01-01T08:00:00.009+08:00| 4| 7. +-----------------------------+---------------------+ - 示例 2：

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

1. +-----------------------------+-------------+---------+---------+ 2. | Time| Device|count(s1)|count(s2)| 3. +-----------------------------+-------------+---------+---------+ 4. |1970-01-01T08:00:00.001+08:00|root.test.sg1| 1| 2| 5. |1970-01-01T08:00:00.003+08:00|root.test.sg1| 1| 0| 6. |1970-01-01T08:00:00.005+08:00|root.test.sg1| 1| 2| 7. |1970-01-01T08:00:00.007+08:00|root.test.sg1| 2| 1| 8. |1970-01-01T08:00:00.009+08:00|root.test.sg1| 2| 2| 9. |1970-01-01T08:00:00.001+08:00|root.test.sg2| 2| 2| 10. |1970-01-01T08:00:00.003+08:00|root.test.sg2| 0| 0| 11. |1970-01-01T08:00:00.005+08:00|root.test.sg2| 1| 2| 12. |1970-01-01T08:00:00.007+08:00|root.test.sg2| 1| 1| 13. |1970-01-01T08:00:00.009+08:00|root.test.sg2| 2| 2| 14. +-----------------------------+-------------+---------+---------+

1. select count(s1), count(s2) from root.** group by ([1,11),2ms) having count(s2) > 1 align by device;

执行结果如下：

1. +-----------------------------+-------------+---------+---------+ 2. | Time| Device|count(s1)|count(s2)| 3. +-----------------------------+-------------+---------+---------+ 4. |1970-01-01T08:00:00.001+08:00|root.test.sg1| 1| 2| 5. |1970-01-01T08:00:00.005+08:00|root.test.sg1| 1| 2| 6. |1970-01-01T08:00:00.009+08:00|root.test.sg1| 2| 2| 7. |1970-01-01T08:00:00.001+08:00|root.test.sg2| 2| 2| 8. |1970-01-01T08:00:00.005+08:00|root.test.sg2| 1| 2| 9. |1970-01-01T08:00:00.009+08:00|root.test.sg2| 2| 2| 10. +-----------------------------+-------------+---------+---------+