SQL 面試：依 30 分鐘無活動間隔切分使用者工作階段

題目與適用情境

有一張 PostgreSQL 事件表：

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

對每位使用者依 occurredat、eventid 排序。第一筆事件開啟工作階段；之後若目前事件與 前一筆事件的間隔大於或等於 30 分鐘，就開啟新的工作階段。回傳 user_id、從 1 開始的 sessionseq、sessionstart、sessionend、eventcount 與 session_duration。剛好 30 分鐘屬於新工作階段，這是本題明確的邊界規則。

這類工作階段切分常用於點擊流、產品分析與行為漏斗。它和「連續登入天數」不同：連續天數 比較日曆日期是否相鄰，工作階段切分比較同一位使用者相鄰事件的實際時間間隔。主要答案在完整、 已去重的事件集合上計算精確結果；限定時間範圍與增量維護需要額外定義邊界。

面試官在考察什麼

第一個訊號是候選人能否把自然語言變成可執行契約。> 30 分鐘 與 >= 30 分鐘 會讓臨界 事件得到不同歸屬；比較前一筆事件或工作階段第一筆事件，也會得到不同結果。本題採用相鄰事件 間隔，因此持續活動可以形成超過 30 分鐘的長工作階段。

第二個訊號是能否把問題拆成三層視窗邏輯：先用 LAG() 讀取前一筆事件，再標記工作階段 邊界，最後對邊界標記做累計 SUM()。PostgreSQL 不允許把所需視窗計算任意巢狀放在同一個 運算式，CTE 也讓每個中間結果可以單獨檢查。

第三個訊號是排序是否確定。兩筆事件可能有相同的 occurred_at。只按時間排序時，資料庫可用 任意順序處理這些同儕列；加入唯一 event_id 後，LAG() 與累計和共用同一個全序。相同 時間戳之間的間隔為零，不應開啟新工作階段。

第四個訊號是時間語意。timestamptz 表示可比較的絕對時刻，30 分鐘門檻應直接在這些時刻 之間計算。先轉成當地牆上時間會把日光節約時間跳變混入時長。時區轉換只用於結果呈現，不用於 本題的工作階段邊界。

最後還要看候選人是否意識到晚到事件會改寫歷史。一筆插入舊時間位置的事件可能連接原本分離 的兩段工作階段，所以增量系統不能只追加新的 session_seq；它需要有界重算、修訂版本或 明確的最終水位。

回答前需要先釐清的問題

• 30 分鐘臨界點歸哪一邊？ 本題規定間隔 >= 30 minutes 開啟新工作階段；若產品使用

嚴格大於，只需改變比較運算子，但測試預期也必須同步。

• 比較相鄰事件還是工作階段第一筆事件？ 本題比較相鄰事件。若工作階段最長只能 30 分鐘，

就要額外保存起點並採用不同狀態邏輯。

• 重複事件如何處理？ event_id 是邏輯事件唯一鍵，重複投遞應在進入本查詢前去重。

同一使用者、同一時間的不同事件 ID 可能是真實事件，仍分別計數。

• 時間依哪個時區？ 間隔依絕對時間計算。命名時區只影響呈現，不改變經過的秒數。
• 查詢是否帶時間範圍？ 全歷史查詢最簡單。若只查報表視窗，必須說明是否保留從視窗前

延續進來的完整工作階段，並至少讀取每位使用者在起點前的前驅事件。

• 晚到事件會出現多久？ 臨時查詢可以重算。物化結果需要水位、允許修訂的時間窗，以及

下游接受更新或撤回的協定。

• 空表與單事件使用者如何回傳？ 空表回傳零列；單事件使用者得到持續時間為零的一個工作階段。

30 秒回答框架

「我會在每位使用者內依 occurredat, eventid 建立確定順序，用 LAG(occurred_at) 取得 前一筆事件。第一列或間隔大於等於 30 分鐘時標記 1，其餘標記 0。接著用明確的 ROWS 視窗 框架對標記做累計和，這個累計值就是從 1 開始的工作階段序號。最後依使用者與序號彙總開始、 結束、事件數與持續時間。驗證會涵蓋 29 分 59 秒、剛好 30 分鐘、相同時間戳、單事件使用者、 重複 ID、晚到事件與報表起點。若結果需要增量物化，我會依允許晚到視窗重算受影響使用者， 不會假設工作階段只會追加。」

