PricingLoginStart free
Persediaan Temu Duga SQL · Pelajaran

Baris Teratas-N Setiap Kumpulan Dengan ROW_NUMBER

Corak partition-dan-kedudukan piawai untuk “3 teratas bagi setiap kategori”

Pelajaran 1 daripada 413 langkah

Baris Teratas-N Setiap Kumpulan Dengan ROW_NUMBER 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.

Soalan N Teratas bagi Setiap Kumpulan

Salah satu soalan temu duga SQL yang paling lazim kedengaran mudah: "Kembalikan 3 pekerja dengan gaji tertinggi dalam setiap jabatan." Calon yang terus menggunakan LIMIT akan gagal kerana LIMIT mengehadkan keseluruhan set hasil, bukan setiap kumpulan.

Penemu duga sedang menguji sama ada anda mengetahui fungsi tetingkap. Jawapan piawai ialah: nomborkan baris dalam setiap kumpulan, kemudian kekalkan baris yang nombornya ≤ N. Pelajaran ini membina corak tersebut langkah demi langkah.

Mengapa LIMIT Tidak Dapat Menyelesaikannya

Andaikan anda menulis pertanyaan di bawah. Pertanyaan itu hanya mengembalikan 3 baris keseluruhannya merentas seluruh jadual, bukannya 3 baris bagi setiap jabatan.

LIMIT (atau TOP, atau FETCH FIRST) beroperasi pada set hasil akhir. Tiada LIMIT bagi setiap kumpulan dalam SQL piawai. Apabila penemu duga mendengar anda mencadangkan LIMIT 3 untuk masalah bagi setiap kumpulan, itu menunjukkan anda belum benar-benar memahami pembahagian kepada kumpulan.

-- WRONG: only 3 rows total, not 3 per department
SELECT department, name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Kenali ROW_NUMBER

ROW_NUMBER() ialah fungsi tetingkap yang memberikan nombor bulat unik tanpa jurang kepada setiap baris mengikut susunan tertentu. Dengan sendirinya, fungsi ini menomborkan keseluruhan hasil.

Bahan pentingnya ialah PARTITION BY: fungsi ini memulakan semula penomboran pada 1 bagi setiap kumpulan. Gabungkan PARTITION BY department dengan ORDER BY salary DESC dan setiap jabatan akan mempunyai kedudukan 1, 2, 3, ... tersendiri mengikut gaji.

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS rn
FROM employees;

Membaca Hasil yang Dinomborkan

Selepas menjalankan pertanyaan sebelumnya, setiap baris membawa nilai rn. Dalam setiap jabatan, gaji tertinggi mendapat rn = 1, gaji seterusnya mendapat 2, dan seterusnya. Jabatan baharu menetapkan semula nilai kepada 1.

  • Jualan: Ana (1), Bo (2), Cal (3), Dee (4)
  • Kejuruteraan: Eve (1), Fin (2), Gus (3)

Kini, "3 teratas bagi setiap jabatan" bermaksud "kekalkan baris yang mempunyai rn <= 3".

Anda Tidak Boleh Menapis rn dalam WHERE

Langkah seterusnya yang semula jadi ialah WHERE rn <= 3, tetapi langkah itu gagal. Fungsi tetingkap dikira selepas klausa WHERE dalam susunan pelaksanaan logik, jadi nama alternatif rn belum wujud apabila WHERE dilaksanakan.

Penemu duga gemar menggunakan perangkap ini. Penyelesaiannya ialah mengira fungsi tetingkap dalam subpertanyaan atau CTE, kemudian menapis hasil pertanyaan dalaman itu dalam pertanyaan luaran.

-- ERROR: rn does not exist in WHERE
SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees
WHERE rn <= 3;

Penyelesaian CTE Piawai

Balut penomboran dalam CTE bernama ranked, kemudian pilih daripadanya dengan penapis dalam WHERE luaran. Inilah jawapan yang ingin dilihat oleh penemu duga dan susunannya mudah dibaca.

Hafalkan rangka ini: bahagikan mengikut kumpulan, susun mengikut metrik, tapis rn ≤ N dalam pertanyaan luaran. Corak ini boleh digunakan untuk satu teratas, lima teratas atau sebarang N dengan menukar satu nombor sahaja.

WITH ranked AS (
  SELECT
    name, department, salary,
    ROW_NUMBER() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn <= 3
ORDER BY department, rn;

Bentuk Subpertanyaan

Jika dialek yang digunakan oleh penemu duga lebih lama atau mereka lebih suka subpertanyaan, logik yang sama boleh diletakkan dalam jadual terbitan di dalam FROM. Ingat bahawa jadual terbitan mesti mempunyai nama alternatif (r di sini), jika tidak, anda akan mendapat ralat sintaks.

Bentuk CTE dan jadual terbitan boleh saling menggantikan untuk masalah ini. Pilih bentuk yang lebih mudah dibaca oleh penemu duga; kedua-duanya sama tepat.

SELECT name, department, salary
FROM (
  SELECT name, department, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department ORDER BY salary DESC
         ) AS rn
  FROM employees
) AS r
WHERE rn <= 3;

