Temu Duga SQL: Sesi-kan (Sessionize) Peristiwa Pengguna dengan Jurang Ketidakaktifan 30 Minit

Masalah dan Konteks yang Berkenaan

Andaikan jadual peristiwa PostgreSQL ini:

CREATE TABLE events (
  event_id bigint PRIMARY KEY,
  user_id bigint NOT NULL,
  occurred_at timestamptz NOT NULL
);

Susun peristiwa setiap pengguna mengikut occurred_at dan event_id. Peristiwa pertama membuka sesi. Setiap peristiwa terkemudian membuka sesi baharu apabila jurangnya dari peristiwa sebelumnya adalah lebih besar daripada atau sama dengan 30 minit. Kembalikan user_id, session_seq berasaskan-satu, session_start, session_end, event_count, dan session_duration. Jurang tepat 30 minit memulakan sesi baharu; sempadan itu adalah sebahagian daripada kontrak.

Corak ini muncul dalam pemprosesan strim klik (clickstream), analitik produk, dan corong tingkah laku (behavioral funnels). Ini bukan masalah yang sama seperti mencari hari log masuk berturut-turut. Rentetan harian membandingkan tarikh kalendar bersebelahan; sesi-fikasi (sessionization) membandingkan masa yang berlalu antara peristiwa bersebelahan bagi seorang pengguna. Penyelesaian utama mengira hasil yang tepat ke atas set peristiwa yang lengkap dan dinyahduplikasi secara logik. Pertanyaan bersempadan dan pematerian berperingkat (incremental materialization) memerlukan peraturan sempadan tambahan.

Perkara yang Dinilai oleh Penemu Duga

Isyarat pertama ialah sama ada calon menukar prosa kepada kontrak yang boleh dilaksanakan. > 30 minutes dan >= 30 minutes menetapkan peristiwa sempadan secara berbeza. Membandingkan dengan peristiwa sebelumnya dan membandingkan dengan peristiwa pertama dalam sesuatu sesi juga merupakan takrifan yang berbeza. Gesaan ini menggunakan jurang bersebelahan, jadi sesi yang aktif secara berterusan mungkin berlangsung jauh lebih lama daripada 30 minit.

Isyarat kedua ialah penguraian kepada tiga peringkat tetingkap: gunakan LAG() untuk membaca pendahulu, tandakan sempadan sesi, dan jalankan SUM() kumulatif ke atas tanda tersebut. Pengiraan tetingkap yang diperlukan tidak boleh disarangkan sewenang-wenangnya dalam satu ungkapan PostgreSQL. CTE yang berasingan juga mendedahkan setiap hubungan perantaraan untuk pemeriksaan.

Isyarat ketiga ialah susunan deterministik. Dua peristiwa mungkin berkongsi occurred_at yang sama. Menyusun mengikut masa sahaja membiarkan susunan relatifnya tidak ditentukan. Menambah event_id yang unik memberikan LAG() dan jumlah berjalan tertib total yang sama. Masa yang berlalu antara cap masa yang sama adalah sifar, jadi kedua-duanya kekal dalam satu sesi.

Isyarat keempat ialah semantik masa. timestamptz menandakan detik mutlak yang sesuai untuk perbandingan masa yang berlalu. Menukar kepada masa dinding tempatan sebelum menolak boleh menyuntik lompatan waktu jimat siang (daylight-saving). Penukaran zon bernama tergolong pada masa pembentangan, bukan dalam pengiraan sempadan sesi ini.

Akhir sekali, jawapan yang kukuh mengiktiraf bahawa data lewat boleh menulis semula sejarah. Peristiwa yang disisipkan di tengah-tengah garis masa boleh menyambungkan dua sesi yang sebelum ini berasingan. Oleh itu, sistem berperingkat tidak boleh menganggap session_seq sebagai append-only; ia memerlukan pengiraan semula bersempadan, pembetulan berversi, atau tera air (watermark) kemuktamadan yang jelas.

