Semua yang Anda Perlu Tahu Mengenai Pernyataan SQL GROUP BY

Semua yang Anda Perlu Tahu Mengenai Pernyataan SQL GROUP BY

Sebilangan besar kekuatan pangkalan data hubungan berasal dari menyaring data dan menggabungkan jadual bersama. Inilah sebabnya mengapa kami mewakili hubungan tersebut. Tetapi sistem pangkalan data moden menyediakan teknik lain yang berharga: pengelompokan.





Pengelompokan membolehkan anda mengekstrak maklumat ringkasan dari pangkalan data. Ini membolehkan anda menggabungkan hasil untuk membuat data statistik yang berguna. Pengelompokan menyelamatkan anda dari menulis kod untuk kes biasa seperti rata-rata senarai angka. Dan ia dapat menghasilkan sistem yang lebih cekap.



Apa yang dilakukan oleh Fasal GROUP BY?

KUMPULAN OLEH, seperti namanya, kumpulan menghasilkan kumpulan yang lebih kecil. Hasilnya terdiri daripada satu baris untuk setiap nilai yang berbeza dari lajur yang dikelompokkan. Kami dapat menunjukkan penggunaannya dengan melihat beberapa contoh data dengan baris yang mempunyai beberapa nilai bersama.





adakah tv saya mempunyai HDMI 2.1

Berikut adalah pangkalan data yang sangat sederhana dengan dua jadual yang mewakili album rakaman. Anda boleh menyediakan pangkalan data sedemikian dengan menulis skema asas untuk sistem pangkalan data pilihan anda. The album jadual mempunyai sembilan baris dengan kunci utama ID lajur dan lajur untuk nama, artis, tahun pelepasan, dan penjualan:

+----+---------------------------+-----------+--------------+-------+
| id | name | artist_id | release_year | sales |
+----+---------------------------+-----------+--------------+-------+
| 1 | Abbey Road | 1 | 1969 | 14 |
| 2 | The Dark Side of the Moon | 2 | 1973 | 24 |
| 3 | Rumours | 3 | 1977 | 28 |
| 4 | Nevermind | 4 | 1991 | 17 |
| 5 | Animals | 2 | 1977 | 6 |
| 6 | Goodbye Yellow Brick Road | 5 | 1973 | 8 |
| 7 | 21 | 6 | 2011 | 25 |
| 8 | 25 | 6 | 2015 | 22 |
| 9 | Bat Out of Hell | 7 | 1977 | 28 |
+----+---------------------------+-----------+--------------+-------+

The seniman meja lebih sederhana. Ia mempunyai tujuh baris dengan lajur id dan nama:



+----+---------------+
| id | name |
+----+---------------+
| 1 | The Beatles |
| 2 | Pink Floyd |
| 3 | Fleetwood Mac |
| 4 | Nirvana |
| 5 | Elton John |
| 6 | Adele |
| 7 | Meat Loaf |
+----+---------------+

Anda boleh memahami pelbagai aspek GROUP BY hanya dengan satu set data ringkas seperti ini. Sudah tentu, satu set data kehidupan nyata akan mempunyai banyak, lebih banyak baris, tetapi prinsipnya tetap sama.

Pengelompokan mengikut Lajur Tunggal

Katakanlah kami ingin mengetahui berapa banyak album yang kami ada untuk setiap artis. Mulakan dengan yang biasa PILIH pertanyaan untuk mengambil lajur artis_id:



SELECT artist_id FROM albums

Ini mengembalikan kesembilan baris, seperti yang diharapkan:

+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 2 |
| 5 |
| 6 |
| 6 |
| 7 |
+-----------+

Untuk mengumpulkan hasil ini oleh artis, tambahkan frasa KUMPULAN OLEH artis_id :



SELECT artist_id FROM albums GROUP BY artist_id

Yang memberikan hasil berikut:

+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+-----------+

Terdapat tujuh baris dalam set hasil, dikurangkan dari jumlah sembilan di album meja. Setiap unik artis_id mempunyai satu baris. Akhirnya, untuk mendapatkan jumlah sebenar, tambah COUNT (*) ke lajur yang dipilih:

