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

Statistics 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 have any values in the specified field, the row is not considered in the statistics. You can set a default value for rows that do not have any values for the specified field.
    • Parameters
      Parameter Description
      Name The unique name specified for the aggregation. You can find the required aggregation results based on the name.
      FieldName The name of the field from 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 values for the specified field. If a row is missing a value for a specified field and the Missing parameter is not specified, the row is ignored. If the Missing parameter is set, the value of this parameter is used as the field value of the row.
    • 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";
       */
      func min(client *tablestore.TableStoreClient, tableName string, indexName string) {
          searchRequest := &tablestore.SearchRequest{}
      
          searchRequest.
              SetTableName(tableName).    // Set the table name.
              SetIndexName(indexName).    // Set the search index name.
              SetSearchQuery(search.NewSearchQuery().
                  SetQuery(&search.TermQuery{"place_of_production", "Zhejiang"}).
                  SetLimit(0).    // If you want to obtain only the aggregation results, you can set the number of matched results to be returned to 0 to reduce the response time.
                  Aggregation(search.NewMinAggregation("min_agg_1", "column_price").Missing(0.00)))
      
          searchResponse, err := client.Search(searchRequest)    // Execute the query.
          aggResults := searchResponse.AggregationResults        // Obtain the aggregation results.
          agg1, err := aggResults.Min("min_agg_1")        // Obtain the aggregation result of min_agg_1.
          if err ! = nil {
              panic(err)
          }
           if agg1.HasValue() {        // The aggregation min_agg_1 has a value.
              fmt.Println(agg1.Value)    // Display the aggregation result.
          }
      }
  • Maximum value
    • Definition: Query the maximum value of a field. This query is equivalent to the SQL MAX function. If a row does not have any values in the specified field, the row is not considered in the statistics. You can set a default value for rows that do not have any values for the specified field.
    • Parameters
      Parameter Description
      Name The unique name specified for the aggregation. You can find the required aggregation results based on the name.
      FieldName The name of the field from 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 values for the specified field. If a row is missing a value for a specified field and the Missing parameter is not specified, the row is ignored. If the Missing parameter is set, the value of this parameter is used as the field value of the row.
    • 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";
       */
      func max(client *tablestore.TableStoreClient, tableName string, indexName string) {
          searchRequest := &tablestore.SearchRequest{}
      
          searchRequest.
              SetTableName(tableName).    // Set the table name.
              SetIndexName(indexName).    // Set the search index name.
              SetSearchQuery(search.NewSearchQuery().
                  SetQuery(&search.TermQuery{"place_of_production", "Zhejiang"}).
                  SetLimit(0).    // If you want to obtain only the aggregation results, you can set the number of matched results to be returned to 0 to reduce the response time.
                  Aggregation(search.NewMaxAggregation("max_agg_1", "column_price").Missing(0.00)))
      
          searchResponse, err := client.Search(searchRequest)    // Execute the query.
          aggResults := searchResponse.AggregationResults        // Obtain the aggregation results.
          agg1, err := aggResults.Max("max_agg_1")        // Obtain the aggregation result of max_agg_1.
          if err ! = nil {
              panic(err)
          }
           if agg1.HasValue() {        // The aggregation max_agg_1 has a value.
              fmt.Println(agg1.Value)    // Display the aggregation result.
          }
      }
  • Sum
    • Definition: Query the sum of all rows for a numeric field. This query is equivalent to the SQL SUM function. If a row does not have any values in the specified field, the row is not considered in the statistics. You can set a default value for rows that do not have any values for the specified field.
    • Parameters
      Parameter Description
      Name The unique name specified for the aggregation. You can find the required aggregation results based on the name.
      FieldName The name of the field from 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 values for the specified field. If a row is missing a value for a specified field and the Missing parameter is not specified, the row is ignored. If the Missing parameter is set, the value of this parameter is used as the field value of the row.
    • 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";
       */
      func sum(client *tablestore.TableStoreClient, tableName string, indexName string) {
          searchRequest := &tablestore.SearchRequest{}
      
          searchRequest.
              SetTableName(tableName).    // Set the table name.
              SetIndexName(indexName).    // Set the search index name.
              SetSearchQuery(search.NewSearchQuery().
                  SetQuery(&search.TermQuery{"place_of_production", "Zhejiang"}).
                  SetLimit(0).    // If you want to obtain only the aggregation results, you can set the number of matched results to be returned to 0 to reduce the response time.
                  Aggregation(search.NewSumAggregation("sum_agg_1", "column_price").Missing(0.00)))
      
          searchResponse, err := client.Search(searchRequest)    // Execute the query.
          aggResults := searchResponse.AggregationResults        // Obtain the aggregation results.
           agg1, err := aggResults.Sum("sum_agg_1")        // Obtain the aggregation result of sum_agg_1.
          if err ! = nil {
              panic(err)
          }
          fmt.Println(agg1.Value)    // Display the aggregation result.
      }
  • 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 have any values in the specified field, the row is not considered in the statistics. You can set a default value for rows that do not have any values for the specified field.
    • Parameters
      Parameter Description
      Name The unique name specified for the aggregation. You can find the required aggregation results based on the name.
      FieldName The name of the field from 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 values for the specified field. If a row is missing a value for a specified field and the Missing parameter is not specified, the row is ignored. If the Missing parameter is set, the value of this parameter is used as the field value of the row.
    • 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";
       */
      func avg(client *tablestore.TableStoreClient, tableName string, indexName string) {
          searchRequest := &tablestore.SearchRequest{}
      
          searchRequest.
              SetTableName(tableName).    // Set the table name.
              SetIndexName(indexName).    // Set the search index name.
              SetSearchQuery(search.NewSearchQuery().
                  SetQuery(&search.TermQuery{"place_of_production", "Zhejiang"}).
                  SetLimit(0).    // If you want to obtain only the aggregation results, you can set the number of matched results to be returned to 0 to reduce the response time.
                  Aggregation(search.NewAvgAggregation("avg_agg_1", "column_price").Missing(0.00)))
      
          searchResponse, err := client.Search(searchRequest)    // Execute the query.
          aggResults := searchResponse.AggregationResults        // Obtain the aggregation results.
          agg1, err := aggResults.Avg("avg_agg_1")        // Obtain the aggregation result of avg_agg_1.
          if err ! = nil {
              panic(err)
          }
          if agg1.HasValue() { // The aggregation agg1 has a value.
              fmt.Println(agg1.Value)    // Display the aggregation result.
          }
      }
  • Count
    • Definition: Query the total number of values for a field. This query is equivalent to the SQL COUNT function. If a row does not have any values in the specified field, the row is not considered in the statistics.
    • Parameters
      Parameter Description
      Name The unique name specified for the aggregation. You can find the required aggregation results based on the name.
      FieldName The name of the field from which to obtain statistics. Only fields of the long, double, boolean, keyword and geo_point types are supported.
    • Example
      
      /**
       * The punishment records of each merchant are 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";
       */
      func count(client *tablestore.TableStoreClient, tableName string, indexName string) {
          searchRequest := &tablestore.SearchRequest{}
      
          searchRequest.
              SetTableName(tableName).    // Set the table name.
              SetIndexName(indexName).    // Set the search index name.
              SetSearchQuery(search.NewSearchQuery().
                  SetQuery(&search.TermQuery{"place_of_production", "Zhejiang"}).
                  SetLimit(0).    // If you want to obtain only the aggregation results, you can set the number of matched results to be returned to 0 to reduce the response time.
                  Aggregation(search.NewCountAggregation("count_agg_1", "column_price")))
      
          searchResponse, err := client.Search(searchRequest)    // Execute the query.
          aggResults := searchResponse.AggregationResults        // Obtain the aggregation results.
          agg1, err := aggResults.Count("count_agg_1")        // Obtain the aggregation result of count_agg_1.
          if err ! = nil {
              panic(err)
          }
          fmt.Println(agg1.Value)    // Display the aggregation result.
      }
    • 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 values for the specified field.
      • Error: The number of distinct values is approximate. A certain error may occur when the total number of values is large. When the number of values is 100 million, the error rate is less than 2%.
      • Parameters
        Parameter Description
        Name The unique name specified for the aggregation. You can find the required aggregation results based on the name.
        FieldName The name of the field from 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 values for the specified field. If a row is missing a value for a specified field and the Missing parameter is not specified, the row is ignored. If the Missing parameter is set, the value of this parameter is used as the field value of the row.
      • Example
        /**
         * Query the number of distinct provinces from which all products come.
         * The equivalent SQL statement: SELECT count(distinct column_place) FROM product;
         */
        func distinctCount(client *tablestore.TableStoreClient, tableName string, indexName string) {
            searchRequest := &tablestore.SearchRequest{}
        
            searchRequest.
                SetTableName(tableName).    // Set the table name.
                SetIndexName(indexName).    // Set the search index name.
                SetSearchQuery(search.NewSearchQuery().
                    SetQuery(&search.TermQuery{"place_of_production", "Zhejiang"}).
                    SetLimit(0).    // If you want to obtain only the aggregation results, you can set the number of matched results to be returned to 0 to reduce the response time.
                    Aggregation(search.NewDistinctCountAggregation("distinct_count_agg_1", "column_price").Missing(0.00)))
        
            searchResponse, err := client.Search(searchRequest)    // Execute the query.
            aggResults := searchResponse.AggregationResults        // Obtain the aggregation results.
            agg1, err := aggResults.DistinctCount("distinct_count_agg_1")        // Obtain the aggregation result of distinct_count_agg_1.
            if err ! = nil {
                panic(err)
            }
            fmt.Println(agg1.Value)    // Display the aggregation result.
        }

