聚合查询

聚合查询会处理多个已编入索引的实体中的数据，然后返回单个摘要值。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 秒时限内完成，作为一种可能的解决方法，您可以使用光标来合并多个汇总。

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

• 如果向查询添加 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 的最佳实践。