SQL中基于指定顺序的LAG函数使用问题

问题描述 #

需要基于event_time（事件时间）的顺序生成对应滞后值，同时保证groups（分组）内的记录顺序不被打乱。当前的问题是：CL1分组的LAG值未按时间逻辑生成，而是受分组规则影响，不符合预期。

当前尝试的SQL语句：

SELECT LAG() OVER (PARTITION BY Personal ID, Groups ORDER BY Personal ID, Event Time)

更新示例 #

以下代码在测试案例中可行，但不确定数据量增大后是否依然正确。我仅在最后一个CTE w2 中加入了分组逻辑。

测试数据 #

WITH sample_table AS (
  SELECT '99999999' personal_id, '1:04:12 AM' event_time, 'CL1' groups UNION ALL
  SELECT '99999999' personal_id, '1:04:12 AM' event_time, 'GM2' UNION ALL
  SELECT '99999999' personal_id, '1:04:12 AM' event_time, 'FZ1' UNION ALL
  SELECT '99999999' personal_id, '8:13:42 AM' event_time, 'GM2' UNION ALL
  SELECT '99999999' personal_id, '8:13:42 AM' event_time, 'GM2' 
)

期望输出 #

正确解决方案 #

核心逻辑是：先按personal_id和groups分组，在组内按event_time生成行号以固定顺序，再基于分组和行号计算LAG值，确保同一分组内按时间顺序获取上一条记录的时间。完整SQL如下：

WITH sample_table AS (
  SELECT '99999999' personal_id, CAST('1:04:12 AM' AS TIME) event_time, 'CL1' groups UNION ALL
  SELECT '99999999' personal_id, CAST('1:04:12 AM' AS TIME) event_time, 'GM2' UNION ALL
  SELECT '99999999' personal_id, CAST('1:04:12 AM' AS TIME) event_time, 'FZ1' UNION ALL
  SELECT '99999999' personal_id, CAST('8:13:42 AM' AS TIME) event_time, 'GM2' UNION ALL
  SELECT '99999999' personal_id, CAST('8:13:42 AM' AS TIME) event_time, 'GM2' 
),
group_ranked AS (
  SELECT *,
         -- 分组内按时间生成行号，固定记录顺序
         ROW_NUMBER() OVER (PARTITION BY personal_id, groups ORDER BY event_time) AS group_row_num
  FROM sample_table
),
lag_calculated AS (
  SELECT *,
         -- 仅在同一用户+分组内，按行号取上一条记录的时间
         LAG(event_time) OVER (PARTITION BY personal_id, groups ORDER BY group_row_num) AS lag_event_time
  FROM group_ranked
)
SELECT personal_id, event_time, groups, lag_event_time
FROM lag_calculated
-- 最终按时间+分组排序，符合全局展示要求
ORDER BY event_time, groups;

关键说明 #

1. 将event_time转换为时间类型（CAST(... AS TIME)），避免字符串排序导致的顺序错误
2. 使用ROW_NUMBER()确保同一分组内的记录严格按时间顺序排序
3. LAG函数的窗口仅限定在personal_id + groups的分组内，保证不同分组之间的滞后值互不干扰
4. 最终排序保证全局结果按事件时间和分组展示，完全匹配期望输出

内容的提问来源于stack exchange，提问作者leaner007