0Pricing
SQL Interview Prep · Pelajaran

Penerima Penghasilan Tertinggi Per Departemen

Gabungkan partisi dengan pemeringkatan untuk masalah gaji Top-N berdasarkan grup.

Penerima Penghasilan Tertinggi Per Departemen adalah pelajaran SQL Interview Prep gratis di CoddyKit. Ini adalah pelajaran 3 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 SQL Interview Prep, dan progresmu tersinkronisasi di web dan aplikasi CoddyKit. Kursus SQL Interview Prep mencakup 4 pelajaran total.

Dari Peringkat Global ke Per Kelompok

Eskalasi berikutnya: "Temukan karyawan bergaji tertinggi di setiap departemen." Ini menggabungkan pemeringkatan dengan pengelompokan dan merupakan pertanyaan tingkat menengah yang hampir pasti muncul.

Misalkan ada tabel employee dengan id, name, department_id, dan salary. Kita menginginkan satu karyawan bergaji tertinggi per departemen (atau lebih jika ada nilai yang sama), bukan hanya maksimum global.

Alat baru yang utama adalah PARTITION BY, yang memulai kembali pemeringkatan di dalam setiap departemen.

CREATE TABLE employee (
  id            INT PRIMARY KEY,
  name          VARCHAR(100),
  department_id INT,
  salary        INT
);

PARTITION BY mengatur ulang peringkat

Menambahkan PARTITION BY department_id pada fungsi jendela memberi tahu basis data untuk menghitung peringkat secara independen di setiap departemen.

Setiap departemen memulai peringkatnya sendiri dari 1. Jadi, karyawan bergaji tertinggi di departemen 1 dan karyawan bergaji tertinggi di departemen 5 sama-sama mendapat peringkat 1. Tanpa pembagian partisi, hanya maksimum global yang mendapat peringkat 1.

SELECT name, department_id, salary,
       DENSE_RANK() OVER (
         PARTITION BY department_id
         ORDER BY salary DESC
       ) AS rnk
FROM employee;

Memfilter ke peringkat 1

Untuk menyisakan hanya karyawan bergaji tertinggi, bungkus kueri yang sudah diberi peringkat lalu filter peringkat 1. Seperti biasa, fungsi jendela harus dihitung dalam subkueri atau CTE sebelum Anda dapat memfilternya.

Menggunakan DENSE_RANK (atau RANK) di sini berarti bahwa jika dua karyawan memiliki gaji tertinggi yang sama dalam suatu departemen, keduanya akan dikembalikan. Biasanya, inilah interpretasi yang tepat untuk "karyawan bergaji tertinggi".

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
WHERE rnk = 1;

ROW_NUMBER saat Anda menginginkan tepat satu

Terkadang pewawancara menginginkan tepat satu baris per departemen meskipun ada nilai yang sama. Gunakan ROW_NUMBER dan tambahkan pemecah seri yang deterministik, seperti pengenal terendah.

Tanpa pemecah seri, nilai yang sama akan ditentukan secara sembarang dan hasil Anda tidak deterministik. Menambahkan , id ASC membuat pilihan tersebut dapat diulang.

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC, id ASC
         ) AS rn
  FROM employee
) t
WHERE rn = 1;

DENSE_RANK dibandingkan dengan ROW_NUMBER dan RANK di sini

Pilih berdasarkan rumusan persisnya:

  • DENSE_RANK = 1: semua karyawan yang memiliki gaji tertinggi per departemen dan nilainya sama.
  • RANK = 1: identik dengan DENSE_RANK untuk peringkat teratas (kesenjangan hanya penting di bawah peringkat 1).
  • ROW_NUMBER = 1: tepat satu karyawan per departemen, dengan nilai yang sama diputuskan oleh ORDER BY Anda.

Menyebutkan yang Anda pilih dan alasannya adalah bagian yang dinilai oleh pewawancara.

Pendekatan subkueri berkorelasi sebelum fungsi jendela

Sebelum fungsi jendela tersedia, solusi standar adalah subkueri berkorelasi: pertahankan sebuah baris hanya jika tidak ada karyawan lain di departemen yang sama yang berpenghasilan lebih tinggi.

Pendekatan ini secara alami mengembalikan semua karyawan berpenghasilan tertinggi yang nilainya sama. Pendekatan ini dapat digunakan lintas sistem, tetapi bisa lambat karena MAX bagian dalam dievaluasi untuk setiap baris luar, kecuali pengoptimal menulis ulang kueri tersebut.

SELECT e.name, e.department_id, e.salary
FROM employee e
WHERE e.salary = (
  SELECT MAX(e2.salary)
  FROM employee e2
  WHERE e2.department_id = e.department_id
);

Pendekatan penggabungan dengan GROUP BY

Pola lain yang dapat digunakan lintas sistem: hitung gaji maksimum per departemen dengan GROUP BY, lalu gabungkan kembali untuk mendapatkan karyawan yang cocok.

