Persediaan Temu Duga SQL · Pelajaran

Peristiwa Mengikut Turutan dan Tetingkap Masa

Memastikan langkah berlaku mengikut urutan dan dalam had masa menggunakan fungsi tetingkap.

Pelajaran 2 daripada 413 langkah

Peristiwa Mengikut Turutan dan Tetingkap Masa 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.

Mengapa Turutan dan Masa Penting

Corong asas daripada pelajaran sebelumnya hanya menyemak sama ada pengguna melakukan setiap langkah. Penemuduga yang lebih teliti akan bertanya: adakah langkah-langkah tersebut berlaku mengikut turutan yang betul dan dalam tempoh yang munasabah?

Pengguna yang membuat pembelian pada hari Isnin dan melawat halaman pemasaran pada hari Jumaat tidak menukar melalui corong Anda. Urutan dan masa mengubah corong berasaskan penanda yang naif menjadi corong yang boleh dipercayai.

Idea Cap Masa Pertama Bagi Setiap Pengguna

Untuk menilai turutan, catat masa pertama setiap pengguna pada setiap langkah. Lawatan paling awal, pendaftaran paling awal, pembelian paling awal.

Kemudian, penukaran yang bersih bermaksud first_signup_time >= first_visit_time dan seterusnya mengikut rangkaian tersebut. MIN(event_time) yang dikumpulkan mengikut langkah memberikan titik rujukan itu.

SELECT
  user_id,
  MIN(CASE WHEN event_name = 'visit'    THEN event_time END) AS first_visit,
  MIN(CASE WHEN event_name = 'signup'   THEN event_time END) AS first_signup,
  MIN(CASE WHEN event_name = 'purchase' THEN event_time END) AS first_purchase
FROM events
GROUP BY user_id;

Mewajibkan Langkah Berlaku Mengikut Turutan

Dengan cap masa pertama bagi setiap langkah, penguatkuasaan turutan menjadi suatu perbandingan. Pengguna benar-benar menukar kepada langkah 3 hanya jika setiap cap masa tidak NULL dan meningkat secara monoton.

Perhatikan bahawa cap masa NULL (langkah itu tidak pernah berlaku) secara semula jadi gagal dalam perbandingan, tepat seperti yang Anda mahukan.

WITH t AS (
  SELECT user_id,
    MIN(CASE WHEN event_name='visit'    THEN event_time END) AS visit_t,
    MIN(CASE WHEN event_name='signup'   THEN event_time END) AS signup_t,
    MIN(CASE WHEN event_name='purchase' THEN event_time END) AS purchase_t
  FROM events GROUP BY user_id
)
SELECT COUNT(*) AS converted_in_order
FROM t
WHERE visit_t IS NOT NULL
  AND signup_t  >= visit_t
  AND purchase_t >= signup_t;

Menambahkan Tetingkap Masa

Kebanyakan corong mempunyai tarikh akhir: "menukar dalam masa 7 hari selepas lawatan pertama." Tambahkan had selang antara langkah pertama dengan langkah terakhir.

Aritmetik tarikh berbeza mengikut dialek. Dalam Postgres, Anda boleh menulis visit_t + INTERVAL '7 days'; dalam MySQL, gunakan DATE_ADD(visit_t, INTERVAL 7 DAY). Sentiasa nyatakan dialek yang digunakan.

WITH t AS (
  SELECT user_id,
    MIN(CASE WHEN event_name='visit'    THEN event_time END) AS visit_t,
    MIN(CASE WHEN event_name='purchase' THEN event_time END) AS purchase_t
  FROM events GROUP BY user_id
)
SELECT COUNT(*) AS purchased_within_7d
FROM t
WHERE purchase_t >= visit_t
  AND purchase_t <  visit_t + INTERVAL '7 days';

Mengapa Cap Masa Pertama, Bukan Sebarang Cap Masa

Satu perkara halus dalam temu duga: patutkah tetingkap bermula daripada lawatan pertama pengguna atau lawatan terkini mereka sebelum pendaftaran? Jawapannya bergantung pada soalan produk.

Tetingkap sentuhan pertama mengukur tempoh dari minat awal hingga penukaran.
Tetingkap sentuhan terakhir mengukur pecutan penukaran selepas lawatan terakhir.

Tanya penemuduga maksud yang mereka kehendaki; membuat pilihan secara sengaja menunjukkan pengalaman yang tinggi.

Peristiwa Mengikut Turutan Dengan LEAD

Untuk laluan berbilang langkah yang kompleks, fungsi tetingkap sangat berguna. Susun peristiwa setiap pengguna mengikut masa, kemudian gunakan LEAD untuk melihat peristiwa seterusnya dan mengesahkan bahawa ia ialah langkah seterusnya yang dijangkakan.

Ini mengendalikan laluan yang langkah-langkahnya berselang-seli dengan peristiwa lain yang tidak berkaitan.

SELECT
  user_id,
  event_name,
  event_time,
  LEAD(event_name) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event,
  LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS next_time
FROM events;

Memadankan Langkah Seterusnya Yang Dijangkakan

Bina berdasarkan LEAD: kekalkan baris apabila 'lawatan' diikuti serta-merta oleh 'pendaftaran'. Ini mencari peralihan berurutan yang sebenar, bukan sekadar kemunculan bersama.

Anda boleh merangkaikan semakan peralihan ini untuk mengesahkan keseluruhan laluan mengikut turutan, langkah demi langkah.