Soalan untuk Dijelaskan Sebelum Menjawab

• Pihak manakah yang memiliki sempadan 30 minit? Gesaan ini memulakan sesi baharu pada >= 30 minutes. Peraturan

produk lebih-besar-secara-ketat (strict-greater-than) mengubah satu pengendali dan semua jangkaan sempadan.

• Adakah kita membandingkan peristiwa bersebelahan atau peristiwa pertama sesi? Peristiwa bersebelahan di sini. Panjang

sesi maksimum memerlukan keadaan berasingan yang berpaut pada permulaan sesi.

• Bagaimanakah pendua dikendalikan? event_id ialah kunci peristiwa logik, jadi penghantaran semula mesti

dinyahduplikasi sebelum pertanyaan ini. Peristiwa berbeza pada pengguna dan cap masa yang sama kekal sebagai baris yang sah.

• Zon masa manakah yang mentakrifkan jurang? Masa yang berlalu antara detik mutlak. Zon masa bernama mengubah

paparan, bukan bilangan saat yang berlalu.

• Adakah pertanyaan dibatasi masa? Sejarah penuh adalah mudah. Julat pelaporan mesti menentukan

sama ada sesi yang bermula sebelum julat kekal utuh dan mesti membaca konteks pendahulu.

• Berapa lewat peristiwa boleh tiba? Pertanyaan ad hoc boleh mengira semula. Hasil termateri memerlukan

horizon pembetulan, tera air, dan protokol kemas kini atau penarikan balik (retraction) hiliran.

• Apakah yang patut dikembalikan oleh jadual kosong atau pengguna satu peristiwa? Baris sifar untuk jadual kosong; satu

sesi berdurasi sifar untuk pengguna satu peristiwa.

Rangka Kerja Jawapan 30 Saat

“Dalam setiap pengguna, saya mencipta susunan deterministik melalui occurred_at, event_id dan menggunakan LAG(occurred_at) untuk mendapatkan pendahulu. Saya menandakan baris pertama dan setiap jurang sekurang-kurangnya 30 minit sebagai 1, dengan semua baris lain ditandakan 0. Hasil tambah kumulatif ke atas bingkai ROWS yang jelas memberikan jujukan sesi berasaskan-satu. Saya kemudian mengumpulkan mengikut pengguna dan jujukan untuk mengira masa mula, tamat, kiraan, dan tempoh. Ujian meliputi 29 minit 59 saat, tepat 30 minit, cap masa terikat, pengguna satu peristiwa, ID pendua, data lewat, dan permulaan julat pelaporan. Untuk pematerian berperingkat, saya mengira semula pengguna yang terjejas di dalam tetingkap kelewatan yang dibenarkan dan bukannya menganggap sesi hanya ditambah (append-only).”

Penyelaman Mendalam Langkah demi Langkah

Mula-mula dapatkan pendahulu dengan satu peraturan susunan yang dikongsi. event_id tidak menjejaskan masa yang berlalu; ia hanya menstabilkan cap masa yang sama:

