You can perform aggregation operations to obtain the minimum value, maximum value, sum, average, count and distinct count of rows, percentile statistics, and rows in each group. You can also perform aggregation operations to group results by field value, range, geographical location, filter, or histogram, and perform nested queries. You can perform multiple aggregation operations for complex queries.

## Background information

The following table describes the aggregation methods.
Method Description
Minimum value

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

Maximum value

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

Sum

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

Average value

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

Count The aggregation method that can be used to return the total number of values for a specified field or the total number of rows in a search index. This method can be used in a similar manner as the SQL COUNT function.
Distinct count The aggregation method that can be used to return the number of distinct values for a field. This method can be used in a similar manner as the SQL COUNT(DISTINCT) function.
Percentile statistics

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

Group by field value
The aggregation method that can be used to group query results based on field values. The values that are the same are grouped together. The identical value of each group and the number of identical values in each group are returned.
Note The calculated number may be different from the actual number if the number of values in a group is very large.
Group by range

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

Group by geographical location

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

Group by filter

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

Query by histogram

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

Query the rows that are obtained from the results of an aggregation operation in each group

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

Nesting

GroupBy supports nesting. You can perform sub-aggregation operations by using GroupBy.

Multiple aggregations
You can perform multiple aggregation operations.
Note If you perform multiple complex aggregation operations at the same time, a long period of time may be required.

## API operations

You can call the Search operation to use the aggregation feature.

## Use Tablestore SDKs

You can use the following Tablestore SDKs to perform aggregation operations:

## Minimum value

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

• Parameters
Parameter Description
aggregationName The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
fieldName The name of the field that is used to perform the aggregation operation. Only the LONG and DOUBLE data types are supported.
missing

The default value for the field that is used to perform the aggregation operation on a row when 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) {
// Create a query statement.
SearchRequest searchRequest = SearchRequest.newBuilder()
.tableName("tableName")
.indexName("indexName")
.searchQuery(
SearchQuery.newBuilder()
.query(QueryBuilders.term("place_of_production","Zhejiang"))
.limit(0) // If you want to obtain only the aggregation results instead of specific data, you can set limit to 0 to improve query performance.
.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());
}``````

## Maximum value

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

• Parameters
Parameter Description
aggregationName The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
fieldName The name of the field that is used to perform the aggregation operation. Only the LONG and DOUBLE data types are supported.
missing

The default value for the field that is used to perform the aggregation operation on a row when 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) {
// Create a query statement.
SearchRequest searchRequest = SearchRequest.newBuilder()
.tableName("tableName")
.indexName("indexName")
.searchQuery(
SearchQuery.newBuilder()
.query(QueryBuilders.term("place_of_production", "Zhejiang"))
.limit(0) // If you want to obtain only the aggregation results instead of specific data, you can set limit to 0 to improve query performance.
.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());
}``````

## Sum

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

• Parameters
Parameter Description
aggregationName The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
fieldName The name of the field that is used to perform the aggregation operation. Only the LONG and DOUBLE data types are supported.
missing

The default value for the field that is used to perform the aggregation operation on a row when 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 volume of each product is listed in the product table. Query the total number of the sold products that are produced in Zhejiang. If the sales volume of a product is empty, 10 is used as the sales volume by default.
* SQL statement: SELECT sum(column_price) FROM product where place_of_production = "Zhejiang".
*/
public void sum(SyncClient client) {
// Create a query statement.
SearchRequest searchRequest = SearchRequest.newBuilder()
.tableName("tableName")
.indexName("indexName")
.searchQuery(
SearchQuery.newBuilder()
.query(QueryBuilders.term("place_of_production", "Zhejiang"))
.limit(0) // If you want to obtain only the aggregation results instead of specific data, you can set limit to 0 to improve query performance.
.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());
}``````

## Average value

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

• Parameters
Parameter Description
aggregationName The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
fieldName The name of the field that is used to perform the aggregation operation. Only the LONG and DOUBLE data types are supported.
missing

The default value for the field that is used to perform the aggregation operation on a row when 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 volume of each product is 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) {
// Create a query statement.
SearchRequest searchRequest = SearchRequest.newBuilder()
.tableName("tableName")
.indexName("indexName")
.searchQuery(
SearchQuery.newBuilder()
.query(QueryBuilders.term("place_of_production", "Zhejiang"))
.limit(0) // If you want to obtain only the aggregation results instead of specific data, you can set limit to 0 to improve query performance.
.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());
}``````

## Count

The aggregation method that can be used to return the total number of values for a specified field or the total number of rows in a search index. This method can be used in a similar manner as the SQL COUNT function.
Note You can 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.

