← 返回知识库← 上一题下一题 →
SQL (ANSI)编程SQL开发人员

如何仅使用ANSI SQL窗口函数在禁止逐行处理的情况下识别顺序数据中的连续范围（岛屿）？

用 Hintsage AI 助手通过面试

问题的答案

问题的历史

这个问题源于SQL标准从SQL-92演变到SQL:2003，窗口函数正式引入该标准。在这项进展之前，开发人员依赖于过程游标或计算开销大的自连接来解决面向序列的问题。缺口-岛屿模式代表了从过程算法到声明性、基于集合逻辑的范式转变，后者定义结果集而不是处理步骤。

问题

在处理包含顺序值（如时间戳、ID或日期）的表时，您必须将连续值分组为连续块（岛屿），同时将其与断点（缺口）区分开来。根本挑战在于ANSI SQL表代表无序的数学集合，而检测序列需要明确的排序。传统的GROUP BY子句聚合相似值，但破坏了识别连续性所必需的顺序关系。

解决方案

利用ROW_NUMBER()在整个数据集上的算术差异和按分组键划分的ROW_NUMBER()，生成一个常量岛屿标识符。该技术为同一连续序列中的所有行创建相同的计算值，从而允许标准聚合重建岛屿。

WITH numbered AS (
  SELECT 
    event_date,
    ROW_NUMBER() OVER (ORDER BY event_date) AS rn_global,
    event_date - ROW_NUMBER() OVER (ORDER BY event_date) AS island_grp
  FROM events
)
SELECT 
  MIN(event_date) AS island_start,
  MAX(event_date) AS island_end,
  COUNT(*) AS consecutive_days
FROM numbered
GROUP BY island_grp;

生活中的情况

一个零售分析团队需要从存储在PostgreSQL中的点击流数据中重建客户购物会话。该系统记录了数百万个包含user_id和event_time的事件，但缺乏预先计算的会话标识符。业务需求将会话定义为事件序列，其中没有超过30分钟的非活动间隔。

第一个方法考虑使用带相关子查询的自连接来定位每个事件的直接前身。这种方法需要O(n²)的行比较，在处理超过五百万行的每日批处理时导致查询超时，尽管它与缺乏现代窗口函数的传统SQL-92系统保持兼容。

团队随后评估了pl/pgSQL游标，以逐行迭代事件，同时在过程变量中保持会话状态。虽然该方法提供了对应用程序开发人员熟悉的直观逻辑，但它放弃了基于集合的处理原则，并且每日批处理需要超过四个小时才能完成，导致不可接受的ETL延迟和显著的表锁定问题。

所选解决方案仅使用ANSI SQL窗口函数。通过应用LAG()来捕获每个用户的上一个时间戳并计算时间差，该团队识别出了缺口超过30分钟的会话边界。条件运行总和生成唯一的会话标识符，启用基于集合的聚合。该方法在八分钟内处理了整个数据集，根据数据量线性扩展，并且在Oracle、SQL Server和PostgreSQL之间保持可移植性，而无需供应商特定的语法修改。

候选人常常忽略的内容

我为什么不能简单地将时间戳截断到小时并按该值分组以查找会话？

使用DATE_TRUNC或类似函数截断时间戳迫使在时钟小时处强加人为边界，而不是相对时间差。发生在10:55和11:05的两个事件将被分到不同的组，即使它们相隔仅10分钟，而发生在10:01和10:59的事件会聚集在一起，尽管它们相隔58分钟。真正的会话检测需要计算每个事件的直接前身的间隔，而不是对齐日历边界。

排序列中的NULL值如何影响使用LAG或LEAD进行岛屿检测？

LAG和LEAD分别为每个分区的第一行和最后行返回NULL。在从当前时间戳中减去滞后的时间戳以计算缺口时，使用NULL进行的算术运算会产生NULL结果，这可能导致整个岛屿在聚合中消失。您必须在LAG中利用可选的default参数（例如，LAG(event_time, 1, event_time) OVER (...)）或通过COALESCE显式处理NULL，以防止在分区边界处出现岛屿碎片。

在同时检测多个类别（例如按用户或设备）之间的岛屿时有什么变化？

候选人经常忽略窗口函数中的PARTITION BY子句，在整个表中计算ROW_NUMBER，而不是按类别。没有按user_id或等效分组列进行分区，不同用户的岛屿在其序列恰好在时间上对齐时会错误合并。参与岛屿计算的每个窗口函数必须包括PARTITION BY user_id，以确保算术在每个不同实体上重置，从而保持每个分区的独立岛屿检测。