This topic describes statistical operations used in search index-based queries.

Types

  • Minimum value
    • Definition: Query the minimum value of a field. This query is equivalent to the SQL MIN function. If a row does not include a field value, the row is not included in the statistics. However, you can set a default value for rows that do not have any value for the specified field.
    • The following table describes the parameters.
      Parameter Description
      aggregationName The name given to the aggregation to distinguish it from other aggregation operations. You can find the required aggregation results based on its name.
      fieldName The name of the field for which to obtain statistics. Only fields of the long and double types are supported.
      missing The default value of rows that do not have any value for the specified field. If a row is missing a value for a specified field and this parameter is not specified, the row is ignored. If this parameter is set, the value of this parameter is used as the field value of the row.
    • Java example
      /**
       * The price of each product is listed in the product table. Query the minimum price among the products produced in Zhejiang.
       * The equivalent SQL statement: SELECT min (column_price) FROM product where place_of_production = "Zhejiang";
       */
      public void min(SyncClient client) {
          // Create a query
          SearchRequest searchRequest = SearchRequest.newBuilder()
              .tableName("tableName")
              .indexName("indexName")
              .searchQuery(
                  SearchQuery.newBuilder()
                      .query(QueryBuilders.term("place_of_production","Zhejiang"))
                      .limit(0)   // If you only want to obtain the aggregation results, you can set the number of matched results to be returned to 0 to reduce the response time.
                      .addAggregation(AggregationBuilders.min("min_agg_1", "column_price").missing(100))
                      .build())
              .build();
          //Execute the query
          SearchResponse resp = client.search(searchRequest);
          //Obtain the aggregation results
          System.out.println(resp.getAggregationResults().getAsMinAggregationResult("min_agg_1").getValue());
      }
  • Maximum value
    • Definition: Query the maximum value of a field. This query is equivalent to the SQL MAX function. If a row does not include a field value, the row is not included in the statistics. However, you can set a default value for rows that do not have any value for the specified field.
    • Parameters
      Parameter Description
      aggregationName The name given to the aggregation to distinguish it from other aggregation operations. You can find the required aggregation results based on its name.
      fieldName The name of the field for which to obtain statistics. Only fields of the long and double types are supported.
      missing The default value of rows that do not have any value for the specified field. If a row is missing a value for a specified field and this parameter is not specified, the row is ignored. If this parameter is set, the value of this parameter is used as the field value of the row.
    • Java example
      /**
       * The price of each product is listed in the product table. Query the maximum price among the products produced in Zhejiang.
       * The equivalent SQL statement: SELECT max (column_price) FROM product where place_of_production = "Zhejiang";
       */
      public void max(SyncClient client) {
          // Create a query
          SearchRequest searchRequest = SearchRequest.newBuilder()
              .tableName("tableName")
              .indexName("indexName")
              .searchQuery(
                  SearchQuery.newBuilder()
                      .query(QueryBuilders.term("place_of_production", "Zhejiang"))
                      .limit(0)   // If you only want to obtain the aggregation results, you can set the number of matched results to be returned to 0 to reduce the response time.
                      .addAggregation(AggregationBuilders.max("max_agg_1", "column_price").missing(0))
                      .build())
              .build();
          //Execute the query
          SearchResponse resp = client.search(searchRequest);
          //Obtain the aggregation results
          System.out.println(resp.getAggregationResults().getAsMaxAggregationResult("max_agg_1").getValue());
      }
  • Sum
    • Definition: Query the total value of all rows for a numeric field. This query is equivalent to the SQL SUM function. If a row does not include a field value, the row is not included in the statistics. However, you can set a default value for rows that do not have any value for the specified field.
    • Parameters
      Parameter Description
      aggregationName The name given to the aggregation to distinguish it from other aggregation operations. You can find the required aggregation results based on its name.
      fieldName The name of the field for which to obtain statistics. Only fields of the long and double types are supported.
      missing The default value of rows that do not have any value for the specified field. If a row is missing a value for a specified field and this parameter is not specified, the row is ignored. If this parameter is set, the value of this parameter is used as the field value of the row.
    • Java example
      /**
       * The sales volume of each product is listed in the product table. Query the total number of the sold products that are produced in Zhejiang. Set the value of missing to 10.
       * The equivalent SQL statement: SELECT sum (column_price) FROM product where place_of_production = "Zhejiang";
       */
      public void sum(SyncClient client) {
          // Create a query
          SearchRequest searchRequest = SearchRequest.newBuilder()
              .tableName("tableName")
              .indexName("indexName")
              .searchQuery(
                  SearchQuery.newBuilder()
                      .query(QueryBuilders.term("place_of_production", "Zhejiang"))
                      .limit(0)   // If you only want to obtain the aggregation results, you can set the number of matched results to be returned to 0 to reduce the response time.
                      .addAggregation(AggregationBuilders.sum("sum_agg_1", "column_number").missing(10))
                      .build())
              .build();
          //Execute the query
          SearchResponse resp = client.search(searchRequest);
          //Obtain the aggregation results
          System.out.println(resp.getAggregationResults().getAsSumAggregationResult("sum_agg_1").getValue());
      }
  • Average
    • Definition: Query the average value of all rows for a numeric field. This query is equivalent to the SQL AVG function. If a row does not include a field value, the row is not included in the statistics. However, you can set a default value for rows that do not have any value for the specified field.
    • Parameters
      Parameter Description
      aggregationName The name given to the aggregation to distinguish it from other aggregation operations. You can find the required aggregation results based on its name.
      fieldName The name of the field for which to obtain statistics. Only fields of the long and double types are supported.
      missing The default value of rows that do not have any value for the specified field. If a row is missing a value for a specified field and this parameter is not specified, the row is ignored. If this parameter is set, the value of this parameter is used as the field value of the row.
    • Java example
      /**
       * The sales volume of each product is listed in the product table. Query the average price of the products produced in Zhejiang.
       * The equivalent SQL statement: SELECT avg (column_price) FROM product where place_of_production = "Zhejiang";
       */
      public void avg(SyncClient client) {
          // Create a query
          SearchRequest searchRequest = SearchRequest.newBuilder()
              .tableName("tableName")
              .indexName("indexName")
              .searchQuery(
                  SearchQuery.newBuilder()
                      .query(QueryBuilders.term("place_of_production", "Zhejiang"))
                      .limit(0)   // If you only want to obtain the aggregation results, you can set the number of matched results to be returned to 0 to reduce the response time.
                      .addAggregation(AggregationBuilders.avg("avg_agg_1", "column_number"))
                      .build())
              .build();
          //Execute the query
          SearchResponse resp = client.search(searchRequest);
          //Obtain the aggregation results
          System.out.println(resp.getAggregationResults().getAsAvgAggregationResult("avg_agg_1").getValue());
      }
  • Count
    • Definition: Query the total number of values for a field. This query is equivalent to the SQL COUNT function. If the field value in a row does not exist, the row is not included in the statistics.
      Note The current count operation does not support the COUNT (*) function. To count the rows in an index or the matched rows in a query, use query operations and set the setGetTotalCount parameter to true in the query.
    • Parameters
      Parameter Description
      aggregationName The name given to the aggregation to distinguish it from other aggregation operations. You can find the required aggregation results based on its name.
      fieldName The name of the field for which to obtain statistics. Only fields of the long, double, Boolean, keyword, and geo_point types are supported.
    • Java example
      /**
       * The punishment records of each merchant is recorded in the merchant table. Query the number of merchants in Zhejiang who have punishment records. (Assume that merchants with no punishment records do not have a value for the specified field.)
       * The equivalent SQL statement: SELECT count(column_history) FROM product where place_of_production="Zhejiang";
       */
      public void count(SyncClient client) {
          // Create a query
          SearchRequest searchRequest = SearchRequest.newBuilder()
              .tableName("tableName")
              .indexName("indexName")
              .searchQuery(
                  SearchQuery.newBuilder()
                      .query(QueryBuilders.term("place", "Zhejiang"))
                      .limit(0)
                      .addAggregation(AggregationBuilders.count("count_agg_1", "column_history"))
                      .build())
              .build();
          //Execute the query
          SearchResponse resp = client.search(searchRequest);
          //Obtain the aggregation results
          System.out.println(resp.getAggregationResults().getAsCountAggregationResult("count_agg_1").getValue());
      }
    • Distinct count
      • Definition: Query the number of distinct values for a field. This query is equivalent to the SQL COUNT (distinct) function. You can set a default value for rows that do not have any value for the specified field.
      • Parameters
        Parameter Description
        aggregationName The name given to the aggregation to distinguish it from other aggregation operations. You can find the required aggregation results based on its name.
        fieldName The name of the field for which to obtain statistics. Only fields of the long, double, Boolean, keyword, and geo_point types are supported.
        missing The default value of rows that do not have any value for the specified field. If a row is missing a value for a specified field and this parameter is not specified, the row is ignored. If this parameter is set, the value of this parameter is used as the field value of the row.
      • Java example
        /**
         * Query the number of distinct provinces from which all products come.
         * The equivalent SQL statement: SELECT count(distinct column_place) FROM product;
         */
        public void distinctCount(SyncClient client) {
            // Create a query
            SearchRequest searchRequest = SearchRequest.newBuilder()
                .tableName("tableName")
                .indexName("indexName")
                .searchQuery(
                    SearchQuery.newBuilder()
                        .query(QueryBuilders.matchAll())
                        .limit(0)
                        .addAggregation(AggregationBuilders.distinctCount("dis_count_agg_1", "column_place"))
                        .build())
                .build();
            //Execute the query
            SearchResponse resp = client.search(searchRequest);
            //Obtain the aggregation results
            System.out.println(resp.getAggregationResults().getAsDistinctCountAggregationResult("dis_count_agg_1").getValue());
        }

Multiple statistics

Each request supports multiple statistics.

Java example

public void multipleAggregation(SyncClient client) {
    // Create a query
    SearchRequest searchRequest = SearchRequest.newBuilder()
        .tableName("tableName")
        .indexName("indexName")
        .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
    SearchResponse resp = client.search(searchRequest);
    //Obtain the results of the first aggregation
    System.out.println(resp.getAggregationResults().getAsMinAggregationResult("name1").getValue());
    //Obtain the results of the second aggregation
    System.out.println(resp.getAggregationResults().getAsSumAggregationResult("name2").getValue());
    //Obtain the results of the third aggregation
    System.out.println(resp.getAggregationResults().getAsDistinctCountAggregationResult("name3").getValue());
}