Tablestore:Aggregation

The aggregation method that can be used to return the minimum value of a field. This method can be used in a similar manner as the MIN operator in SQL.

• Parameters

  Parameter	Description
  aggregationName	The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
  fieldName	The name of the field that is used to perform the aggregation operation. Only the Long, Double, and Date types are supported.
  missing	The default value for the field on which the aggregation operation is performed, which is applied to rows in which the field value is empty. If you do not specify a value for the missing parameter, the row is ignored. If you specify a value for the missing parameter, the value of this parameter is used as the field value of the row.

• Examples

  /**
   * The price of each product is listed in the product table. Query the minimum price of the products that are produced in Zhejiang.
   * SQL statement: SELECT min(column_price) FROM product where place_of_production="Zhejiang".
   */
  public void min(SyncClient client) {
      //Use builder to create a query statement.
      {
          SearchRequest searchRequest = SearchRequest.newBuilder()
                  .tableName("<TABLE_NAME>")
                  .indexName("<SEARCH_INDEX_NAME>")
                  .searchQuery(
                          SearchQuery.newBuilder()
                                  .query(QueryBuilders.term("place_of_production", "Zhejiang"))
                                  .limit(0) //If you want to obtain only the aggregation results instead of specific data, you can set limit to 0 to improve query performance.
                                  .addAggregation(AggregationBuilders.min("min_agg_1", "column_price").missing(100))
                                  .build())
                  .build();
          //Execute the query statement.
          SearchResponse resp = client.search(searchRequest);
          //Obtain the aggregation results.
          System.out.println(resp.getAggregationResults().getAsMinAggregationResult("min_agg_1").getValue());
      }
  
      //Create a query statement without using builder.
      {
          SearchRequest searchRequest = new SearchRequest();
          searchRequest.setTableName("<TABLE_NAME>");
          searchRequest.setIndexName("<SEARCH_INDEX_NAME>");
  
          SearchQuery searchQuery = new SearchQuery();
          TermQuery query = new TermQuery();
          query.setTerm(ColumnValue.fromString("Zhejiang"));
          query.setFieldName("place_of_production");
          //In the following comment, builder is used to create a query statement. The method that uses builder to create a query statement has the same effect as the method that uses TermQuery to create a query statement.
          // Query query2 = QueryBuilders.term("place_of_production", "Zhejiang").build();
  
          searchQuery.setQuery(query);
          searchQuery.setLimit(0);
  
          MinAggregation aggregation = new MinAggregation();
          aggregation.setAggName("min_agg_1");
          aggregation.setFieldName("column_price");
          aggregation.setMissing(ColumnValue.fromLong(100));
          //In the following comment, builder is used to create a query statement. The method that uses builder to create a query statement has the same effect as the method that uses aggregation to create a query statement.
          // MinAggregation aggregation2 = AggregationBuilders.min("min_agg_1", "column_price").missing(100).build();
          List<Aggregation> aggregationList = new ArrayList<Aggregation>();
          aggregationList.add(aggregation);
          searchQuery.setAggregationList(aggregationList);
  
          //Execute the query statement.
          SearchResponse resp = client.search(searchRequest);
          //Obtain the aggregation results.
          System.out.println(resp.getAggregationResults().getAsMinAggregationResult("min_agg_1").getValue());
      }
  }

The aggregation method that can be used to return the maximum value of a field. This method can be used in a similar manner as the MAX operator in SQL.

• Parameters

  Parameter	Description
  aggregationName	The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
  fieldName	The name of the field that is used to perform the aggregation operation. Only the Long, Double, and Date types are supported.
  missing	The default value for the field on which the aggregation operation is performed, which is applied to rows in which the field value is empty. If you do not specify a value for the missing parameter, the row is ignored. If you specify a value for the missing parameter, the value of this parameter is used as the field value of the row.

• Examples

  /**
   * The price of each product is listed in the product table. Query the maximum price of the products that are produced in Zhejiang.
   * SQL statement: SELECT max(column_price) FROM product where place_of_production="Zhejiang".
   */
  public void max(SyncClient client) {
      //Use builder to create a query statement.
      {
          SearchRequest searchRequest = SearchRequest.newBuilder()
                  .tableName("<TABLE_NAME>")
                  .indexName("<SEARCH_INDEX_NAME>")
                  .searchQuery(
                          SearchQuery.newBuilder()
                                  .query(QueryBuilders.term("place_of_production", "Zhejiang"))
                                  .limit(0) //If you want to obtain only the aggregation results instead of specific data, you can set limit to 0 to improve query performance.
                                  .addAggregation(AggregationBuilders.max("max_agg_1", "column_price").missing(0))
                                  .build())
                  .build();
          //Execute the query statement.
          SearchResponse resp = client.search(searchRequest);
          //Obtain the aggregation results.
          System.out.println(resp.getAggregationResults().getAsMaxAggregationResult("max_agg_1").getValue());
      }
  
      //Create a query statement without using builder.
      {
          SearchRequest searchRequest = new SearchRequest();
          searchRequest.setTableName("<TABLE_NAME>");
          searchRequest.setIndexName("<SEARCH_INDEX_NAME>");
  
          SearchQuery searchQuery = new SearchQuery();
          TermQuery query = new TermQuery();
          query.setTerm(ColumnValue.fromString("Zhejiang"));
          query.setFieldName("place_of_production");
          //In the following comment, builder is used to create a query statement. The method that uses builder to create a query statement has the same effect as the method that uses TermQuery to create a query statement.
          // Query query2 = QueryBuilders.term("place_of_production", "Zhejiang").build();
  
          searchQuery.setQuery(query);
          searchQuery.setLimit(0);
  
          MaxAggregation aggregation = new MaxAggregation();
          aggregation.setAggName("max_agg_1");
          aggregation.setFieldName("column_price");
          aggregation.setMissing(ColumnValue.fromLong(100));
          //In the following comment, builder is used to create a query statement. The method that uses builder to create a query statement has the same effect as the method that uses aggregation to create a query statement.
          // MaxAggregation aggregation2 = AggregationBuilders.max("max_agg_1", "column_price").missing(100).build();
          List<Aggregation> aggregationList = new ArrayList<Aggregation>();
          aggregationList.add(aggregation);
          searchQuery.setAggregationList(aggregationList);
  
          //Execute the query statement.
          SearchResponse resp = client.search(searchRequest);
          //Obtain the aggregation results.
          System.out.println(resp.getAggregationResults().getAsMaxAggregationResult("max_agg_1").getValue());
      }
  }

