Share via

GROUP BY (kueri NoSQL)

  • Artikel

BERLAKU UNTUK: NoSQL

Klausul GROUP BY membagi hasil kueri berdasarkan nilai dari satu atau beberapa properti yang ditentukan.

Sintaks

<group_by_clause> ::= GROUP BY <scalar_expression_list>

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

Argumen

Deskripsi
<scalar_expression_list> Menentukan ekspresi yang digunakan untuk mengelompokkan (atau membagi) hasil kueri.
<scalar_expression> Ekspresi skalar apa pun diizinkan, kecuali untuk subkueri skalar dan agregat skalar. Setiap ekspresi skalar harus berisi setidaknya satu referensi properti. Tidak ada batasan jumlah ekspresi individual atau kardinalitas setiap ekspresi.

Contoh

Untuk contoh di bagian ini, kumpulan referensi item ini digunakan. Setiap item menyertakan capabilities objek yang mungkin menyertakan softwareDevelopment properti dan 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"
  }
]

Dalam contoh pertama ini, GROUP BY klausul digunakan untuk membuat grup item menggunakan nilai properti tertentu.

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

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

Dalam contoh berikutnya ini, fungsi sistem agregat (COUNT) digunakan dengan pengelompokan untuk menyediakan jumlah total item per grup.

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
  }
]

Dalam contoh akhir ini, item dikelompokkan menggunakan beberapa properti.

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
  }
]

Keterangan

  • Saat kueri menggunakan klausa, SELECT klausul GROUP BY hanya dapat berisi subset properti dan fungsi sistem yang disertakan dalam GROUP BY klausa. Salah satu pengecualian adalah fungsi agregat, yang dapat muncul dalam SELECT klausul tanpa disertakan dalam GROUP BY klausa. Anda juga selalu dapat menyertakan nilai harfiah dalam SELECT klausa.
  • Klausa GROUP BY harus setelah SELECTklausul , FROM, dan WHERE dan sebelum OFFSET LIMIT klausul . Anda tidak dapat menggunakan GROUP BY dengan klausa ORDER BY .
  • Klausa GROUP BY tidak mengizinkan salah satu fitur, properti, atau fungsi berikut:
    • Properti alias atau fungsi sistem alias (aliasing masih diizinkan dalam SELECT klausa)
    • Subkueri
    • Fungsi sistem agregat (fungsi-fungsi ini hanya diizinkan dalam SELECT klausa)
  • Kueri dengan fungsi sistem agregat dan subkueri dengan GROUP BY tidak didukung.
  • Kueri lintas partisi GROUP BY dapat memiliki maksimum 21 fungsi sistem agregat.

Konten terkait