SELECT artist_id, COUNT(*)
FROM albums
GROUP BY artist_id
+-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 2 |
| 7 | 1 |
+-----------+----------+

Hasilnya mengumpulkan dua pasang baris untuk artis dengan id 2 dan 6 . Masing-masing mempunyai dua album dalam pangkalan data kami.

Berkaitan: The Essential SQL Memerintah Lembaran Menipu untuk Pemula

Cara Mengakses Data Berkumpulan Dengan Fungsi Gabungan

Anda mungkin telah menggunakan BANYAK berfungsi sebelum ini, terutamanya di COUNT (*) bentuk seperti yang dilihat di atas. Ia memperoleh jumlah hasil dalam satu set. Anda boleh menggunakannya untuk mendapatkan jumlah rekod dalam jadual:

SELECT COUNT(*) FROM albums
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+

COUNT adalah fungsi agregat. Istilah ini merujuk kepada fungsi yang menerjemahkan nilai dari beberapa baris menjadi satu nilai. Ia sering digunakan bersama dengan pernyataan GROUP BY.

Daripada menghitung jumlah baris, kita dapat menerapkan fungsi agregat pada nilai yang dikelompokkan:

SELECT artist_id, SUM(sales)
FROM albums
GROUP BY artist_id
+-----------+------------+
| artist_id | SUM(sales) |
+-----------+------------+
| 1 | 14 |
| 2 | 30 |
| 3 | 28 |
| 4 | 17 |
| 5 | 8 |
| 6 | 47 |
| 7 | 28 |
+-----------+------------+

Jumlah penjualan yang ditunjukkan di atas untuk artis 2 dan 6 adalah gabungan pelbagai album mereka:

SELECT artist_id, sales
FROM albums
WHERE artist_id IN (2, 6)
+-----------+-------+
| artist_id | sales |
+-----------+-------+
| 2 | 24 |
| 2 | 6 |
| 6 | 25 |
| 6 | 22 |
+-----------+-------+

Pengumpulan mengikut Pelbagai Lajur

Anda boleh mengumpulkan mengikut lebih daripada satu lajur. Cukup sertakan beberapa lajur atau ungkapan, dipisahkan dengan koma. Hasilnya akan dikumpulkan mengikut gabungan lajur ini.

SELECT release_year, sales, count(*)
FROM albums
GROUP BY release_year, sales

Ini biasanya akan menghasilkan lebih banyak hasil daripada mengelompokkan dengan satu lajur:

+--------------+-------+----------+
| release_year | sales | count(*) |
+--------------+-------+----------+
| 1969 | 14 | 1 |
| 1973 | 24 | 1 |
| 1977 | 28 | 2 |
| 1991 | 17 | 1 |
| 1977 | 6 | 1 |
| 1973 | 8 | 1 |
| 2011 | 25 | 1 |
| 2015 | 22 | 1 |
+--------------+-------+----------+

Perhatikan bahawa, dalam contoh kecil kami, hanya dua album yang mempunyai tahun dan jumlah penjualan yang sama (28 pada tahun 1977).

Fungsi Agregat Berguna

Selain COUNT, beberapa fungsi berfungsi dengan baik dengan GROUP. Setiap fungsi mengembalikan nilai berdasarkan catatan milik setiap kumpulan hasil.

  • COUNT () mengembalikan jumlah rekod yang sepadan.
  • SUM () mengembalikan jumlah semua nilai dalam lajur tertentu yang ditambah.
  • MIN () mengembalikan nilai terkecil dalam lajur tertentu.
  • MAX () mengembalikan nilai terbesar dalam lajur tertentu.
  • AVG () mengembalikan purata purata. Ia setara dengan SUM () / COUNT ().

Anda juga boleh menggunakan fungsi ini tanpa klausa KUMPULAN:

