PricingLoginStart free
Persediaan Temu Duga SQL · Pelajaran

Penerima Pendapatan Tertinggi Setiap Jabatan

Gabungkan partition dengan kedudukan untuk masalah gaji teratas-N mengikut kumpulan

Pelajaran 3 daripada 413 langkah

Penerima Pendapatan Tertinggi Setiap Jabatan ialah pelajaran Persediaan Temu Duga SQL percuma di CoddyKit. Ini ialah pelajaran 3 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.

Daripada Kedudukan Global kepada Setiap Kumpulan

Peningkatan seterusnya: "Cari pekerja bergaji tertinggi dalam setiap jabatan." Ini menggabungkan kedudukan dengan pengumpulan dan merupakan soalan peringkat pertengahan yang sering ditanya.

Anggap terdapat jadual employee dengan id, name, department_id dan salary. Kita mahukan seorang pekerja bergaji tertinggi bagi setiap jabatan, atau lebih jika terdapat nilai seri, bukan sekadar maksimum global.

Alat baharu yang penting ialah PARTITION BY, yang memulakan semula kedudukan dalam setiap jabatan.

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

PARTITION BY menetapkan semula kedudukan

Menambahkan PARTITION BY department_id pada tetingkap memberitahu pangkalan data supaya mengira kedudukan secara berasingan dalam setiap jabatan.

Setiap jabatan memulakan kedudukan 1 sendiri. Jadi, pekerja bergaji tertinggi dalam jabatan 1 dan pekerja bergaji tertinggi dalam jabatan 5 kedua-duanya mendapat kedudukan 1. Tanpa pembahagian, hanya maksimum global tunggal yang akan mendapat kedudukan 1.

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

Menapis kepada kedudukan 1

Untuk mengekalkan pekerja bergaji tertinggi sahaja, bungkus pertanyaan yang telah disusun kedudukannya dan tapis kepada kedudukan 1. Seperti biasa, fungsi tetingkap mesti dikira dalam subpertanyaan atau CTE sebelum Anda boleh menapisnya.

Menggunakan DENSE_RANK (atau RANK) di sini bermaksud jika dua pekerja seri dengan gaji tertinggi dalam sesebuah jabatan, kedua-duanya akan dikembalikan. Biasanya, inilah tafsiran yang betul bagi "pekerja 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 apabila anda memerlukan tepat satu

Kadang-kadang penemuduga mahukan tepat satu baris bagi setiap jabatan walaupun terdapat seri. Kemudian gunakan ROW_NUMBER dan tambahkan pemecah seri yang deterministik, seperti pengenal pasti paling rendah.

Tanpa pemecah seri, seri diselesaikan secara rawak dan hasil anda tidak deterministik. Menambahkan , id ASC menjadikan pilihan itu boleh 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 berbanding ROW_NUMBER berbanding RANK di sini

Pilih berdasarkan perkataan yang tepat:

  • DENSE_RANK = 1: semua pekerja yang berkongsi gaji tertinggi bagi setiap jabatan.
  • RANK = 1: sama seperti DENSE_RANK untuk kedudukan teratas (jurang hanya penting di bawah kedudukan 1).
  • ROW_NUMBER = 1: tepat seorang pekerja bagi setiap jabatan, dengan seri dipecahkan berdasarkan ORDER BY anda.

Menyatakan pilihan anda dan sebabnya ialah bahagian yang dinilai oleh penemuduga.

Pendekatan subkueri berkorelasi sebelum fungsi tetingkap

Sebelum fungsi tetingkap diperkenalkan, penyelesaian lazim ialah subkueri berkorelasi: kekalkan baris hanya jika tiada sesiapa dalam jabatan yang sama memperoleh gaji lebih tinggi.

Kaedah ini secara semula jadi mengembalikan semua pekerja bergaji tertinggi yang seri. Kaedah ini serasi merentas dialek, tetapi boleh menjadi perlahan kerana MAX dalaman dinilai untuk setiap baris luar, melainkan pengoptimum menulisnya semula.

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 cantuman GROUP BY

Satu lagi corak yang serasi merentas dialek: kira gaji maksimum bagi setiap jabatan dengan GROUP BY, kemudian cantumkan semula untuk mendapatkan pekerja yang sepadan.