The aggregation method that can be used to return the sum of all values for a numeric field. This method can be used in a similar manner as the SUM operator in SQL.

• Parameters

  Parameter	Description
  aggregationName	The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
  fieldName	The name of the field that is used to perform the aggregation operation. Only the Long and Double types are supported.
  missing	The default value for the field on which the aggregation operation is performed, which is applied to rows in which the field value is empty. If you do not specify a value for the missing parameter, the row is ignored. If you specify a value for the missing parameter, the value of this parameter is used as the field value of the row.

• Examples

  /**
   * The price of each product is listed in the product table. Query the maximum price of the products that are produced in Zhejiang.
   * SQL statement: SELECT sum(column_price) FROM product where place_of_production="Zhejiang".
   */
  public void sum(SyncClient client) {
      //Use builder to create a query statement.
      {
          SearchRequest searchRequest = SearchRequest.newBuilder()
                  .tableName("<TABLE_NAME>")
                  .indexName("<SEARCH_INDEX_NAME>")
                  .searchQuery(
                          SearchQuery.newBuilder()
                                  .query(QueryBuilders.term("place_of_production", "Zhejiang"))
                                  .limit(0) //If you want to obtain only the aggregation results instead of specific data, you can set limit to 0 to improve query performance.
                                  .addAggregation(AggregationBuilders.sum("sum_agg_1", "column_number").missing(10))
                                  .build())
                  .build();
          //Execute the query statement.
          SearchResponse resp = client.search(searchRequest);
          //Obtain the aggregation results.
          System.out.println(resp.getAggregationResults().getAsSumAggregationResult("sum_agg_1").getValue());
      }
  
      // Create a query statement without using builder.
      {
          SearchRequest searchRequest = new SearchRequest();
          searchRequest.setTableName("<TABLE_NAME>");
          searchRequest.setIndexName("<SEARCH_INDEX_NAME>");
  
          SearchQuery searchQuery = new SearchQuery();
          TermQuery query = new TermQuery();
          query.setTerm(ColumnValue.fromString("Zhejiang"));
          query.setFieldName("place_of_production");
          //In the following comment, builder is used to create a query statement. The method that uses builder to create a query statement has the same effect as the method that uses TermQuery to create a query statement.
          // Query query2 = QueryBuilders.term("place_of_production", "Zhejiang").build();
  
          searchQuery.setQuery(query);
          searchQuery.setLimit(0);
  
          SumAggregation aggregation = new SumAggregation();
          aggregation.setAggName("sum_agg_1");
          aggregation.setFieldName("column_number");
          aggregation.setMissing(ColumnValue.fromLong(100));
          //In the following comment, builder is used to create a query statement. The method that uses builder to create a query statement has the same effect as the method that uses aggregation to create a query statement.
          // SumAggregation aggregation2 = AggregationBuilders.sum("sum_agg_1", "column_number").missing(10).build();
          List<Aggregation> aggregationList = new ArrayList<Aggregation>();
          aggregationList.add(aggregation);
          searchQuery.setAggregationList(aggregationList);
  
          //Execute the query statement.
          SearchResponse resp = client.search(searchRequest);
          //Obtain the aggregation results.
          System.out.println(resp.getAggregationResults().getAsSumAggregationResult("sum_agg_1").getValue());
      }
  }

The aggregation method that can be used to return the average of all values for a numeric field. This method can be used in a similar manner as the AVG operator in SQL.

• Parameters

  Parameter	Description
  aggregationName	The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
  fieldName	The name of the field that is used to perform the aggregation operation. Only the Long, Double, and Date types are supported.
  missing	The default value for the field on which the aggregation operation is performed, which is applied to rows in which the field value is empty. If you do not specify a value for the missing parameter, the row is ignored. If you specify a value for the missing parameter, the value of this parameter is used as the field value of the row.

• Examples

  /**
   * The sales of each product are listed in the product table. Query the average price of the products that are produced in Zhejiang.
   * SQL statement: SELECT avg(column_price) FROM product where place_of_production="Zhejiang".
   */
  public void avg(SyncClient client) {
      //Use builder to create a query statement.
      {
          SearchRequest searchRequest = SearchRequest.newBuilder()
                  .tableName("<TABLE_NAME>")
                  .indexName("<SEARCH_INDEX_NAME>")
                  .searchQuery(
                          SearchQuery.newBuilder()
                                  .query(QueryBuilders.term("place_of_production", "Zhejiang"))
                                  .limit(0) //If you want to obtain only the aggregation results instead of specific data, you can set limit to 0 to improve query performance.
                                  .addAggregation(AggregationBuilders.avg("avg_agg_1", "column_price"))
                                  .build())
                  .build();
          //Execute the query statement.
          SearchResponse resp = client.search(searchRequest);
          //Obtain the aggregation results.
          System.out.println(resp.getAggregationResults().getAsAvgAggregationResult("avg_agg_1").getValue());
      }
  
      //Create a query statement without using builder.
      {
          SearchRequest searchRequest = new SearchRequest();
          searchRequest.setTableName("<TABLE_NAME>");
          searchRequest.setIndexName("<SEARCH_INDEX_NAME>");
  
          SearchQuery searchQuery = new SearchQuery();
          TermQuery query = new TermQuery();
          query.setTerm(ColumnValue.fromString("Zhejiang"));
          query.setFieldName("place_of_production");
          //In the following comment, builder is used to create a query statement. The method that uses builder to create a query statement has the same effect as the method that uses TermQuery to create a query statement.
          // Query query2 = QueryBuilders.term("place_of_production", "Zhejiang").build();
  
          searchQuery.setQuery(query);
          searchQuery.setLimit(0);
  
          AvgAggregation aggregation = new AvgAggregation();
          aggregation.setAggName("avg_agg_1");
          aggregation.setFieldName("column_price");
          //In the following comment, builder is used to create a query statement. The method that uses builder to create a query statement has the same effect as the method that uses aggregation to create a query statement.
          // AvgAggregation aggregation2 = AggregationBuilders.avg("avg_agg_1", "column_price").build();
          List<Aggregation> aggregationList = new ArrayList<Aggregation>();
          aggregationList.add(aggregation);
          searchQuery.setAggregationList(aggregationList);
  
          //Execute the query statement.
          SearchResponse resp = client.search(searchRequest);
          //Obtain the aggregation results.
          System.out.println(resp.getAggregationResults().getAsAvgAggregationResult("avg_agg_1").getValue());
      }
  }                    

