聚合查询

聚合查询会处理来自多个已编入索引实体的数据，以返回单个摘要值。Datastore 模式的 Firestore 支持以下聚合查询：

• count()
• sum()
• avg()

聚合查询简化了应用代码，并且比提取每个实体进行处理的费用更低。请参阅本页面，了解如何使用聚合查询。

count() 汇总

使用 count() 聚合可返回与给定查询匹配的已编入索引的实体的总数。例如，此 count() 聚合会返回某个种类中的实体总数。

Java

import static com.google.cloud.datastore.aggregation.Aggregation.count;

import com.google.cloud.datastore.AggregationQuery;
import com.google.cloud.datastore.AggregationResult;
import com.google.cloud.datastore.Datastore;
import com.google.cloud.datastore.DatastoreOptions;
import com.google.cloud.datastore.Entity;
import com.google.cloud.datastore.EntityQuery;
import com.google.cloud.datastore.Key;
import com.google.cloud.datastore.Query;
import com.google.common.collect.Iterables;

public class CountAggregationOnKind {
  // Instantiates a client.
  private static final Datastore datastore = DatastoreOptions.getDefaultInstance().getService();

  // The kind for the new entity.
  private static final String kind = "Task";

  // Setting up Tasks in database
  private static void setUpTasks() {
    Key task1Key = datastore.newKeyFactory().setKind(kind).newKey("task1");
    Key task2Key = datastore.newKeyFactory().setKind(kind).newKey("task2");
    Key task3Key = datastore.newKeyFactory().setKind(kind).newKey("task3");

    // Save all the tasks.
    datastore.put(
        Entity.newBuilder(task1Key).set("done", true).build(),
        Entity.newBuilder(task2Key).set("done", false).build(),
        Entity.newBuilder(task3Key).set("done", true).build());
  }

  // Accessing aggregation result by the generated alias.
  private static void usageWithGeneratedAlias() {
    EntityQuery selectAllTasks = Query.newEntityQueryBuilder().setKind(kind).build();
    // Creating an aggregation query to get the count of all tasks.
    AggregationQuery allTasksCountQuery =
        Query.newAggregationQueryBuilder().over(selectAllTasks).addAggregation(count()).build();
    // Executing aggregation query.
    AggregationResult aggregationResult =
        Iterables.getOnlyElement(datastore.runAggregation(allTasksCountQuery));

    System.out.printf(
        "Total tasks (accessible from default alias) is %d",
        aggregationResult.get("property_1")); // 3
  }

  // Accessing aggregation result by the provided custom alias.
  private static void usageWithCustomAlias() {
    EntityQuery selectAllTasks = Query.newEntityQueryBuilder().setKind(kind).build();
    // Creating an aggregation query to get the count of all tasks.
    AggregationQuery allTasksCountQuery =
        Query.newAggregationQueryBuilder()
            .over(selectAllTasks)
            // passing 'total_count' as alias in the aggregation query.
            .addAggregation(count().as("total_count"))
            .build();
    // Executing aggregation query.
    AggregationResult aggregationResult =
        Iterables.getOnlyElement(datastore.runAggregation(allTasksCountQuery));

    System.out.printf("Total tasks count is %d", aggregationResult.get("total_count")); // 3
  }

  public static void invoke() {
    setUpTasks();
    usageWithGeneratedAlias();
    usageWithCustomAlias();
  }
}

Python

task1 = datastore.Entity(client.key("Task", "task1"))
task2 = datastore.Entity(client.key("Task", "task2"))

tasks = [task1, task2]
client.put_multi(tasks)
all_tasks_query = client.query(kind="Task")
all_tasks_count_query = client.aggregation_query(all_tasks_query).count()
query_result = all_tasks_count_query.fetch()
for aggregation_results in query_result:
    for aggregation in aggregation_results:
        print(f"Total tasks (accessible from default alias) is {aggregation.value}")

Go

aggregationCountQuery := datastore.NewQuery("Task").
  NewAggregationQuery().
  WithCount("total_tasks")

countResults, err := client.RunAggregationQuery(ctx, aggregationCountQuery)

count := countResults["total_tasks"]
countValue := count.(*datastorepb.Value)
fmt.Printf("Number of results from query: %d\n", countValue.GetIntegerValue())

GQL

AGGREGATE COUNT(*) AS total OVER ( SELECT * AS total FROM tasks )

GQL 支持简化形式的 count() 查询：

SELECT COUNT(*) AS total FROM tasks

此示例使用了可选的别名 total。

简化形式仅支持 FROM 和 WHERE 子句。如需了解详情，请参阅 GQL 参考文档。

count() 聚合会考虑查询的所有过滤条件以及所有 limit 子句。例如，以下聚合会返回与指定过滤条件匹配的实体数量的计数。

