Temu Duga SQL: Mengira Pengguna Aktif 7 Hari Bergolek (Rolling 7-Day Active Users)

Prompt dan Konteks yang Berkenaan

Satu jadual analitik mengandungi events(event_id, user_id, event_at, event_name, is_internal_user). event_at ialah timestamptz PostgreSQL. Pengguna dianggap aktif selepas sekurang-kurangnya satu peristiwa app_open, view_dashboard, atau run_report. Pengguna dalaman tidak diambil kira.

Kembalikan satu baris bagi setiap tarikh kalendar America/New_York dari 1 Jun hingga 30 Jun 2026. Bagi setiap tarikh laporan, rolling_7d_active_users ialah bilangan pengguna layak distink yang aktif pada tarikh tersebut atau mana-mana daripada enam tarikh kalendar sebelumnya. Oleh itu, 1 Jun memerlukan aktiviti dari 26 Mei hingga 1 Jun, inklusif. Pengguna dengan dua puluh peristiwa pada tiga tarikh tetap dikira sekali sahaja dalam tetingkap tersebut.

Pertanyaan mestilah mengekalkan tarikh yang mempunyai sifar pengguna, menggunakan sempadan masa yang eksplisit, dan menyatakan bagaimana peristiwa yang tiba lewat (late-arriving events) memberi kesan kepada hasil yang diterbitkan sebelum ini. Cabaran utamanya ialah rolling distinct union. Ia bukanlah rolling sum bagi kiraan harian yang telah diagregatkan.

Perkara yang Dinilai oleh Penemu Duga

Isyarat pertama ialah takrifan metrik sebelum sintaks. Calon yang mantap menyatakan peristiwa yang layak, populasi yang dikecualikan, zon masa pelaporan, output grain, tetingkap tujuh tarikh inklusif, dan sempadan kesempurnaan data. Tanpa perkara tersebut, dua pertanyaan yang sah dari segi sintaks boleh menjawab soalan yang berbeza.

Isyarat kedua ialah kawalan grain. Peristiwa mentah mesti dijadikan pasangan (user_id, activity_date) yang unik terlebih dahulu. Ini menyingkirkan duplikasi pada hari yang sama tetapi sengaja mengekalkan pengguna pada beberapa tarikh. Tetingkap terakhir kemudiannya mengira distinct union bagi set-set pengguna tersebut.

Isyarat ketiga ialah menolak jalan pintas yang menggoda. Menjumlahkan tujuh nilai DAU mengira pengguna sekali bagi setiap tarikh aktif. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW menerangkan tujuh baris, bukan semestinya tujuh tarikh kalendar, dan menerapkannya pada kiraan harian masih tidak dapat membina semula distinct union merentas hari.

Isyarat terakhir ialah pertimbangan pengeluaran (production judgment): imbas warm-up enam hari sebelum julat yang diminta, kekalkan tarikh kosong dengan calendar spine, tukar cap masa menggunakan zon masa yang diisytiharkan, takrifkan semantik muat semula late-event, dan pilih strategi penskalaan tepat atau anggaran secara sengaja.

Soalan untuk Dijelaskan Sebelum Menjawab

Apakah yang melayakkan pengguna sebagai aktif? Takrifan log masuk sahaja menghasilkan set yang berbeza daripada

peristiwa produk yang bermakna. Nama peristiwa serta pengecualian bot atau pengguna dalaman tergolong dalam kontrak metrik.

Zon masa manakah yang mentakrifkan hari? Jawapan ini menggunakan America/New_York. UTC akan mengalihkan peristiwa yang berhampiran

tengah malam tempatan ke tarikh laporan yang berbeza.

Adakah tetingkap tersebut merupakan tujuh tarikh kalendar atau 168 jam berlalu? Prompt meminta tarikh kalendar tempatan.

Peralihan daylight-saving boleh menyebabkan tujuh tarikh tersebut mengandungi 167 atau 169 jam berlalu.

