聚合结果过滤
如果想对聚合查询的结果进行过滤,可以在 GROUP BY
子句之后使用 HAVING
子句。
注意:
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
SQL 示例:
示例 1:
对于以下聚合结果进行过滤:
+-----------------------------+---------------------+---------------------+
| Time|count(root.test.*.s1)|count(root.test.*.s2)|
+-----------------------------+---------------------+---------------------+
|1970-01-01T08:00:00.001+08:00| 4| 4|
|1970-01-01T08:00:00.003+08:00| 1| 0|
|1970-01-01T08:00:00.005+08:00| 2| 4|
|1970-01-01T08:00:00.007+08:00| 3| 2|
|1970-01-01T08:00:00.009+08:00| 4| 4|
+-----------------------------+---------------------+---------------------+
select count(s1) from root.** group by ([1,11),2ms), level=1 having count(s2) > 2;
执行结果如下:
+-----------------------------+---------------------+
| Time|count(root.test.*.s1)|
+-----------------------------+---------------------+
|1970-01-01T08:00:00.001+08:00| 4|
|1970-01-01T08:00:00.005+08:00| 2|
|1970-01-01T08:00:00.009+08:00| 4|
+-----------------------------+---------------------+
示例 2:
对于以下聚合结果进行过滤:
+-----------------------------+-------------+---------+---------+
| Time| Device|count(s1)|count(s2)|
+-----------------------------+-------------+---------+---------+
|1970-01-01T08:00:00.001+08:00|root.test.sg1| 1| 2|
|1970-01-01T08:00:00.003+08:00|root.test.sg1| 1| 0|
|1970-01-01T08:00:00.005+08:00|root.test.sg1| 1| 2|
|1970-01-01T08:00:00.007+08:00|root.test.sg1| 2| 1|
|1970-01-01T08:00:00.009+08:00|root.test.sg1| 2| 2|
|1970-01-01T08:00:00.001+08:00|root.test.sg2| 2| 2|
|1970-01-01T08:00:00.003+08:00|root.test.sg2| 0| 0|
|1970-01-01T08:00:00.005+08:00|root.test.sg2| 1| 2|
|1970-01-01T08:00:00.007+08:00|root.test.sg2| 1| 1|
|1970-01-01T08:00:00.009+08:00|root.test.sg2| 2| 2|
+-----------------------------+-------------+---------+---------+
select count(s1), count(s2) from root.** group by ([1,11),2ms) having count(s2) > 1 align by device;
执行结果如下:
+-----------------------------+-------------+---------+---------+
| Time| Device|count(s1)|count(s2)|
+-----------------------------+-------------+---------+---------+
|1970-01-01T08:00:00.001+08:00|root.test.sg1| 1| 2|
|1970-01-01T08:00:00.005+08:00|root.test.sg1| 1| 2|
|1970-01-01T08:00:00.009+08:00|root.test.sg1| 2| 2|
|1970-01-01T08:00:00.001+08:00|root.test.sg2| 2| 2|
|1970-01-01T08:00:00.005+08:00|root.test.sg2| 1| 2|
|1970-01-01T08:00:00.009+08:00|root.test.sg2| 2| 2|
+-----------------------------+-------------+---------+---------+