Analyze data using Table Store aggregation-Tablestore(Tablestore)-阿里云帮助中心

This topic describes how to use the aggregation feature in Table Store for data analysis. You can use this feature to: calculate metrics such as minimum, maximum, sum, average, count, distinct count, and percentiles; group data by field value, range, location, or filters; generate histograms and date histograms; retrieve rows from within each group; and perform nested queries. You can also combine multiple aggregation functions to build complex queries.

Procedure

The following figure illustrates the complete aggregation procedure.

fig_agg_pro

After a query completes, the server aggregates all matching documents. Therefore, a request that includes an aggregation is more complex to process than one that does not.

Features

Aggregation provides functions similar to various SQL functions, including MIN(), MAX(), SUM(), AVG(), COUNT(), COUNT(DISTINCT), ANY_VALUE(), and GROUP BY. It also supports other features such as percentile statistics, group by field value, group by multiple fields, group by range, group by geographical location, group by filter, histogram aggregation, date histogram aggregation, and sub-aggregation. The following table details these features.

Feature	Description
Minimum value	Returns the minimum value in a field, similar to the MIN function in SQL.
Maximum value	Returns the maximum value of a field, similar to the SQL `max` function.
Sum	Returns the sum of values in a numeric field, similar to the SQL sum function.
Average value	Calculates the average of a numeric field, similar to the SQL `avg` function.
Count	Returns the number of values for a specified field or the total number of rows in a Search Index, similar to the COUNT function in SQL.
Distinct count	Returns the number of distinct values for a specified field, similar to the COUNT(DISTINCT) function in SQL.
Percentile statistics	Use percentile statistics to analyze the percentile distribution of a data set. For example, during routine O&M, you can track the distribution of request latencies by checking percentiles such as P25, P50, P90, and P99.
Group by field value	Groups query results based on the value of a specified field. Rows with the same field value form a group. The operation returns each group's value and its row count.
Sub-aggregation	Group-by aggregations support nesting, allowing you to add sub-aggregations within a group.
Group by multiple fields	Groups query results based on multiple fields. This feature supports paging using a pagination token.
Group by range	Groups query results into custom ranges based on a field's value, returning a count for each range.
Group by geographical location	Groups query results by distance from an origin. The operation places items within a specified distance range into the same group and returns the count for each range.
Group by filter	Groups query results based on specified filters. This aggregation returns the count of documents that match each filter. The results are returned in the same order the filters are added.
Histogram aggregation	Groups query results by a specified data interval. Rows with field values that fall within the same interval are placed in the same group. The operation returns the starting value of each group and its corresponding row count.
Date histogram aggregation	For a date field, groups rows into buckets of a specified time interval and returns the row count for each bucket.
Get rows from aggregation group	After grouping query results, a top-rows aggregation retrieves a selection of rows from each group. This feature is similar to the `ANY_VALUE(field)` function in MySQL.
Multiple aggregations	You can combine multiple aggregations in a single query. Note Complex queries with multiple aggregations may affect response time.

Related APIs

The API operation for aggregation is Search.

Prerequisites

You can run aggregations by using the Tablestore console, CLI, or an SDK.

Important

The Tablestore console and CLI support only a subset of aggregation features.

Use an Alibaba Cloud account or a RAM user with the required permissions for Table Store operations. To grant permissions to a RAM user, see Grant permissions to a RAM user by using a RAM policy.
If you use an SDK or a command-line tool, create an AccessKey for your Alibaba Cloud account or RAM user if you do not have one.
You have created a data table.
A Search Index has been created for the data table.
If you use an SDK, initialize the Tablestore Client.
If you use the command-line tool, download and start the tool, then configure the connection to your instance and select the target table. For more information, see Download the command-line tool, Start the tool and configure connection information, and Data table operations.

Console

Go to the Index Management tab.
1. Log on to the Table Store console.
2. In the top navigation bar, select a resource group and a region.
3. On the Overview page, click the instance name or click Instance Management in the Actions column.
4. On the Instance Details tab, in the Data Table List tab, click the data table name or click Index Management in the Actions column.
On the Index Management tab, find the target Search Index and click Search in the Actions column.
In the Search dialog box, specify the query conditions.
1. By default, all columns are returned. To return specific columns, turn off Retrieve All Columns and enter the column names, separated by commas.
  Note
  By default, Table Store returns the primary key columns of the data table.