Adakah kedua-dua sempadan bersifat inklusif? Set pengguna merangkumi report_date - 6 hingga report_date.

Penapis cap masa sumber menggunakan julat separuh terbuka (half-open range) bagi mengelakkan pengiraan berganda pada tengah malam berikutnya.

Adakah tarikh yang tiada mesti dipaparkan? Ya. Jana kesemua tiga puluh tarikh laporan dan bukannya menerbitkan tarikh hanya

daripada peristiwa sedia ada.

Sejauh manakah jadual peristiwa itu lengkap? Jika peristiwa mungkin tiba lewat tiga hari, keputusan terkini adalah

bersifat sementara atau memerlukan watermark yang dinyatakan. SQL sahaja tidak dapat menjadikan input yang tidak lengkap sebagai muktamad.

Adakah distink tepat diperlukan? Pertanyaan temu duga ini adalah tepat. Pada skala yang sangat besar, perwakilan

mergeable-set anggaran mungkin boleh diterima hanya selepas kontrak ralatnya dipersetujui.

Apakah pangkalan data dan skala yang diguna pakai? Jawapan ini menggunakan PostgreSQL. Data warehouse mungkin menggunakan fungsi date-spine

yang lain atau primitif bitmap sambil mengekalkan semantik set yang sama.

Kerangka Jawapan 30 Saat

“Saya akan mentakrifkan peristiwa aktif, pengecualian, America/New_York sebagai sempadan hari, dan output grain sebanyak satu baris bagi setiap tarikh laporan terlebih dahulu. Saya akan mengimbas dari 26 Mei kerana 1 Jun memerlukan enam tarikh sebelumnya, menukar cap masa kepada tarikh tempatan, dan menyahduplikasi kepada satu baris bagi setiap pengguna bagi setiap tarikh. Kemudian saya akan menjana 1 Jun hingga 30 Jun dengan generate_series, left join setiap tarikh kepada aktiviti dari date - 6 hingga tarikh tersebut, dan mengira distinct users.

Saya tidak akan menjumlahkan daily active users kerana pengguna yang aktif pada beberapa hari akan dikira berulang kali. Tetingkap enam baris juga gagal apabila ada tarikh yang hilang dan ia tidak menghasilkan distinct union. Saya akan menguji sempadan tengah malam, duplikasi, tarikh kosong, dan aktiviti warm-up, kemudian menerbitkan watermark as-of atau memuat semula tarikh-tarikh terkini apabila late events tiba.”

Huraian Terperinci Langkah demi Langkah

Langkah 1: Tetapkan kontrak metrik dan julat input

Output yang diminta bermula pada 1 Jun, tetapi imbasan sumber bermula pada 26 Mei. Membaca baris bulan Jun sahaja akan mengakibatkan kurangan kiraan (undercount) pada enam tarikh laporan pertama. Sempadan atas sumber ialah tengah malam tempatan 1 Julai; aktiviti masa hadapan tidak relevan untuk trailing window yang berakhir pada 30 Jun.

Tukarkan tengah malam tempatan tersebut kepada pemalar timestamptz di dalam penapis. Ini mengekalkan predikat pada lajur event_at yang diindeks. Menghantar (casting) setiap cap masa sumber kepada tarikh di dalam klausa WHERE boleh menghalang indeks julat biasa daripada melakukan pemangkasan (pruning) yang berguna.

Langkah 2: Menormalkan peristiwa kepada user-days distink

Tukarkan setiap peristiwa yang layak kepada tarikh kalendar New York hanya selepas mengenakan penapis cap masa yang bersempadan. Kemudian buat group by mengikut user_id dan tarikh tempatan. Percubaan semula peristiwa (retry) dengan ID baris yang berbeza dan dua puluh peristiwa daripada seorang pengguna pada satu hari semuanya menjadi satu user-day.

