Persediaan Temu Duga SQL · Pelajaran

Helah Perbezaan Nombor Baris

Tolak ROW_NUMBER daripada jujukan untuk mengumpulkan nilai berturutan menjadi pulau

Pelajaran 2 daripada 413 langkah

Helah Perbezaan Nombor Baris ialah pelajaran Persediaan Temu Duga SQL percuma di CoddyKit. Ini ialah pelajaran 2 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.

Kunci Pulau Paling Elegan

Helah perbezaan nombor baris ialah teknik yang paling ingin dilihat oleh penemuduga untuk pulau nombor bulat atau tarikh berturutan. Teknik ini menghasilkan kunci kumpulan melalui satu operasi penolakan sahaja, tanpa LAG dan tanpa memerlukan jumlah terkumpul.

Idea keseluruhannya ialah menolak ROW_NUMBER daripada nilai itu sendiri. Bagi mana-mana rentetan nilai berturutan, kedua-dua nilai dan nombor baris meningkat tepat 1 pada setiap langkah, maka perbezaannya kekal sama sepanjang rentetan itu. Nilai tetap tersebut ialah kunci pulau anda.

Mengapa Perbezaan Kekal Sama

Fikirkan dua baris bersebelahan dalam rentetan berturutan. Apabila bergerak dari satu baris ke baris seterusnya, nilai meningkat sebanyak 1 dan nombor baris juga meningkat sebanyak 1. Tolakkan kedua-duanya dan nilai +1 itu saling menghapuskan, maka value - row_number tidak berubah.

Namun, sebaik sahaja terdapat jurang, nilai melonjak lebih daripada 1 sedangkan nombor baris hanya meningkat sebanyak 1. Perbezaannya berubah kepada nilai tetap yang baharu. Perubahan itulah yang memisahkan satu pulau daripada pulau seterusnya.

Melihatnya pada Data Kita

Ingat kembali hari log masuk 1, 2, 3, 7, 8, 10. Mari kita susun nombor baris dan perbezaannya bersebelahan:

hari 1, rn 1, diff 0
hari 2, rn 2, diff 0
hari 3, rn 3, diff 0
hari 7, rn 4, diff 3
hari 8, rn 5, diff 3
hari 10, rn 6, diff 4

Perbezaan (0,0,0,3,3,4) membahagikan baris dengan sempurna kepada tiga pulau. Perbezaan yang sama bermaksud pulau yang sama.

SELECT
  day_no,
  ROW_NUMBER() OVER (ORDER BY day_no) AS rn,
  day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
FROM logins
ORDER BY day_no;

Meringkaskan Menjadi Pulau

Dengan perbezaan sebagai kunci kumpulan, pertanyaan akhir ialah ringkasan standard. Bungkus perbezaan itu dalam CTE dan gunakan GROUP BY padanya:

Hasilnya ialah tiga pulau yang sama seperti sebelumnya, tetapi SQL ini lebih pendek dan jelas berbanding versi LAG ditambah jumlah terkumpul. Bagi jujukan nombor bulat atau jujukan yang meningkat secara sekata, inilah jawapan yang patut digunakan terlebih dahulu.

WITH keyed AS (
  SELECT
    day_no,
    day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
  FROM logins
)
SELECT
  MIN(day_no) AS start_day,
  MAX(day_no) AS end_day,
  COUNT(*)    AS length
FROM keyed
GROUP BY grp
ORDER BY start_day;

Perkara Penting: Nilai Mesti Bertambah Satu Setiap Langkah

Helah perbezaan mudah mengandaikan jujukan meningkat tepat 1 pada setiap langkah. Hal ini benar untuk integer tanpa jurang dan hari kalendar berturutan, tetapi tidak berhasil jika nilai bertambah dengan jumlah tetap yang lain atau jika terdapat pendua.

Nilai genap 2,4,6,8 akan kelihatan seperti jurang apabila menggunakan penolakan nilai dengan nombor baris.
Nilai pendua mengganggu penjajaran kerana nombor baris terus meningkat sedangkan nilai tidak berubah.

Memahami batasan ini dan cara membaikinya membezakan helah yang dihafal daripada pemahaman sebenar.

Membaiki Jujukan Dengan Langkah Tetap

Jika nilai bertambah dengan pemalar k yang diketahui dan bukannya 1, normalkan nilai terlebih dahulu: bahagikan nilai dengan k (atau gunakan value / k untuk nombor bulat) supaya setiap langkah kembali menjadi 1, kemudian tolak nombor baris.

Contohnya, untuk nombor genap yang bertambah sebanyak 2, gunakan day_no / 2 - ROW_NUMBER(). Nilai yang telah dinormalkan kini meningkat sebanyak 1 bagi setiap item berturutan, lalu memulihkan sifat perbezaan malar.

SELECT
  val,
  (val / 2) - ROW_NUMBER() OVER (ORDER BY val) AS grp
FROM even_series
ORDER BY val;

Menggunakannya Pada Tarikh

Tarikh ialah bentuk sebenar yang paling biasa. Tarikh kalendar tidak boleh ditolak terus dengan nombor baris, jadi tukarkan tarikh kepada bilangan hari terlebih dahulu. Dalam Postgres, tolak satu tarikh rujukan tetap untuk mendapatkan bilangan hari dalam bentuk nombor bulat, kemudian gunakan helah yang sama.

Memandangkan hari kalendar berturutan berbeza sebanyak 1, perbezaan antara bilangan hari dengan nombor baris sekali lagi menjadi malar dalam setiap segmen.