Satu Teratas bagi Setiap Kumpulan

"Cari pekerja dengan gaji tertinggi dalam setiap jabatan" hanyalah N = 1. Tetapkan penapis kepada rn = 1.

Mengapa tidak menggunakan MAX(salary) dengan GROUP BY department? Kerana MAX memberikan nilai gaji tetapi bukan maklumat lain dalam baris pekerja itu, seperti nama dan tarikh pengambilan. ROW_NUMBER mengekalkan keseluruhan baris pemenang, iaitu perkara yang biasanya benar-benar dikehendaki oleh soalan tersebut.

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY department ORDER BY salary DESC
         ) AS rn
  FROM employees
)
SELECT name, department, salary, hire_date
FROM ranked
WHERE rn = 1;

Menambah Pemecah Seri yang Konsisten

ROW_NUMBER sentiasa mengembalikan tepat N baris, walaupun terdapat gaji yang sama. Namun, baris seri yang mendapat rn = 1 adalah sewenang-wenangnya dipilih melainkan seri itu dipecahkan. Jika dua orang memperoleh 90000 dan anda hanya mengekalkan rn = 1, orang yang dipilih tidak dapat dijangka antara pelaksanaan.

Tambahkan kunci susunan kedua yang unik seperti employee_id supaya hasilnya stabil dan boleh dihasilkan semula. Penemu duga menghargai calon yang menyebut keperluan hasil yang konsisten tanpa diminta.

ROW_NUMBER() OVER (
  PARTITION BY department
  ORDER BY salary DESC, employee_id ASC
) AS rn

Contoh Praktikal Lengkap

Diberikan jadual sales dengan region, product dan revenue, kembalikan 2 produk teratas mengikut hasil bagi setiap rantau. Gunakan kaedah yang sama: bahagikan mengikut region, susun mengikut revenue DESC, dan kekalkan rn <= 2.

Perhatikan bahawa hanya lajur pembahagian dan lajur metrik yang berubah. Strukturnya sama tanpa mengira bidang perniagaan.

WITH ranked AS (
  SELECT region, product, revenue,
         ROW_NUMBER() OVER (
           PARTITION BY region ORDER BY revenue DESC, product
         ) AS rn
  FROM sales
)
SELECT region, product, revenue
FROM ranked
WHERE rn <= 2
ORDER BY region, rn;

Prestasi dan Perkara untuk Disebut

Untuk menunjukkan kelebihan anda selain ketepatan, nyatakan perkara berikut:

  • Indeks pada (department, salary DESC) membantu enjin menghasilkan baris tersusun bagi setiap pembahagian dengan cekap.
  • Pendekatan fungsi tetingkap mengimbas jadual sekali sahaja, jauh lebih baik daripada subpertanyaan berkorelasi yang dijalankan bagi setiap baris.
  • Untuk kes memilih satu teratas daripada set yang sangat besar, sesetengah enjin menyokong DISTINCT ON (Postgres) sebagai jalan pintas, tetapi ROW_NUMBER ialah piawai yang boleh digunakan merentas sistem.

Sentiasa nyatakan pemecah seri dan sahkan N yang diminta.

Semakan Pantas

Uji pemahaman anda tentang corak N teratas bagi setiap kumpulan.

Jawab dalam pelajaran interaktif

Ulang Kaji: N Teratas bagi Setiap Kumpulan

Ringkasan corak ini: bahagikan mengikut kumpulan, susun mengikut metrik, tetapkan ROW_NUMBER, kemudian kekalkan rn ≤ N dalam pertanyaan luaran.

  • LIMIT mengehadkan keseluruhan set, bukan setiap kumpulan.
  • Anda tidak boleh menapis nama pengganti fungsi tetingkap dalam WHERE; letakkan fungsi itu dalam CTE atau subpertanyaan.
  • Tambahkan pemecah seri yang unik untuk mendapatkan hasil yang konsisten.
  • Pemilihan satu teratas mengekalkan keseluruhan baris pemenang, tidak seperti MAX + GROUP BY.

Tukar satu nombor sahaja dan pertanyaan yang sama akan menyelesaikan pemilihan satu teratas, lima teratas atau sebarang N.

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 “Baris Teratas-N Setiap Kumpulan Dengan ROW_NUMBER” percuma?

Ya — teks penuh “Baris Teratas-N Setiap Kumpulan Dengan ROW_NUMBER” 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 “Baris Teratas-N Setiap Kumpulan Dengan ROW_NUMBER”?

Corak partition-dan-kedudukan piawai untuk “3 teratas bagi setiap kategori” 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 “Baris Teratas-N Setiap Kumpulan Dengan ROW_NUMBER” 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. Baris Teratas-N Setiap Kumpulan Dengan ROW_NUMBER
  2. Mengendalikan Nilai Seri dalam Teratas-N
  3. Membuang Pendua Baris dengan Selamat
  4. Mengekalkan Baris Terbaharu bagi Setiap Kunci
← Kembali ke Persediaan Temu Duga SQL