You can 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. You can query the results of a specific aggregation operation based on this name.
fieldName The name of the field that is used for the aggregation operation. Only the following data types are supported: LONG, DOUBLE, BOOLEAN, KEYWORD, and GEOPOINT.
• Examples
``````/**
* Punishment records of merchants are recorded in the merchant table. You can query the number of merchants who are located in Zhejiang and for whom punishment records exist. If no punishment records exist for a merchant, the field that corresponds to punishment 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) {
// Create a query statement.
SearchRequest searchRequest = SearchRequest.newBuilder()
.tableName("tableName")
.indexName("indexName")
.searchQuery(
SearchQuery.newBuilder()
.query(QueryBuilders.term("place", "Zhejiang"))
.limit(0)
.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());
}``````

## Distinct count

The aggregation method that can be used to return the number of distinct values for a field. This method can be used in a similar manner as 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 an 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. You can query the results of a specific aggregation operation based on this name.
fieldName The name of the field that is used for the aggregation operation. Only the following data types are supported: LONG, DOUBLE, BOOLEAN, KEYWORD, and GEOPOINT.
missing The default value for the field that is used to perform the aggregation operation on a row when 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) {
// Create a query statement.
SearchRequest searchRequest = SearchRequest.newBuilder()
.tableName("tableName")
.indexName("indexName")
.searchQuery(
SearchQuery.newBuilder()
.query(QueryBuilders.matchAll())
.limit(0)
.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());
}``````

## Percentile statistics

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

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. You can query the results of a specific aggregation operation based on this name.
fieldName The name of the field that is used to perform the aggregation operation. Only the LONG and DOUBLE data 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 that is used to perform the aggregation operation on a row when 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) {
// Create a query statement.
SearchRequest searchRequest = SearchRequest.newBuilder()
.tableName("tableName")
.indexName("indexName")
.searchQuery(
SearchQuery.newBuilder()
.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 analysis 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

The aggregation method that can be used to group query results based on field values. The values that are the same are grouped together. The identical value of each group and the number of identical values in each group are returned.
Note The calculated number may be different from the actual number if the number of values in a group is very large.
• Parameters
Parameter Description
groupByName The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
fieldName The name of the field that is used for the aggregation operation. Only the following data types are supported: LONG, DOUBLE, BOOLEAN, and KEYWORD.
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. Supported parameters:
• Sort by value in alphabetical order
• Sort by value in reverse alphabetical order
• Sort by row count in ascending order
• Sort by row count in descending order
• Sort by the values that are obtained from sub-aggregation results in ascending order
• Sort by the values that are obtained from sub-aggregation results in descending order
size The number of returned groups. Maximum value: 2000. If the number of groups exceeds 2,000, only the first 2,000 groups are returned.
subAggregation and subGroupBy The sub-aggregation operation. You can perform the sub-aggregation operation based on the grouping results.
• Scenario

Query the number of products in each category, and the maximum and minimum product prices in each category.

• Method

Group query results by product category to obtain the number of products in each category. Then, perform two sub-aggregation operations to obtain the maximum and minimum product prices in each category.

• Sample results
• Fruits: 5. The maximum price is CNY 15. The minimum price is CNY 3.
• Toiletries: 10. The maximum price is CNY 98. The minimum price is CNY 1.
• Electronic devices: 3. The maximum price is CNY 8,699. The minimum price is CNY 2,300.
• Other products: 15. The maximum price is CNY 1,000. The minimum price is CNY 80.
• Example 1
``````/**
* Query the number of products, and the maximum and minimum product prices in each category.
* Example of returned results: Fruits: 5. The maximum price is CNY 15, and the minimum price is CNY 3. Toiletries: 10. The maximum price is CNY 98, and the minimum price is CNY 1. Electronic devices: 3. The maximum price is CNY 8,699, and the minimum price is CNY 2,300.
* Other products: 15. The maximum price is CNY 1,000, and the minimum price is CNY 80.
*/
public void groupByField(SyncClient client) {
// Create a query statement.
SearchRequest searchRequest = SearchRequest.newBuilder()
.tableName("tableName")
.indexName("indexName")
.searchQuery(
SearchQuery.newBuilder()
.query(QueryBuilders.matchAll())
.limit(0)
.groupByField("name1", "column_type")
)
.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());
}
}``````
• Example 2
``````    /**
* Group results based on multiple fields.
* Search indexes do not support the GROUP BY clause for multiple fields in SQL. You can use nested GroupBy parameters to obtain the results that are the same as those obtained by using the GROUP BY clause for multiple fields.
* SQL statement: select a,d, sum(b),sum(c) from user group by a,d.
*/
public void GroupByMultiField() {
SearchRequest searchRequest = SearchRequest.newBuilder()
.tableName("tableName")
.indexName("indexName")
.returnAllColumns(true)   // You can set returnAllColumns to false and specify a value for addColumesToGet to have better query performance.
.searchQuery(SearchQuery.newBuilder()
.query(QueryBuilders.matchAll())   // Specify query conditions. Query conditions can be used in the same manner as the WHERE clause in SQL. You can use QueryBuilders.bool() to perform nested queries.
GroupByBuilders
.groupByField("unique name_1", "field_a")
.size(20)
GroupByBuilders
.groupByField("unique name_2", "field_d")
.size(20)
)
)
.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);
}
}
}``````
• Example 3
``````    /**
* Configure 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, GroupBySorter.rowCountSortInDesc() is used and the groups are sorted by row count in descending order.
*/
public void groupByFieldWithSort(SyncClient client) {
// Create a query statement.
SearchRequest searchRequest = SearchRequest.newBuilder()
.tableName("tableName")
.indexName("indexName")
.searchQuery(
SearchQuery.newBuilder()
.query(QueryBuilders.matchAll())
.limit(0)
.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)
)
.build())
.build();
// Execute the query statement.
SearchResponse resp = client.search(searchRequest);
}``````