2. Select a logical operator: And, Or, or Not.
  If you select And, the query returns data that meets all specified conditions. If you select Or, the query returns data that meets at least one of the specified conditions. If you select Not, the query returns data that does not meet the specified conditions.
3. Select an index field, click Add, then set the query type and value for the field.
  
  You can repeat this step to add query conditions for multiple index fields.
4. By default, sorting is disabled. To sort the results by a specific field, turn on Enable Sorting, add the sort field, and configure the sort order.
5. By default, the statistics feature is disabled. To collect statistics for specific fields, turn on the Collect Statistics switch, add the fields for which you want to collect statistics, then configure the statistics type, Item, and Default value as required.
  
  You can add multiple fields for statistics at once. The valid values for Statistics Type are Minimum, Maximum, Sum, Average, Count, and Distinct count. The Default parameter specifies the value to use if a field does not exist in a row.
Click OK.

The Indexes tab displays the data that matches your query and the statistical results.

CLI

When you query data with thesearch command in the Tablestore CLI, you can also perform aggregations. Supported aggregation types include minimum (min), maximum (max), sum, average (avg), and count. For more information, see search index.

Run the followingsearch command to query and analyze data in the table using the search_index search index and return all indexed columns.
```
search -n search_index --return_all_indexed
```

Enter the query conditions as prompted.

The following example shows a query that retrieves rows where the value of the gid column is less than 10 or is 77. The query also calculates the average value of the gid column.

{
    "Offset": -1,
    "Limit": 10,
    "Collapse": null,
    "Sort": null,
    "GetTotalCount": true,
    "Token": null,
    "Query": {
        "Name": "BoolQuery",
        "Query": {
            "MinimumShouldMatch": 1,
            "MustQueries": null,
            "MustNotQueries": null,
            "FilterQueries": null,
            "ShouldQueries": [{
                "Name": "RangeQuery",
                "Query": {
                    "FieldName": "gid",
                    "From": null,
                    "To": 10,
                    "IncludeLower": false,
                    "IncludeUpper": false
                }
            }, {
                "Name": "TermQuery",
                "Query": {
                    "FieldName": "gid",
                    "Term": 77
                }
            }]
        }
    },
    "Aggregations": [{
        "Name": "avg",
        "Aggregation": {
            "AggName": "agg1",
            "Field": "gid",
            "MissingValue": null
        }
    }]
}

Tablestore SDKs

You can perform aggregations using the Tablestore SDK for Java, Tablestore SDK for Go, Tablestore SDK for Python, Tablestore SDK for Node.js, Tablestore SDK for .NET, and Tablestore SDK for PHP. The example in this topic uses the Tablestore SDK for Java.

Important

The aggregation feature supports the field types defined for a search index. For more information about these types and how they map from data table field types, see Data types.

Minimum value

Returns the minimum value in a field, similar to the MIN function in SQL.

Parameters

Parameter	Description
aggregationName	The unique name of the aggregation operation. 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, 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());
    }
}

Max

Returns the maximum value of a field, similar to the SQL max function.

Parameters

Parameter	Description
aggregationName	The unique name of the aggregation operation. 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, 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());
    }
}

Sum

Returns the sum of values in a numeric field, similar to the SQL sum function.

Parameters

Parameter	Description
aggregationName	The unique name of the aggregation operation. 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, 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());
    }
}

Average

Calculates the average of a numeric field, similar to the SQL avg function.

Parameters

Parameter	Description
aggregationName	The unique name of the aggregation operation. 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, 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());
    }
}

Count

Counts the number of values in a specified field or the total number of rows in a search index, similar to the count function in SQL.

Note

Use the following methods to query the total number of rows in a search index or the total number of rows that meet the query conditions:

Use the count feature of aggregation. Set the count parameter to * in the request.
Use the query feature to obtain the number of rows that meet the query conditions. Set the setGetTotalCount parameter to true in the query. Use MatchAllQuery to obtain the total number of rows in a search index.

