Dataflow SQL是否支持分析与导航函数？FIRST_VALUE该如何实现？

方案1：分组窗口 + ROW_NUMBER() 过滤实现 #

• 核心思路：先给每个滚动/滑动窗口内的记录按事件时间升序排序并标记行号，提取行号为1的记录对应字段值，即为该窗口内的首次出现值
• 代码示例（以10分钟滚动窗口、按用户分组取首次访问页面ID为例）：

SELECT
  user_id,
  window_start,
  window_end,
  page_id AS first_visit_page_id
FROM (
  SELECT
    user_id,
    page_id,
    TUMBLE_START(event_time, INTERVAL 10 MINUTE) AS window_start,
    TUMBLE_END(event_time, INTERVAL 10 MINUTE) AS window_end,
    -- 按窗口+用户分组，内部按事件时间升序打行号
    ROW_NUMBER() OVER (
      PARTITION BY TUMBLE_START(event_time, INTERVAL 10 MINUTE), TUMBLE_END(event_time, INTERVAL 10 MINUTE), user_id
      ORDER BY event_time ASC
    ) AS row_num
  FROM your_event_table
)
WHERE row_num = 1

方案2：分组窗口 + ARRAY_AGG 聚合取首元素 #

• 核心思路：将窗口内的目标字段按事件时间升序排序后聚合成数组，直接取数组第一个元素即可得到首次出现值，写法更简洁
• 代码示例（同上述业务场景）：

SELECT
  user_id,
  TUMBLE_START(event_time, INTERVAL 10 MINUTE) AS window_start,
  TUMBLE_END(event_time, INTERVAL 10 MINUTE) AS window_end,
  -- 排序后聚合取数组第一个元素，NULL值会自动跳过
  ARRAY_AGG(page_id ORDER BY event_time ASC LIMIT 1)[SAFE_OFFSET(0)] AS first_visit_page_id
FROM your_event_table
GROUP BY
  user_id,
  TUMBLE(event_time, INTERVAL 10 MINUTE)

注意事项 #

• 排序字段必须使用*事件时间（event_time）*而非处理时间，避免数据乱序导致的取值错误
• 若需要纳入迟到数据，可在窗口定义后添加ALLOW_LATE配置，设置合理的迟到容忍时长
• 使用SAFE_OFFSET可避免窗口内无数据时返回数组下标越界错误

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