分步深入解析

先用相同排序規則取得前驅事件。event_id 不參與時間差，只負責在時間戳相同時穩定排序：

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;

明確寫出 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 很重要。累計和要依實體排序逐列 吸收邊界標記，不應依賴帶同儕語意的預設視窗框架。這裡雖然唯一 event_id 已消除同儕列， 明確框架仍固定了意圖，也避免日後移除破同分欄位時悄悄改變行為。

用以下事件檢查邊界：

使用者 1 產生兩段工作階段：第一段 2 筆事件、持續 5 分鐘；第二段 3 筆事件、持續 35 分鐘。 使用者 2 雖然總跨度為 58 分鐘，但相鄰間隔都小於 30 分鐘，所以仍是一段工作階段。這正好 證明規則比較的是相鄰事件，不是工作階段總時長。

正確性可以用不變量說明。每位使用者的第一筆記錄讓累計和從 0 增至 1。之後只有符合邊界 條件的記錄會讓累計值增加 1；非邊界記錄維持原值。因此兩筆記錄擁有相同累計值，若且唯若 它們之間沒有被標記的工作階段邊界。依使用者與累計值彙總，恰好得到所有無法再延伸的連續段， 不會跨使用者或跨邊界合併。

若有 N 筆事件，視窗方案需要依使用者與時間排序，通常為 O(N log N) 時間；視窗掃描與 彙總為 O(N)。中間狀態為 O(N)，實際可能寫入磁碟。索引可以和邏輯排序一致：

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

索引不保證所有計畫都免排序：全表讀取成本、可見性、平行計畫與篩選條件都會影響選擇。應在 代表性資料上檢查 EXPLAIN (ANALYZE, BUFFERS) 的掃描、排序、暫存 I/O、估算列數與實際列數， 不能只因索引存在就宣布最佳化完成。

時間範圍是最容易漏掉的正確性邊界。若查詢從 10:00 開始，而使用者在 09:50 與 10:10 各有 一筆事件，直接篩選後會把 10:10 誤標為新工作階段。只需要視窗內歸屬時，至少為每位使用者 讀取起點前最近一筆事件作為上下文，再於輸出時排除上下文列。若要求回傳完整工作階段起點， 就要繼續往前讀，直到遇到真正的 30 分鐘邊界。

晚到事件還會合併歷史工作階段。例如 09:00 與 09:50 原本相隔 50 分鐘，屬於兩段；後來補入 09:25 後，兩個相鄰間隔都變為 25 分鐘，結果合併成一段。批次處理可直接重算受影響分割區； 增量系統應依使用者與允許晚到範圍重算，並替輸出提供版本或撤回機制。水位之後仍到達的事件， 要丟棄、隔離或觸發更大範圍修訂，必須成為明確產品契約。

驗證時逐層檢查。ordered 中每個非首列的 previous_at 必須等於同序前一筆時間；marked 只在首列與門檻邊界為 1；sessionized 的序號對每位使用者從 1 開始、單調不減且每次最多 增加 1。最終層還應滿足 sessionstart <= sessionend、事件數總和等於輸入邏輯事件數， 而且相鄰工作階段的間隔至少 30 分鐘。

高品質示範回答

「我會先確認邊界是大於等於 30 分鐘，且比較相鄰事件。原始事件用唯一 event_id 去重； 同一時間的不同事件仍保留。查詢第一層在每位使用者內依時間與事件 ID 排序，用 LAG 取 前一筆時間。第二層把首列或間隔達到門檻的記錄標成新工作階段。第三層對標記做明確 ROWS 累計和，得到穩定的工作階段序號，再依使用者與序號彙總起訖時間、事件數與持續時間。

我會用 29 分 59 秒和剛好 30 分鐘測試比較運算子，用相同時間戳測試破同分排序，用單事件 使用者和空表測試邊界。還要測試報表起點前存在相鄰事件的情況，避免先篩選時間再做 LAG 而製造假的新工作階段。複雜度主要來自排序，通常為 O(N log N)； (userid, occurredat, event_id) 索引可能提供所需順序，但最終要看包含緩衝資訊的執行計畫。