Use the name of a column as the value of the count expression to query the number of rows that contain the column in a search index. This method is suitable for scenarios that involve sparse columns.

Parameters

Parameter	Description
aggregationName	The unique name of the aggregation operation. 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, 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());
    }
}

Distinct count

Returns the number of distinct values for a specified field, similar to the SQL count(distinct) function.

Note

The number of distinct values is an approximate number.

If the total number of rows before the distinct count feature is used is less than 10,000, the calculated result is close to the exact value.
If the total number of rows before the distinct count feature is used is greater than or equal to 100 million, the error rate is approximately 2%.

Parameters

Parameter	Description
aggregationName	The unique name of the aggregation operation. 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, 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());
    }
}

Percentile statistics

Use percentile statistics to analyze the percentile distribution of a data set. For example, during routine O&M, you can track the distribution of request latencies by checking percentiles such as P25, P50, P90, and P99.

Note

To improve the accuracy of the results, we recommend that you specify extreme percentile values such as p1 and p99. If you use extreme percentile values instead of other values such as p50, the returned results are more accurate.

Parameters

Parameter	Description
aggregationName	The unique name of the aggregation operation. 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, 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());
        }
    }
}

Group by field value

Groups query results based on the value of a specified field. Rows with the same field value form a group. The operation returns each group's value and its row count.

Note

Grouping by field value uses parallel computing. As this is an inexact statistical method, the results may contain minor errors.
To perform multi-field grouping, you can use either nested grouping or multi-field grouping. For a comparison of these two methods, see Appendix: Comparison of different implementations for multi-field grouping.

Parameters

Parameter	Description
groupByName	The unique name of the aggregation operation. 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 USD 2, and the minimum price is USD 0.5. Toiletries: 10. The maximum price is USD 13, and the minimum price is USD 0.1. Electronic devices: 3. The maximum price is USD 1,160, and the minimum price is USD 310. Other products: 15. The maximum price is USD 130, and the minimum price is USD 11.
 */
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, 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, 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)   //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. 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);
}

Nested grouping

Group-by aggregations support nesting, allowing you to add sub-aggregations within a group.

Note

To balance performance and complexity, the maximum nesting depth is limited. For more information, see Search Index limits.

Common scenarios
- Multi-level grouping (GroupBy + SubGroupBy)
  
  After you group data at the first level, perform second-level grouping. For example, 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, 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, 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());
        }
    }
}

Composite grouping

Groups query results based on multiple fields. This feature supports paging using a pagination token.

Important

This feature is available only in the Java SDK and Go SDK.

Parameters

Parameter	Description
groupByName	The unique name of the aggregation operation. Query the results of a specific aggregation operation based on this name.
sources	The fields by which you want to group query results. Group query results by up to 32 fields and perform aggregation operations on the resulting groups. The following group types are supported: GroupByField: Group by field value. Configure the groupByName, fieldName, and groupBySorter parameters. GroupByHistogram: Query by histogram. Configure the groupByName, fieldName, interval, and groupBySorter parameters. GroupByDateHistogram: Query by date histogram. Configure the groupByName, fieldName, interval, timeZone, and groupBySorter parameters. Important For group items in sources, only sorting by group value (groupKeySort) in dictionary order is supported. By default, groups are sorted in descending order. If a field value does not exist for a specific column, the value in the returned results will be NULL.
nextToken	The pagination token that is used to retrieve the next page of data. By default, this parameter is empty. For the first request, set nextToken to empty. If not all data that meets the query conditions is returned in a single request, the nextToken parameter in the response is not empty. Use this nextToken for pagination queries. Note If you need to persist the nextToken or transmit it to a frontend page, we recommend that you use Base64 encoding to encode the nextToken as a string before saving or transmitting it. The nextToken itself is not a string. If you directly use new String(nextToken) for encoding, token information will be lost.
size	The number of groups per page. Default value: 10. Maximum value: 2000. Important If you want to limit the number of groups to return, in most cases, we recommend that you configure the size parameter. You cannot configure the size and suggestedSize parameters at the same time.
suggestedSize	The number of groups to return. Specify a value greater than the maximum number of groups allowed at the server side or -1. The server side returns the number of groups based on its capacity. If you set this parameter to a value that is greater than the maximum number of groups allowed at the server side, the system adjusts the value to the maximum number of groups allowed at the server side. The actual number of groups that are returned equals min(suggestedSize, maximum number of groups allowed at the server side, total number of groups). Important This parameter is suitable for scenarios in which you interconnect Tablestore with a high-throughput computing engine, such as Apache Spark or PrestoSQL.
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. Important The GroupByComposite parameter is not supported in subGroupBy.