Pendekatan ini efisien dan jelas. Penggabungan tersebut mengembalikan setiap karyawan yang gajinya sama dengan gaji maksimum departemennya, sehingga nilai yang sama tetap dipertahankan.

SELECT e.name, e.department_id, e.salary
FROM employee e
JOIN (
  SELECT department_id, MAX(salary) AS max_sal
  FROM employee
  GROUP BY department_id
) m
  ON e.department_id = m.department_id
 AND e.salary = m.max_sal;

N teratas per departemen

Pola ini dapat diperluas menjadi "3 karyawan berpenghasilan tertinggi per departemen" tanpa gagasan baru. Cukup ubah filter menjadi rentang.

Dengan DENSE_RANK, rnk <= 3 mengembalikan tiga tingkat gaji berbeda teratas (mungkin lebih dari tiga baris jika ada nilai yang sama). Dengan ROW_NUMBER, rn <= 3 mengembalikan tepat tiga baris per departemen.

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
WHERE rnk <= 3;

Contoh lengkap

Departemen 1: Ana 120, Bob 120, Cara 90. Departemen 2: Dan 200, Eve 150.

  • DENSE_RANK = 1: Ana (120), Bob (120) dari departemen 1; Dan (200) dari departemen 2. Tiga baris.
  • ROW_NUMBER = 1 dengan pemecah seri berupa pengenal: salah satu dari Ana/Bob (yang memiliki pengenal lebih rendah) ditambah Dan. Dua baris.

Data yang sama dapat menghasilkan jumlah baris yang berbeda, bergantung pada fungsinya. Pilih yang sesuai dengan pertanyaannya.

Menyertakan departemen dan menggabungkan nama

Pewawancara sering menambahkan tabel department dan meminta nama departemen. Cukup gabungkan tabel tersebut setelah pemeringkatan.

Pertahankan pemeringkatan pada tabel employee dan gabungkan tabel referensi di bagian akhir, sehingga pembagian partisi tetap berlangsung pada tingkat perincian yang tepat.

SELECT d.name AS department, t.name AS employee, t.salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
JOIN department d ON d.id = t.department_id
WHERE t.rnk = 1;

Kesalahan yang harus dihindari

Kesalahan umum dalam pemeringkatan per kelompok:

  • Lupa menggunakan PARTITION BY dan melakukan pemeringkatan secara global, sehingga hanya karyawan berpenghasilan tertinggi di seluruh perusahaan yang dikembalikan.
  • Menggunakan ROW_NUMBER ketika pertanyaan menyiratkan bahwa semua nilai yang sama harus ditampilkan, sehingga karyawan lain dengan penghasilan tertinggi diam-diam dihilangkan.
  • Mencoba menempatkan fungsi jendela langsung di dalam WHERE, alih-alih membungkusnya.
  • Menggabungkan tabel departemen sebelum pemeringkatan dan tanpa sengaja mengubah tingkat perincian partisi.

Pemeriksaan singkat

Pilih fungsi pemeringkatan yang tepat untuk persyaratan tersebut.

Rangkuman

Karyawan berpenghasilan tertinggi per departemen adalah pola pemeringkatan global yang ditambah PARTITION BY department_id:

  • DENSE_RANK = 1 mengembalikan semua karyawan berpenghasilan tertinggi per departemen yang nilainya sama.
  • ROW_NUMBER = 1 dengan pemecah seri mengembalikan tepat satu karyawan per departemen.
  • Alternatif yang dapat digunakan lintas sistem: MAX berkorelasi per departemen, atau nilai maksimum dengan GROUP BY yang digabungkan kembali ke tabel.

Perluas ke N teratas dengan mengubah = 1 menjadi <= N. Sampaikan pilihan penanganan nilai yang sama secara jelas.

Pertanyaan yang Sering Diajukan

Apakah pelajaran “Penerima Penghasilan Tertinggi Per Departemen” gratis?

Ya — teks lengkap “Penerima Penghasilan Tertinggi Per Departemen” gratis dibaca di sini di web. Untuk praktiknya secara interaktif (editor kode bawaan dan tutor AI 24/7) dan buka sisa kursus SQL Interview Prep, upgrade ke CoddyKit PRO. Kursus SQL Interview Prep mencakup 4 pelajaran total.

Apa yang akan aku pelajari di “Penerima Penghasilan Tertinggi Per Departemen”?

Gabungkan partisi dengan pemeringkatan untuk masalah gaji Top-N berdasarkan grup. Kamu berlatih SQL 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 SQL Interview Prep?

Tidak diperlukan pengalaman sebelumnya. SQL 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 3 dari 4.

Berapa lama pelajaran “Penerima Penghasilan Tertinggi Per Departemen” 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 SQL Interview Prep ini?

Ya. Setiap pelajaran SQL 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

  1. Gaji Tertinggi Kedua, Lima Cara
  2. Nilai Tertinggi ke-N dengan DENSE_RANK
  3. Penerima Penghasilan Tertinggi Per Departemen
  4. Mengembalikan NULL Saat Nilai ke-N Tidak Ada
← Kembali ke SQL Interview Prep