SQL Interview Prep · Pelajaran

OVER, PARTITION BY, dan ORDER BY

Anatomi spesifikasi jendela dan cara partisi mengatur ulang perhitungan.

OVER, PARTITION BY, dan ORDER BY adalah pelajaran SQL Interview Prep gratis di CoddyKit. Ini adalah pelajaran 1 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.

Mengapa Pewawancara Memilih Fungsi Jendela

Fungsi jendela melakukan perhitungan pada sekumpulan baris yang berkaitan dengan baris saat ini, tanpa menggabungkan baris-baris tersebut seperti yang dilakukan GROUP BY. Sifat inilah yang membuatnya disukai pewawancara: Anda tetap mempertahankan setiap baris detail dan tetap memperoleh agregasi, peringkat, atau total berjalan di sampingnya.

GROUP BY mengembalikan satu baris untuk setiap grup.
Fungsi jendela mengembalikan setiap baris masukan, dengan satu kolom hasil perhitungan tambahan.

Ketika pewawancara berkata "tampilkan setiap karyawan dan rata-rata gaji departemennya pada baris yang sama," mereka sedang menguji apakah Anda memilih fungsi jendela alih-alih self-join.

Anatomi Klausa OVER

Setiap fungsi jendela diikuti klausa OVER (...). Klausa ini memiliki tiga bagian opsional, dan menyebutkannya dengan tepat akan mengesankan pewawancara:

PARTITION BY — membagi baris menjadi beberapa grup; fungsi dimulai ulang pada setiap grup.
ORDER BY — mengurutkan baris di dalam setiap partisi (diperlukan untuk pemeringkatan dan total berjalan).
Bingkai — membatasi baris mana yang digunakan dalam perhitungan (ROWS/RANGE).

OVER () yang kosong memperlakukan seluruh set hasil sebagai satu partisi.

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Jendela vs Agregasi: Fungsi Sama, Hasil Berbeda

Fungsi agregasi yang sama persis berperilaku berbeda ketika digunakan sebagai fungsi jendela. Bandingkan kedua kueri di bawah ini secara konseptual.

AVG(salary) dengan GROUP BY department mengembalikan satu baris untuk setiap departemen.
AVG(salary) OVER (PARTITION BY department) mengembalikan setiap karyawan, dan masing-masing diberi label berupa rata-rata departemennya.

Kiat wawancara: tekankan bahwa versi jendela tidak memerlukan GROUP BY dan tidak menghapus baris detail yang berulang.

-- Aggregate: collapses
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

-- Window: preserves every row
SELECT department, name, AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

PARTITION BY: Mengatur Ulang Perhitungan

PARTITION BY bagi fungsi jendela sama seperti GROUP BY bagi agregat, tetapi tidak meringkas baris menjadi lebih sedikit. Setiap nilai partisi yang berbeda mendapatkan perhitungannya sendiri secara independen.

Dalam contoh ini, nomor baris dimulai kembali dari 1 untuk setiap departemen. Tanpa PARTITION BY, penomoran akan berlanjut terus untuk semua karyawan.

Anda dapat membuat partisi berdasarkan satu kolom atau beberapa kolom.
Tidak adanya PARTITION BY berarti hanya ada satu partisi besar (seluruh kumpulan data).

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

ORDER BY di Dalam OVER

ORDER BY di dalam OVER bukan hal yang sama dengan ORDER BY akhir pada kueri. Klausa ini hanya menentukan urutan baris di dalam setiap partisi yang digunakan fungsi untuk beroperasi.

Fungsi pemeringkatan (ROW_NUMBER, RANK) memerlukannya — fungsi tersebut memerlukan urutan untuk menentukan peringkat.
Agregat biasa atas suatu partisi tidak memerlukannya, kecuali jika Anda menginginkan perhitungan berjalan.

Kesalahan umum saat wawancara adalah mencampuradukkan ORDER BY pada jendela dengan urutan penyajian hasil.

SELECT
  name,
  hire_date,
  ROW_NUMBER() OVER (ORDER BY hire_date) AS seniority_rank
FROM employees
ORDER BY name;  -- output order is independent of the window order

Menggabungkan PARTITION BY dan ORDER BY

Jendela pemeringkatan klasik menggabungkan keduanya: PARTITION BY mengelompokkan, lalu ORDER BY mengurutkan baris di dalam setiap kelompok.

Bacalah spesifikasi di bawah ini sebagai berikut: "Di dalam setiap departemen, urutkan karyawan berdasarkan gaji secara menurun, lalu beri nomor kepada mereka." Orang dengan gaji tertinggi di setiap departemen mendapatkan nomor baris 1.

Spesifikasi tunggal ini menjadi fondasi berbagai masalah wawancara tentang fungsi jendela yang paling umum, termasuk pola N teratas per kelompok.

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

ORDER BY Mengubah Perilaku Agregat

