Persediaan Temu Duga SQL · Pelajaran

Peraturan GROUP BY untuk Lajur SELECT

Mengapa setiap lajur bukan agregat mesti muncul dalam GROUP BY serta mod only-full-group-by

Pelajaran 1 daripada 413 langkah

Peraturan GROUP BY untuk Lajur SELECT ialah pelajaran Persediaan Temu Duga SQL percuma di CoddyKit. Ini ialah pelajaran 1 daripada 4. Anda boleh membaca keseluruhan pelajaran di bawah secara percuma — kemudian berlatih secara praktikal dalam pelayar menggunakan penyunting kod terbina dalam dan tutor kecerdasan buatan 24/7. Pelajaran ini merupakan sebahagian daripada laluan pembelajaran Persediaan Temu Duga SQL, dan kemajuan anda disegerakkan merentas web serta aplikasi CoddyKit. Kursus Persediaan Temu Duga SQL merangkumi sejumlah 4 pelajaran.

Mengapa Penemu Duga Bermula dengan GROUP BY

GROUP BY ialah bahagian yang membezakan calon peringkat pemula daripada calon peringkat pertengahan dalam temu duga. Satu peraturan yang paling kerap diuji ialah: setiap lajur dalam senarai SELECT anda mestilah berada dalam fungsi agregat atau disenaraikan dalam GROUP BY.

Jika anda melanggar peraturan ini, enjin tidak dapat menentukan nilai yang perlu dipaparkan bagi kumpulan yang mempunyai banyak baris. Penemu duga sengaja memasukkan kesilapan ini untuk melihat sama ada anda memahami apakah sebenarnya satu kumpulan.

Apakah Sebenarnya Satu Kumpulan

GROUP BY memampatkan banyak baris menjadi satu baris bagi setiap kunci unik. Selepas pengelompokan, enjin tidak lagi mempunyai baris individu. Enjin hanya mempunyai satu baris ringkasan bagi setiap kumpulan.

Lajur yang digunakan untuk pengelompokan mempunyai satu nilai yang jelas bagi setiap kumpulan.
Agregat seperti COUNT, SUM, AVG mengecilkan banyak nilai menjadi satu.
Mana-mana lajur mentah yang lain adalah kabur: nilai yang manakah daripada banyak nilai harus dipaparkan?

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;

Ralat Klasik

Inilah ralat yang sering digunakan oleh penemu duga. Anda mengelompokkan mengikut department tetapi turut memilih name, iaitu lajur bukan agregat yang tidak terdapat dalam GROUP BY.

Setiap jabatan mempunyai ramai pekerja, jadi terdapat banyak nama dalam setiap kumpulan. Enjin tidak dapat memilih satu nama, maka SQL standard menolak pertanyaan tersebut.

-- ERROR: name is not in GROUP BY and not aggregated
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department;

Dua Cara untuk Membetulkannya

Anda mempunyai dua pembetulan yang sah, dan penemu duga mahu mendengar bahawa anda tahu kedua-duanya menghasilkan jawapan yang berbeza:

Tambahkan lajur itu ke GROUP BY jika anda benar-benar mahukan pengelompokan yang lebih terperinci (satu baris bagi setiap jabatan dan nama).
Bungkusnya dalam agregat seperti MAX(name) atau COUNT(name) jika anda mahukan satu nilai bagi setiap kumpulan sedia ada.

-- Finer grouping
SELECT department, name, COUNT(*) AS rows_for_person
FROM employees
GROUP BY department, name;

ONLY_FULL_GROUP_BY dalam MySQL

Satu perangkap kegemaran: MySQL versi lama membenarkan pemilihan lajur yang tidak dikelompokkan dan secara senyap-senyap mengembalikan nilai sewenang-wenangnya daripada kumpulan tersebut. Hal ini menghasilkan laporan yang salah tetapi kelihatan betul.

MySQL moden mendayakan ONLY_FULL_GROUP_BY secara lalai, yang menguatkuasakan peraturan standard. Postgres, SQL Server dan Oracle sentiasa menguatkuasakannya. Jika ditanya mengapa pertanyaan 'berfungsi pada pelayan lama tetapi kini gagal,' inilah jawapannya.

