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.

语法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)

不支持使用带有 GROUP BY 的聚合系统函数和子查询的查询。Queries with an aggregate system function and a subquery with GROUP BY are not supported. 例如,不支持以下查询:For example, the following query is not supported:

SELECT COUNT(UniqueLastNames)
FROM (
SELECT AVG(f.age)
FROM f
GROUP BY f.lastName
) AS UniqueLastNames

示例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):

[
    {
        "foodGroupCount": 183,
        "foodGroup": "Cereal Grains and Pasta"
    },
    {
        "foodGroupCount": 133,
        "foodGroup": "Nut and Seed Products"
    },
    {
        "foodGroupCount": 113,
        "foodGroup": "Meals, Entrees, and Side Dishes"
    },
    {
        "foodGroupCount": 64,
        "foodGroup": "Spices and Herbs"
    }
]

此查询有两个表达式,用于拆分结果: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:

[
    {
        "foodGroupCount": 183,
        "foodGroup": "Cereal Grains and Pasta",
        "version": 1
    },
    {
        "foodGroupCount": 133,
        "foodGroup": "Nut and Seed Products",
        "version": 1
    },
    {
        "foodGroupCount": 113,
        "foodGroup": "Meals, Entrees, and Side Dishes",
        "version": 1
    },
    {
        "foodGroupCount": 64,
        "foodGroup": "Spices and Herbs",
        "version": 1
    }
]

此查询在 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": 183,
        "upperFoodGroup": "CEREAL GRAINS AND PASTA"
    },
    {
        "foodGroupCount": 133,
        "upperFoodGroup": "NUT AND SEED PRODUCTS"
    },
    {
        "foodGroupCount": 113,
        "upperFoodGroup": "MEALS, ENTREES, AND SIDE DISHES"
    },
    {
        "foodGroupCount": 64,
        "upperFoodGroup": "SPICES AND HERBS"
    }
]

此查询在项属性表达式中使用关键字和系统函数: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:

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

后续步骤Next steps