• Parameters

  Parameter	Description
  aggregationName	The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
  fieldName	The name of the field that is used to perform the aggregation operation. Only the following types are supported: Long, Double, Boolean, Keyword, Geo_point, and Date.

• Examples

  /**
   * Penalty records of merchants are recorded in the merchant table. You can query the number of merchants who are located in Zhejiang and for whom penalty records exist. If no penalty record exists for a merchant, the field that corresponds to penalty records also does not exist for the merchant.
   * SQL statement: SELECT count(column_history) FROM product where place_of_production="Zhejiang".
   */
  public void count(SyncClient client) {
      //Use builder to create a query statement.
      {
          SearchRequest searchRequest = SearchRequest.newBuilder()
                  .tableName("<TABLE_NAME>")
                  .indexName("<SEARCH_INDEX_NAME>")
                  .searchQuery(
                          SearchQuery.newBuilder()
                                  .query(QueryBuilders.term("place_of_production", "Zhejiang"))
                                  .limit(0) //If you want to obtain only the aggregation results instead of specific data, you can set limit to 0 to improve query performance.
                                  .addAggregation(AggregationBuilders.count("count_agg_1", "column_history"))
                                  .build())
                  .build();
          //Execute the query statement.
          SearchResponse resp = client.search(searchRequest);
          //Obtain the aggregation results.
          System.out.println(resp.getAggregationResults().getAsCountAggregationResult("count_agg_1").getValue());
      }
  
      //Create a query statement without using builder.
      {
          SearchRequest searchRequest = new SearchRequest();
          searchRequest.setTableName("<TABLE_NAME>");
          searchRequest.setIndexName("<SEARCH_INDEX_NAME>");
  
          SearchQuery searchQuery = new SearchQuery();
          TermQuery query = new TermQuery();
          query.setTerm(ColumnValue.fromString("Zhejiang"));
          query.setFieldName("place_of_production");
          //In the following comment, builder is used to create a query statement. The method that uses builder to create a query statement has the same effect as the method that uses TermQuery to create a query statement.
          // Query query2 = QueryBuilders.term("place_of_production", "Zhejiang").build();
  
          searchQuery.setQuery(query);
          searchQuery.setLimit(0);
  
          CountAggregation aggregation = new CountAggregation();
          aggregation.setAggName("count_agg_1");
          aggregation.setFieldName("column_history");
          //In the following comment, builder is used to create a query statement. The method that uses builder to create a query statement has the same effect as the method that uses aggregation to create a query statement.
          // CountAggregation aggregation2 = AggregationBuilders.count("count_agg_1", "column_history").build();
          List<Aggregation> aggregationList = new ArrayList<Aggregation>();
          aggregationList.add(aggregation);
          searchQuery.setAggregationList(aggregationList);
  
          //Execute the query statement.
          SearchResponse resp = client.search(searchRequest);
          //Obtain the aggregation results.
          System.out.println(resp.getAggregationResults().getAsCountAggregationResult("count_agg_1").getValue());
      }
  }

• Parameters

  Parameter	Description
  aggregationName	The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
  fieldName	The name of the field that is used to perform the aggregation operation. Only the following types are supported: Long, Double, Boolean, Keyword, Geo_point, and Date.
  missing	The default value for the field on which the aggregation operation is performed, which is applied to rows in which the field value is empty. If you do not specify a value for the missing parameter, the row is ignored. If you specify a value for the missing parameter, the value of this parameter is used as the field value of the row.

• Examples

  /**
   * Query the number of distinct provinces from which the products are produced.
   * SQL statement: SELECT count(distinct column_place) FROM product.
   */
  public void distinctCount(SyncClient client) {
      //Use builder to create a query statement.
      {
          SearchRequest searchRequest = SearchRequest.newBuilder()
                  .tableName("<TABLE_NAME>")
                  .indexName("<SEARCH_INDEX_NAME>")
                  .searchQuery(
                          SearchQuery.newBuilder()
                                  .query(QueryBuilders.matchAll())
                                  .limit(0) //If you want to obtain only the aggregation results instead of specific data, you can set limit to 0 to improve query performance.
                                  .addAggregation(AggregationBuilders.distinctCount("dis_count_agg_1", "column_place"))
                                  .build())
                  .build();
          //Execute the query statement.
          SearchResponse resp = client.search(searchRequest);
          //Obtain the aggregation results.
          System.out.println(resp.getAggregationResults().getAsDistinctCountAggregationResult("dis_count_agg_1").getValue());
      }
  
      //Create a query statement without using builder.
      {
          SearchRequest searchRequest = new SearchRequest();
          searchRequest.setTableName("<TABLE_NAME>");
          searchRequest.setIndexName("<SEARCH_INDEX_NAME>");
  
          SearchQuery searchQuery = new SearchQuery();
          MatchAllQuery query = new MatchAllQuery();
          //In the following comment, builder is used to create a query statement. The method that uses builder to create a query statement has the same effect as the method that uses TermQuery to create a query statement.
          // Query query2 = QueryBuilders.matchAll().build();
  
          searchQuery.setQuery(query);
          searchQuery.setLimit(0);
  
          DistinctCountAggregation aggregation = new DistinctCountAggregation();
          aggregation.setAggName("dis_count_agg_1");
          aggregation.setFieldName("column_place");
          //In the following comment, builder is used to create a query statement. The method that uses builder to create a query statement has the same effect as the method that uses aggregation to create a query statement.
          // DistinctCountAggregation aggregation2 = AggregationBuilders.distinctCount("dis_count_agg_1", "column_place").build();
          List<Aggregation> aggregationList = new ArrayList<Aggregation>();
          aggregationList.add(aggregation);
          searchQuery.setAggregationList(aggregationList);
  
          //Execute the query statement.
          SearchResponse resp = client.search(searchRequest);
          //Obtain the aggregation results.
          System.out.println(resp.getAggregationResults().getAsDistinctCountAggregationResult("dis_count_agg_1").getValue());
      }
  }

