聚合结果过滤

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

注意：

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

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|
  +-----------------------------+-------------+---------+---------+