## Group by range

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

• Parameters
Parameter Description
groupByName The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
fieldName The name of the field that is used to perform the aggregation operation. Only the LONG and DOUBLE data types are supported.
range[double_from, double_to) The value ranges for grouping.

The value range can start from Double.MIN_VALUE and end with Double.MAX_VALUE.

subAggregation and subGroupBy The sub-aggregation operation. You can perform the sub-aggregation operation based on the grouping results.

For example, after you group query results by sales volume and by province, you can obtain the province that has the largest proportion of sales volume in a specified range. You must specify a value for 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("tableName")
.indexName("indexName")
.searchQuery(
SearchQuery.newBuilder()
.query(QueryBuilders.matchAll())
.limit(0)
.groupByRange("name1", "column_number")
)
.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());
}
}``````

## Group by geographical location

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

• Parameters
Parameter Description
groupByName The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
fieldName The name of the field that is used for the aggregation operation. Only the GEOPOINT data 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.

The value range can start from Double.MIN_VALUE and end with Double.MAX_VALUE.

subAggregation and subGroupBy The sub-aggregation operation. You can perform the sub-aggregation operation based on the grouping results.
• Examples
``````/**
* Group users based on geographical locations to a Wanda Plaza to obtain the number of users in each distance range. The distance ranges are [0, 1000), [1000, 5000), and [5000, Double.MAX_VALUE). Unit: meters.
*/
public void groupByGeoDistance(SyncClient client) {
// Create a query statement.
SearchRequest searchRequest = SearchRequest.newBuilder()
.tableName("tableName")
.indexName("indexName")
.searchQuery(
SearchQuery.newBuilder()
.query(QueryBuilders.matchAll())
.limit(0)
.groupByGeoDistance("name1", "column_geo_point")
.origin(3.1, 6.5)
)
.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

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

• Parameters
Parameter Description
groupByName The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
filter The filters that can be used for the query. Results are returned in the order in which the filters are specified.
subAggregation and subGroupBy The sub-aggregation operation. You can perform the sub-aggregation operation based on the grouping results.
• Examples
``````/**
* Specify the following filters to obtain the number of items that match each filter: The sales volume exceeds 100, the place of origin is Zhejiang, and the description contains Hangzhou.
*/
public void groupByFilter(SyncClient client) {
// Create a query statement.
SearchRequest searchRequest = SearchRequest.newBuilder()
.tableName("tableName")
.indexName("indexName")
.searchQuery(
SearchQuery.newBuilder()
.query(QueryBuilders.matchAll())
.limit(0)
.groupByFilter("name1")
)
.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());
}
}``````

## Query by histogram

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

• Parameters
Parameter Description
groupByName The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
fieldName The name of the field that is used to perform the aggregation operation. Only the LONG and DOUBLE data 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 value that is calculated 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 that is used to perform the aggregation operation on a row when 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("tableName")
.indexName("indexName")
.searchQuery(
SearchQuery.newBuilder()
.groupByHistogram("groupByHistogram", "age")
.interval(10)
.minDocCount(0L)
.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());
}
}``````

## Query the rows that are obtained from the results of an aggregation operation in each group

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