-- Legal under ONLY_FULL_GROUP_BY because every
-- selected column is grouped or aggregated
SELECT department, MAX(hire_date) AS latest_hire
FROM employees
GROUP BY department;

Pengecualian Kebergantungan Fungsian

Terdapat satu perincian yang digunakan oleh penemu duga untuk menguji tahap pemahaman anda. Jika anda mengelompokkan mengikut kunci utama sesuatu jadual, maka setiap lajur lain dalam jadual itu bergantung secara fungsi pada kunci tersebut, lalu mempunyai tepat satu nilai bagi setiap kumpulan.

Postgres dan MySQL moden membenarkan pemilihan lajur yang bergantung itu tanpa menyenaraikannya. Kunci yang dikelompokkan menentukan lajur tersebut secara unik, jadi tiada kekaburan.

-- Legal: id is the PK, so name is determined by it
SELECT e.id, e.name, COUNT(o.id) AS orders
FROM employees e
LEFT JOIN orders o ON o.employee_id = e.id
GROUP BY e.id;

Contoh Berpandukan: Jualan mengikut Wilayah

Katakan anda perlu melaporkan jumlah jualan bagi setiap wilayah. Kunci pengelompokan ialah region; ukuran ialah SUM(amount). Semua yang lain mesti diagregatkan atau diketepikan.

Perhatikan betapa kemasnya hasil ini: satu baris bagi setiap wilayah, dan setiap baris membawa satu nilai yang dijumlahkan. Inilah bentuk setiap laporan agregat.

SELECT region,
       SUM(amount)   AS total_sales,
       COUNT(*)      AS num_orders,
       AVG(amount)   AS avg_order
FROM sales
GROUP BY region;

Mencampurkan Butiran dan Ringkasan

Satu soalan perangkap: 'Tunjukkan jumlah setiap pesanan bersama jumlah keseluruhan wilayahnya.' Anda tidak boleh melakukan perkara ini dengan GROUP BY biasa kerana pengelompokan menghapuskan baris individu.

Jawapan yang betul ialah fungsi tetingkap (SUM(amount) OVER (PARTITION BY region)) atau cantuman semula dengan subpertanyaan yang dikelompokkan. Menyedari bahawa GROUP BY ialah alat yang salah dalam situasi ini merupakan tujuan soalan tersebut.

-- Detail rows kept, region total added per row
SELECT order_id, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales;

GROUP BY dan Nama Alternatif SELECT

Bolehkah anda melakukan GROUP BY menggunakan nama alternatif yang ditakrifkan dalam SELECT? Hal ini bergantung pada dialek, dan ketidakselarasan itulah yang cuba diselidiki oleh penemu duga.

MySQL dan Postgres: membenarkan pengelompokan menggunakan nama alternatif SELECT.
SQL Server dan Oracle: tidak membenarkannya; anda mesti mengulangi ungkapan penuh.

Jawapan yang serasi merentas sistem ialah mengulangi ungkapan tersebut dalam GROUP BY, kerana cara ini berfungsi di semua tempat.

-- Portable: repeat the expression rather than the alias
SELECT EXTRACT(YEAR FROM order_date) AS yr, COUNT(*)
FROM sales
GROUP BY EXTRACT(YEAR FROM order_date);

DISTINCT berbanding GROUP BY untuk Keunikan

Jika anda hanya mahukan gabungan unik tanpa agregat, GROUP BY tanpa agregat berfungsi seperti DISTINCT. Penemu duga mungkin bertanya yang manakah lebih jelas.

Gunakan DISTINCT untuk menyatakan niat ('Saya mahukan baris unik'). Simpan GROUP BY untuk keadaan apabila anda turut mengira agregat. Hasilnya sama, tetapi isyarat dari segi kemudahan dibaca berbeza.

-- These return the same rows
SELECT DISTINCT department, role FROM employees;
SELECT department, role FROM employees GROUP BY department, role;

Cara Menyatakannya dalam Temu Duga

Apabila anda mendapat soalan GROUP BY, jelaskan peraturan itu dengan lantang: 'Setiap lajur yang dipilih sama ada merupakan kunci pengelompokan atau dibungkus dalam agregat, kerana pengelompokan meninggalkan satu baris bagi setiap kunci.'