Java

import static com.google.cloud.datastore.aggregation.Aggregation.count;

import com.google.cloud.datastore.AggregationQuery;
import com.google.cloud.datastore.AggregationResult;
import com.google.cloud.datastore.Datastore;
import com.google.cloud.datastore.DatastoreOptions;
import com.google.cloud.datastore.Entity;
import com.google.cloud.datastore.EntityQuery;
import com.google.cloud.datastore.Key;
import com.google.cloud.datastore.Query;
import com.google.cloud.datastore.StructuredQuery.PropertyFilter;
import com.google.common.collect.Iterables;

public class CountAggregationWithPropertyFilter {

  public static void invoke() {
    // Instantiates a client.
    Datastore datastore = DatastoreOptions.getDefaultInstance().getService();

    // The kind for the new entity.
    String kind = "Task";

    Key task1Key = datastore.newKeyFactory().setKind(kind).newKey("task1");
    Key task2Key = datastore.newKeyFactory().setKind(kind).newKey("task2");
    Key task3Key = datastore.newKeyFactory().setKind(kind).newKey("task3");

    // Save all the tasks.
    datastore.put(
        Entity.newBuilder(task1Key).set("done", true).build(),
        Entity.newBuilder(task2Key).set("done", false).build(),
        Entity.newBuilder(task3Key).set("done", true).build());

    EntityQuery completedTasks =
        Query.newEntityQueryBuilder()
            .setKind(kind)
            .setFilter(PropertyFilter.eq("done", true))
            .build();
    EntityQuery remainingTasks =
        Query.newEntityQueryBuilder()
            .setKind(kind)
            .setFilter(PropertyFilter.eq("done", false))
            .build();
    // Creating an aggregation query to get the count of all completed tasks.
    AggregationQuery completedTasksCountQuery =
        Query.newAggregationQueryBuilder()
            .over(completedTasks)
            .addAggregation(count().as("total_completed_count"))
            .build();
    // Creating an aggregation query to get the count of all remaining tasks.
    AggregationQuery remainingTasksCountQuery =
        Query.newAggregationQueryBuilder()
            .over(remainingTasks)
            .addAggregation(count().as("total_remaining_count"))
            .build();

    // Executing aggregation query.
    AggregationResult completedTasksCountQueryResult =
        Iterables.getOnlyElement(datastore.runAggregation(completedTasksCountQuery));
    AggregationResult remainingTasksCountQueryResult =
        Iterables.getOnlyElement(datastore.runAggregation(remainingTasksCountQuery));

    System.out.printf(
        "Total completed tasks count is %d",
        completedTasksCountQueryResult.get("total_completed_count")); // 2
    System.out.printf(
        "Total remaining tasks count is %d",
        remainingTasksCountQueryResult.get("total_remaining_count")); // 1
  }
}

Python

task1 = datastore.Entity(client.key("Task", "task1"))
task2 = datastore.Entity(client.key("Task", "task2"))
task3 = datastore.Entity(client.key("Task", "task3"))

task1["done"] = True
task2["done"] = False
task3["done"] = True

tasks = [task1, task2, task3]
client.put_multi(tasks)
completed_tasks = client.query(kind="Task").add_filter("done", "=", True)
remaining_tasks = client.query(kind="Task").add_filter("done", "=", False)

completed_tasks_query = client.aggregation_query(query=completed_tasks).count(
    alias="total_completed_count"
)
remaining_tasks_query = client.aggregation_query(query=remaining_tasks).count(
    alias="total_remaining_count"
)

completed_query_result = completed_tasks_query.fetch()
for aggregation_results in completed_query_result:
    for aggregation_result in aggregation_results:
        if aggregation_result.alias == "total_completed_count":
            print(f"Total completed tasks count is {aggregation_result.value}")

remaining_query_result = remaining_tasks_query.fetch()
for aggregation_results in remaining_query_result:
    for aggregation_result in aggregation_results:
        if aggregation_result.alias == "total_remaining_count":
            print(f"Total remaining tasks count is {aggregation_result.value}")

Go

aggregationCountQuery := datastore.NewQuery("Task").
  FilterField("done", "=", true).
  NewAggregationQuery().
  WithCount("total_tasks_done")

countResults, err := client.RunAggregationQuery(ctx, aggregationCountQuery)

count := countResults["total_tasks_done"]
countValue := count.(*datastorepb.Value)
fmt.Printf("Number of results from query: %d\n", countValue.GetIntegerValue())

GQL

AGGREGATE COUNT(*) OVER ( SELECT * FROM tasks WHERE is_done = false AND tag = 'house')

GQL 支持简化形式的 count() 查询：

