在BigQuery中实现类SAS Retain逻辑：变量创建时跨行值比较

需求说明 #

现有一张包含ID、StartDate、EndDate的表，需要生成MaxDate列，规则如下：

• 第一行的MaxDate等于当前行的EndDate
• 从第二行开始，若当前行StartDate ≤ 上一行的MaxDate，则MaxDate取当前行EndDate与上一行MaxDate的最大值；否则取当前行的EndDate

示例数据及期望结果：

问题分析 #

你之前尝试的SQL使用普通窗口函数（LAG、固定范围的MAX）无法满足需求，因为这类函数只能基于原始数据或固定范围内的原始值计算，无法引用上一行生成的新列结果，没法实现逐行依赖的累积计算逻辑。

解决方案 #

以下两种方法均可实现需求，按需选择：

方法1：递归CTE（逐行计算） #

递归CTE可以逐行处理数据，直接引用上一行的MaxDate结果，完美匹配你的规则：

WITH ordered_data AS (
  -- 按ID分组，给每行添加行号用于递归关联
  SELECT 
    ID, 
    StartDate, 
    EndDate,
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY StartDate, EndDate) AS rn
  FROM S0
),
recursive_cte AS (
  -- 递归起点：每组的第一行，MaxDate等于自身EndDate
  SELECT 
    ID, 
    StartDate, 
    EndDate,
    EndDate AS MaxDate,
    rn
  FROM ordered_data
  WHERE rn = 1
  
  UNION ALL
  
  -- 递归处理后续行，引用上一行的MaxDate计算当前行值
  SELECT 
    curr.ID,
    curr.StartDate,
    curr.EndDate,
    CASE 
      WHEN curr.StartDate <= prev.MaxDate THEN GREATEST(curr.EndDate, prev.MaxDate)
      ELSE curr.EndDate
    END AS MaxDate,
    curr.rn
  FROM ordered_data curr
  JOIN recursive_cte prev ON curr.ID = prev.ID AND curr.rn = prev.rn + 1
)
SELECT ID, StartDate, EndDate, MaxDate
FROM recursive_cte
ORDER BY ID, rn;

方法2：间隔分组（Gap & Island） #

如果你的场景本质是合并所有重叠/连续的时间区间，可通过标记分组的方式，直接取组内最大EndDate作为MaxDate：

WITH ordered_data AS (
  SELECT 
    ID, 
    StartDate, 
    EndDate,
    -- 标记当前行是否为新分组起点：当前StartDate大于上一行EndDate则为新组
    CASE 
      WHEN StartDate > LAG(EndDate) OVER (PARTITION BY ID ORDER BY StartDate) THEN 1
      ELSE 0
    END AS is_new_group
  FROM S0
),
grouped_data AS (
  SELECT 
    *,
    -- 累加标记值生成分组ID，同一组内值相同
    SUM(is_new_group) OVER (PARTITION BY ID ORDER BY StartDate) AS group_id
  FROM ordered_data
)
SELECT 
  ID,
  StartDate,
  EndDate,
  -- 组内最大EndDate即为该组所有行的MaxDate
  MAX(EndDate) OVER (PARTITION BY ID, group_id) AS MaxDate
FROM grouped_data
ORDER BY ID, StartDate;

原SQL问题说明 #

你写的SQL存在两个关键问题：

1. S1中仅选择了ID和COUNT，丢失了StartDate和EndDate字段，导致后续逻辑无法正确计算
2. 使用MAX(EndDate) OVER (ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)只能取当前行和上一行的原始EndDate最大值，无法引用上一行已计算出的EndDate2，因此无法实现累积的MaxDate逻辑

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