Penyahduplikasian ini tidak menyelesaikan masalah akhir dengan sendirinya. Jika pengguna yang sama aktif pada 1 Jun dan 2 Jun, kedua-dua baris user-day mesti kekal ada supaya trailing window bagi mana-mana tarikh boleh merangkumi pengguna tersebut.

Langkah 3: Jana date spine yang lengkap

generate_series mencipta setiap tarikh laporan secara bebas daripada kewujudan peristiwa. Bermula terus daripada jadual peristiwa akan meninggalkan tarikh yang tiada data, mengubah bilangan baris dalam bingkai ROWS, dan tidak meninggalkan titik papan pemuka bernilai sifar. Spine ialah output grain yang autoritatif.

Langkah 4: Kira distinct union bagi setiap tetingkap

Pertanyaan tepat secara langsung ialah:

sql

WITH params AS (
  SELECT
    DATE '2026-06-01' AS report_start,
    DATE '2026-06-30' AS report_end
),
activity_days AS (
  SELECT
    e.user_id,
    (e.event_at AT TIME ZONE 'America/New_York')::date AS activity_date
  FROM events AS e
  WHERE e.event_at >= TIMESTAMPTZ '2026-05-26 00:00:00 America/New_York'
    AND e.event_at <  TIMESTAMPTZ '2026-07-01 00:00:00 America/New_York'
    AND e.event_name IN ('app_open', 'view_dashboard', 'run_report')
    AND e.is_internal_user = false
  GROUP BY
    e.user_id,
    (e.event_at AT TIME ZONE 'America/New_York')::date
),
report_dates AS (
  SELECT gs::date AS report_date
  FROM params AS p
  CROSS JOIN generate_series(
    p.report_start,
    p.report_end,
    INTERVAL '1 day'
  ) AS gs
)
SELECT
  d.report_date,
  COUNT(DISTINCT a.user_id) AS rolling_7d_active_users
FROM report_dates AS d
LEFT JOIN activity_days AS a
  ON a.activity_date BETWEEN d.report_date - 6 AND d.report_date
GROUP BY d.report_date
ORDER BY d.report_date;

Left join mengekalkan tarikh laporan yang tiada data. COUNT(DISTINCT a.user_id) mengabaikan nilai null yang dihasilkan oleh left join yang tidak sepadan. Selang tersebut mengandungi tepat tujuh nilai date: tarikh semasa dan enam tarikh sebelumnya.

Langkah 5: Buktikan mengapa jalan pintas tetingkap biasa gagal

Katakan pengguna A aktif pada hari Isnin dan Selasa, manakala pengguna B aktif pada hari Selasa sahaja. DAU masing-masing ialah 1 dan 2, tetapi distinct union bagi dua hari tersebut ialah 2, bukan 3. Sebaik sahaja kiraan harian menggantikan identiti pengguna, SQL tidak dapat mengetahui bahawa A wujud pada kedua-dua hari.

Bingkai baris (row frame) memperkenalkan satu lagi ralat. Jika hari Rabu tiada peristiwa dan tidak wujud dalam input, “enam baris sebelumnya” boleh menjangkau lapan hari kalendar atau lebih ke belakang. Date spine membetulkan jurang kalendar, tetapi rolling sum terhadap DAU tetap mengira identiti secara berganda. Operasi yang betul ialah union dahulu, kemudian kekardinalan.

Langkah 6: Menskalakan tanpa mengubah semantik

Bagi julat sederhana, indeks data mentah mengikut event_at dan kurangkannya kepada user-days sebelum join selang 7x. Jadual aktiviti harian yang dimaterialkan dengan kunci (activity_date, user_id) mengelakkan pengimbasan semula peristiwa mentah. Pemangkasan partisi (partition pruning) harus merangkumi tarikh-tarikh warm-up.

