通过

GROUP BY (NoSQL 查询)

  • 项目

适用范围： NoSQL

GROUP BY 子句按照一个或多个指定属性的值来拆分查询的结果。

语法

<group_by_clause> ::= GROUP BY <scalar_expression_list>

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

参数

说明
<scalar_expression_list> 指定用于对查询结果分组（或拆分）的表达式。
<scalar_expression> 任何标量表达式都是允许的，但标量子查询和标量聚合除外。 每个标量表达式必须包含至少一个属性引用。 单个表达式的数目或每个表达式的多重性没有限制。

示例

本节中的示例使用了此参考项集。 每个项都包含一个 capabilities 对象，该对象可能包含 softwareDevelopment 和 mediaTrained 属性。

[
  {
    "name": "Jordan Mitchell",
    "capabilities": {
      "softwareDevelopment": "python",
      "mediaTrained": true
    },
    "team": "Cloud software engineering"
  },
  {
    "name": "Mikaela Lee",
    "capabilities": {
      "softwareDevelopment": "javascript",
      "mediaTrained": false
    },
    "team": "Cloud software engineering"
  },
  {
    "name": "Graham Barnes",
    "capabilities": {
      "softwareDevelopment": "c-sharp",
      "mediaTrained": true
    },
    "team": "Cloud software engineering"
  },
  {
    "name": "Hayden Cook",
    "capabilities": {
      "softwareDevelopment": "javascript",
      "mediaTrained": true
    },
    "team": "Cloud software engineering"
  },
  {
    "name": "Morgan Connors",
    "capabilities": {
      "mediaTrained": true
    },
    "team": "Cloud software engineering"
  },
  {
    "name": "Devon Torres",
    "capabilities": {
      "softwareDevelopment": "python",
      "mediaTrained": false
    },
    "team": "Cloud software engineering"
  },
  {
    "name": "Sam Centrell",
    "capabilities": {
      "softwareDevelopment": "javascript",
      "mediaTrained": true
    },
    "team": "Cloud software engineering"
  }
]

在第一个示例中， GROUP BY 子句用于使用指定属性的值创建项组。

SELECT 
    e.capabilities.softwareDevelopment AS developmentLang
FROM
    employees e
GROUP BY
    e.capabilities.softwareDevelopment

[
  {
    "developmentLang": "python"
  },
  {
    "developmentLang": "javascript"
  },
  {
    "developmentLang": "c-sharp"
  },
  {}
]

在下一个示例中，(COUNT) 的聚合系统函数与分组一起使用，以提供每个组的项总数。

SELECT 
    COUNT(1) AS trainedEmployees, 
    e.capabilities.softwareDevelopment AS developmentLang
FROM
    employees e
GROUP BY
    e.capabilities.softwareDevelopment

[
  {
    "trainedEmployees": 2,
    "developmentLang": "python"
  },
  {
    "trainedEmployees": 3,
    "developmentLang": "javascript"
  },
  {
    "trainedEmployees": 1,
    "developmentLang": "c-sharp"
  },
  {
    "trainedEmployees": 1
  }
]

在此最后一个示例中，使用多个属性对项进行分组。

SELECT 
    COUNT(1) AS employeesWithThisTraining, 
    e.capabilities.softwareDevelopment AS developmentLang,
    e.capabilities.mediaTrained AS mediaReady
FROM
    employees e
GROUP BY
    e.capabilities.softwareDevelopment,
    e.capabilities.mediaTrained

[
  {
    "employeesWithThisTraining": 1,
    "developmentLang": "python",
    "mediaReady": true
  },
  {
    "employeesWithThisTraining": 1,
    "developmentLang": "javascript",
    "mediaReady": false
  },
  {
    "employeesWithThisTraining": 1,
    "developmentLang": "c-sharp",
    "mediaReady": true
  },
  {
    "employeesWithThisTraining": 2,
    "developmentLang": "javascript",
    "mediaReady": true
  },
  {
    "employeesWithThisTraining": 1,
    "mediaReady": true
  },
  {
    "employeesWithThisTraining": 1,
    "developmentLang": "python",
    "mediaReady": false
  }
]

注解

  • 当查询使用 GROUP BY 子句时，SELECT 子句只能包含包括在 GROUP BY 子句中的属性和系统函数的一部分。 一个例外是聚合函数，此类函数可以出现在 SELECT 子句中，但不需包含在 GROUP BY 子句中。 也可始终在 SELECT 子句中包含文本值。
  • GROUP BY 字句必须在 SELECT、FROM 和 WHERE 字句之后，OFFSET LIMIT 字句之前。 不能将 GROUP BY 与 ORDER BY 子句一起使用。
  • GROUP BY 子句不支持任何以下功能、属性或函数：
    • 别名属性或别名系统函数（在 SELECT 子句中，别名仍然是允许的）
    • 子查询
    • 聚合系统函数（仅在 SELECT 子句中允许这些函数）
  • 不支持查询包含使用 GROUP BY 的聚合系统函数和子查询。
  • 跨分区 GROUP BY 查询最多可以有 21 个聚合系统函数。

相关内容