在BigQuery中用Case When实现按条件取前一行值的视图需求

需求逻辑 #

• 当cond_Value > 100时，Output取当前行的Reqd_Col
• 其他情况取最近一次满足cond_Value > 100的行的Reqd_Col
• 首次无前置满足条件行时，Output取自身Reqd_Col

实现SQL代码 #

假设你的源表为your_dataset.your_table，且存在用于确定行顺序的字段（比如时间戳event_time，必须替换为你表中实际的排序字段，否则“最近一次”无意义）：

CREATE OR REPLACE VIEW `your_dataset.your_target_view` AS
WITH valid_markers AS (
  SELECT
    User,
    cond_Value,
    Reqd_Col,
    -- 标记有效行，无效行设为NULL
    CASE WHEN cond_Value > 100 THEN Reqd_Col END AS valid_reqd_col,
    -- 替换为你的实际排序字段（如时间戳、自增ID）
    event_time
  FROM `your_dataset.your_table`
)
SELECT
  User,
  cond_Value,
  Reqd_Col,
  -- 填充最近有效值，首次行 fallback 到自身
  COALESCE(
    LAST_VALUE(valid_reqd_col IGNORE NULLS) OVER (
      PARTITION BY User
      ORDER BY event_time
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ),
    Reqd_Col
  ) AS Output
FROM valid_markers;

代码说明 #

1. CTE valid_markers：筛选出满足条件的有效Reqd_Col值，不满足条件的行对应字段设为NULL，同时保留排序字段用于确定行顺序。
2. LAST_VALUE窗口函数：按User分组，按排序字段从小到大排序，从当前行向前查找最近的非NULL有效取值，实现“最近一次满足条件的值”的填充逻辑。
3. COALESCE处理边界情况：如果当前行之前没有任何满足条件的行（即LAST_VALUE返回NULL），则直接取当前行的Reqd_Col，覆盖首次行的需求。

注意事项 #

• 必须指定排序字段：行的顺序是实现“最近一次”逻辑的核心，务必替换代码中的event_time为你表中实际的排序字段（比如业务时间戳、自增主键等）。
• 按User分区：确保每个用户的填充逻辑独立，不会跨用户取有效值。

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