PricingLoginStart free
Persediaan Temu Duga Pengaturcaraan · Pelajaran

Menapis Keputusan Tetingkap

Mengapa anda mesti membungkus fungsi tetingkap dalam subpertanyaan atau CTE untuk menapisnya

Pelajaran 4 daripada 413 langkah

Menapis Keputusan Tetingkap ialah pelajaran Persediaan Temu Duga Pengaturcaraan percuma di CoddyKit. Ini ialah pelajaran 4 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 Pengaturcaraan, dan kemajuan anda disegerakkan merentas web serta aplikasi CoddyKit. Kursus Persediaan Temu Duga Pengaturcaraan merangkumi sejumlah 4 pelajaran.

Mengapa Anda Tidak Boleh Menapis Tetingkap dalam WHERE

Satu "perangkap" temu duga yang biasa: menulis WHERE ROW_NUMBER() OVER (...) = 1 akan menghasilkan ralat. Fungsi tetingkap tidak dibenarkan dalam WHERE, GROUP BY atau HAVING.

Sebabnya ialah urutan pelaksanaan secara logik. WHERE dijalankan untuk memilih baris sebelum fungsi tetingkap dinilai. Tetingkap itu bahkan belum dikira, jadi ia tidak boleh dirujuk dalam penapis.

Penjelasan Urutan Pelaksanaan

Fungsi tetingkap dikira dalam fasa khusus yang berlaku selepas FROM, WHERE, GROUP BY dan HAVING, tetapi sebelum ORDER BY dan LIMIT terakhir.

Jadi, ketika WHERE dijalankan, kedudukan atau nombor baris masih belum wujud. Untuk menapis berdasarkan nilai tersebut, anda perlu membiarkan tetingkap selesai dahulu, kemudian menapis lajur yang terhasil dalam lapisan pertanyaan luar.

Corak Pembungkus Subpertanyaan

Pembaikan standard: kira fungsi tetingkap dalam pertanyaan dalam (jadual terbitan), berikan nama alias kepada hasilnya, kemudian tapis alias tersebut dalam WHERE luar.

Jadual terbitan mesti mempunyai alias (t di sini) — penemuduga biasanya menyedari calon yang terlupa memberikannya. Kini rn ialah lajur biasa yang boleh dibandingkan oleh pertanyaan luar.

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

Corak CTE (Selalunya Lebih Kemas)

Ungkapan Jadual Umum menjalankan tugas yang sama dengan struktur yang lebih mudah dibaca. Takrifkan penentuan kedudukan dalam langkah WITH, kemudian tapisnya dalam pertanyaan utama.

Dari segi fungsi, kaedah ini sama seperti subpertanyaan, tetapi penemuduga biasanya lebih menggemari CTE dalam pengekodan secara langsung kerana tujuannya boleh dibaca dari atas ke bawah.

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 = 1;

Contoh Berpandu: N Teratas Bagi Setiap Kumpulan

Masalah fungsi tetingkap yang paling kerap berlaku: "3 pekerja bergaji tertinggi bagi setiap jabatan." Tentukan kedudukan dalam CTE, kemudian kekalkan rn <= 3 di luar.

Pilih fungsi penentuan kedudukan berdasarkan cara nilai yang sama perlu dikendalikan: ROW_NUMBER mengehadkan kepada tepat 3 baris bagi setiap jabatan; tukar kepada RANK/DENSE_RANK jika nilai yang sama pada sempadan perlu disertakan.

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

Contoh Berpandu: Menapis Jumlah Terkumpul

Corak pembungkus bukan hanya untuk kedudukan. Sebarang hasil tetingkap — jumlah terkumpul, purata bergerak, perbezaan LAG — mesti ditapis dengan cara yang sama.

Di sini, kami mengira baki terkumpul, kemudian mengekalkan hanya baris yang nilainya melebihi 1000 buat kali pertama. Penapis berada di luar lapisan tetingkap.

WITH balances AS (
  SELECT
    account_id, txn_date, amount,
    SUM(amount) OVER (
      PARTITION BY account_id ORDER BY txn_date
    ) AS running_balance
  FROM transactions
)
SELECT *
FROM balances
WHERE running_balance > 1000;

QUALIFY: Jalan Pintas dalam Sesetengah Pangkalan Data

Snowflake, BigQuery, Teradata dan DuckDB menawarkan klausa QUALIFY yang menapis hasil tetingkap secara langsung — tanpa memerlukan pembungkus. Klausa ini dijalankan selepas fungsi tetingkap, tepat pada tempat yang diperlukan.

Sebut QUALIFY untuk menunjukkan keluasan pengetahuan, tetapi nyatakan bahawa ia bukan SQL standard dan tidak tersedia dalam PostgreSQL, MySQL dan SQL Server, yang masih memerlukan pembungkus subpertanyaan/CTE.

-- Snowflake / BigQuery only:
SELECT department, name, salary
FROM employees
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY department ORDER BY salary DESC
) = 1;

Jangan Kelirukan HAVING dengan Penapisan Tetingkap