SELECT COUNT(*) AS total
FROM tasks
WHERE is_done = false AND tag = 'house'

此示例使用了可选的别名 total。

简化形式仅支持 FROM 和 WHERE 子句。如需了解详情，请参阅 GQL 参考文档。

以下示例展示了如何将计数达到特定值。例如，您可以使用此参数来停止在达到某个数字时计数，并通知用户超出了该数字。

Java

import static com.google.cloud.datastore.aggregation.Aggregation.count;

import com.google.cloud.datastore.AggregationQuery;
import com.google.cloud.datastore.AggregationResult;
import com.google.cloud.datastore.Datastore;
import com.google.cloud.datastore.DatastoreOptions;
import com.google.cloud.datastore.Entity;
import com.google.cloud.datastore.EntityQuery;
import com.google.cloud.datastore.Key;
import com.google.cloud.datastore.Query;
import com.google.common.collect.Iterables;

public class CountAggregationWithLimit {
  public static void invoke() {
    // Instantiates a client.
    Datastore datastore = DatastoreOptions.getDefaultInstance().getService();

    // The kind for the new entity.
    String kind = "Task";

    Key task1Key = datastore.newKeyFactory().setKind(kind).newKey("task1");
    Key task2Key = datastore.newKeyFactory().setKind(kind).newKey("task2");
    Key task3Key = datastore.newKeyFactory().setKind(kind).newKey("task3");

    // Save all the tasks.
    datastore.put(
        Entity.newBuilder(task1Key).set("done", true).build(),
        Entity.newBuilder(task2Key).set("done", false).build(),
        Entity.newBuilder(task3Key).set("done", true).build());

    EntityQuery selectAllTasks = Query.newEntityQueryBuilder().setKind(kind).setLimit(2).build();
    // Creating an aggregation query to get the count of all tasks.
    AggregationQuery allTasksCountQuery =
        Query.newAggregationQueryBuilder()
            .over(selectAllTasks)
            .addAggregation(count().as("at_least"))
            .build();
    // Executing aggregation query.
    AggregationResult limitQueryResult =
        Iterables.getOnlyElement(datastore.runAggregation(allTasksCountQuery));

    System.out.printf("We have at least %d tasks", limitQueryResult.get("at_least")); // 2
  }
}

Python

task1 = datastore.Entity(client.key("Task", "task1"))
task2 = datastore.Entity(client.key("Task", "task2"))
task3 = datastore.Entity(client.key("Task", "task3"))

tasks = [task1, task2, task3]
client.put_multi(tasks)
all_tasks_query = client.query(kind="Task")
all_tasks_count_query = client.aggregation_query(all_tasks_query).count()
query_result = all_tasks_count_query.fetch(limit=2)
for aggregation_results in query_result:
    for aggregation in aggregation_results:
        print(f"We have at least {aggregation.value} tasks")

Go

aggregationCountQuery := datastore.NewQuery("Task").
  Limit(2).
  NewAggregationQuery().
  WithCount("at_least")

countResults, err := client.RunAggregationQuery(ctx, aggregationCountQuery)

count := countResults["at_least"]
countValue := count.(*datastorepb.Value)
fmt.Printf("We have at least %d tasks\n", countValue.GetIntegerValue())

GQL

AGGREGATE COUNT_UP_TO(1000) OVER ( SELECT * FROM tasks WHERE is_done = false)

GQL 支持简化形式的 count_up_to() 查询：

SELECT COUNT_UP_TO(1000) AS total
FROM tasks
WHERE is_done = false AND tag = 'house'

此示例使用了可选的别名 total。

简化形式仅支持 FROM 和 WHERE 子句。如需了解详情，请参阅 GQL 参考文档。

sum() 汇总

使用 sum() 聚合可返回与指定查询匹配的数值的总和。例如，以下 sum() 聚合会返回给定种类实体中给定属性的数值的总和：

Java

import static com.google.cloud.datastore.aggregation.Aggregation.sum;

import com.google.cloud.datastore.AggregationQuery;
import com.google.cloud.datastore.AggregationResult;
import com.google.cloud.datastore.Datastore;
import com.google.cloud.datastore.DatastoreOptions;
import com.google.cloud.datastore.Entity;
import com.google.cloud.datastore.EntityQuery;
import com.google.cloud.datastore.Key;
import com.google.cloud.datastore.Query;
import com.google.common.collect.Iterables;

public class SumAggregationOnKind {

  // Instantiates a client.
  private static final Datastore datastore = DatastoreOptions.getDefaultInstance().getService();

  // The kind for the new entity.
  private static final String kind = "Sales";

