Coding Interview Prep · Pelajaran

Agregat Per Grup Tanpa GROUP BY

Gunakan subkueri berkorelasi untuk menghitung nilai maksimum grup di samping baris detail.

Agregat Per Grup Tanpa GROUP BY adalah pelajaran Coding Interview Prep gratis di CoddyKit. Ini adalah pelajaran 2 dari 4. Kamu bisa membaca pelajaran lengkapnya di bawah secara gratis — lalu praktikkan langsung di browser dengan editor kode bawaan dan tutor AI 24/7. Ini adalah bagian dari jalur belajar Coding Interview Prep, dan progresmu tersinkronisasi di web dan aplikasi CoddyKit. Kursus Coding Interview Prep mencakup 4 pelajaran total.

Masalah Detail dan Agregat

Pertanyaan wawancara yang umum: "Tampilkan setiap baris bersama agregat dari grupnya." Misalnya, tampilkan setiap karyawan dengan gaji maksimum departemennya pada baris yang sama.

GROUP BY biasa menggabungkan baris, sehingga tidak dapat mempertahankan detail per karyawan. Anda memerlukan baris detail dan angka tingkat grup secara bersamaan.

Subkueri berkorelasi menyelesaikan masalah ini dengan elegan: subkueri tersebut menghitung agregat grup untuk setiap baris detail tanpa menggabungkan baris apa pun.

Mengapa GROUP BY Biasa Gagal di Sini

Jika Anda menulis SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id, Anda mendapatkan satu baris per departemen dan kehilangan nama setiap karyawan.

Menambahkan name ke SELECT tanpa menambahkannya ke GROUP BY menghasilkan kesalahan klasik "kolom harus muncul dalam GROUP BY".

Pewawancara sedang memeriksa apakah Anda memahami bahwa GROUP BY mengurangi kardinalitas. Untuk mempertahankan baris detail, Anda harus menghitung agregat dengan cara lain.

Subkueri Berkorelasi sebagai Solusi

Tempatkan agregat grup dalam daftar SELECT sebagai subkueri berkorelasi. Setiap baris karyawan memicu MAX dalam yang dibatasi pada departemen karyawan tersebut.

Korelasi e2.dept_id = e1.dept_id mengaitkan agregat dengan grup yang tepat, sementara kueri luar tetap mengembalikan satu baris per karyawan.

SELECT e1.name,
       e1.dept_id,
       e1.salary,
       (SELECT MAX(e2.salary)
        FROM employees e2
        WHERE e2.dept_id = e1.dept_id) AS dept_max_salary
FROM employees e1;

Membandingkan Setiap Baris dengan Grupnya

Setelah agregat grup ditempatkan dalam kueri, Anda dapat membandingkan setiap baris dengannya. Pertanyaan yang sering muncul: "Temukan karyawan yang berpenghasilan lebih besar daripada rata-rata departemennya."

Di sini AVG berkorelasi ditempatkan dalam WHERE, sehingga setiap karyawan diuji terhadap rata-rata departemennya sendiri.

SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

Menghitung Selisih dari Grup

Anda juga dapat menampilkan seberapa jauh setiap baris dari agregat grupnya. Mengurangkan rata-rata berkorelasi menghasilkan selisih per baris.

Perhatikan bahwa subkueri berkorelasi yang sama dapat digunakan kembali dalam beberapa ekspresi SELECT; mesin mengevaluasinya untuk setiap baris setiap kali subkueri tersebut muncul.

SELECT e1.name,
       e1.salary,
       e1.salary - (SELECT AVG(e2.salary)
                    FROM employees e2
                    WHERE e2.dept_id = e1.dept_id) AS gap_from_dept_avg
FROM employees e1;

Menemukan Penghasil Tertinggi per Grup

Untuk hanya mengembalikan orang dengan gaji tertinggi di setiap departemen, bandingkan setiap gaji dengan MAX berkorelasi dan pertahankan yang cocok.

Pola ini mengembalikan nilai yang sama: jika dua karyawan memiliki nilai maksimum departemen yang sama, keduanya akan ditampilkan. Perilaku penanganan nilai seri ini sering menjadi pertanyaan lanjutan dari pewawancara.

SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

Alternatif Fungsi Jendela

SQL modern menawarkan alat yang lebih rapi: fungsi jendela. MAX(salary) OVER (PARTITION BY dept_id) menghitung agregat grup tanpa menggabungkan baris dan tanpa pemindaian ulang yang berkorelasi.

Pewawancara senang jika Anda dapat memberikan kedua solusi dan menjelaskan bahwa versi fungsi jendela biasanya berkinerja lebih baik karena memindai tabel sekali.

SELECT name,
       dept_id,
       salary,
       MAX(salary) OVER (PARTITION BY dept_id) AS dept_max_salary
FROM employees;

Perbandingan Subkueri Berkorelasi dan Fungsi Jendela

Kedua pendekatan mengembalikan bentuk hasil yang sama, tetapi memiliki perbedaan:

Subkueri berkorelasi: portabel, dapat digunakan pada mesin yang sangat lama, tetapi dievaluasi ulang untuk setiap baris.
Fungsi jendela: satu kali lintasan, jauh lebih cepat pada tabel besar, tetapi memerlukan dukungan fungsi jendela SQL.

