Persediaan Temu Duga Pengaturcaraan · Pelajaran

Pulau Dengan Perubahan Tarikh dan Status

Kumpulkan tempoh berturutan dengan status yang sama, iaitu soalan lazim tentang keadaan langganan

Pelajaran 4 daripada 413 langkah

Pulau Dengan Perubahan Tarikh dan Status 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.

Segmen Yang Ditakrifkan Oleh Nilai Yang Berubah

Varian jurang dan segmen yang paling berkaitan dengan perniagaan mengumpulkan baris berturutan yang berkongsi status yang sama, lalu memadatkan log peristiwa yang bising menjadi tempoh keadaan yang kemas. Soalan klasiknya: "Diberikan log peristiwa langganan, kembalikan satu baris bagi setiap tempoh berterusan pengguna berada dalam setiap status."

Di sini, bersebelahan tidak bermaksud 'nilai berbeza sebanyak 1'. Sebaliknya, ia bermaksud status tidak berubah daripada baris sebelumnya. Segmen baharu bermula sebaik sahaja status bertukar. Inilah keadaan teknik berasaskan LAG lebih menonjol berbanding helah nombor baris yang tulen.

Contoh Langganan

Pertimbangkan jadual sub_events untuk seorang pengguna, disusun mengikut tarikh:

2026-01-01 aktif
2026-02-01 aktif
2026-03-01 dijeda
2026-04-01 aktif
2026-05-01 aktif

Output yang dikehendaki ialah tiga tempoh status: aktif Jan-Feb, dijeda Mac, aktif Apr-Mei. Perhatikan bahawa dua rentetan aktif itu ialah segmen yang berasingan kerana tempoh dijeda menyelangi kedua-duanya. Status yang sama tetapi tidak berturutan bermaksud segmen yang berbeza.

CREATE TABLE sub_events (
  user_id INT, status TEXT, event_date DATE
);
INSERT INTO sub_events VALUES
 (1,'active','2026-01-01'),(1,'active','2026-02-01'),
 (1,'paused','2026-03-01'),(1,'active','2026-04-01'),
 (1,'active','2026-05-01');

Tandakan Tempat Status Berubah

Gunakan LAG untuk membandingkan status setiap baris dengan baris sebelumnya. Apabila status berbeza (atau nilai sebelumnya ialah NULL bagi baris pertama), segmen baharu bermula. Hasilkan 1 untuk perubahan dan 0 jika tiada perubahan.

Susun dengan ketat mengikut tarikh dalam kalangan pengguna tersebut. Untuk data kita, penanda perubahan ialah 1,0,1,1,0, yang menandakan tiga sempadan tempoh.

SELECT
  user_id, status, event_date,
  CASE
    WHEN status = LAG(status)
      OVER (PARTITION BY user_id ORDER BY event_date)
    THEN 0 ELSE 1
  END AS is_change
FROM sub_events;

Jadikan Jumlah Terkumpul Sebagai Kunci Tempoh

Seperti sebelum ini, jumlah terkumpul penanda perubahan menghasilkan kunci kumpulan yang malar dalam setiap tempoh status: 1,1,2,3,3 untuk baris kita. Setiap kunci yang berbeza ialah satu tempoh berterusan.

Helah perbezaan nombor baris tidak akan berhasil di sini kerana status bukan nombor yang bertambah sebanyak 1; resipi LAG ditambah jumlah terkumpul ialah alat yang tepat apabila bersebelahan bermaksud 'nilai tidak berubah'.

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS is_change
  FROM sub_events
)
SELECT user_id, status, event_date,
  SUM(is_change)
    OVER (PARTITION BY user_id ORDER BY event_date) AS grp
FROM flagged;

Memadatkan Kepada Tempoh Status

Sekarang gunakan GROUP BY pada user_id, status dan kunci jumlah terkumpul untuk melaporkan rentang setiap tempoh. Menyertakan status dalam GROUP BY adalah selamat kerana status itu malar dalam sesuatu tempoh, dan hal ini membolehkan Anda memilihnya tanpa fungsi agregat.

Hasilnya tepat tiga baris: aktif 01-01 hingga 02-01, dijeda 03-01 hingga 03-01, aktif 04-01 hingga 05-01.

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
),
keyed AS (
  SELECT user_id, status, event_date,
    SUM(chg) OVER (PARTITION BY user_id ORDER BY event_date) AS grp
  FROM flagged
)
SELECT user_id, status,
  MIN(event_date) AS period_start,
  MAX(event_date) AS period_end
FROM keyed
GROUP BY user_id, status, grp
ORDER BY user_id, period_start;

Daripada Peristiwa kepada Selang Separuh Terbuka

Perkara halus dalam temu duga: tarikh peristiwa menandakan masa sesuatu status bermula, dan tempoh itu benar-benar berakhir apabila status seterusnya bermula, bukannya pada tarikh peristiwa terakhir yang mempunyai status sama. Hujung tempoh yang betul selalunya ialah permulaan tempoh seterusnya, dimodelkan sebagai selang separuh terbuka [mula, mula_seterusnya).

Kira permulaan tempoh seterusnya dengan LEAD merentasi tempoh yang telah diringkaskan, dan biarkan tempoh akhir tanpa penghujung (NULL atau 'semasa').