Berikut hal penting yang sering diuji pewawancara: menambahkan ORDER BY ke jendela agregat mengubahnya menjadi perhitungan berjalan, karena bingkai implisit ("dari awal partisi hingga baris saat ini") mulai berlaku.

SUM(x) OVER (PARTITION BY g) → total kelompok yang sama pada setiap baris.
SUM(x) OVER (PARTITION BY g ORDER BY d) → total berjalan hingga baris saat ini.

Memahami bahwa ORDER BY secara implisit menambahkan bingkai membedakan kandidat tingkat menengah dari kandidat pemula.

SELECT
  account_id,
  txn_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY account_id
    ORDER BY txn_date
  ) AS running_balance
FROM transactions;

Tempat Fungsi Jendela Diizinkan

Fungsi jendela hanya dapat muncul dalam daftar SELECT dan klausa ORDER BY. Fungsi tersebut tidak diizinkan dalam WHERE, GROUP BY, atau HAVING.

Alasannya berkaitan dengan urutan eksekusi logis: fungsi jendela dievaluasi setelah WHERE, GROUP BY, dan HAVING dijalankan. Baris sudah dipilih sebelum fungsi jendela melihatnya.

Itulah sebabnya pemfilteran berdasarkan pemeringkatan memerlukan subkueri atau CTE — hal ini akan dibahas secara menyeluruh dalam pelajaran selanjutnya.

-- This FAILS: window function in WHERE
-- SELECT name FROM employees
-- WHERE ROW_NUMBER() OVER (ORDER BY salary) = 1;

-- This works: window in SELECT, filter outside
SELECT * FROM (
  SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn = 1;

Beberapa Fungsi Jendela dalam Satu Kueri

Anda dapat menggunakan beberapa fungsi jendela dalam SELECT yang sama, masing-masing dengan spesifikasi sendiri atau spesifikasi yang sama. Basis data menghitungnya dalam satu lintasan atas data yang telah dipartisi.

Hal ini berguna dalam wawancara ketika Anda memerlukan peringkat dan rata-rata departemen secara bersamaan. Jika dua fungsi menggunakan spesifikasi yang sama, beberapa dialek memungkinkan Anda menamainya dengan klausa WINDOW agar tidak perlu mengulanginya.

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

Contoh Terapan: Gaji vs Rata-rata Departemen

Pertanyaan yang sering diajukan kepada analis: "Tampilkan setiap karyawan beserta gajinya, rata-rata departemennya, dan selisihnya." Satu ekspresi jendela menangani bagian tersulit; aritmetika menyelesaikan sisanya.

Perhatikan bahwa tidak ada GROUP BY dan setiap baris karyawan tetap ada. dept_avg diulang untuk semua orang dalam departemen yang sama, dan itulah yang memungkinkan perbandingan baris demi baris.

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees
ORDER BY department, salary DESC;

Kesalahan Umum yang Diwaspadai Pewawancara

Hindari jebakan berikut ketika fungsi jendela dibahas:

Menempatkan fungsi jendela dalam WHERE atau HAVING — tidak valid; gunakan subkueri.
Melupakan ORDER BY pada fungsi pemeringkatan — hasilnya menjadi tidak dapat ditentukan.
Menganggap PARTITION BY mengurangi jumlah baris — hal itu tidak pernah terjadi.
Mencampuradukkan ORDER BY pada jendela dengan urutan hasil akhir.
Menambahkan ORDER BY ke jendela agregat tanpa menyadari bahwa hasilnya berubah menjadi total berjalan.

Uji Cepat

Ujilah pemahaman Anda tentang spesifikasi jendela.

Rangkuman: Spesifikasi Jendela

Sekarang Anda telah memahami anatomi OVER (...):

Fungsi jendela mempertahankan setiap baris sambil melakukan perhitungan pada baris-baris yang berkaitan.
PARTITION BY mengelompokkan dan memulai ulang perhitungan; fungsi ini tidak pernah menghapus baris.
ORDER BY mengurutkan baris di dalam partisi; fungsi pemeringkatan memerlukannya, dan fungsi ini mengubah agregat menjadi perhitungan berjalan.
Fungsi jendela hanya valid dalam SELECT dan ORDER BY — tidak pernah dalam WHERE/HAVING.

Selanjutnya, Anda akan menetapkan nomor urut yang deterministik dengan ROW_NUMBER.

Pertanyaan yang Sering Diajukan

Apakah pelajaran “OVER, PARTITION BY, dan ORDER BY” gratis?

Ya — teks lengkap “OVER, PARTITION BY, dan ORDER BY” 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 “OVER, PARTITION BY, dan ORDER BY”?

Anatomi spesifikasi jendela dan cara partisi mengatur ulang perhitungan. 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 1 dari 4.

Berapa lama pelajaran “OVER, PARTITION BY, dan ORDER 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 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

← Kembali ke SQL Interview Prep