Note When you query the rows that are obtained from the results of an aggregation operation in each group, the returned results contain only the primary key information if the search index contains the NESTED, GEOPOINT, or ARRAY field. To obtain the required field, you must query the data table.
• Parameters
Parameter Description
aggregationName The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
limit The maximum number of rows that can be returned for each group. By default, only one row of data is returned.
sort The sorting method that is used to sort data in groups.
columnsToGet The fields that you want to return. Only fields in search indexes are supported. ARRAY, DATE, GEOPOINT, and NESTED fields are not supported.
• Examples
``````/**
* An activity application form of a school contains fields in which information such as the names of students, classes, head teachers, and class presidents can be specified. You can group students by class to view the application statistics and the property information of each class.
* SQL statement: select className, teacher, monitor, COUNT(*) as number from table GROUP BY className.
*/
public void testTopRows(SyncClient client) {
SearchRequest searchRequest = SearchRequest.newBuilder()
.indexName("indexName")
.tableName("tableName")
.searchQuery(
SearchQuery.newBuilder()
.query(QueryBuilders.matchAll())
.limit(0)
.size(5)
.limit(1)
.sort(new Sort(Arrays.asList(new FieldSort("teacher", SortOrder.DESC)))) // Sort rows by teacher in descending order.
)
)
.build())
.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();
}
}``````

## Nesting

GroupBy supports nesting. You can perform sub-aggregation operations by using GroupBy.

You can use nesting to perform sub-aggregation operations in a group. For example, you can perform nesting aggregation operations up to two levels.
• GroupBy + SubGroupBy: Items are grouped by province and by city to obtain data for each city in each province.
• GroupBy + SubAggregation: Items are grouped by province and the maximum value of a metric is calculated to obtain the maximum value of a metric for each province.
Note To ensure high performance of queries and perform GroupBy operations, you can specify only a small number of levels for nesting. For more information, see Search index limits.
Examples
``````/**
* Perform nesting-based aggregation.
* Two aggregations and one GroupByField attribute are specified in the outermost level. Two sub-aggregations and one GroupByRange attribute are specified in GroupByField.
*/
public void subGroupBy(SyncClient client) {
// Create a query statement.
SearchRequest searchRequest = SearchRequest.newBuilder()
.indexName("index_name")
.tableName("table_name")
.returnAllColumns(true)
.searchQuery(
SearchQuery.newBuilder()
.query(QueryBuilders.match("textField", "hello"))
.limit(10)
.groupByField("name3", "fieldName3")
.groupByRange("subName3", "fieldName6")
))
.build())
.build();
// Execute the query statement.
SearchResponse resp = client.search(searchRequest);
// Obtain the maximum and minimum values for the first level.
AggregationResults aggResults = resp.getAggregationResults();
System.out.println(aggResults.getAsMinAggregationResult("name1").getValue());
System.out.println(aggResults.getAsMaxAggregationResult("name2").getValue());

// Obtain the GroupByField results of the first level and the results of the aggregations that are nested in GroupByField.
GroupByFieldResult results = resp.getGroupByResults().getAsGroupByFieldResult("someName1");
for (GroupByFieldResultItem item : results.getGroupByFieldResultItems()) {
System.out.println("count:" + item.getRowCount());
System.out.println("key:" + item.getKey());

// Obtain the sub-aggregation results.
// Display the maximum value that is obtained from the results of the sub-aggregation operation.
System.out.println(item.getSubAggregationResults().getAsMaxAggregationResult("subName1"));
// Display the sum that is obtained from the results of the sub-aggregation operation.
System.out.println(item.getSubAggregationResults().getAsSumAggregationResult("subName2"));
// Display the GroupByRange values that are obtained from the results of the sub-aggregation operation.
GroupByRangeResult subResults = resp.getGroupByResults().getAsGroupByRangeResult("subName3");
for (GroupByRangeResultItem subItem : subResults.getGroupByRangeResultItems()) {
System.out.println("count:" + subItem.getRowCount());
System.out.println("key:" + subItem.getKey());
}
}
}``````

## Multiple aggregations

You can perform multiple aggregation operations.
Note If you perform multiple complex aggregation operations at the same time, a long period of time may be required.
• Example 1
``````public void multipleAggregation(SyncClient client) {
// Create a query statement.
SearchRequest searchRequest = SearchRequest.newBuilder()
.tableName("tableName")
.indexName("indexName")
.searchQuery(
SearchQuery.newBuilder()
.query(QueryBuilders.matchAll())
.limit(0)
.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());
}``````
• Example 2
``````public void multipleGroupBy(SyncClient client) {
// Create a query statement.
SearchRequest searchRequest = SearchRequest.newBuilder()
.tableName("tableName")
.indexName("indexName")
.searchQuery(
SearchQuery.newBuilder()
.query(QueryBuilders.matchAll())
.limit(0)
.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());
}
}``````