WITH periods AS (
  -- output of the previous collapse step
  SELECT user_id, status, period_start FROM collapsed
)
SELECT user_id, status, period_start,
  LEAD(period_start)
    OVER (PARTITION BY user_id ORDER BY period_start)
    AS period_end_exclusive
FROM periods;

Mengendalikan Status Berulang Berturut-turut

Bagaimana jika catatan mempunyai baris berlebihan seperti aktif, aktif, aktif tanpa perubahan antaranya? Penanda perubahan ialah 0 bagi pengulangan, jadi jumlah terkumpul mengekalkan semuanya dalam satu pulau secara automatik. Itulah tingkah laku yang dikehendaki: status yang sama secara berturutan diringkaskan menjadi satu tempoh.

Penghapusan pendua secara semula jadi bagi pengulangan ialah kelebihan utama kaedah penanda perubahan dan wajar dinyatakan kepada penemuduga.

Apabila Jurang Masa Patut Memutuskan Tempoh

Kadangkala, “status yang sama” tidak mencukupi; jurang masa yang besar juga patut memutuskan tempoh walaupun statusnya sama. Contohnya, status aktif pada Januari kemudian aktif lagi selepas enam bulan tanpa aktiviti mungkin dikira sebagai dua tempoh.

Luaskan penanda perubahan dengan syarat kedua: mulakan pulau baharu apabila status berubah atau masa sejak peristiwa sebelumnya melebihi ambang. Ini menggabungkan kedua-dua peraturan kesinambungan dengan kemas.

CASE
  WHEN status = LAG(status)
         OVER (PARTITION BY user_id ORDER BY event_date)
   AND event_date - LAG(event_date)
         OVER (PARTITION BY user_id ORDER BY event_date) <= 31
  THEN 0 ELSE 1
END AS is_change

Mengira Pertukaran Status yang Unik

Susulan semula jadi: “Berapa kali pengguna ini menukar status?” Itu hanyalah kiraan penanda perubahan ditolak penanda pertama (yang menandakan keadaan awal, bukannya pertukaran).

Secara setara, bilangan tempoh ditolak 1. Kunci jumlah terkumpul sudah mengekodkan perkara ini, jadi jawapannya terhasil terus daripada mekanisme yang sama yang anda bina untuk tempoh tersebut.

WITH flagged AS (
  SELECT user_id,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
)
SELECT user_id, SUM(chg) - 1 AS status_switches
FROM flagged GROUP BY user_id;

Mengapa Kaedah Ini Mengatasi Cantuman Kendiri di Sini

Penyelesaian cantuman kendiri untuk tempoh status perlu memadankan setiap baris dengan jirannya, mengesan perubahan, kemudian menyambungkan sempadan, suatu proses berbilang langkah yang mudah menghasilkan ralat dan sukar mengendalikan tiga tempoh atau lebih.

Aliran LAG-penanda-jumlah terkumpul-GROUP BY mengendalikan sebarang bilangan tempoh dalam satu laluan tanpa cantuman. Menghuraikan perbezaan ini, iaitu laluan tunggal linear berbanding cantuman kendiri kuadratik, ialah penaakulan peringkat kanan yang sememangnya dihargai oleh penemuduga.

Templat yang Boleh Digunakan Semula

Hafalkan templat empat klausa ini; ia menyelesaikan seluruh keluarga pulau status dengan hanya menukar ujian kesinambungan dalam CASE:

penanda: CASE dengan LAG untuk mengesan pulau baharu.
kunci: jumlah terkumpul penanda, dibahagikan dan disusun.
ringkasan: GROUP BY lajur pembahagian, status dan kunci.
selang (pilihan): LEAD untuk hujung tempoh separuh terbuka.

Rangka asas yang sama mengendalikan nombor bulat berturutan, tarikh dan status; hanya syarat CASE yang berubah.

Semakan Pantas

Sahkan bahawa anda memahami peraturan pengelompokan pulau status.

Jawab dalam pelajaran interaktif

Imbas Kembali: Pulau Status dan Tarikh

Anda kini boleh menyelesaikan variasi jurang dan pulau yang paling lengkap:

Kesinambungan = status tidak berubah daripada baris sebelumnya; penanda berubah dengan LAG.
Jadikan penanda perubahan sebagai jumlah terkumpul untuk membentuk kunci kumpulan bagi setiap tempoh.
Ringkaskan dengan GROUP BY user_id, status, key untuk mendapatkan julat tempoh.
Gunakan LEAD untuk hujung selang separuh terbuka; luaskan penanda supaya jurang masa yang besar turut memutuskan tempoh.
Baris berulang yang sama diringkaskan secara automatik; kiraan pertukaran terhasil daripada penanda yang sama.
Satu templat boleh guna semula merangkumi nombor bulat, tarikh dan status; hanya CASE yang berubah.

Dengan itu, tamatlah kursus jurang dan pulau, satu petunjuk kukuh tahap kanan dalam temu duga SQL.

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

Soalan Lazim

Adakah pelajaran “Pulau Dengan Perubahan Tarikh dan Status” percuma?

Ya — teks penuh “Pulau Dengan Perubahan Tarikh dan Status” 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 “Pulau Dengan Perubahan Tarikh dan Status”?

Kumpulkan tempoh berturutan dengan status yang sama, iaitu soalan lazim tentang keadaan langganan 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 “Pulau Dengan Perubahan Tarikh dan Status” 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

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 Pengaturcaraan