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

相關內容