@Test
  public void countSumAvgSlidingWindowWithNonAlignedTest() throws SQLException {
    String[] retArray =
        new String[] {
          "1,0,null,null,0,null,null",
          "3,2,13.0,6.5,2,13.0,6.5",
          "5,4,30.0,7.5,4,30.0,7.5",
          "7,4,45.0,8.75,5,45.0,9.0",
          "9,4,55.0,11.25,5,130042.0,11.0",
          "11,5,65.0,13.0,5,130052.0,13.0",
          "13,5,75.0,15.0,5,130062.0,15.0",
          "15,5,85.0,17.0,5,85.0,17.0",
          "17,4,74.0,18.5,5,74.0,19.0",
          "19,3,39.0,19.5,5,39.0,21.0",
          "21,1,null,null,5,null,23.0",
          "23,1,null,null,5,null,25.0",
          "25,0,null,null,5,null,27.0",
          "27,0,31.0,null,4,31.0,28.5",
          "29,0,96.0,null,2,96.0,29.5",
          "31,0,165.0,null,0,165.0,null",
          "33,0,175.0,null,0,175.0,null",
          "35,0,185.0,null,0,185.0,null",
          "37,0,154.0,null,0,154.0,null",
          "39,0,79.0,null,0,79.0,null"
        };
    try (Connection connection = EnvFactory.getEnv().getConnection();
        Statement statement = connection.createStatement()) {

      int cnt;
      try (ResultSet resultSet =
          statement.executeQuery(
              "select count(d1.s1), sum(d2.s2), avg(d2.s1), count(d1.s3), sum(d1.s2), avg(d2.s3) "
                  + "from root.sg1 where time > 5 GROUP BY ([1, 41), 5ms, 2ms)")) {
        cnt = 0;
        while (resultSet.next()) {
          String ans =
              resultSet.getString(TIMESTAMP_STR)
                  + ","
                  + resultSet.getString(count("root.sg1.d1.s1"))
                  + ","
                  + resultSet.getString(sum("root.sg1.d2.s2"))
                  + ","
                  + resultSet.getString(avg("root.sg1.d2.s1"))
                  + ","
                  + resultSet.getString(count("root.sg1.d1.s3"))
                  + ","
                  + resultSet.getString(sum("root.sg1.d1.s2"))
                  + ","
                  + resultSet.getString(avg("root.sg1.d2.s3"));
          Assert.assertEquals(retArray[cnt], ans);
          cnt++;
        }
        Assert.assertEquals(retArray.length, cnt);
      }

      try (ResultSet resultSet =
          statement.executeQuery(
              "select count(d1.s1), sum(d2.s2), avg(d2.s1), count(d1.s3), sum(d1.s2), avg(d2.s3) "
                  + "from root.sg1 where time > 5 GROUP BY ([1, 41), 5ms, 2ms) order by time desc")) {
        cnt = retArray.length;
        while (resultSet.next()) {
          String ans =
              resultSet.getString(TIMESTAMP_STR)
                  + ","
                  + resultSet.getString(count("root.sg1.d1.s1"))
                  + ","
                  + resultSet.getString(sum("root.sg1.d2.s2"))
                  + ","
                  + resultSet.getString(avg("root.sg1.d2.s1"))
                  + ","
                  + resultSet.getString(count("root.sg1.d1.s3"))
                  + ","
                  + resultSet.getString(sum("root.sg1.d1.s2"))
                  + ","
                  + resultSet.getString(avg("root.sg1.d2.s3"));
          Assert.assertEquals(retArray[cnt - 1], ans);
          cnt--;
        }
        Assert.assertEquals(0, cnt);
      }
    }
  }
aggregate multi columns of aligned timeseries in one SQL
integration-test/src/test/java/org/apache/iotdb/db/it/aligned/IoTDBAlignedSeriesQueryIT.java