  // Setting up Sales in database
  private static void setUpSales() {
    Key sales1Key = datastore.newKeyFactory().setKind(kind).newKey("sales1");
    Key sales2Key = datastore.newKeyFactory().setKind(kind).newKey("sales2");
    Key sales3Key = datastore.newKeyFactory().setKind(kind).newKey("sales3");

    // Save all the sales.
    datastore.put(
        Entity.newBuilder(sales1Key).set("amount", 89).build(),
        Entity.newBuilder(sales2Key).set("amount", 95).build(),
        Entity.newBuilder(sales3Key).set("amount", 55).build());
  }

  // Accessing aggregation result by the provided custom alias.
  private static void usageWithCustomAlias() {
    EntityQuery selectAllSales = Query.newEntityQueryBuilder().setKind(kind).build();
    // Creating an aggregation query to get the sum of all sales.
    AggregationQuery sumOfSalesQuery =
        Query.newAggregationQueryBuilder()
            .over(selectAllSales)
            // passing 'total_sales_amount' as alias in the aggregation query.
            .addAggregation(sum("amount").as("total_sales_amount"))
            .build();
    // Executing aggregation query.
    AggregationResult aggregationResult =
        Iterables.getOnlyElement(datastore.runAggregation(sumOfSalesQuery));

    System.out.printf("Total sales is %d", aggregationResult.getLong("total_sales_amount")); // 239
  }

  public static void invoke() {
    setUpSales();
    usageWithCustomAlias();
  }
}

Python

# Set up sample entities
# Use incomplete key to auto-generate ID
task1 = datastore.Entity(client.key("Task"))
task2 = datastore.Entity(client.key("Task"))
task3 = datastore.Entity(client.key("Task"))

task1["hours"] = 5
task2["hours"] = 3
task3["hours"] = 1

tasks = [task1, task2, task3]
client.put_multi(tasks)

# Execute sum aggregation query
all_tasks_query = client.query(kind="Task")
all_tasks_sum_query = client.aggregation_query(all_tasks_query).sum("hours")
query_result = all_tasks_sum_query.fetch()
for aggregation_results in query_result:
    for aggregation in aggregation_results:
        print(f"Total sum of hours in tasks is {aggregation.value}")

Go

aggregationSumQuery := datastore.NewQuery("Task").
  NewAggregationQuery().
  WithSum("hours", "total_hours")
sumResults, err := client.RunAggregationQuery(ctx, aggregationSumQuery)

sum := sumResults["total_hours"]
sumValue := sum.(*datastorepb.Value)
fmt.Printf("Sum of results from query: %d\n", sumValue.GetIntegerValue())

GQL

AGGREGATE
  SUM(hours) AS total_hours
OVER (
  SELECT *
  FROM tasks
)

GQL 支持简化形式的 sum() 查询：

SELECT SUM(hours) AS total_hours FROM tasks

此示例使用了可选的别名 total_hours。

简化形式仅支持 FROM 和 WHERE 子句。如需了解详情，请参阅 GQL 参考文档。

sum() 聚合会考虑查询的所有过滤条件以及所有 limit 子句。例如，以下聚合会返回与指定过滤条件匹配的实体中的指定属性及其数值的总和。

Java

import static com.google.cloud.datastore.aggregation.Aggregation.sum;

import com.google.cloud.datastore.AggregationQuery;
import com.google.cloud.datastore.AggregationResult;
import com.google.cloud.datastore.Datastore;
import com.google.cloud.datastore.DatastoreOptions;
import com.google.cloud.datastore.Entity;
import com.google.cloud.datastore.EntityQuery;
import com.google.cloud.datastore.Key;
import com.google.cloud.datastore.Query;
import com.google.cloud.datastore.StructuredQuery.PropertyFilter;
import com.google.common.collect.Iterables;

public class SumAggregationWithPropertyFilter {

  public static void invoke() {
    // Instantiates a client.
    Datastore datastore = DatastoreOptions.getDefaultInstance().getService();

    // The kind for the new entity.
    String kind = "Sales";

    Key sales1Key = datastore.newKeyFactory().setKind(kind).newKey("sales1");
    Key sales2Key = datastore.newKeyFactory().setKind(kind).newKey("sales2");
    Key sales3Key = datastore.newKeyFactory().setKind(kind).newKey("sales3");

    // Save all the tasks.
    datastore.put(
        Entity.newBuilder(sales1Key).set("amount", 89).set("customerId", 1).build(),
        Entity.newBuilder(sales2Key).set("amount", 95).set("customerId", 1).build(),
        Entity.newBuilder(sales3Key).set("amount", 55).set("customerId", 2).build());

    EntityQuery customer1Sales =
        Query.newEntityQueryBuilder()
            .setKind(kind)
            .setFilter(PropertyFilter.eq("customerId", 1))
            .build();

    // Creating an aggregation query to get the sum of all sales for customerId 1.
    AggregationQuery customer1SalesSum =
        Query.newAggregationQueryBuilder()
            .over(customer1Sales)
            .addAggregation(sum("amount").as("total_sales"))
            .build();

    // Executing aggregation query.
    AggregationResult customer1SalesSumQueryResult =
        Iterables.getOnlyElement(datastore.runAggregation(customer1SalesSum));

    System.out.printf(
        "Customer 1 sales sum is %d", customer1SalesSumQueryResult.getLong("total_sales")); // 184
  }
}