Bagi julat laporan yang lebih panjang, kembangkan setiap user-day kepada paling banyak tujuh tarikh laporan yang layak, hadkan tarikh-tarikh tersebut kepada julat yang diminta, dan kemudian kelompokkan distinct users. Ini mengubah bentuk join tetapi bukan peluasan kes terburuk 7x. Enjin dengan set bitmap tepat boleh menggabungkan (union) bitmap pengguna harian; lakaran anggaran (approximate sketches) mesti menyokong set union dan mesti mendedahkan ralat yang diukur. Menambah anggaran HyperLogLog harian adalah tidak sah kerana kekardinalan anggaran tidak boleh ditambah untuk mendapatkan satu union.

Langkah 7: Takrifkan data lewat dan penentusahan

Terbitkan watermark as_of bersama-sama hasil. Jika talian paip menerima peristiwa sehingga tiga hari lewat, muat semula sekurang-kurangnya setiap tarikh laporan yang tetingkap input tujuh harinya bersilang dengan data boleh ubah tersebut. ID peristiwa yang stabil membantu penyahduplikasian penyerapan (ingestion), manakala pengelompokan user-day melindungi metrik ini daripada berbilang peristiwa yang layak; tiada satu pun daripadanya menggantikan pemantauan kesempurnaan data.

Gunakan oracle manual dengan peristiwa duplikasi, pengguna yang sama pada beberapa tarikh, pengguna dalaman, peristiwa tidak layak, peristiwa warm-up 26 Mei dan 31 Mei, tarikh kosong, masa tepat tengah malam tempatan pada kedua-dua belah, dan sempadan daylight-saving. Bandingkan setiap tarikh output dengan set union peringkat aplikasi yang mudah. Jalankan EXPLAIN (ANALYZE, BUFFERS) pada volum menyerupai pengeluaran dan sahkan pemangkasan sumber, kekardinalan user-day, peluasan join, masa jalanan, serta kelakuan limpahan (spill).

Contoh Jawapan yang Mantap

“Saya akan mentakrifkan set sebelum menulis SQL: pengguna yang layak mempunyai sekurang-kurangnya satu peristiwa produk yang diluluskan, pengguna dalaman dikecualikan, dan satu hari bermaksud America/New_York. Bagi tarikh laporan D, set tersebut ialah setiap pengguna yang layak dengan tarikh aktiviti tempatan antara D tolak enam dan D, inklusif. Output mesti mengandungi kesemua tiga puluh tarikh.

Saya akan menapis cap masa mentah dari tengah malam tempatan 26 Mei hingga tengah malam tempatan 1 Julai, mengekalkan sempadan atas sebagai eksklusif. Selepas menapis, saya menukarnya kepada tarikh tempatan dan membuat group by mengikut pengguna dan tarikh. Spine generate_series membekalkan 1 Jun hingga 30 Jun. Setiap tarikh spine melakukan left join dengan user-days dalam trailing interval miliknya, dan COUNT(DISTINCT user_id) mengembalikan kekardinalan union.

Saya akan menolak SUM(DAU) kerana identiti yang aktif pada beberapa tarikh berulang, dan menolak ROWS 6 PRECEDING kerana baris bukanlah tarikh kalendar dan pengagregatan harian telah membuang identiti. Untuk skala, saya akan mematerialkan (activity_date, user_id), memangkas julat warm-up, dan mempertimbangkan union bitmap tepat atau set union anggaran terukur hanya apabila output tepat tidak diperlukan. Hasilnya membawa watermark as_of, dan data lewat mencetuskan pengiraan semula yang terikat.”

Kesilapan Biasa

Menjumlahkan tujuh nilai DAU → pengguna berulang dikira sekali bagi setiap tarikh aktif → **gabungkan (union) identiti pengguna

merentas tetingkap, kemudian kira.**

Menggunakan ROWS 6 PRECEDING pada tarikh yang jarang (sparse) → enam baris mungkin menjangkau lebih daripada enam tarikh sebelumnya → **jana

calendar spine yang lengkap dan nyatakan sempadan kalendar.**

Mengimbas bulan Jun sahaja → tetingkap awal Jun kehilangan aktiviti bulan Mei → sertakan warm-up enam hari.
Menghantar (casting) event_at dalam penapis sumber → indeks cap masa biasa mungkin tidak memangkas dengan cekap →

