Azure Cosmos DB 中的聚合函数Aggregate functions in Azure Cosmos DB

聚合函数对 SELECT 子句中的一组值执行计算,并返回单个值。Aggregate functions perform a calculation on a set of values in the SELECT clause and return a single value. 例如,以下查询返回 Families 容器中的项计数。For example, the following query returns the count of items within the Families container:

示例Examples

    SELECT COUNT(1)
    FROM Families f

结果有:The results are:

    [{
        "$1": 2
    }]

也可以使用 VALUE 关键字来仅返回聚合的标量值。You can also return only the scalar value of the aggregate by using the VALUE keyword. 例如,以下查询将值的计数作为单个值返回:For example, the following query returns the count of values as a single number:

    SELECT VALUE COUNT(1)
    FROM Families f

结果有:The results are:

    [ 2 ]

还可以将聚合与筛选器结合使用。You can also combine aggregations with filters. 例如,以下查询返回包含 WA 州地址的项计数。For example, the following query returns the count of items with the address state of WA.

    SELECT VALUE COUNT(1)
    FROM Families f
    WHERE f.address.state = "WA"

结果有:The results are:

    [ 1 ]

聚合函数的类型Types of aggregate functions

SQL API 支持以下聚合函数。The SQL API supports the following aggregate functions. SUMAVG 对数值执行运算,COUNTMINMAX 可以处理数字、字符串、布尔值和 null。SUM and AVG operate on numeric values, and COUNT, MIN, and MAX work on numbers, strings, Booleans, and nulls.

函数Function 说明Description
COUNTCOUNT 在表达式中返回项的数目。Returns the number of items in the expression.
SUMSUM 在表达式中返回所有值的总和。Returns the sum of all the values in the expression.
最小值MIN 在表达式中返回最小值。Returns the minimum value in the expression.
MAXMAX 在表达式中返回最大值。Returns the maximum value in the expression.
平均值AVG 在表达式中返回多个值的平均值。Returns the average of the values in the expression.

还可以基于数组迭代的结果进行聚合。You can also aggregate over the results of an array iteration.

备注

在 Azure 门户的数据资源管理器中,聚合查询可以仅基于一个查询页面聚合部分结果。In the Azure portal's Data Explorer, aggregation queries may aggregate partial results over only one query page. SDK 跨所有页面生成单个累计值。The SDK produces a single cumulative value across all pages. 若要使用代码执行聚合查询,需要 .NET SDK 1.12.0、.NET Core SDK 1.1.0,或者 Java SDK 1.9.5 或更高版本。To perform aggregation queries using code, you need .NET SDK 1.12.0, .NET Core SDK 1.1.0, or Java SDK 1.9.5 or above.

备注Remarks

这些聚合系统函数将从范围索引中获益。These aggregate system functions will benefit from a range index. 如果希望对某个属性执行 COUNTSUMMINMAXAVG 运算,则应在索引策略中包含相关路径If you expect to do a COUNT, SUM, MIN, MAX, or AVG on a property, you should include the relevant path in the indexing policy.

后续步骤Next steps