SQL Interview Prep · 课时

有序事件与时间窗口

Q: 「有序事件与时间窗口」这节课中我会学到什么？

使用窗口函数确保各步骤按顺序且在时限内发生。 你通过在浏览器中直接运行的动手代码来练习 SQL Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

使用窗口函数确保各步骤按顺序且在时限内发生。

有序事件与时间窗口 是 CoddyKit 上的免费 SQL Interview Prep 课时。这是第 2 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Interview Prep 课程共包含 4 节课。

顺序和时间为何重要

上一课的基础漏斗只检查用户是否完成了每个步骤。更深入的面试官会问：这些步骤是否按正确顺序发生，并且是否在合理的时间内完成？

一位用户周一完成了购买、周五才访问营销页面，这并不算通过您的漏斗完成转化。顺序和时间安排能将基于简单标志的漏斗变成可信的漏斗。

按用户记录各步骤首次时间

要分析顺序，请记录每位用户在各个步骤的首次时间：首次访问时间、首次注册时间、首次购买时间。

这样，符合顺序的转化就意味着 first_signup_time >= first_visit_time，后续步骤依此类推。按步骤分组使用 MIN(event_time)，即可得到这些时间锚点。

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;

要求步骤按顺序发生

有了各步骤的首次时间，强制执行顺序就变成了一个比较问题。用户只有在每个时间戳都不为 NULL 且按单调顺序递增时，才算真正转化到第 3 步。

请注意，NULL 时间戳（表示该步骤从未发生）会自然导致比较失败，而这正是您希望得到的结果。

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;

添加时间窗口

大多数漏斗都有截止期限，例如“在首次访问后的 7 天内完成转化”。请在第一步和最后一步之间加入时间区间上限。

日期运算会因数据库方言而异。在 Postgres 中可以写成 visit_t + INTERVAL '7 days'；在 MySQL 中使用 DATE_ADD(visit_t, INTERVAL 7 DAY)。请始终说明您使用的方言。

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';

为什么使用首次时间，而不是任意时间

面试中有一个细微但重要的问题：时间窗口应该从用户的首次访问开始，还是从注册前最近的一次访问开始？这取决于您要回答的产品问题。

首次触达窗口衡量从最初产生兴趣到完成转化所需的时间。
末次触达窗口衡量最后一次访问后完成转化所需的时间。

请询问面试官具体指哪一种；有意识地做出选择，能够体现您的资深程度。

使用 LEAD 处理有序事件

对于复杂的多步骤路径，窗口函数非常适用。按时间排列每位用户的事件，然后使用 LEAD 查看下一个事件，并确认它是否是预期的下一步。

这种方法可以处理步骤之间穿插无关事件的路径。

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;

匹配预期的下一步骤

在 LEAD 的基础上继续：保留紧接着“访问”之后发生“注册”的行。这样找到的是实际的连续转移，而不只是同时出现过这两个事件。

您可以逐步串联这些转移检查，以验证完整的有序路径。

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';

连续步骤之间的时间

面试官很喜欢问“每个步骤需要多长时间？”在时间戳上使用 LEAD 并进行相减。连续事件之间的差值，就是用户在该阶段的停留时长。

按转移计算中位数或平均值，就能找出漏斗中耗时最长的阶段。

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;

相同时间戳的边界情况

如果两个事件拥有完全相同的 event_time，会发生什么？此时即使它们同时发生，signup_t >= visit_t 仍然为真，仅按时间排序就无法确定顺序。

请有意识地选择使用 >= 还是 >，并说明原因。
向 ORDER BY 添加事件序列标识符之类的次级排序条件，使窗口计算具有确定性。

主动提到这一点，会给面试官留下深刻印象。

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

在一个查询中结合顺序和时间窗口

这是完整的按顺序且在时间窗口内完成的漏斗查询。它以首次访问为锚点，要求每个后续步骤的首次发生时间都晚于前一步，并将整个路径限制在 7 天内。

这个答案能区分真正理解漏斗的候选人与只会统计标志的候选人。

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;

跨数据库方言说明

实时编程时请记住两个可移植性问题：

聚合函数上的 FILTER (WHERE ...) 属于标准 SQL，可在 Postgres 中使用；在 MySQL 或较旧的数据库引擎中，请改用 SUM(CASE WHEN ... THEN 1 ELSE 0 END)。
区间语法有所不同：Postgres 使用 + INTERVAL '7 days'，MySQL 使用 DATE_ADD(d, INTERVAL 7 DAY)，SQL Server 使用 DATEADD(day, 7, d)。

说明您的假设即可；面试官通常并不在意您使用哪种方言，只在意您知道它们有所不同。

快速检查

您必须按顺序统计完成访问 -> 注册 -> 购买的用户，并且整个路径必须发生在首次访问后的 7 天内。哪种方法正确？

回顾：有序事件与时间窗口

要点：

使用 MIN(CASE ...) 获取每个用户每个步骤的首次时间戳。
要求每个步骤的时间不早于前一个步骤的时间，以确保顺序正确。
使用时间区间限定路径，并写明所用方言的语法。
使用 LEAD/LAG 检查步骤转换以及步骤间的停留时间。
对于相同时间戳的并列记录，在 ORDER BY 中加入破平规则。

下一步：从漏斗分析转向实验，并计算每个变体的指标。

常见问题解答

「有序事件与时间窗口」课时是免费的吗？

是的 — 「有序事件与时间窗口」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Interview Prep 课程的其余内容，请升级到 CoddyKit PRO。 SQL Interview Prep 课程共包含 4 节课。

「有序事件与时间窗口」这节课中我会学到什么？

使用窗口函数确保各步骤按顺序且在时限内发生。你通过在浏览器中直接运行的动手代码来练习 SQL Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Interview Prep 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Interview Prep 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 2 节课，共 4 节。

「有序事件与时间窗口」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Interview Prep 课中编写并运行代码吗？

能。每节 SQL Interview Prep 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 SQL Interview Prep