分享方式：

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": "c-sharp"
  },
  {
    "developmentLang": "javascript"
  },
  {
    "developmentLang": "python"
  }
]

在下一個範例中，匯總系統函式會COUNT與群組搭配使用，以提供每個群組的項目總數。

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

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

在此最後一個範例中，專案會使用多個屬性分組。

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句必須在 、 FROM和 WHERE 子句之後SELECT，以及 子句之前OFFSET LIMIT。 您無法搭配 ORDER BY 子句使用GROUP BY。
  • 子 GROUP BY 句不允許下列任何功能、屬性或函式：
    • 別名屬性或別名系統函式（子句中 SELECT 仍允許別名）
    • 子查詢 (部分機器翻譯)
    • 匯總系統函式（這些函式只能在 子句中 SELECT 允許）
  • 不支援具有匯總系統函式和 子查詢的 GROUP BY 查詢。
  • 跨分割區 GROUP BY 查詢最多 可以有21 個匯總系統函數。

相關內容