BigQuery SQL：rank/last_value()/first_value()选型及日期计算问题

我明白你的痛点了——用LAST_VALUE()的时候总是拿到整个窗口的最后日期，而不是Value2变更前的那个临界点，确实容易踩坑。咱们一步步来解决这个问题：

核心思路 #

要实现需求，关键是先识别Value2的连续变化区间，然后找到每个区间对应的上一个区间的最大日期，同时先过滤掉keeprow = FALSE的行。

具体SQL实现 #

我们可以用CTE分层处理，逻辑更清晰：

WITH filtered_data AS (
  -- 第一步：先移除标记为keeprow = FALSE的行
  SELECT *
  FROM `your_project.your_dataset.your_table`
  WHERE keeprow = TRUE
),
grouped_value2 AS (
  SELECT
    *,
    -- 标记当前行是否是Value2发生变化的起始行
    CASE 
      WHEN LAG(Value2) OVER (PARTITION BY Value1 ORDER BY startdate) != Value2 
      THEN 1 
      ELSE 0 
    END AS value2_changed,
    -- 生成连续相同Value2的分组ID，同一组内Value2不变
    SUM(CASE 
          WHEN LAG(Value2) OVER (PARTITION BY Value1 ORDER BY startdate) != Value2 
          THEN 1 
          ELSE 0 
        END) OVER (
          PARTITION BY Value1 
          ORDER BY startdate 
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS value2_group_id
  FROM filtered_data
),
group_end_dates AS (
  SELECT
    *,
    -- 计算每个Value2分组的最大enddate（即该阶段的最后日期）
    MAX(enddate) OVER (PARTITION BY Value1, value2_group_id) AS current_group_last_date,
    -- 拿到上一个Value2分组的最大enddate，就是我们要的datewanted
    LAG(MAX(enddate) OVER (PARTITION BY Value1, value2_group_id)) OVER (
      PARTITION BY Value1 
      ORDER BY value2_group_id
    ) AS datewanted
  FROM grouped_value2
)
-- 最终输出需要的列
SELECT
  Value1,
  Value2,
  startdate,
  enddate,
  datewanted
FROM group_end_dates
ORDER BY Value1, startdate;

代码解释 #

1. filtered_data：先过滤掉不需要的行，避免后续计算被无效数据干扰。
2. grouped_value2：
   • 用LAG(Value2)对比当前行和上一行的Value2，标记变化点；
   • 通过累计求和生成value2_group_id，把连续相同Value2的行归为同一组。
3. group_end_dates：
   • 先算出每个分组的最大enddate（该Value2阶段的最后日期）；
   • 再用LAG()把上一个分组的最大日期拉过来，这就是Value2变化前的最大日期，也就是我们要的datewanted。

补充说明 #

• 对于每个Value1的第一个Value2分组，datewanted会是NULL（因为没有之前的变化），如果需要默认值，可以用COALESCE(datewanted, '1970-01-01')这样的语句调整。
• 如果你的startdate和enddate是日期时间类型，排序和最大值计算逻辑依然适用。

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