WITH ordered AS (
  SELECT
    event_id,
    user_id,
    occurred_at,
    LAG(occurred_at) OVER (
      PARTITION BY user_id
      ORDER BY occurred_at, event_id
    ) AS previous_at
  FROM events
),
marked AS (
  SELECT
    event_id,
    user_id,
    occurred_at,
    CASE
      WHEN previous_at IS NULL THEN 1
      WHEN occurred_at - previous_at >= INTERVAL '30 minutes' THEN 1
      ELSE 0
    END AS is_new_session
  FROM ordered
),
sessionized AS (
  SELECT
    event_id,
    user_id,
    occurred_at,
    SUM(is_new_session) OVER (
      PARTITION BY user_id
      ORDER BY occurred_at, event_id
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_seq
  FROM marked
)
SELECT
  user_id,
  session_seq,
  MIN(occurred_at) AS session_start,
  MAX(occurred_at) AS session_end,
  COUNT(*) AS event_count,
  MAX(occurred_at) - MIN(occurred_at) AS session_duration
FROM sessionized
GROUP BY user_id, session_seq
ORDER BY user_id, session_seq;

Bingkai ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW yang jelas adalah penting. Jumlah berjalan mesti menyerap penanda sempadan satu baris fizikal pada satu masa dan bukannya mewarisi semantik rakan sebaya (peer semantics) daripada bingkai tetingkap lalai. event_id yang unik membuang rakan sebaya dalam pertanyaan ini, namun menyatakan bingkai mengunci niat asal dan menghalang penyingkiran pemutus seri pada masa hadapan daripada mengubah tingkah laku secara senyap.

Gunakan peristiwa ini untuk menyemak sempadan:

Pengguna 1 mempunyai dua sesi: dua peristiwa dalam tempoh lima minit, kemudian tiga peristiwa dalam tempoh 35 minit. Jumlah rentang Pengguna 2 ialah 58 minit, namun setiap jurang bersebelahan adalah di bawah 30 minit, jadi ketiga-tiga peristiwa kekal dalam satu sesi. Ini membezakan sesi-fikasi jurang bersebelahan daripada tempoh sesi maksimum.

Ketepatan berpunca daripada satu tak varian yang mudah. Baris pertama untuk pengguna menaikkan jumlah berjalan daripada sifar kepada satu. Selepas itu, hanya baris yang memenuhi predikat sempadan meningkatkan jumlah tersebut; setiap baris bukan sempadan mengekalkannya. Dua baris mempunyai nilai kumulatif yang sama tepat apabila tiada sempadan bertanda yang memisahkan kedua-duanya. Oleh itu, pengelompokan mengikut pengguna dan nilai tersebut menghasilkan setiap segmen bersebelahan maksimum tanpa menggabungkan pengguna atau melintasi sempadan.

Bagi peristiwa N, pelan tetingkap biasanya menyusun mengikut pengguna dan masa, menghasilkan masa O(N log N); imbasan tetingkap dan pengagregatan ialah O(N). Keadaan perantaraan ialah O(N) dan mungkin melimpah (spill). Indeks boleh memadankan susunan logik:

CREATE INDEX events_session_order_idx
  ON events (user_id, occurred_at, event_id);

Indeks tidak menjamin pelan bebas susunan (sort-free). Kos imbasan penuh, keterlihatan, keselarian, dan predikat semuanya mempengaruhi pengoptimum. Periksa imbasan, susunan, I/O sementara, anggaran baris, dan baris sebenar dengan EXPLAIN (ANALYZE, BUFFERS) pada data yang representatif dan bukannya mengisytiharkan kejayaan hanya daripada kehadiran indeks.

Penapisan julat masa ialah perangkap ketepatan yang paling mudah berlaku. Jika pertanyaan bermula pada 10:00 manakala pengguna mempunyai peristiwa pada 09:50 dan 10:10, penapisan terlebih dahulu secara silap menandakan 10:10 sebagai sesi baharu. Jika output hanya memerlukan keahlian di dalam julat, baca sekurang-kurangnya pendahulu terdekat sebelum permulaan bagi setiap pengguna, kemudian kecualikan baris konteks daripada output. Jika output mesti menyertakan permulaan sesi yang lengkap, teruskan membaca ke belakang sehingga mencapai sempadan 30 minit yang tulen.

Data lewat juga boleh menggabungkan sejarah. Peristiwa pada 09:00 dan 09:50 pada mulanya membentuk sesi berasingan. Peristiwa lewat pada 09:25 menukar kedua-dua jurang bersebelahan kepada 25 minit dan menggabungkannya. Pemprosesan kelompok boleh mengira semula partisi yang terjejas. Sistem berperingkat harus mengira semula mengikut pengguna ke atas horizon kelewatan yang dibenarkan dan menerbitkan versi atau penarikan balik. Kontrak mesti menyatakan sama ada data di luar tera air dikuarantin, dibuang, atau dibenarkan untuk mencetuskan pembetulan yang lebih luas.

Sahkan setiap peringkat. Setiap previous_at bukan pertama dalam ordered mesti sama dengan cap masa sebelumnya dalam susunan yang dikongsi. marked mengandungi 1 hanya pada baris pertama dan sempadan ambang. Bagi setiap pengguna, session_seq bermula pada satu, tidak pernah berkurang, dan meningkat paling banyak sebanyak satu. Hubungan akhir mesti mempunyai session_start <= session_end; kiraan peristiwanya mesti berjumlah kepada bilangan peristiwa input logik; dan jurang merentasi sesi bersebelahan mestilah sekurang-kurangnya 30 minit.

Contoh Jawapan Berkualiti Tinggi

“Mula-mula saya akan mengesahkan bahawa sempadan adalah lebih besar daripada atau sama dengan 30 minit dan perbandingan menggunakan peristiwa bersebelahan. event_id yang unik menyahduplikasi peristiwa logik, manakala peristiwa berbeza pada masa yang sama dikekalkan. CTE pertama menyusun setiap pengguna mengikut masa dan ID peristiwa serta menggunakan LAG untuk cap masa sebelumnya. Yang kedua menandakan baris pertama dan setiap jurang ambang. Yang ketiga mengambil hasil tambah berjalan ke atas bingkai ROWS yang jelas untuk mencipta jujukan sesi yang stabil. Saya kemudian mengagregat mengikut pengguna dan jujukan untuk mula, tamat, kiraan, dan tempoh.

Saya akan menguji 29 minit 59 saat dan tepat 30 minit, cap masa yang sama, pengguna satu peristiwa, dan jadual kosong. Saya juga akan menguji pendahulu sebelum julat pelaporan, kerana menapis sebelum LAG mencipta sempadan sesi palsu. Pengisihan mendominasi pada sekitar O(N log N). Indeks pada (user_id, occurred_at, event_id) mungkin menyediakan susunan yang diperlukan, tetapi keputusan dibuat berdasarkan pelan pelaksanaan yang peka penimbal (buffer-aware).

Bagi hasil yang dimaterikan secara berterusan, saya tidak akan menganggap nombor sesi sebagai tidak boleh diubah (immutable). Peristiwa pada 09:00 dan 09:50 adalah berasingan sehingga peristiwa lewat 09:25 merapatkan kedua-duanya. Sistem ini memerlukan pengiraan semula berskop pengguna di dalam tetingkap kelewatan dan pengguna (consumers) yang peka versi. Peristiwa di luar tera air mesti mengikut kuarantin yang jelas atau dasar pembetulan yang lebih luas.”

Kesilapan Biasa

• Menolak peristiwa pertama sesi daripada peristiwa semasa → Pengguna yang aktif secara berterusan dipecahkan

sebaik sahaja jumlah tempoh melebihi 30 minit → bandingkan peristiwa bersebelahan seperti yang dinyatakan.

• Mengekalkan jurang tepat 30 minit dalam sesi lama → Ini melanggar kontrak >= 30 minutes →

tulis ujian ambang khusus.

• Menyusun hanya mengikut occurred_at → Cap masa yang sama tidak mempunyai susunan total yang stabil → **tambah

event_id yang unik dan guna semula susunan dalam kedua-dua tetingkap.**

• Meninggalkan bingkai ROWS yang jelas → Tingkah laku rakan sebaya lalai mungkin berbeza daripada pengumpulan baris demi baris →

nyatakan bingkai dari baris pertama hingga baris semasa.

• Menolak masa dinding tempatan → Lompatan waktu jimat siang mencipta atau menyembunyikan satu jam → **bandingkan

detik timestamptz dan setempatkan hanya untuk paparan.**

• Menapis pada permulaan laporan sebelum LAG → Peristiwa dalam julat pertama kehilangan pendahulunya dan

menjadi sempadan palsu → baca konteks pra-sempadan sebelum memotong output.

• Mengira penghantaran semula sebagai peristiwa → event_count melambung → nyahduplikasi mengikut kunci peristiwa logik.
• Menganggap sesi sejarah hanya bertambah (append-only) → Data lewat boleh mengalihkan sempadan atau menggabungkan sesi →

kira semula julat bersempadan dan terbitkan hasil yang boleh dibetulkan.

• Menganggap indeks yang sepadan menghapuskan setiap susunan → Pengoptimum boleh memilih pelan lain berdasarkan

kos imbasan dan predikat → periksa pelan yang representatif dan I/O sementara.

Soalan dan Jawapan Susulan

Susulan 1: Apakah yang berubah jika jurang tepat 30 minit kekal dalam sesi lama?

Tukar predikat sempadan daripada >= INTERVAL '30 minutes' kepada > INTERVAL '30 minutes'. Saluran paip tetingkap yang lain tidak berubah, tetapi definisi metrik dan setiap lekapan ujian mesti berubah bersamanya. Kekalkan kes ujian untuk 29:59, 30:00, dan 30:01 supaya dasar tidak tersasar kemudian.

Susulan 2: Adakah penyelesaian running-sum mencukupi jika sesi boleh berlangsung paling lama dua jam?

Tidak. Jurang kecil yang bersebelahan boleh memanjangkan sesi tanpa had, jadi sempadan seterusnya juga bergantung pada permulaan sesi dinamik. CTE rekursif, mesin keadaan tersusun, atau keadaan per pengguna dalam pemproses strim biasanya lebih jelas. Mula-mula bezakan tetingkap dua jam tetap daripada “dua jam selepas peristiwa pertama sesi”; itu adalah kontrak yang berbeza.

Susulan 3: Bagaimanakah anda membetulkan hasil termateri bagi peristiwa yang tiba lewat 24 jam?

Cari peristiwa mengikut user_id, baca julat yang merangkumi sekurang-kurangnya satu sempadan yang disahkan pada mana-mana sisi cap masa lewat, kira semula segmen itu, dan bezakan (diff) dengan versi terdahulu. Output memerlukan kunci perniagaan dan versi yang stabil supaya pengguna boleh menggunakan kemas kini, gabungan, dan penarikan balik secara idempoten. Jika tera air melarang mengubah output berusia 24 jam, kuarantinkan peristiwa dan dedahkan isyarat kualiti data daripada mengabaikannya secara senyap.

Susulan 4: Bagaimanakah anda mengoptimumkan ini untuk berbilion peristiwa?

Baca partisi masa yang boleh dipangkas (pruned) dan manfaatkan susunan (user_id, occurred_at, event_id) untuk mengurangkan pengisihan. Kerja berkala membawa peristiwa terakhir setiap pengguna dan keadaan sesi terbuka merentasi sempadan partisi supaya sempadan fail atau tarikh tidak menjadi sempadan sesi. Sahkan reka bentuk dengan kecondongan pengguna (user skew), limpahan isihan, bait diimbas, dan kependaman hujung ke hujung yang sebenar. Titik panas pengguna tegar tunggal mungkin memerlukan laluan tersusun khusus.

Susulan 5: Bagaimanakah anda membuktikan bahawa tiada peristiwa yang ditinggalkan atau dikira dua kali?

Gunakan semakan pemuliharaan. Jumlah nilai akhir event_count mestilah sama dengan kiraan baris input yang dinyahduplikasi. Setiap event_id memetakan kepada tepat satu (user_id, session_seq). Jujukan sesi bermula pada satu dan meningkat secara bersebelahan bagi setiap pengguna. Jurang bersebelahan di dalam sesi adalah di bawah 30 minit, manakala sempadan antara sesi adalah sekurang-kurangnya 30 minit. Kemudian jalankan ujian sifat dengan input yang dirombak, penghantaran semula, cap masa yang sama, tepi partisi, dan gabungan peristiwa lewat.