Python

# Set up sample entities
# Use incomplete key to auto-generate ID
task1 = datastore.Entity(client.key("Task"))
task2 = datastore.Entity(client.key("Task"))
task3 = datastore.Entity(client.key("Task"))

task1["hours"] = 5
task2["hours"] = 3
task3["hours"] = 1

task1["done"] = True
task2["done"] = True
task3["done"] = False

tasks = [task1, task2, task3]
client.put_multi(tasks)

# Execute sum aggregation query with filters
completed_tasks = client.query(kind="Task").add_filter("done", "=", True)
completed_tasks_query = client.aggregation_query(query=completed_tasks).sum(
    property_ref="hours",
    alias="total_completed_sum_hours"
)

completed_query_result = completed_tasks_query.fetch()
for aggregation_results in completed_query_result:
    for aggregation_result in aggregation_results:
        if aggregation_result.alias == "total_completed_sum_hours":
            print(f"Total sum of hours in completed tasks is {aggregation_result.value}")

此查询需要如下索引：

- kind: Task
  properties:
  - name: done
  - name: hours

Go

aggregationSumQuery := datastore.NewQuery("Task").
  FilterField("done", "=", false).
  FilterField("tag", "=", "house").
  NewAggregationQuery().
  WithSum("hours", "total_hours")
sumResults, err := client.RunAggregationQuery(ctx, aggregationSumQuery)

sum := sumResults["total_hours"]
sumValue := sum.(*datastorepb.Value)
fmt.Printf("Sum of results from query: %d\n", sumValue.GetIntegerValue())

GQL

AGGREGATE
  SUM(hours) AS total_hours
OVER (
  SELECT *
  FROM tasks
  WHERE is_done = false AND tag = 'house'
)

GQL 支持简化形式的 sum() 查询：

SELECT
  SUM(hours) AS total_hours
FROM tasks
WHERE is_done = false AND tag = 'house'

此示例使用了可选的别名 total_hours。

简化形式仅支持 FROM 和 WHERE 子句。如需了解详情，请参阅 GQL 参考文档。

avg() 汇总

使用 avg() 聚合返回与给定查询匹配的数值的平均值。例如，以下 avg() 聚合会从与查询匹配的实体的数值属性值返回指定属性的算术平均值：

Java

import static com.google.cloud.datastore.aggregation.Aggregation.avg;

import com.google.cloud.datastore.AggregationQuery;
import com.google.cloud.datastore.AggregationResult;
import com.google.cloud.datastore.Datastore;
import com.google.cloud.datastore.DatastoreOptions;
import com.google.cloud.datastore.Entity;
import com.google.cloud.datastore.EntityQuery;
import com.google.cloud.datastore.Key;
import com.google.cloud.datastore.Query;
import com.google.common.collect.Iterables;

public class AvgAggregationOnKind {

  // Instantiates a client.
  private static final Datastore datastore = DatastoreOptions.getDefaultInstance().getService();

  // The kind for the new entity.
  private static final String kind = "Sales";

  // Setting up Sales in database
  private static void setUpSales() {
    Key sales1Key = datastore.newKeyFactory().setKind(kind).newKey("sales1");
    Key sales2Key = datastore.newKeyFactory().setKind(kind).newKey("sales2");
    Key sales3Key = datastore.newKeyFactory().setKind(kind).newKey("sales3");

    // Save all the sales.
    datastore.put(
        Entity.newBuilder(sales1Key).set("amount", 89).build(),
        Entity.newBuilder(sales2Key).set("amount", 95).build(),
        Entity.newBuilder(sales3Key).set("amount", 55).build());
  }

  // Accessing aggregation result by the provided custom alias.
  private static void usageWithCustomAlias() {
    EntityQuery selectAllSales = Query.newEntityQueryBuilder().setKind(kind).build();
    // Creating an aggregation query to get the avg of all sales.
    AggregationQuery avgOfSalesQuery =
        Query.newAggregationQueryBuilder()
            .over(selectAllSales)
            // passing 'avg_sales_amount' as alias in the aggregation query.
            .addAggregation(avg("amount").as("avg_sales_amount"))
            .build();
    // Executing aggregation query.
    AggregationResult aggregationResult =
        Iterables.getOnlyElement(datastore.runAggregation(avgOfSalesQuery));

    System.out.printf(
        "Average sales is %.8f", aggregationResult.getDouble("avg_sales_amount")); // 79.66666667
  }