如果工作階段結果要持續物化，我不會把序號視為永不變動。09:00 與 09:50 原本是兩段，晚到的 09:25 可以把它們合併。系統必須依允許晚到視窗重算受影響使用者，並讓下游接受版本更新； 超出水位的事件則依事先約定進入隔離或更大範圍修訂。」

常見錯誤

• 用目前事件減工作階段第一筆事件 → 會把持續活躍但總時長超過 30 分鐘的使用者錯誤拆段

→ 依題意比較相鄰事件。

• 把剛好 30 分鐘留在舊工作階段 → 與 >= 30 minutes 契約衝突 → 替臨界值寫獨立測試。
• 只依 occurredat 排序 → 相同時間戳沒有穩定全序 → **加入唯一 eventid，並讓兩個

視窗使用相同排序。**

• 省略明確 ROWS 視窗框架 → 預設框架的同儕行為可能不符逐列累計意圖 → **寫明從首列

到目前列的 ROWS 框架。**

• 把當地牆上時間用於間隔 → 日光節約時間跳變會製造或隱藏一小時 → **直接比較

timestamptz 絕對時刻，只在呈現時轉換時區。**

• 先依報表起點篩選 → 視窗第一筆事件失去前驅，被誤標為新工作階段 → **讀取邊界前上下文，

再裁切輸出。**

• 把重複投遞當作真實事件 → event_count 被放大 → 用邏輯事件唯一鍵去重。
• 認為歷史工作階段只會追加 → 晚到事件可能移動邊界或合併工作階段 → **有界重算並發布

可修訂結果。**

• 看到複合索引就斷言沒有排序 → 最佳化器可能因全表成本或篩選方式選擇其他計畫 → **檢查

代表性執行計畫與暫存 I/O。**

追問與回答

追問 1：如果剛好 30 分鐘仍屬於舊工作階段，要改哪裡？

把邊界判斷從 >= INTERVAL '30 minutes' 改成 > INTERVAL '30 minutes'。其他視窗邏輯不變， 但所有語言、指標定義與測試資料都要同步。尤其要保留 29 分 59 秒、30 分鐘與 30 分 1 秒三組 案例，避免日後再次混淆。

追問 2：如果工作階段最長不能超過 2 小時，累計和還夠嗎？

單純比較前一筆事件不夠，因為連續小間隔可以無限延長工作階段。新邊界同時依賴動態工作階段 起點，通常需要遞迴 CTE、順序狀態機，或在串流處理器中維護每位使用者的工作階段狀態。回答時 應先釐清「2 小時固定視窗」或「從第一筆事件起最多 2 小時」，兩者邊界也不同。

追問 3：晚到 24 小時的事件如何修訂物化結果？

依 user_id 定位事件落點，讀取涵蓋晚到時間前後至少一個已確認邊界的區間，重新計算這段 工作階段並與舊版本做差異。輸出使用穩定業務鍵與版本，允許更新、合併與撤回；依賴方按版本 冪等套用。若產品水位禁止修改 24 小時前結果，就把事件放入隔離佇列與資料品質指標，不能 靜默忽略。

追問 4：資料量到數十億筆時如何最佳化？

先依可裁剪的時間分割區讀取，並利用 (userid, occurredat, event_id) 順序降低排序成本。 週期性任務保存每位使用者在分割區末端的最後事件時間與未關閉工作階段狀態，下一個分割區 接續該狀態，避免把分割區邊界誤當成工作階段邊界。最佳化必須用真實使用者傾斜、排序溢寫、 掃描位元組與端到端延遲驗證；若單一超級使用者形成熱點，還要單獨拆分其順序處理路徑。

追問 5：如何證明沒有事件漏算或重複計算？

對輸入與輸出建立守恆檢查：最終所有 event_count 總和必須等於去重後輸入列數；每個 eventid 必須映射到恰好一個 (userid, session_seq)；每位使用者的工作階段序號從 1 連續成長；工作階段內相鄰間隔小於 30 分鐘，工作階段之間的邊界間隔大於或等於 30 分鐘。 再用亂序輸入、重複投遞、相同時間戳、分割區交界與晚到合併做屬性測試。