A percentile value indicates the relative position of a value in a dataset. For example, when you collect statistics for the response time of each request during the routine O&M of your system, you must analyze the response time distribution by using percentiles such as p25, p50, p90, and p99.

• Parameters

  Parameter	Description
  aggregationName	The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
  fieldName	The name of the field that is used to perform the aggregation operation. Only the Long, Double, and Date types are supported.
  percentiles	The percentiles such as p50, p90, and p99. You can specify one or more percentiles.
  missing	The default value for the field on which the aggregation operation is performed, which is applied to rows in which the field value is empty. If you do not specify a value for the missing parameter, the row is ignored. If you specify a value for the missing parameter, the value of this parameter is used as the field value of the row.

• Examples

  /**
   * Analyze the distribution of the response time of each request that is sent to the system by using percentiles.
   */
  public void percentilesAgg(SyncClient client) {
      //Use builder to create a query statement.
      {
          SearchRequest searchRequest = SearchRequest.newBuilder()
                  .tableName("<TABLE_NAME>")
                  .indexName("indexName")
                  .searchQuery(
                          SearchQuery.newBuilder()
                                  .query(QueryBuilders.matchAll())
                                  .limit(0) //If you want to obtain only the aggregation results instead of specific data, you can set limit to 0 to improve query performance.
                                  .addAggregation(AggregationBuilders.percentiles("percentilesAgg", "latency")
                                          .percentiles(Arrays.asList(25.0d, 50.0d, 99.0d))
                                          .missing(1.0))
                                  .build())
                  .build();
          //Execute the query statement.
          SearchResponse resp = client.search(searchRequest);
          //Obtain the results.
          PercentilesAggregationResult percentilesAggregationResult = resp.getAggregationResults().getAsPercentilesAggregationResult("percentilesAgg");
          for (PercentilesAggregationItem item : percentilesAggregationResult.getPercentilesAggregationItems()) {
              System.out.println("key：" + item.getKey() + " value:" + item.getValue().asDouble());
          }
      }
  
      //Create a query statement without using builder.
      {
          SearchRequest searchRequest = new SearchRequest();
          searchRequest.setTableName("<TABLE_NAME>");
          searchRequest.setIndexName("<SEARCH_INDEX_NAME>");
  
          SearchQuery searchQuery = new SearchQuery();
          MatchAllQuery query = new MatchAllQuery();
          //In the following comment, builder is used to create a query statement. The method that uses builder to create a query statement has the same effect as the method that uses TermQuery to create a query statement.
          // Query query2 = QueryBuilders.matchAll().build();
  
          searchQuery.setQuery(query);
          searchQuery.setLimit(0);
  
          PercentilesAggregation aggregation = new PercentilesAggregation();
          aggregation.setAggName("percentilesAgg");
          aggregation.setFieldName("latency");
          aggregation.setPercentiles(Arrays.asList(25.0d, 50.0d, 99.0d));
          //In the following comment, builder is used to create a query statement. The method that uses builder to create a query statement has the same effect as the method that uses aggregation to create a query statement.
          // AggregationBuilders.percentiles("percentilesAgg", "latency").percentiles(Arrays.asList(25.0d, 50.0d, 99.0d)).missing(1.0).build();
          List<Aggregation> aggregationList = new ArrayList<Aggregation>();
          aggregationList.add(aggregation);
          searchQuery.setAggregationList(aggregationList);
  
          //Execute the query statement.
          SearchResponse resp = client.search(searchRequest);
          //Obtain the results.
          PercentilesAggregationResult percentilesAggregationResult = resp.getAggregationResults().getAsPercentilesAggregationResult("percentilesAgg");
          for (PercentilesAggregationItem item : percentilesAggregationResult.getPercentilesAggregationItems()) {
              System.out.println("key：" + item.getKey() + " value:" + item.getValue().asDouble());
          }
      }
  }

The aggregation method that can be used to group query results based on field values. The values that are the same are grouped together. The value of each group and the number of members in each group are returned.

• Parameters

  Parameter	Description
  groupByName	The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
  fieldName	The name of the field that is used to perform the aggregation operation. Only the Long, Double, Boolean, Keyword, and Date types are supported.
  groupBySorter	The sorting rules for groups. By default, groups are sorted based on the number of items in the groups in descending order. If you configure multiple sorting rules, the groups are sorted based on the order in which the rules are configured. The following sorting rules are supported: groupKeySortInAsc: Sort by value in alphabetical order. groupKeySortInDesc: Sort by value in reverse alphabetical order. rowCountSortInAsc: Sort by row count in ascending order. rowCountSortInDesc (default): Sort by row count in descending order. subAggSortInAsc: Sort by the values that are obtained from sub-aggregation results in ascending order. subAggSortInDesc: Sort by the values that are obtained from sub-aggregation results in descending order.
  size	The number of groups that you want to return. Default value: 10. Maximum value: 2000.
  subAggregations	The sub-aggregation operations that can be performed on the data in each group, such as calculating the maximum value, sum, or average value.
  subGroupBys	The sub-grouping operations that can be performed on the data in each parent group to further group the data.