  public static void invoke() {
    setUpSales();
    usageWithCustomAlias();
  }
}

Python

# Set up sample entities
# Use incomplete key to auto-generate ID
task1 = datastore.Entity(client.key("Task"))
task2 = datastore.Entity(client.key("Task"))
task3 = datastore.Entity(client.key("Task"))

task1["hours"] = 5
task2["hours"] = 3
task3["hours"] = 1

tasks = [task1, task2, task3]
client.put_multi(tasks)

# Execute average aggregation query
all_tasks_query = client.query(kind="Task")
all_tasks_avg_query = client.aggregation_query(all_tasks_query).avg("hours")
query_result = all_tasks_avg_query.fetch()
for aggregation_results in query_result:
    for aggregation in aggregation_results:
        print(f"Total average of hours in tasks is {aggregation.value}")

Go

aggregationAvgQuery := datastore.NewQuery("Task").
  NewAggregationQuery().
  WithAvg("hours", "avg_hours")
avgResults, err := client.RunAggregationQuery(ctx, aggregationAvgQuery)

avg := avgResults["avg_hours"]
avgValue := avg.(*datastorepb.Value)
fmt.Printf("average hours: %f\n", avgValue.GetDoubleValue())

GQL

AGGREGATE
  AVG(hours) as avg_hours
OVER (
  SELECT *
  FROM tasks
)

GQL 支持简化形式的 avg() 查询：

SELECT AVG(hours) as avg_hours

此示例使用了可选的别名 avg_hours。

简化形式仅支持 FROM 和 WHERE 子句。如需了解详情，请参阅 GQL 参考文档。

avg() 聚合会考虑查询的所有过滤条件以及所有 limit 子句。例如，以下聚合会从与查询过滤条件匹配的实体的数值属性值返回指定属性的算术平均值。

Java

import static com.google.cloud.datastore.aggregation.Aggregation.avg;

import com.google.cloud.datastore.AggregationQuery;
import com.google.cloud.datastore.AggregationResult;
import com.google.cloud.datastore.Datastore;
import com.google.cloud.datastore.DatastoreOptions;
import com.google.cloud.datastore.Entity;
import com.google.cloud.datastore.EntityQuery;
import com.google.cloud.datastore.Key;
import com.google.cloud.datastore.Query;
import com.google.cloud.datastore.StructuredQuery.PropertyFilter;
import com.google.common.collect.Iterables;

public class AvgAggregationWithPropertyFilter {

  public static void invoke() {
    // Instantiates a client.
    Datastore datastore = DatastoreOptions.getDefaultInstance().getService();

    // The kind for the new entity.
    String kind = "Sales";

    Key sales1Key = datastore.newKeyFactory().setKind(kind).newKey("sales1");
    Key sales2Key = datastore.newKeyFactory().setKind(kind).newKey("sales2");
    Key sales3Key = datastore.newKeyFactory().setKind(kind).newKey("sales3");

    // Save all the tasks.
    datastore.put(
        Entity.newBuilder(sales1Key).set("amount", 89).set("customerId", 1).build(),
        Entity.newBuilder(sales2Key).set("amount", 95).set("customerId", 1).build(),
        Entity.newBuilder(sales3Key).set("amount", 55).set("customerId", 2).build());

    EntityQuery customer1Sales =
        Query.newEntityQueryBuilder()
            .setKind(kind)
            .setFilter(PropertyFilter.eq("customerId", 1))
            .build();

    // Creating an aggregation query to get the avg of all sales for customerId 1.
    AggregationQuery customer1SalesAvg =
        Query.newAggregationQueryBuilder()
            .over(customer1Sales)
            .addAggregation(avg("amount").as("total_sales"))
            .build();

    // Executing aggregation query.
    AggregationResult customer1SalesAvgQueryResult =
        Iterables.getOnlyElement(datastore.runAggregation(customer1SalesAvg));

    System.out.printf(
        "Customer 1 sales avg is %d", customer1SalesAvgQueryResult.getLong("total_sales")); // 92
  }
}

Python

# Set up sample entities
# Use incomplete key to auto-generate ID
task1 = datastore.Entity(client.key("Task"))
task2 = datastore.Entity(client.key("Task"))
task3 = datastore.Entity(client.key("Task"))

task1["hours"] = 5
task2["hours"] = 3
task3["hours"] = 1

task1["done"] = True
task2["done"] = True
task3["done"] = False

tasks = [task1, task2, task3]
client.put_multi(tasks)