WITH seq AS (
  SELECT user_id, event_name, event_time,
    LEAD(event_name) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event
  FROM events
)
SELECT COUNT(DISTINCT user_id) AS visit_then_signup
FROM seq
WHERE event_name = 'visit' AND next_event = 'signup';

Masa Antara Langkah Berturutan

Penemuduga gemar bertanya "berapa lama masa yang diperlukan bagi setiap langkah?" Gunakan LEAD pada cap masa dan lakukan penolakan. Perbezaan antara peristiwa berturutan ialah tempoh pengguna berada pada peringkat tersebut.

Agregatkan median atau purata bagi setiap peralihan untuk mencari peringkat corong yang paling lambat.

WITH seq AS (
  SELECT user_id, event_name, event_time,
    LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS next_time
  FROM events
)
SELECT
  event_name,
  AVG(EXTRACT(EPOCH FROM (next_time - event_time)) / 3600.0) AS avg_hours_to_next
FROM seq
WHERE next_time IS NOT NULL
GROUP BY event_name;

Kes Tepi Cap Masa Yang Sama

Bagaimana jika dua peristiwa berkongsi event_time yang sama tepat? Dalam keadaan itu, signup_t >= visit_t adalah benar walaupun kedua-duanya berlaku serentak, dan penyusunan berdasarkan masa sahaja menjadi kabur.

Gunakan >= berbanding > secara sengaja dan jelaskan sebabnya.
Tambahkan pemecah seri seperti id urutan peristiwa kepada ORDER BY supaya tetingkap bersifat deterministik.

Menyebut perkara ini tanpa diminta akan mengagumkan penemuduga.

SELECT user_id, event_name,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time, event_id) AS step_seq
FROM events;

Menggabungkan Turutan dan Tetingkap Dalam Satu Pertanyaan

Inilah corong lengkap yang mengikut turutan dan berada dalam tetingkap masa. Ia berasaskan lawatan pertama, memerlukan kemunculan pertama setiap langkah seterusnya berlaku selepas langkah sebelumnya, dan mengehadkan keseluruhan laluan kepada 7 hari.

Inilah jawapan yang membezakan calon yang memahami corong daripada calon yang hanya mengira penanda.

WITH t AS (
  SELECT user_id,
    MIN(CASE WHEN event_name='visit'    THEN event_time END) AS v,
    MIN(CASE WHEN event_name='signup'   THEN event_time END) AS s,
    MIN(CASE WHEN event_name='purchase' THEN event_time END) AS p
  FROM events GROUP BY user_id
)
SELECT
  COUNT(*) FILTER (WHERE v IS NOT NULL)                                   AS visited,
  COUNT(*) FILTER (WHERE s >= v AND s < v + INTERVAL '7 days')            AS signed_up,
  COUNT(*) FILTER (WHERE s >= v AND p >= s AND p < v + INTERVAL '7 days') AS purchased
FROM t;

Nota Merentas Dialek

Dua peringatan tentang kebolehgunaan merentas platform bagi pengekodan langsung:

FILTER (WHERE ...) pada fungsi agregat ialah SQL standard dan berfungsi dalam Postgres; dalam MySQL atau enjin lama, gunakan SUM(CASE WHEN ... THEN 1 ELSE 0 END) sebagai pilihan sandaran.
Sintaks selang berbeza: Postgres + INTERVAL '7 days', MySQL DATE_ADD(d, INTERVAL 7 DAY), SQL Server DATEADD(day, 7, d).

Nyatakan andaian Anda, dan penemuduga biasanya tidak kisah dialek yang digunakan, asalkan Anda tahu bahawa dialek-dialek tersebut berbeza.

Semakan Pantas

Anda mesti mengira pengguna yang melengkapkan lawatan -> pendaftaran -> pembelian mengikut urutan, dalam tempoh 7 hari daripada lawatan pertama. Pendekatan manakah yang betul?

Jawab dalam pelajaran interaktif

Imbas Kembali: Peristiwa Mengikut Urutan dan Tetingkap Masa

Intipati utama:

Rekod cap masa pertama bagi setiap langkah setiap pengguna dengan MIN(CASE ...).
Pastikan urutan dengan mewajibkan masa setiap langkah berada pada atau selepas masa langkah sebelumnya.
Hadkan laluan dengan selang, dan nyatakan sintaks dialek anda.
Gunakan LEAD/LAG untuk menyemak peralihan dan tempoh antara langkah.
Tangani seri dengan cap masa yang sama menggunakan pemecah seri dalam ORDER BY.

Seterusnya: beralih daripada corong kepada eksperimen dan mengira metrik bagi setiap varian.

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 “Peristiwa Mengikut Turutan dan Tetingkap Masa” percuma?

Ya — teks penuh “Peristiwa Mengikut Turutan dan Tetingkap Masa” 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 “Peristiwa Mengikut Turutan dan Tetingkap Masa”?

Memastikan langkah berlaku mengikut urutan dan dalam had masa menggunakan fungsi tetingkap. 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 “Peristiwa Mengikut Turutan dan Tetingkap Masa” 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

Membina Corong Berbilang Langkah
Peristiwa Mengikut Turutan dan Tetingkap Masa
Penetapan Ujian A/B dan Metrik
Peningkatan, Keberertian dan Pengawal dalam SQL

← Kembali ke Persediaan Temu Duga SQL