• Examples

  Group by single field

  /**
   * Query the number of products, and the maximum and minimum product prices in each category.
   * Example of returned results: Fruits: 5. The maximum price is CNY 15, and the minimum price is CNY 3. Toiletries: 10. The maximum price is CNY 98, and the minimum price is CNY 1. Electronic devices: 3. The maximum price is CNY 8,699, and the minimum price is CNY 2,300. Other products: 15. The maximum price is CNY 1,000, and the minimum price is CNY 80.
   */
  public void groupByField(SyncClient client) {
      //Use builder to create a query statement.
      {
          SearchRequest searchRequest = SearchRequest.newBuilder()
                  .tableName("<TABLE_NAME>")
                  .indexName("<SEARCH_INDEX_NAME>")
                  .searchQuery(
                          SearchQuery.newBuilder()
                                  .query(QueryBuilders.matchAll())
                                  .limit(0)  //If you want to obtain only the aggregation results instead of specific data, you can set limit to 0 to improve query performance.
                                  .addGroupBy(GroupByBuilders
                                          .groupByField("name1", "column_type")
                                          .addSubAggregation(AggregationBuilders.min("subName1", "column_price"))
                                          .addSubAggregation(AggregationBuilders.max("subName2", "column_price"))
                                  )
                                  .build())
                  .build();
          //Execute the query statement.
          SearchResponse resp = client.search(searchRequest);
          //Obtain the aggregation results.
          for (GroupByFieldResultItem item : resp.getGroupByResults().getAsGroupByFieldResult("name1").getGroupByFieldResultItems()) {
              //Display values.
              System.out.println(item.getKey());
              //Display the number of rows.
              System.out.println(item.getRowCount());
              //Display the minimum prices.
              System.out.println(item.getSubAggregationResults().getAsMinAggregationResult("subName1").getValue());
              //Display the maximum prices.
              System.out.println(item.getSubAggregationResults().getAsMaxAggregationResult("subName2").getValue());
          }
      }
  
      //Create a query statement without using builder.
      {
          SearchRequest searchRequest = new SearchRequest();
          searchRequest.setTableName("<TABLE_NAME>");
          searchRequest.setIndexName("<SEARCH_INDEX_NAME>");
  
          SearchQuery searchQuery = new SearchQuery();
          MatchAllQuery query = new MatchAllQuery();
          //In the following comment, builder is used to create a query statement. The method that uses builder to create a query statement has the same effect as the method that uses TermQuery to create a query statement.
          // Query query2 = QueryBuilders.matchAll().build();
  
          searchQuery.setQuery(query);
          searchQuery.setLimit(0);
  
          GroupByField groupByField = new GroupByField();
          groupByField.setGroupByName("name1");
          groupByField.setFieldName("column_type");
          //Configure sub-aggregation operations.
          MinAggregation minAggregation = AggregationBuilders.min("subName1", "column_price").build();
          MaxAggregation maxAggregation = AggregationBuilders.max("subName2", "column_price").build();
          groupByField.setSubAggregations(Arrays.asList(minAggregation, maxAggregation));
  
          //In the following comment, builder is used to create a query statement. The method that uses builder to create a query statement has the same effect as the method that uses aggregation to create a query statement.
          // GroupByBuilders.groupByField("name1", "column_type")
          //        .addSubAggregation(AggregationBuilders.min("subName1", "column_price"))
          //        .addSubAggregation(AggregationBuilders.max("subName2", "column_price").build());
          List<GroupBy> groupByList = new ArrayList<GroupBy>();
          groupByList.add(groupByField);
          searchQuery.setGroupByList(groupByList);
          searchRequest.setSearchQuery(searchQuery);
  
          //Execute the query statement.
          SearchResponse resp = client.search(searchRequest);
          //Obtain the aggregation results.
          for (GroupByFieldResultItem item : resp.getGroupByResults().getAsGroupByFieldResult("name1").getGroupByFieldResultItems()) {
              //Display values.
              System.out.println(item.getKey());
              //Display the number of rows.
              System.out.println(item.getRowCount());
              //Display the minimum prices.
              System.out.println(item.getSubAggregationResults().getAsMinAggregationResult("subName1").getValue());
              //Display the maximum prices.
              System.out.println(item.getSubAggregationResults().getAsMaxAggregationResult("subName2").getValue());
          }
      }
  }

  Group by multiple fields in nested mode

  /**
   * Example of grouping query results by multiple fields in nested mode.
   * In a search index, you can use two groupBy fields in nested mode to achieve the same effect as using multiple groupBy fields in an SQL statement.
   * SQL statement: select a,d, sum(b),sum(c) from user group by a,d.
   */
  public void GroupByMultiField(SyncClient client) {
      SearchRequest searchRequest = SearchRequest.newBuilder()
          .tableName("<TABLE_NAME>")
          .indexName("<SEARCH_INDEX_NAME>")
          .returnAllColumns(true)   //You can set returnAllColumns to false and specify a value for addColumesToGet to obtain better query performance.
          //.addColumnsToGet("col_1","col_2")
          .searchQuery(SearchQuery.newBuilder()
              .query(QueryBuilders.matchAll())   //Specify query conditions. Query conditions can be used in the same manner as the WHERE clause in SQL. You can use QueryBuilders.bool() to perform nested queries.
              .addGroupBy(
                  GroupByBuilders
                      .groupByField("unique name_1", "field_a")
                      .size(20)
                      .addSubGroupBy(
                          GroupByBuilders
                              .groupByField("unique name_2", "field_d")
                              .size(20)
                              .addSubAggregation(AggregationBuilders.sum("unique name_3", "field_b"))
                              .addSubAggregation(AggregationBuilders.sum("unique name_4", "field_c"))
                      )
              )
              .build())
          .build();
      SearchResponse response = client.search(searchRequest);
      //Query rows that meet the specified conditions.
      List<Row> rows = response.getRows();
      //Obtain the aggregation results.
      GroupByFieldResult groupByFieldResult1 = response.getGroupByResults().getAsGroupByFieldResult("unique name_1");
      for (GroupByFieldResultItem resultItem : groupByFieldResult1.getGroupByFieldResultItems()) {
          System.out.println("field_a key:" + resultItem.getKey() + " Count:" + resultItem.getRowCount());
          //Obtain the sub-aggregation results.
          GroupByFieldResult subGroupByResult = resultItem.getSubGroupByResults().getAsGroupByFieldResult("unique name_2");
          for (GroupByFieldResultItem item : subGroupByResult.getGroupByFieldResultItems()) {
              System.out.println("field_a " + resultItem.getKey() + " field_d key:" + item.getKey() + " Count: " + item.getRowCount());
              double sumOf_field_b = item.getSubAggregationResults().getAsSumAggregationResult("unique name_3").getValue();
              double sumOf_field_c = item.getSubAggregationResults().getAsSumAggregationResult("unique name_4").getValue();
              System.out.println("sumOf_field_b:" + sumOf_field_b);
              System.out.println("sumOf_field_c:" + sumOf_field_c);
          }
      }
  }

  Sort groups for aggregation

  /**
   * Example of configuring sorting rules for aggregation.
   * Method: Configure sorting rules by specifying GroupBySorter. If you configure multiple sorting rules, the groups are sorted based on the order in which the rules are configured. GroupBySorter supports sorting in ascending or descending order.
   * By default, the groups are sorted by row count in descending order (GroupBySorter.rowCountSortInDesc()).
   */
  public void groupByFieldWithSort(SyncClient client) {
      //Create a query statement.
      SearchRequest searchRequest = SearchRequest.newBuilder()
          .tableName("<TABLE_NAME>")
          .indexName("<SEARCH_INDEX_NAME>")
          .searchQuery(
              SearchQuery.newBuilder()
                  .query(QueryBuilders.matchAll())
                  .limit(0)
                  .addGroupBy(GroupByBuilders
                      .groupByField("name1", "column_type")
                      //.addGroupBySorter(GroupBySorter.subAggSortInAsc("subName1")) //Sort the groups in ascending order based on the values that are obtained from sub-aggregation results.
                      .addGroupBySorter(GroupBySorter.groupKeySortInAsc())           //Sort the groups in ascending order based on the values that are obtained from aggregation results.
                      //.addGroupBySorter(GroupBySorter.rowCountSortInDesc())        //Sort the groups in descending order based on the number of rows that are obtained from the aggregation results in each group.
                      .size(20)
                      .addSubAggregation(AggregationBuilders.min("subName1", "column_price"))
                      .addSubAggregation(AggregationBuilders.max("subName2", "column_price"))
                  )
                  .build())
          .build();
      //Execute the query statement.
      SearchResponse resp = client.search(searchRequest);
  }