Kaedah ini cekap dan jelas. Cantuman itu mengembalikan setiap pekerja yang gajinya sama dengan gaji maksimum jabatan mereka, jadi seri dikekalkan.

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 bagi setiap jabatan

Corak ini boleh dikembangkan kepada "tiga pekerja bergaji tertinggi bagi setiap jabatan" tanpa idea baharu. Anda hanya perlu menukar penapis kepada julat.

Dengan DENSE_RANK, rnk <= 3 mengembalikan tiga tahap gaji berbeza teratas (mungkin lebih daripada tiga baris jika terdapat seri). Dengan ROW_NUMBER, rn <= 3 mengembalikan tepat tiga baris bagi setiap jabatan.

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

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

  • DENSE_RANK = 1: Ana (120), Bob (120) daripada jabatan 1; Dan (200) daripada jabatan 2. Tiga baris.
  • ROW_NUMBER = 1 dengan pemecah seri berdasarkan pengenal pasti: salah seorang daripada Ana/Bob (yang mempunyai pengenal pasti lebih rendah) serta Dan. Dua baris.

Data yang sama boleh menghasilkan bilangan baris yang berbeza bergantung pada fungsi. Pilih fungsi yang sepadan dengan soalan.

Menyertakan jabatan dan mencantumkan nama

Penemuduga sering menambahkan jadual department dan meminta nama jabatan. Cantumkan jadual itu selepas pemeringkatan.

Kekalkan pemeringkatan pada jadual employee dan cantumkan jadual rujukan pada akhir, supaya pembahagian masih berlaku pada tahap perincian yang betul.

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;

Kesilapan yang perlu dielakkan

Kesilapan pemeringkatan mengikut kumpulan yang biasa berlaku:

  • Terlupa PARTITION BY lalu melakukan pemeringkatan secara global, sehingga hanya mengembalikan pekerja bergaji tertinggi seluruh syarikat.
  • Menggunakan ROW_NUMBER apabila soalan menunjukkan semua yang seri perlu dipaparkan, lalu menggugurkan pekerja yang berkongsi kedudukan teratas tanpa disedari.
  • Cuba meletakkan fungsi tetingkap terus dalam WHERE dan bukannya membungkusnya.
  • Mencantumkan jadual jabatan sebelum pemeringkatan lalu mengubah tahap perincian pembahagian secara tidak sengaja.

Semakan Pantas

Pilih fungsi pemeringkatan yang betul berdasarkan keperluan.

Jawab dalam pelajaran interaktif

Ringkasan

Pekerja bergaji tertinggi bagi setiap jabatan ialah corak pemeringkatan global ditambah PARTITION BY department_id:

  • DENSE_RANK = 1 mengembalikan semua pekerja yang berkongsi gaji tertinggi bagi setiap jabatan.
  • ROW_NUMBER = 1 dengan pemecah seri mengembalikan tepat seorang bagi setiap jabatan.
  • Alternatif yang serasi merentas dialek: subkueri MAX berkorelasi bagi setiap jabatan, atau gaji maksimum dengan GROUP BY yang dicantumkan semula kepada jadual.

Kembangkan kepada N teratas dengan menukar = 1 kepada <= N. Nyatakan pilihan cara menangani seri dengan jelas.

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 percumaLihat kursus penuh

Soalan Lazim

Adakah pelajaran “Penerima Pendapatan Tertinggi Setiap Jabatan” percuma?

Ya — teks penuh “Penerima Pendapatan Tertinggi Setiap Jabatan” 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 “Penerima Pendapatan Tertinggi Setiap Jabatan”?

Gabungkan partition dengan kedudukan untuk masalah gaji teratas-N mengikut kumpulan 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 3 daripada 4.

Berapa lamakah pelajaran “Penerima Pendapatan Tertinggi Setiap Jabatan” 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

  1. Gaji Kedua Tertinggi, Lima Cara
  2. Nilai Tertinggi Ke-n Dengan DENSE_RANK
  3. Penerima Pendapatan Tertinggi Setiap Jabatan
  4. Mengembalikan NULL apabila Nilai Ke-n Tiada
← Kembali ke Persediaan Temu Duga SQL