# Execute average aggregation query with filters
completed_tasks = client.query(kind="Task").add_filter("done", "=", True)
completed_tasks_query = client.aggregation_query(query=completed_tasks).avg(
    property_ref="hours",
    alias="total_completed_avg_hours"
)

completed_query_result = completed_tasks_query.fetch()
for aggregation_results in completed_query_result:
    for aggregation_result in aggregation_results:
        if aggregation_result.alias == "total_completed_avg_hours":
            print(f"Total average of hours in completed tasks is {aggregation_result.value}")

此查询需要如下索引：

- kind: Task
  properties:
  - name: done
  - name: hours

Go

aggregationAvgQuery := datastore.NewQuery("Task").
  FilterField("done", "=", false).
  FilterField("tag", "=", "house").
  NewAggregationQuery().
  WithAvg("hours", "avg_hours")
avgResults, err := client.RunAggregationQuery(ctx, aggregationAvgQuery)

avg := avgResults["avg_hours"]
avgValue := avg.(*datastorepb.Value)
fmt.Printf("average hours: %f\n", avgValue.GetDoubleValue())

GQL

AGGREGATE
  AVG(hours) as avg_hours
OVER (
  SELECT *
  FROM tasks
  WHERE is_done = false AND tag = 'house'
)

GQL 支持简化形式的 avg() 查询：

SELECT
  AVG(hours) as avg_hours
FROM tasks
WHERE is_done = false AND tag = 'house'

此示例使用了可选的别名 avg_hours。

简化形式仅支持 FROM 和 WHERE 子句。如需了解详情，请参阅 GQL 参考文档。

在查询中计算多项聚合

您可以将多项聚合合并到一个聚合流水线中。这样可以减少所需的索引读取次数。如果查询包含针对多个字段的聚合，该查询需要复合索引，并且每次聚合计算只会涵盖包含每次聚合使用的所有字段的实体。

以下示例在单个聚合查询中执行多次聚合：

Java

import static com.google.cloud.datastore.aggregation.Aggregation.avg;
import static com.google.cloud.datastore.aggregation.Aggregation.count;
import static com.google.cloud.datastore.aggregation.Aggregation.sum;

import com.google.cloud.datastore.AggregationQuery;
import com.google.cloud.datastore.AggregationResult;
import com.google.cloud.datastore.Datastore;
import com.google.cloud.datastore.DatastoreOptions;
import com.google.cloud.datastore.Entity;
import com.google.cloud.datastore.EntityQuery;
import com.google.cloud.datastore.Key;
import com.google.cloud.datastore.Query;
import com.google.common.collect.Iterables;

public class MultipleAggregationsInStructuredQuery {

  public static void invoke() {
    // Instantiates a client.
    Datastore datastore = DatastoreOptions.getDefaultInstance().getService();

    // The kind for the new entity.
    String kind = "Sales";

    Key sales1Key = datastore.newKeyFactory().setKind(kind).newKey("sales1");
    Key sales2Key = datastore.newKeyFactory().setKind(kind).newKey("sales2");
    Key sales3Key = datastore.newKeyFactory().setKind(kind).newKey("sales3");

    // Save all the sales.
    datastore.put(
        Entity.newBuilder(sales1Key).set("amount", 89).set("customerId", 1).build(),
        Entity.newBuilder(sales2Key).set("amount", 95).set("customerId", 1).build(),
        Entity.newBuilder(sales3Key).set("amount", 55).set("customerId", 2).build());

    EntityQuery baseQuery = Query.newEntityQueryBuilder().setKind(kind).build();

    // Creating an aggregation query with COUNT, SUM and AVG aggregations.
    AggregationQuery aggregationQuery =
        Query.newAggregationQueryBuilder()
            .over(baseQuery)
            .addAggregation(count().as("total_count"))
            .addAggregation(sum("amount").as("sales_sum"))
            .addAggregation(avg("amount").as("sales_avg"))
            .build();

    // Executing aggregation query.
    AggregationResult aggregationResult =
        Iterables.getOnlyElement(datastore.runAggregation(aggregationQuery));

    System.out.printf("Total sales count: %d", aggregationResult.getLong("total_count")); // 3
    System.out.printf("Sum of sales: %d", aggregationResult.getLong("sales_sum")); // 239
    System.out.printf(
        "Avg of sales: %.8f", aggregationResult.getDouble("sales_avg")); // 79.66666667
  }
}

Python

# Set up sample entities
# Use incomplete key to auto-generate ID
task1 = datastore.Entity(client.key("Task"))
task2 = datastore.Entity(client.key("Task"))
task3 = datastore.Entity(client.key("Task"))

task1["hours"] = 5
task2["hours"] = 3
task3["hours"] = 1