GroupBy supports nesting operations, which allows you to add sub-level aggregations based on parent-level grouping results.

• Common scenarios

  • Multi-level grouping (GroupBy + SubGroupBy)

    After you group data at the first level, you can perform second-level grouping. For example, you can first group data by province and then by city to obtain data for each city in each province.

  • Grouping followed by aggregation (GroupBy + SubAggregation)

    After you group data at the first level, you can perform aggregation operations (such as calculating the maximum value or average value) on the data in each group. For example, after you group data by province, you can obtain the maximum value of a specific metric for each province group.

• Examples

  The following example shows how to perform nested grouping by province and city, and calculate the total number of orders for each province and the total number of orders and maximum order amount for each city.

  public static void subGroupBy(SyncClient client) {
      //Create a query statement.
      SearchRequest searchRequest = SearchRequest.newBuilder()
              .tableName("<TABLE_NAME>")
              .indexName("<SEARCH_INDEX_NAME>")
              .returnAllColumns(true)
              .searchQuery(SearchQuery.newBuilder()
                      .query(QueryBuilders.matchAll()).limit(20)
                      //First-level grouping: Group by province and count the number of order IDs (total orders) for each province.
                      //Second-level grouping: Group by city and count the number of order IDs (total orders) for each city, and calculate the maximum order amount for each city.
                      .addGroupBy(GroupByBuilders.groupByField("provinceName", "province")
                              .addSubAggregation(AggregationBuilders.count("provinceOrderCounts", "order_id"))
                              .addGroupBySorter(GroupBySorter.rowCountSortInDesc())
                              .addSubGroupBy(GroupByBuilders.groupByField("cityName", "city")
                                      .addSubAggregation(AggregationBuilders.count("cityOrderCounts", "order_id"))
                                      .addSubAggregation(AggregationBuilders.max("cityMaxAmount", "order_amount"))
                                      .addGroupBySorter(GroupBySorter.subAggSortInDesc("cityMaxAmount"))))
                      .build())
              .build();
      
      //Execute the query statement.
      SearchResponse resp = client.search(searchRequest);
      
      //Obtain the first-level grouping results (total orders for each province)
      GroupByFieldResult results = resp.getGroupByResults().getAsGroupByFieldResult("provinceName");
      for (GroupByFieldResultItem item : results.getGroupByFieldResultItems()) {
          System.out.println("Province:" + item.getKey()
                  + "\tTotal Orders:" + item.getSubAggregationResults().getAsCountAggregationResult("provinceOrderCounts").getValue());
          
          //Obtain the second-level grouping results (total orders and maximum order amount for each city)
          GroupByFieldResult subResults = item.getSubGroupByResults().getAsGroupByFieldResult("cityName");
          for (GroupByFieldResultItem subItem : subResults.getGroupByFieldResultItems()) {
              System.out.println("\t(City)" + subItem.getKey()
                      + "\tTotal Orders:" + subItem.getSubAggregationResults().getAsCountAggregationResult("cityOrderCounts").getValue()
                      + "\tMaximum Order Amount:" + subItem.getSubAggregationResults().getAsMaxAggregationResult("cityMaxAmount").getValue());
          }
      }
  }

The aggregation method that can be used to group query results based on the value ranges of a field. Field values that are within a specific range are grouped together. The number of values in each range is returned.

• Parameters

  Parameter	Description
  groupByName	The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
  fieldName	The name of the field that is used to perform the aggregation operation. Only the Long and Double types are supported.
  range[double_from, double_to)	The value ranges for grouping. You can set double_from to Double.MIN_VALUE to specify the minimum value and set double_to to Double.MAX_VALUE to specify the maximum value.
  subAggregation and subGroupBy	The sub-aggregation operation. You can perform the sub-aggregation operation based on the grouping results. For example, after you group query results by sales volume and by province, you can obtain the province that has the largest proportion of sales volume in a specified range. You need to specify GroupByField in GroupByRange to perform this query.