Examples

/**
 * Group and aggregate query results: Group the query results and perform the aggregation operation on the resulting groups based on the parameters such as groupbyField, groupByHistogram, and groupByDataHistogram that are passed to the SourceGroupBy parameter.
 * Return the aggregated results of multiple fields in a flat structure.
 */
public static void groupByComposite(SyncClient client) {
    GroupByComposite.Builder compositeBuilder = GroupByBuilders
            .groupByComposite("groupByComposite")
            .size(2000)
            .addSources(GroupByBuilders.groupByField("groupByField", "Col_Keyword")
                    .addGroupBySorter(GroupBySorter.groupKeySortInAsc()).build())
            .addSources(GroupByBuilders.groupByHistogram("groupByHistogram", "Col_Long")
                    .addGroupBySorter(GroupBySorter.groupKeySortInAsc())
                    .interval(5)
                    .build())
            .addSources(GroupByBuilders.groupByDateHistogram("groupByDateHistogram", "Col_Date")
                    .addGroupBySorter(GroupBySorter.groupKeySortInAsc())
                    .interval(5, DateTimeUnit.DAY)
                    .timeZone("+05:30").build());

    SearchRequest searchRequest = SearchRequest.newBuilder()
            .indexName("<SEARCH_INDEX_NAME>")
            .tableName("<TABLE_NAME>")
            .returnAllColumnsFromIndex(true)
            .searchQuery(SearchQuery.newBuilder()
                    .addGroupBy(compositeBuilder.build())
                    .build())
            .build();

    SearchResponse resp = client.search(searchRequest);

    while (true) {
        if (resp.getGroupByResults() == null || resp.getGroupByResults().getResultAsMap().size() == 0) {
            System.out.println("groupByComposite Result is null or empty");
            return;
        }

        GroupByCompositeResult result = resp.getGroupByResults().getAsGroupByCompositeResult("groupByComposite");

        if(!result.getSourceNames().isEmpty()) {
            for (String sourceGroupByNames: result.getSourceNames()) {
                System.out.printf("%s\t", sourceGroupByNames);
            }
            System.out.print("rowCount\t\n");
        }


        for (GroupByCompositeResultItem item : result.getGroupByCompositeResultItems()) {
            for (String value : item.getKeys()) {
                String val = value == null ? "NULL" : value;
                System.out.printf("%s\t", val);

            }
            System.out.printf("%d\t\n", item.getRowCount());
        }

        // Use the token to page through groups.
        if (result.getNextToken() != null) {
            searchRequest.setSearchQuery(
                    SearchQuery.newBuilder()
                            .addGroupBy(compositeBuilder.nextToken(result.getNextToken()).build())
                            .build()
            );
            resp = client.search(searchRequest);
        } else {
            break;
        }
    }
}

Group by range

Groups query results into custom ranges based on a field's value, returning a count for each range.

Parameters

Parameter	Description
groupByName	The unique name of the aggregation operation. 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. 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. Perform the sub-aggregation operation based on the grouping results. For example, after you group query results by sales volume and by province, get 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());
    }
}

Geographic grouping

Groups query results by distance from an origin. The operation places items within a specified distance range into the same group and returns the count for each range.

Parameters

Parameter	Description
groupByName	The unique name of the aggregation operation. 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. 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. 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());
    }
}

Group by filter

Groups query results based on specified filters. This aggregation returns the count of documents that match each filter. The results are returned in the same order the filters are added.

Parameters

Parameter	Description
groupByName	The unique name of the aggregation operation. 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. 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());
    }
}