tasks = [task1, task2, task3]
client.put_multi(tasks)

# Execute query with multiple aggregations
all_tasks_query = client.query(kind="Task")
aggregation_query = client.aggregation_query(all_tasks_query)
# Add aggregations
aggregation_query.add_aggregations(
    [
        datastore.aggregation.CountAggregation(alias="count_aggregation"),
        datastore.aggregation.SumAggregation(
            property_ref="hours", alias="sum_aggregation"),
        datastore.aggregation.AvgAggregation(
            property_ref="hours", alias="avg_aggregation")
    ]
)

query_result = aggregation_query.fetch()
for aggregation_results in query_result:
    for aggregation in aggregation_results:
        print(f"{aggregation.alias} value is {aggregation.value}")

Go

aggregationQuery := datastore.NewQuery("Task").
  NewAggregationQuery().
  WithCount("total_tasks").
  WithSum("hours", "total_hours").
  WithAvg("hours", "avg_hours")
Results, err := client.RunAggregationQuery(ctx, aggregationQuery)

fmt.Printf("Number of results from query: %d\n", Results["total_tasks"].(*datastorepb.Value).GetIntegerValue())
fmt.Printf("Sum of results from query: %d\n", Results["total_hours"].(*datastorepb.Value).GetIntegerValue())
fmt.Printf("Avg of results from query: %f\n", Results["avg_hours"].(*datastorepb.Value).GetDoubleValue())

GQL

AGGREGATE
  SUM(hours) AS total_hours,
  COUNT(*) AS total_tasks
OVER (
  SELECT *
  FROM tasks
  WHERE is_done = false AND tag = 'house'
)

GQL 支持简化形式的聚合查询：

SELECT
  SUM(hours) AS total_hours,
  COUNT(*) AS total_tasks
FROM tasks
WHERE is_done = false AND tag = 'house'

此示例使用 total_hours 和 total_tasks 的可选别名。

简化形式仅支持 FROM 和 WHERE 子句。如需了解详情，请参阅 GQL 参考文档。

具有多个聚合的查询仅包含含有每个聚合中所有属性的实体。这可能会导致与单独执行每次汇总的不同结果。

行为和限制

在使用聚合查询时，请注意以下行为和限制：

• 您向汇总提供的查询必须满足查询的限制。

• 如果聚合查询无法在 60 秒内完成解析，则会返回 DEADLINE_EXCEEDED 错误。聚合性能取决于您的索引配置以及数据集的规模。

  注意：大多数查询会根据结果集的大小（而不是数据集的大小）进行扩缩。但是，聚合查询会根据数据集的大小以及扫描到的索引条目数量进行扩缩。

  如果操作无法在 60 秒的时限内完成，可能的权宜解决方法是使用cursors来合并多个汇总。

• 聚合查询会从索引条目中读取内容，并且在计算中仅包含编入索引的属性。

• 如果向查询添加 OrderBy 子句，则聚合操作只会对存在排序属性的实体进行聚合。

• 在 GQL 中，简化形式不支持 ORDER BY、LIMIT 或 OFFSET 子句。

• 在投影查询中，您只能从投影中的属性聚合数据。例如，在 GQL 查询 SELECT a, b FROM k WHERE c = 1 中，您只能聚合 a 或 b 中的数据。

• count() 聚合不会去除具有数组属性的重复实体。与查询匹配的每个数组值都会加 1。

• 对于 sum() 和 avg() 聚合，系统会忽略非数值。sum() 和 avg() 聚合仅考虑整数值、浮点数值和时间戳。对于 sum()、avg() 和投影，时间戳会转换为微秒整数值。

• 在单个查询中组合多项聚合时，请注意，sum() 和 avg() 会忽略非数字值，而 count() 会包含非数字值。

• 如果合并具有不同属性的汇总，则计算仅包括包含所有这些属性的实体。这可能会导致与分别执行每次汇总的不同结果。

价格

count()、sum() 和 avg() 聚合查询的价格取决于操作期间扫描到的索引条目数量。您需要为最多 1,000 个匹配的索引条目支付一次实体读取费用。后续索引条目与费用额外读取单元匹配。每个查询的最低费用为一个读取单元。如需了解价格信息，请参阅 Datastore 模式 Firestore 的价格。

如果您在单个查询中结合使用多个聚合，该查询会对每次聚合使用相同的索引，并对数据进行一次扫描。与单独执行每次聚合相比，这有助于减少计费的索引扫描和读取次数。但是，具有多个聚合的查询仅包含包含所有这些属性的实体。这可能会导致单独执行每次聚合时得到不同的结果。

后续步骤

• 了解查询。
• 了解 Datastore 模式 Firestore 的最佳实践。