Azure Cosmos DB 中的 GROUP BY 子句GROUP BY clause in Azure Cosmos DB

GROUP BY 子句按照一个或多个指定属性的值来拆分查询的结果。The GROUP BY clause divides the query's results according to the values of one or more specified properties.

Note

Azure Cosmos DB 目前在 .NET SDK 3.3 或更高版本中支持 GROUP BY。Azure Cosmos DB currently supports GROUP BY in .NET SDK 3.3 or later. 对其他语言 SDK 的以及 Azure 门户的支持目前尚未发布,但已在计划内。Support for other language SDK's and the Azure Portal is not currently available but is planned.

语法Syntax

<group_by_clause> ::= GROUP BY <scalar_expression_list>

<scalar_expression_list> ::=
          <scalar_expression>
        | <scalar_expression_list>, <scalar_expression>

参数Arguments

  • <scalar_expression_list>

    指定将要用来拆分查询结果的表达式。Specifies the expressions that will be used to divide query results.

  • <scalar_expression>

    任何标量表达式都是允许的,但标量子查询和标量聚合除外。Any scalar expression is allowed except for scalar subqueries and scalar aggregates. 每个标量表达式必须包含至少一个属性引用。Each scalar expression must contain at least one property reference. 单个表达式的数目或每个表达式的多重性没有限制。There is no limit to the number of individual expressions or the cardinality of each expression.

备注Remarks

当查询使用 GROUP BY 子句时,SELECT 子句只能包含包括在 GROUP BY 子句中的属性和系统函数的一部分。When a query uses a GROUP BY clause, the SELECT clause can only contain the subset of properties and system functions included in the GROUP BY clause. 一个例外是聚合系统函数,此类函数可以出现在 SELECT 子句中,但不需包含在 GROUP BY 子句中。One exception is aggregate system functions, which can appear in the SELECT clause without being included in the GROUP BY clause. 也可始终在 SELECT 子句中包含文本值。You can also always include literal values in the SELECT clause.

GROUP BY 子句必须位于 SELECT、FROM 和 WHERE 子句后面,OFFSET LIMIT 子句前面。The GROUP BY clause must be after the SELECT, FROM, and WHERE clause and before the OFFSET LIMIT clause. 目前不能将 GROUP BY 和 ORDER BY 子句配合使用,但这已在计划内。You currently cannot use GROUP BY with an ORDER BY clause but this is planned.

GROUP BY 子句不允许下述任何项:The GROUP BY clause does not allow any of the following:

  • 别名属性或别名系统函数(在 SELECT 子句中,别名仍然是允许的)Aliasing properties or aliasing system functions (aliasing is still allowed within the SELECT clause)
  • 子查询Subqueries
  • 聚合系统函数(在 SELECT 子句中,仍然允许这些函数)Aggregate system functions (these are only allowed in the SELECT clause)

示例Examples

这些示例使用可以通过 Azure Cosmos DB 查询操场获取的营养数据集。These examples use the nutrition data set available through the Azure Cosmos DB Query Playground.

例如,下面这个查询返回每个 foodGroup 中项的总计数:For example, here's a query which returns the total count of items in each foodGroup:

SELECT TOP 4 COUNT(1) AS foodGroupCount, f.foodGroup
FROM Food f
GROUP BY f.foodGroup

部分结果如下(使用了 TOP 关键字来限制结果):Some results are (TOP keyword is used to limit results):

[{
  "foodGroup": "Fast Foods",
  "foodGroupCount": 371
},
{
  "foodGroup": "Finfish and Shellfish Products",
  "foodGroupCount": 267
},
{
  "foodGroup": "Meals, Entrees, and Side Dishes",
  "foodGroupCount": 113
},
{
  "foodGroup": "Sausages and Luncheon Meats",
  "foodGroupCount": 244
}]

此查询有两个表达式,用于拆分结果:This query has two expressions used to divide results:

SELECT TOP 4 COUNT(1) AS foodGroupCount, f.foodGroup, f.version
FROM Food f
GROUP BY f.foodGroup, f.version

部分结果如下:Some results are:

[{
  "version": 1,
  "foodGroup": "Nut and Seed Products",
  "foodGroupCount": 133
},
{
  "version": 1,
  "foodGroup": "Finfish and Shellfish Products",
  "foodGroupCount": 267
},
{
  "version": 1,
  "foodGroup": "Fast Foods",
  "foodGroupCount": 371
},
{
  "version": 1,
  "foodGroup": "Sausages and Luncheon Meats",
  "foodGroupCount": 244
}]

此查询在 GROUP BY 子句中有一个系统函数:This query has a system function in the GROUP BY clause:

SELECT TOP 4 COUNT(1) AS foodGroupCount, UPPER(f.foodGroup) AS upperFoodGroup
FROM Food f
GROUP BY UPPER(f.foodGroup)

部分结果如下:Some results are:

[{
  "foodGroupCount": 371,
  "upperFoodGroup": "FAST FOODS"
},
{
  "foodGroupCount": 267,
  "upperFoodGroup": "FINFISH AND SHELLFISH PRODUCTS"
},
{
  "foodGroupCount": 389,
  "upperFoodGroup": "LEGUMES AND LEGUME PRODUCTS"
},
{
  "foodGroupCount": 113,
  "upperFoodGroup": "MEALS, ENTREES, AND SIDE DISHES"
}]

此查询在项属性表达式中使用关键字和系统函数:This query uses both keywords and system functions in the item property expression:

SELECT COUNT(1) AS foodGroupCount, ARRAY_CONTAINS(f.tags, {name: 'orange'}) AS containsOrangeTag,  f.version BETWEEN 0 AND 2 AS correctVersion
FROM Food f
GROUP BY ARRAY_CONTAINS(f.tags, {name: 'orange'}), f.version BETWEEN 0 AND 2

其结果是:The results are:

[{
  "correctVersion": true,
  "containsOrangeTag": false,
  "foodGroupCount": 8608
},
{
  "correctVersion": true,
  "containsOrangeTag": true,
  "foodGroupCount": 10
}]

后续步骤Next steps