• Examples

  /**
   * Group sales volumes based on ranges [0, 1000), [1000, 5000), and [5000, Double.MAX_VALUE) to obtain the sales volume in each range.
   */
  public void groupByRange(SyncClient client) {
      //Create a query statement.
      SearchRequest searchRequest = SearchRequest.newBuilder()
          .tableName("<TABLE_NAME>")
          .indexName("<SEARCH_INDEX_NAME>")
          .searchQuery(
              SearchQuery.newBuilder()
                  .query(QueryBuilders.matchAll())
                  .limit(0)
                  .addGroupBy(GroupByBuilders
                      .groupByRange("name1", "column_number")
                      .addRange(0, 1000)
                      .addRange(1000, 5000)
                      .addRange(5000, Double.MAX_VALUE)
                  )
                  .build())
          .build();
      //Execute the query statement.
      SearchResponse resp = client.search(searchRequest);
      //Obtain the aggregation results.
      for (GroupByRangeResultItem item : resp.getGroupByResults().getAsGroupByRangeResult("name1").getGroupByRangeResultItems()) {
  
          //Display the number of rows.
          System.out.println(item.getRowCount());
      }
  }

The aggregation method that can be used to group query results based on distances from geographical locations to a central point. Query results in distances that are within a specific range are grouped together. The number of values in each range is returned.

• Parameters

  Parameter	Description
  groupByName	The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
  fieldName	The name of the field that is used to perform the aggregation operation. Only the Geo_point type is supported.
  origin(double lat, double lon)	The longitude and latitude of the central point. double lat specifies the latitude of the central point. double lon specifies the longitude of the central point.
  range[double_from, double_to)	The distance ranges that are used for grouping. Unit: meters. You can set double_from to Double.MIN_VALUE to specify the minimum value and set double_to to Double.MAX_VALUE to specify the maximum value.
  subAggregation and subGroupBy	The sub-aggregation operation. You can perform the sub-aggregation operation based on the grouping results.

• Examples

  /**
   * Group users based on geographical locations to a Wanda Plaza to obtain the number of users in each distance range. The distance ranges are [0, 1000), [1000, 5000), and [5000, Double.MAX_VALUE). Unit: meters.
   */
  public void groupByGeoDistance(SyncClient client) {
      //Create a query statement.
      SearchRequest searchRequest = SearchRequest.newBuilder()
          .tableName("<TABLE_NAME>")
          .indexName("<SEARCH_INDEX_NAME>")
          .searchQuery(
              SearchQuery.newBuilder()
                  .query(QueryBuilders.matchAll())
                  .limit(0)
                  .addGroupBy(GroupByBuilders
                      .groupByGeoDistance("name1", "column_geo_point")
                      .origin(3.1, 6.5)
                      .addRange(0, 1000)
                      .addRange(1000, 5000)
                      .addRange(5000, Double.MAX_VALUE)
                  )
                  .build())
          .build();
      //Execute the query statement.
      SearchResponse resp = client.search(searchRequest);
      //Obtain the aggregation results.
      for (GroupByGeoDistanceResultItem item : resp.getGroupByResults().getAsGroupByGeoDistanceResult("name1").getGroupByGeoDistanceResultItems()) {
         //Display the number of rows.
          System.out.println(item.getRowCount());
      }
  }

The aggregation method that can be used to filter the query results and group them based on each filter to obtain the number of matching results. Results are returned in the order in which the filters are specified.

• Parameters

  Parameter	Description
  groupByName	The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
  filter	The filters that can be used for the query. Results are returned in the order in which the filters are specified.
  subAggregation and subGroupBy	The sub-aggregation operation. You can perform the sub-aggregation operation based on the grouping results.

• Examples

  /**
   * Specify the following filters to obtain the number of items that match each filter: The sales volume exceeds 100, the place of origin is Zhejiang, and the description contains Hangzhou.
   */
  public void groupByFilter(SyncClient client) {
      //Create a query statement.
      SearchRequest searchRequest = SearchRequest.newBuilder()
          .tableName("<TABLE_NAME>")
          .indexName("<SEARCH_INDEX_NAME>")
          .searchQuery(
              SearchQuery.newBuilder()
                  .query(QueryBuilders.matchAll())
                  .limit(0) 
                  .addGroupBy(GroupByBuilders
                      .groupByFilter("name1")
                      .addFilter(QueryBuilders.range("number").greaterThanOrEqual(100))
                      .addFilter(QueryBuilders.term("place","Zhejiang"))
                      .addFilter(QueryBuilders.match("text","Hangzhou"))
                  )
                  .build())
          .build();
      //Execute the query statement.
      SearchResponse resp = client.search(searchRequest);
      //Obtain the aggregation results based on the order of filters.
      for (GroupByFilterResultItem item : resp.getGroupByResults().getAsGroupByFilterResult("name1").getGroupByFilterResultItems()) {
          //Display the number of rows.
          System.out.println(item.getRowCount());
      }
  }

The aggregation method can be used to group query results based on specific data intervals. Field values that are within the same range are grouped together. The value range of each group and the number of values in each group are returned.

• Parameters

  Parameter	Description
  groupByName	The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
  fieldName	The name of the field that is used to perform the aggregation operation. Only the Long and Double types are supported.
  interval	The data interval that is used to obtain aggregation results.
  fieldRange[min,max]	The range that is used together with the interval parameter to limit the number of groups. The number of groups that is determined by using the (fieldRange.max-fieldRange.min)/interval formula cannot exceed 2,000.
  minDocCount	The minimum number of rows. If the number of rows in a group is less than the minimum number of rows, the aggregation results for the group are not returned.
  missing	The default value for the field on which the aggregation operation is performed, which is applied to rows in which the field value is empty. If you do not specify a value for the missing parameter, the row is ignored. If you specify a value for the missing parameter, the value of this parameter is used as the field value of the row.

• Examples

  /**
   * Collect statistics on the distribution of users by age group.
   */
  public static void groupByHistogram(SyncClient client) {
      //Create a query statement.
      SearchRequest searchRequest = SearchRequest.newBuilder()
          .tableName("<TABLE_NAME>")
          .indexName("<SEARCH_INDEX_NAME>")
          .searchQuery(
              SearchQuery.newBuilder()
                  .addGroupBy(GroupByBuilders
                      .groupByHistogram("groupByHistogram", "age")
                      .interval(10)
                      .minDocCount(0L)
                      .addFieldRange(0, 99))
                  .build())
          .build();
      //Execute the query statement.
      SearchResponse resp = ots.search(searchRequest);
      //Obtain the results that are returned when the aggregation operation is performed.
      GroupByHistogramResult results = resp.getGroupByResults().getAsGroupByHistogramResult("groupByHistogram");
      for (GroupByHistogramItem item : results.getGroupByHistogramItems()) {
          System.out.println("key：" + item.getKey().asLong() + " value:" + item.getValue());
      }
  }