komputer tidak mengenali cakera keras
SELECT AVG(sales) FROM albums
+------------+
| AVG(sales) |
+------------+
| 19.1111 |
+------------+

Menggunakan KUMPULAN OLEH Dengan Klausa DI MANA

Sama seperti PILIH biasa, anda masih boleh menggunakan DI MANA untuk menapis set hasil:

SELECT artist_id, COUNT(*)
FROM albums
WHERE release_year > 1990
GROUP BY artist_id +-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 4 | 1 |
| 6 | 2 |
+-----------+----------+

Kini anda hanya mempunyai album yang dikeluarkan selepas tahun 1990, dikelompokkan oleh artis. Anda juga boleh menggunakan penggabungan dengan klausa WHERE, secara bebas dari GROUP BY:

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id +---------+--------+
| name | albums |
+---------+--------+
| Nirvana | 1 |
| Adele | 2 |
+---------+--------+

Namun, perhatikan bahawa jika anda cuba menapis berdasarkan lajur gabungan:

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND albums > 2
GROUP BY artist_id;

Anda akan mendapat ralat:

ERROR 1054 (42S22): Unknown column 'albums' in 'where clause'

Lajur berdasarkan data agregat tidak tersedia untuk klausa WHERE.

Menggunakan Klausa HAVING

Jadi, bagaimana anda menyaring hasil yang telah ditetapkan setelah pengelompokan dilakukan? The MEMPUNYAI klausa menangani keperluan ini:

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
GROUP BY artist_id
HAVING albums > 1;

Perhatikan bahawa klausa HAVING muncul selepas GROUP BY. Jika tidak, pada dasarnya ini adalah penggantian sederhana dari MANA dengan MEMILIKI. Hasilnya adalah:

+------------+--------+
| name | albums |
+------------+--------+
| Pink Floyd | 2 |
| Adele | 2 |
+------------+--------+

Anda masih boleh menggunakan keadaan DI MANA untuk menapis hasil sebelum pengelompokan. Ia akan bekerjasama dengan klausa MEMILIKI untuk menapis selepas pengelompokan:

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id
HAVING albums > 1;

Hanya satu artis dalam pangkalan data kami yang mengeluarkan lebih daripada satu album selepas tahun 1990:

+-------+--------+
| name | albums |
+-------+--------+
| Adele | 2 |
+-------+--------+

Menggabungkan Hasil Dengan KUMPULAN OLEH

Pernyataan GROUP BY adalah bahagian yang sangat berguna dari bahasa SQL. Contohnya dapat memberikan maklumat ringkas data, untuk halaman isi, misalnya. Ini adalah alternatif yang sangat baik untuk mengambil sejumlah besar data. Pangkalan data menangani beban kerja tambahan ini dengan baik kerana reka bentuknya menjadikannya optimum untuk pekerjaan.

Setelah anda memahami pengelompokan dan cara menggabungkan beberapa jadual, anda akan dapat menggunakan sebahagian besar kekuatan pangkalan data hubungan.

Berkongsi Berkongsi Tweet E-mel Cara Menanyakan Pelbagai Jadual Pangkalan Data Sekali Dengan SQL Bergabung

Pelajari cara menggunakan SQL bergabung untuk menyelaraskan pertanyaan, menjimatkan masa, dan membuat anda merasa seperti pengguna kuasa SQL.

saiz memori maya yang disyorkan untuk ram 16gb
Baca Seterusnya Topik-topik yang berkaitan
  • Pengaturcaraan
  • SQL
Mengenai Pengarang Bobby Jack(58 Artikel Diterbitkan)

Bobby adalah peminat teknologi yang bekerja sebagai pembangun perisian selama hampir dua dekad. Dia meminati permainan, bekerja sebagai Editor Ulasan di Switch Player Magazine, dan terlibat dalam semua aspek penerbitan dalam talian & pembangunan web.

Lagi Dari Bobby Jack

Langgan buletin kami

Sertailah buletin kami untuk mendapatkan petua, ulasan, ebook percuma, dan tawaran eksklusif!

Klik di sini untuk melanggan