Multiple statistics

A request can aggregate multiple statistics at a time.

Example

func MultipleAggregations(client *tablestore.TableStoreClient, tableName string, indexName string) {
    searchRequest := &tablestore.SearchRequest{}

    searchRequest.
        SetTableName(tableName).    // Set the table name.
        SetIndexName(indexName).    // Set the search index name.
        SetSearchQuery(search.NewSearchQuery().
            SetQuery(&search.MatchAllQuery{}).    // Specify to match all rows.
            SetLimit(0).
            Aggregation(search.NewAvgAggregation("agg1", "Col_Long")).                // Calculate the average value for the Col_Long field.
            Aggregation(search.NewDistinctCountAggregation("agg2", "Col_Long")).    // Calculate the number of distinct values for the Col_Long field.
            Aggregation(search.NewMaxAggregation("agg3", "Col_Long")).                // Calculate the maximum value for the Col_Long field.
             Aggregation(search.NewSumAggregation("agg4", "Col_Long")).                // Calculate the sum for the Col_Long field.
            Aggregation(search.NewCountAggregation("agg5", "Col_Long")))            // Calculate the rows for the Col_Long field.

    // Specify to return all columns.
    searchRequest.SetColumnsToGet(&tablestore.ColumnsToGet{
        ReturnAll: true,
    })
    searchResponse, err := client.Search(searchRequest)
    if err ! = nil {
        fmt.Printf("%#v", err)
        return
    }
    aggResults := searchResponse.AggregationResults        // Obtain the aggregation results

    //avg agg
    agg1, err := aggResults.Avg("agg1")        // Obtain the average of agg1.
    if err ! = nil {
        panic(err)
    }
    if agg1.HasValue() {                            // Determine whether the aggregation agg1 contains any values.
        fmt.Println("(avg) agg1: ", agg1.Value)    // Display the average value of the Col_Long field.
    } else {
        fmt.Println("(avg) agg1: no value")        // No rows contain any values for the Col_Long field.
    }

    //distinct count agg
    agg2, err := aggResults.DistinctCount("agg2")    // Obtain the DistinctCount of agg2.
    if err ! = nil {
        panic(err)
    }
    fmt.Println("(distinct) agg2: ", agg2.Value)        // Display the number of distinct values for the Col_Long field.

    //max agg
    agg3, err := aggResults.Max("agg3")        // Obtain the Max of agg3.
    if err ! = nil {
        panic(err)
    }
    if agg3.HasValue() {
        fmt.Println("(max) agg3: ", agg3.Value)    // Display the maximum value for the Col_Long field.
    } else {
        fmt.Println("(max) agg3: no value")        // No rows contain any values for the Col_Long field.
    }

    //sum agg
    agg4, err := aggResults.Sum("agg4")        // Obtain the sum of agg4.
    if err ! = nil {
        panic(err)
    }
    fmt.Println("(sum) agg4: ", agg4.Value)        // Display the sum for the Col_Long field.

    //count agg
    agg5, err := aggResults.Count("agg5")        // Obtain the count of agg5.
    if err ! = nil {
        panic(err)
    }
    fmt.Println("(count) agg6: ", agg5.Value)    // Display the number of values for the Col_Long field.
}