Calon kadangkala cuba menggunakan HAVING untuk menapis kedudukan. HAVING menapis kumpulan selepas pengagregatan GROUP BY dan masih dijalankan sebelum fungsi tetingkap, jadi ia juga tidak boleh merujuk kepada lajur tetingkap.

  • WHERE → menapis baris sebelum pengumpulan dan sebelum tetingkap.
  • HAVING → menapis kumpulan terkumpul, masih sebelum tetingkap.
  • Menapis tetingkap → memerlukan pertanyaan luar (atau QUALIFY).

Menggabungkan Penapis Awal dengan Penapis Tetingkap

Selalunya anda menapis sebelum dan selepas tetingkap. Gunakan penapis baris biasa dalam WHERE dalam pertanyaan dalam supaya tetingkap hanya melihat baris yang berkaitan, kemudian tapis hasil tetingkap dalam pertanyaan luar.

Dalam contoh ini, kami mula-mula mengehadkan data kepada pekerja aktif, kemudian memilih pekerja bergaji tertinggi bagi setiap jabatan dalam kalangan mereka. Meletakkan WHERE active di dalam mengubah baris yang akan ditentukan kedudukannya.

WITH ranked AS (
  SELECT department, name, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department ORDER BY salary DESC
         ) AS rn
  FROM employees
  WHERE is_active = true        -- pre-filter before ranking
)
SELECT * FROM ranked
WHERE rn = 1;                   -- post-filter on the window

Nota Prestasi

Penemuduga mungkin bertanya sama ada pembungkus menjejaskan prestasi. Biasanya tidak: pengoptimum menganggap subpertanyaan/CTE sebagai sebahagian daripada satu pelan dan mengira tetingkap sekali sahaja. Tiada imbasan tambahan hanya kerana anda membungkusnya.

Satu peringatan: dalam sesetengah enjin, CTE boleh menjadi penghadang pengoptimuman (dimaterialkan), jadi bagi laluan kritikal, jadual terbitan atau QUALIFY mungkin menghasilkan pelan yang lebih baik. Analisis dengan EXPLAIN jika perkara ini penting.

Kesilapan Lazim

Senarai semak terakhir:

  • Jangan sekali-kali meletakkan fungsi tetingkap dalam WHERE/HAVING — ia akan menghasilkan ralat.
  • Sentiasa berikan alias kepada jadual terbitan; subpertanyaan tanpa nama dalam FROM akan ditolak.
  • Pilih fungsi penentuan kedudukan berdasarkan cara nilai yang sama perlu dikendalikan oleh soalan tersebut.
  • Gunakan QUALIFY hanya apabila disokong; jika tidak, kembali kepada pembungkus CTE/subpertanyaan.

Semakan Ringkas

Mengapa penapisan fungsi tetingkap memerlukan pembungkus?

Jawab dalam pelajaran interaktif

Imbas Kembali: Menapis Hasil Tetingkap

Anda telah melengkapkan pemahaman tentang fungsi tetingkap penentuan kedudukan:

  • Fungsi tetingkap dijalankan selepas WHERE/GROUP BY/HAVING, jadi anda tidak boleh menapisnya di situ.
  • Balut fungsi tetingkap dalam subpertanyaan atau CTE (sentiasa diberikan alias), kemudian tapis hasilnya dalam pertanyaan luar.
  • Kaedah ini menyokong N teratas bagi setiap kumpulan, baris terkini bagi setiap kunci dan ambang jumlah terkumpul.
  • QUALIFY ialah jalan pintas bukan standard yang berguna, hanya dalam Snowflake/BigQuery.

Anda kini mempunyai set alat penentuan kedudukan lengkap yang paling kerap diuji oleh penemuduga.

Percuma untuk bermula

Pelajari Persediaan Temu Duga Pengaturcaraan 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
90
Pelajaran
360
Mula belajar secara percumaLihat kursus penuh

Soalan Lazim

Adakah pelajaran “Menapis Keputusan Tetingkap” percuma?

Ya — teks penuh “Menapis Keputusan Tetingkap” 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 Pengaturcaraan, tingkat taraf kepada CoddyKit PRO. Kursus Persediaan Temu Duga Pengaturcaraan merangkumi sejumlah 4 pelajaran.

Apakah yang akan saya pelajari dalam “Menapis Keputusan Tetingkap”?

Mengapa anda mesti membungkus fungsi tetingkap dalam subpertanyaan atau CTE untuk menapisnya Anda berlatih Persediaan Temu Duga Pengaturcaraan 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 Pengaturcaraan?

Tiada pengalaman terdahulu diperlukan. Pembelajaran Persediaan Temu Duga Pengaturcaraan 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 4 daripada 4.

Berapa lamakah pelajaran “Menapis Keputusan Tetingkap” 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 Pengaturcaraan ini?

Ya. Setiap pelajaran Persediaan Temu Duga Pengaturcaraan 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. OVER, PARTITION BY dan ORDER BY
  2. ROW_NUMBER untuk Penjujukan Unik
  3. RANK berbanding DENSE_RANK apabila Seri
  4. Menapis Keputusan Tetingkap
← Kembali ke Persediaan Temu Duga Pengaturcaraan