Kemudian nyatakan kunci pengelompokan dan ukuran anda, serta pastikan tiada lajur terlepas tanpa pengelompokan. Jawapan yang tersusun itu menunjukkan kecekapan peringkat pertengahan walaupun sebelum anda menulis pertanyaan.

Semakan Pantas

Uji pemahaman Anda tentang peraturan teras GROUP BY.

Jawab dalam pelajaran interaktif

Imbas Kembali

Peraturannya: setiap lajur SELECT ialah kunci pengumpulan atau nilai agregat. Sebabnya: GROUP BY meninggalkan satu baris bagi setiap kunci, jadi lajur mentah yang tidak dikumpulkan adalah tidak jelas.

Betulkan pelanggaran dengan mengumpulkan lajur tersebut atau mengagregatkannya.
Tingkah laku lama MySQL mengembalikan nilai sewenang-wenangnya; ONLY_FULL_GROUP_BY menguatkuasakan piawaian.
Kebergantungan fungsi kunci utama ialah satu-satunya pengecualian yang sah.
Untuk mengekalkan baris terperinci di sebelah jumlah kumpulan, gunakan fungsi tetingkap, bukan GROUP BY.

Percuma untuk bermula

Pelajari SQL dengan tutor kecerdasan buatan — percuma

Tulis dan jalankan kod sebenar dalam pelayar anda, dapatkan bantuan segera daripada tutor kecerdasan buatan yang tersedia 24/7, dan sambung semula dari tempat anda berhenti di web atau dalam aplikasi.

Kursus: 30
Pelajaran: 120

Mula belajar secara percuma Lihat kursus penuh

Soalan Lazim

Adakah pelajaran “Peraturan GROUP BY untuk Lajur SELECT” percuma?

Ya — teks penuh “Peraturan GROUP BY untuk Lajur SELECT” boleh dibaca secara percuma di web ini. Untuk berlatih secara interaktif menggunakan penyunting kod terbina dalam dan tutor kecerdasan buatan 24/7, serta membuka kunci baki kursus Persediaan Temu Duga SQL, tingkat taraf kepada CoddyKit PRO. Kursus Persediaan Temu Duga SQL merangkumi sejumlah 4 pelajaran.

Apakah yang akan saya pelajari dalam “Peraturan GROUP BY untuk Lajur SELECT”?

Mengapa setiap lajur bukan agregat mesti muncul dalam GROUP BY serta mod only-full-group-by Anda berlatih Persediaan Temu Duga SQL menggunakan kod praktikal yang dijalankan terus dalam pelayar, manakala tutor kecerdasan buatan 24/7 menjawab soalan anda semasa anda mengikuti pelajaran.

Adakah saya memerlukan pengalaman untuk memulakan Persediaan Temu Duga SQL?

Tiada pengalaman terdahulu diperlukan. Pembelajaran Persediaan Temu Duga SQL di CoddyKit disusun untuk pelajar daripada peringkat pemula hingga lanjutan, jadi anda boleh bermula di sini atau dari awal dan belajar mengikut kadar anda sendiri. Ini ialah pelajaran 1 daripada 4.

Berapa lamakah pelajaran “Peraturan GROUP BY untuk Lajur SELECT” diambil?

Kebanyakan pelajaran CoddyKit mengambil masa kira-kira 5–10 minit. Setiap pelajaran ringkas dan interaktif, jadi anda boleh membuat kemajuan secara berterusan dan menyambung tepat dari tempat anda berhenti di web atau aplikasi.

Bolehkah saya menulis dan menjalankan kod dalam pelajaran Persediaan Temu Duga SQL ini?

Ya. Setiap pelajaran Persediaan Temu Duga SQL menyertakan penyunting kod terbina dalam, jadi anda boleh menulis dan menjalankan kod sebenar terus dalam pelayar serta menerima maklum balas kecerdasan buatan serta-merta — tanpa memerlukan persediaan setempat.

Semua pelajaran dalam kursus ini

Peraturan GROUP BY untuk Lajur SELECT
HAVING berbanding WHERE
Mengumpulkan Mengikut Pelbagai Lajur dan Ungkapan
Mengira dan Menapis Kumpulan

← Kembali ke Persediaan Temu Duga SQL