After you group query results, you can query the rows in each group. This method can be used in a similar manner as ANY_VALUE(field) in MySQL.

• Parameters

  Parameter	Description
  aggregationName	The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
  limit	The maximum number of rows that can be returned for each group. By default, only one row of data is returned.
  sort	The sorting method that is used to sort data in groups.
  columnsToGet	The fields that you want to return. Only fields in search indexes are supported. Array, Date, Geopoint, and Nested fields are not supported. The value of this parameter is the same as the value of the columnsToGet parameter in SearchRequest. You need to only specify a value for the columnsToGet parameter in SearchRequest.

• Examples

  /**
   * An activity application form of a school contains fields in which information such as the names of students, classes, head teachers, and class presidents can be specified. You can group students by class to view the application statistics and the property information of each class.
   * SQL statement: select className, teacher, monitor, COUNT(*) as number from table GROUP BY className.
   */
  public void testTopRows(SyncClient client) {
      SearchRequest searchRequest = SearchRequest.newBuilder()
              .indexName("<SEARCH_INDEX_NAME>")
              .tableName("<TABLE_NAME>")
              .searchQuery(
                      SearchQuery.newBuilder()
                              .query(QueryBuilders.matchAll())
                              .limit(0) 
                              .addGroupBy(GroupByBuilders.groupByField("groupName", "className")
                                      .size(5)  //Specify the number of groups that you want to return. For information about the maximum value that you can specify for the number of groups to return, see the description of the number of groups returned by GroupByField in the "Search index limits" topic.
                                      .addSubAggregation(AggregationBuilders.topRows("topRowsName")
                                              .limit(1)
                                              .sort(new Sort(Arrays.asList(new FieldSort("teacher", SortOrder.DESC)))) //Sort rows by teacher in descending order.
                                      )
                              )
                              .build())
              .addColumnsToGet(Arrays.asList("teacher", "monitor"))
              .build();
      SearchResponse resp = client.search(searchRequest);
      List<GroupByFieldResultItem> items = resp.getGroupByResults().getAsGroupByFieldResult("groupName").getGroupByFieldResultItems();
      for (GroupByFieldResultItem item : items) {
          String className = item.getKey();
          long number = item.getRowCount();
          List<Row> topRows = item.getSubAggregationResults().getAsTopRowsAggregationResult("topRowsName").getRows();
          Row row = topRows.get(0);
          String teacher = row.getLatestColumn("teacher").getValue().asString();
          String monitor = row.getLatestColumn("monitor").getValue().asString();
      }
  }

You can perform multiple aggregation operations.

public void multipleAggregation(SyncClient client) {
    //Create a query statement.
    SearchRequest searchRequest = SearchRequest.newBuilder()
        .tableName("<TABLE_NAME>")
        .indexName("<SEARCH_INDEX_NAME>")
        .searchQuery(
            SearchQuery.newBuilder()
                .query(QueryBuilders.matchAll())
                .limit(0) 
                .addAggregation(AggregationBuilders.min("name1", "long"))
                .addAggregation(AggregationBuilders.sum("name2", "long"))
                .addAggregation(AggregationBuilders.distinctCount("name3", "long"))
                .build())
        .build();
    //Execute the query statement.
    SearchResponse resp = client.search(searchRequest);
    //Obtain the minimum value from the results of the aggregation operation.
    System.out.println(resp.getAggregationResults().getAsMinAggregationResult("name1").getValue());
    //Obtain the sum from the results of the aggregation operation.
    System.out.println(resp.getAggregationResults().getAsSumAggregationResult("name2").getValue());
    //Obtain the number of distinct values from the results of the aggregation operation.
    System.out.println(resp.getAggregationResults().getAsDistinctCountAggregationResult("name3").getValue());
}

public void multipleGroupBy(SyncClient client) {
    //Create a query statement.
    SearchRequest searchRequest = SearchRequest.newBuilder()
        .tableName("<TABLE_NAME>")
        .indexName("<SEARCH_INDEX_NAME>")
        .searchQuery(
            SearchQuery.newBuilder()
                .query(QueryBuilders.matchAll())
                .limit(0)
                .addAggregation(AggregationBuilders.min("name1", "long"))
                .addAggregation(AggregationBuilders.sum("name2", "long"))
                .addAggregation(AggregationBuilders.distinctCount("name3", "long"))
                .addGroupBy(GroupByBuilders.groupByField("name4", "type"))
                .addGroupBy(GroupByBuilders.groupByRange("name5", "long").addRange(1, 15))
                .build())
        .build();
    //Execute the query statement.
    SearchResponse resp = client.search(searchRequest);
    //Obtain the minimum value from the results of the aggregation operation.
    System.out.println(resp.getAggregationResults().getAsMinAggregationResult("name1").getValue());
    //Obtain the sum from the results of the aggregation operation.
    System.out.println(resp.getAggregationResults().getAsSumAggregationResult("name2").getValue());
    //Obtain the number of distinct values from the results of the aggregation operation.
    System.out.println(resp.getAggregationResults().getAsDistinctCountAggregationResult("name3").getValue());
    //Obtain the values of GroupByField from the results of the aggregation operation.
    for (GroupByFieldResultItem item : resp.getGroupByResults().getAsGroupByFieldResult("name4").getGroupByFieldResultItems()) {
        //Display the keys.
        System.out.println(item.getKey());
        //Display the number of rows.
        System.out.println(item.getRowCount());
    }
    //Obtain the values of GroupByRange from the results of the aggregation operation.
    for (GroupByRangeResultItem item : resp.getGroupByResults().getAsGroupByRangeResult("name5").getGroupByRangeResultItems()) {
        //Display the number of rows.
        System.out.println(item.getRowCount());
    }
}