Group by histogram

Groups query results by a specified data interval. Rows with field values that fall within the same interval are placed in the same group. The operation returns the starting value of each group and its corresponding row count.

Parameters

Parameter	Description
groupByName	The unique name of the aggregation operation. 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());
    }
}

Date histogram

Groups query results for a date field into buckets at a specified time interval. Each bucket contains rows from the same interval and returns its start time and row count.

Important

This feature requires Tablestore Java SDK 5.16.1 or later. For the complete version history, see Java SDK version history.

Parameters

Parameter	Description
groupByName	The unique name of the aggregation operation. 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 Date type is supported. Important The Date type of search indexes is supported by Tablestore SDK for Java V5.13.9 and later.
interval	Statistical interval.
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.
timeZone	The time zone in the `+hh:mm` or `-hh:mm` format, such as `+08:00` or `-09:00`. This parameter is required only when the field is of the Date type. If this parameter is not specified for the field of the Date type, the offset of N hours may occur in the aggregation results. Specify the timeZone parameter to prevent this issue.

Examples

/**
 * Collect statistics on the daily distribution of data in the col_date field from 10:00:00 on May 1, 2017 to 13:00:00 May 21, 2017.
 */
public static void groupByDateHistogram(SyncClient client) {
    //Create a query statement.
    SearchRequest searchRequest = SearchRequest.newBuilder()
    .returnAllColumns(false)
    .indexName("<SEARCH_INDEX_NAME>")
    .tableName("<TABLE_NAME>")
    .searchQuery(
        SearchQuery.newBuilder()
        .query(QueryBuilders.matchAll())
        .limit(0)
        .getTotalCount(false)
        .addGroupBy(GroupByBuilders
                    .groupByDateHistogram("groupByDateHistogram", "col_date")
                    .interval(1, DateTimeUnit.DAY)
                    .minDocCount(1)
                    .missing("2017-05-01 13:01:00")
                    .fieldRange("2017-05-01 10:00", "2017-05-21 13:00:00"))
        .build())
    .build();
    //Execute the query statement.
    SearchResponse resp = ots.search(searchRequest);
    //Obtain the results that are returned when the aggregation operation is performed.
    List<GroupByDateHistogramItem> items = resp.getGroupByResults().getAsGroupByDateHistogramResult("groupByDateHistogram").getGroupByDateHistogramItems();
    for (GroupByDateHistogramItem item : items) {
        System.out.printf("millisecondTimestamp:%d, count:%d \n", item.getTimestamp(), item.getRowCount());
    }
}

Top-rows aggregation

After grouping query results, a top-rows aggregation retrieves a selection of rows from each group. This feature is similar to the ANY_VALUE(field) function in MySQL.

Note

When using a top-rows aggregation, if the Search Index contains fields of the nested type, Geopoint, or array type, the aggregation returns only the primary key of each row. To retrieve other fields, query the data table separately.

Parameters

Parameter	Description
aggregationName	The unique name of the aggregation operation. 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. 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();
    }
}

Multiple aggregations

You can combine multiple aggregations in a single query.

Note

Complex queries with multiple aggregations may increase response time.

Combine multiple aggregations

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());
}

Combine Aggregation and GroupBy

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());
    }
}

Appendix: Comparison of multi-field grouping methods

If you want to group query results by multiple fields, use the groupBy parameter in nested mode or use the GroupByComposite parameter. The following table describes the difference between the groupBy parameter in nested mode and the GroupByComposite parameter.

Feature	groupBy (nested)	Multi-field grouping
size	2000	2000
Limits on fields	Up to 3 levels are supported.	Up to 32 levels are supported.
Pagination	Not supported	Supported by using the nextToken parameter
Sorting rules for rows in groups	In alphabetical order or reverse alphabetical order By row count in ascending order or row count in descending order By the values that are obtained from sub-aggregation results in ascending order or the values that are obtained from sub-aggregation results in descending order	In alphabetical order or reverse alphabetical order
Supports aggregation	Yes	Yes
Compatibility	For fields of the Date type, the query results are returned in the specified format.	For fields of the DATE type, the query results are returned as timestamp strings.