WITH keyed AS (
  SELECT
    login_date,
    (login_date - DATE '2000-01-01')
      - ROW_NUMBER() OVER (ORDER BY login_date) AS grp
  FROM daily_logins
)
SELECT MIN(login_date) AS start_date,
       MAX(login_date) AS end_date,
       COUNT(*)        AS days_in_run
FROM keyed GROUP BY grp ORDER BY start_date;

Perbezaan Tarikh Merentas Dialek

Langkah menukar tarikh kepada nombor bulat berbeza mengikut enjin, dan penemu duga menghargai pengetahuan merentas dialek:

Postgres: tolak literal tarikh: login_date - DATE '2000-01-01' menghasilkan nombor bulat.
MySQL: gunakan DATEDIFF(login_date, '2000-01-01').
SQL Server: gunakan DATEDIFF(day, '2000-01-01', login_date).

Pada sesetengah enjin, cara yang lebih kemas ialah menolak ROW_NUMBER hari terus daripada tarikh menggunakan aritmetik selang masa, kemudian GROUP BY tarikh rujukan yang terhasil.

SELECT
  login_date,
  login_date - (ROW_NUMBER() OVER (ORDER BY login_date)
               * INTERVAL '1 day') AS grp_date
FROM daily_logins;

Menambah Pembahagian Mengikut Kumpulan

Untuk segmen mengikut pengguna, bahagikan nombor baris berdasarkan lajur kumpulan. Yang penting, kunci kumpulan juga mesti merangkumi lajur pembahagian kerana dua pengguna yang berbeza boleh menghasilkan nilai perbezaan yang sama secara kebetulan.

Jadi, gunakan GROUP BY pada kedua-dua user_id dan perbezaan yang dikira. Terlupa memasukkan user_id dalam GROUP BY terakhir ialah pepijat halus yang gemar dikesan oleh penemu duga.

WITH keyed AS (
  SELECT user_id, day_no,
    day_no - ROW_NUMBER()
      OVER (PARTITION BY user_id ORDER BY day_no) AS grp
  FROM logins
)
SELECT user_id, MIN(day_no) AS start_day,
       MAX(day_no) AS end_day, COUNT(*) AS len
FROM keyed
GROUP BY user_id, grp
ORDER BY user_id, start_day;

Helah Berbanding LAG: Mana Yang Patut Digunakan

Kini terdapat dua teknik kukuh dalam himpunan alat Anda. Buat pilihan dengan sengaja:

Perbezaan nombor baris: paling ringkas dan kemas untuk rentetan nilai yang langkahnya seragam (integer padat, tarikh berturutan). Pilihan pertama apabila bersebelahan bermaksud 'berbeza dengan pemalar'.
LAG ditambah jumlah terkumpul: lebih fleksibel apabila hubungan bersebelahan bukan langkah berangka tetap, contohnya 'status yang sama seperti baris sebelumnya' atau peraturan tersuai yang tidak seragam.

Nyatakan pilihan Anda dan sebabnya dalam temu duga; penaakulan lebih mengagumkan daripada sintaks.

Mengendalikan Pendua Dengan Berhati-hati

Jika nilai boleh berulang dan Anda masih mahukan satu segmen bagi setiap rentetan berturutan, buang pendua terlebih dahulu menggunakan DISTINCT atau langkah pengelompokan supaya nombor baris sejajar satu dengan satu dengan nilai. Sebagai alternatif, gunakan DENSE_RANK dan bukannya ROW_NUMBER supaya nilai yang sama berkongsi pangkat.

Sentiasa tanyakan kepada penemu duga sama ada pendua boleh berlaku; langkah perlindungan yang betul bergantung pada sama ada pendua patut memanjangkan atau diabaikan dalam sesuatu rentetan.

WITH d AS (SELECT DISTINCT day_no FROM logins)
SELECT day_no,
  day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
FROM d;

Semakan Pantas

Pastikan Anda memahami sebab helah ini berhasil.

Jawab dalam pelajaran interaktif

Imbas Kembali: Helah Perbezaan

Kini Anda memiliki kunci segmen yang paling kemas:

Formula kunci: value - ROW_NUMBER() OVER (ORDER BY value) adalah malar bagi setiap rentetan berturutan.
Runtuhkan dengan GROUP BY pada perbezaan tersebut untuk mendapatkan permulaan, penghujung dan panjang.
Untuk jujukan dengan langkah tetap, lakukan penormalan (bahagikan dengan langkah) terlebih dahulu.
Untuk tarikh, tukarkan kepada bilangan hari dalam bentuk nombor bulat melalui fungsi perbezaan mengikut dialek.
Mengikut kumpulan: gunakan PARTITION BY pada nombor baris dan sertakan lajur kumpulan dalam GROUP BY terakhir.
Lindungi daripada pendua menggunakan DISTINCT atau DENSE_RANK.

Seterusnya, kita beralih daripada segmen kepada ruang kosong: mencari jurang.

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 percuma Lihat kursus penuh

Soalan Lazim

Adakah pelajaran “Helah Perbezaan Nombor Baris” percuma?

Ya — teks penuh “Helah Perbezaan Nombor Baris” 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 “Helah Perbezaan Nombor Baris”?

Tolak ROW_NUMBER daripada jujukan untuk mengumpulkan nilai berturutan menjadi pulau 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 2 daripada 4.

Berapa lamakah pelajaran “Helah Perbezaan Nombor Baris” 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

Mengenal Pasti Masalah Jurang dan Pulau
Helah Perbezaan Nombor Baris
Mencari Jurang dalam Jujukan
Pulau Dengan Perubahan Tarikh dan Status

← Kembali ke Persediaan Temu Duga SQL