Sampaikan mana yang akan Anda pilih dan alasannya. Untuk penggunaan sekali pada tabel kecil, keduanya baik; untuk analisis dalam skala besar, pilih fungsi jendela.

Contoh: Pesanan di Atas Rata-Rata Pelanggan

Terapkan pola ini pada pesanan. Tampilkan pesanan yang jumlahnya melebihi nilai pesanan rata-rata milik pelanggan yang membuatnya.

AVG berkorelasi dibatasi oleh o2.customer_id = o1.customer_id, sehingga setiap pesanan memiliki tolok ukur pribadi milik pelanggannya.

SELECT o1.order_id, o1.customer_id, o1.amount
FROM orders o1
WHERE o1.amount > (
    SELECT AVG(o2.amount)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
);

Perhatikan Kasus NULL dan Grup Kosong

Jika sebuah grup hanya memiliki satu baris, rata-ratanya sama dengan baris tersebut, sehingga salary > avg bernilai false dan baris itu tidak disertakan. Sebutkan kasus khusus ini secara proaktif.

Selain itu, gaji NULL diabaikan oleh AVG dan MAX, sesuai dengan semantik agregat SQL. Jika semua nilai dalam sebuah grup adalah NULL, agregatnya adalah NULL dan perbandingan menjadi UNKNOWN, sehingga baris tersebut tidak disertakan. Mengantisipasi kasus-kasus ini membedakan jawaban yang menyeluruh.

Menghitung Peringkat dalam Grup

Anda dapat menyatakan peringkat sebuah baris di dalam grupnya dengan COUNT berkorelasi. Untuk menemukan peringkat gaji setiap karyawan dalam departemennya, hitung berapa banyak rekan yang berpenghasilan lebih besar.

Peringkat 1 berarti penghasilan tertinggi. Menambahkan 1 mengubah jumlah penghasilan yang lebih tinggi menjadi posisi berbasis 1, dan korelasi membatasinya pada departemen tersebut.

SELECT e1.name,
       e1.dept_id,
       e1.salary,
       (SELECT COUNT(*) + 1
        FROM employees e2
        WHERE e2.dept_id = e1.dept_id
          AND e2.salary > e1.salary) AS salary_rank_in_dept
FROM employees e1;

Pemeriksaan Singkat

Pilih alasan mengapa subkueri berkorelasi lebih unggul daripada GROUP BY biasa untuk tugas ini.

Rekap: Agregat per Grup Tanpa GROUP BY

Hal-hal penting:

Subkueri berkorelasi menempatkan agregat tingkat grup pada setiap baris detail tanpa menggabungkannya.
Gunakan subkueri tersebut dalam SELECT untuk menampilkan agregat, atau dalam WHERE untuk membandingkan setiap baris dengan grupnya.
Pola = MAX(...) mengembalikan semua baris tertinggi yang memiliki nilai sama.
Fungsi jendela dengan PARTITION BY melakukan hal yang sama dalam satu kali lintasan dan biasanya memiliki skala yang lebih baik.

Tawarkan kedua solusi dan berikan alasan atas pilihan Anda dalam wawancara.

Pertanyaan yang Sering Diajukan

Apakah pelajaran “Agregat Per Grup Tanpa GROUP BY” gratis?

Ya — teks lengkap “Agregat Per Grup Tanpa GROUP BY” gratis dibaca di sini di web. Untuk praktiknya secara interaktif (editor kode bawaan dan tutor AI 24/7) dan buka sisa kursus Coding Interview Prep, upgrade ke CoddyKit PRO. Kursus Coding Interview Prep mencakup 4 pelajaran total.

Apa yang akan aku pelajari di “Agregat Per Grup Tanpa GROUP BY”?

Gunakan subkueri berkorelasi untuk menghitung nilai maksimum grup di samping baris detail. Kamu berlatih Coding Interview Prep dengan kode praktik yang langsung kamu jalankan di browser, dan tutor AI 24/7 menjawab pertanyaanmu saat kamu mengerjakan pelajaran ini.

Apakah aku perlu pengalaman untuk memulai Coding Interview Prep?

Tidak diperlukan pengalaman sebelumnya. Coding Interview Prep di CoddyKit dirancang untuk pemula hingga pelajar tingkat lanjut, jadi kamu bisa memulai di sini atau dari awal dan belajar sesuai kecepatan kamu sendiri. Ini adalah pelajaran 2 dari 4.

Berapa lama pelajaran “Agregat Per Grup Tanpa GROUP BY” memakan waktu?

Sebagian besar pelajaran CoddyKit memakan waktu sekitar 5–10 menit. Setiap pelajaran ringkas dan interaktif, jadi kamu membuat kemajuan stabil dan melanjutkan dari tempat kamu tinggalkan di web dan aplikasi.

Bisakah aku menulis dan menjalankan kode dalam pelajaran Coding Interview Prep ini?

Ya. Setiap pelajaran Coding Interview Prep menyertakan editor kode bawaan, jadi kamu menulis dan menjalankan kode nyata langsung di browser dan mendapatkan umpan balik AI instan — tidak diperlukan penyiapan lokal.

Semua pelajaran dalam kursus ini

Anatomi Subkueri Berkorelasi
Agregat Per Grup Tanpa GROUP BY
EXISTS dan NOT EXISTS Berkorelasi
Menulis Ulang Subkueri Berkorelasi sebagai JOIN

← Kembali ke Coding Interview Prep