tapis dengan sempadan half-open timestamptz sebelum menerbitkan tarikh tempatan.

Mengira raw events → percubaan semula dan penggunaan berulang menggelembungkan bilangan pengguna → **nyahduplikasi kepada user-day dan tetap

kira distink merentas tetingkap akhir.**

Menerbitkan tarikh output daripada peristiwa → tarikh kosong hilang → jadikan date spine sebagai output grain.
Memanggil output terkini sebagai muktamad → peristiwa lewat boleh mengubah set → **terbitkan watermark dan muat semula

tetingkap yang terjejas.**

Menambah kekardinalan anggaran harian → penambahan kekardinalan tidak dapat membuang pertindihan → **cantumkan (merge)

lakaran atau bitmap yang menyokong set sebelum menganggarkan union.**

Soalan Susulan dan Jawapan

Soalan Susulan 1: Apakah yang berubah jika metrik ini bermaksud 168 jam sebelumnya?

Bandingkan masa tepat (instants) dan bukannya nilai date tempatan. Bagi setiap masa laporan, gunakan tetingkap cap masa separuh terbuka seperti (report_at - interval '168 hours', report_at], dengan sempadan produk yang dinyatakan secara tepat. Sekitar peralihan daylight-saving, ini berbeza daripada tujuh tarikh kalendar New York. Jangan namakan satu takrifan sebagai takrifan yang lain.

Soalan Susulan 2: Bolehkah window function menyelesaikan exact rolling distinct dengan sendirinya?

Window aggregate berguna apabila agregat dibina daripada nilai baris, seperti rolling sum. Di sini, keadaan (state) yang diperlukan ialah satu set berserta pemadaman apabila tarikh lama keluar dari tetingkap. Jawapan langsung PostgreSQL mengekalkan identiti dan melakukan join ke tarikh laporan. Enjin khusus mungkin menyediakan fungsi tetingkap exact bitmap-union, tetapi itu merupakan ciri enjin, bukan alasan untuk menjumlahkan kiraan harian.

Soalan Susulan 3: Bagaimanakah anda memuat semula selepas satu peristiwa tiba tiga hari lewat?

Cari tarikh aktiviti tempatan bagi peristiwa tersebut, iaitu A. Ia boleh menjejaskan tarikh laporan A hingga A tambah enam, yang bersilang dengan julat yang diterbitkan. Kira semula atau ganti partisi tersebut sahaja, majukan watermark selepas penyelarasan, dan kekalkan operasi sebagai idempoten. Memuat semula tarikh A sahaja akan terlepas enam tetingkap hiliran.

Soalan Susulan 4: Bagaimanakah anda melakukan pembahagian (segmentation) mengikut negara?

Mula-mula tentukan sama ada negara kepunyaan peristiwa, profil semasa pengguna, atau profil yang berubah secara perlahan (slowly changing profile) pada masa aktiviti berlaku. Pilihan tersebut mengubah kebenaran sejarah. Tambahkan negara yang dipilih ke dalam grain user-day, date spine, pengelompokan dan oracle pengesahan. Join profil semasa boleh menulis semula sejarah apabila pengguna berpindah.

Soalan Susulan 5: Bagaimana jika distink tepat terlalu mahal dari segi sumber?

Ukur pertanyaan tepat terlebih dahulu. Jika bajet ralat yang diluluskan membenarkan anggaran, simpan satu mergeable set sketch bagi setiap tarikh aktiviti dan segmen, gabungkan (union) tujuh lakaran, dan anggarkan sekali. Sahkan pincang (bias) dan ralat relatif terhadap set tepat bagi hirisan berkekardinalan rendah, biasa dan tinggi. Kekalkan pemprosesan tepat untuk pengebilan, kelayakan, atau keputusan lain yang tidak bertolak ansur dengan ralat anggaran.