BigQuery如何实现行级全量历史唯一值判断并标记新老用户

你可以通过窗口函数快速实现全量数据的标记需求，核心思路是先统计每个Value的首次出现日期，再将每行日期与对应Value的首次日期对比即可。

全量计算查询语句 #

SELECT
  ID,
  Day,
  Value,
  IF(Day = MIN(Day) OVER(PARTITION BY Value), 'New', 'Returning') AS Type
FROM `你的业务表名`
ORDER BY ID

逻辑说明 #

• PARTITION BY Value 按Value字段对全表数据分组，相同Value的行会被分到同一组
• MIN(Day) OVER(...) 计算每个分组内所有行的最小日期，也就是该Value首次出现的日期
• 若当前行的日期等于该Value的首次出现日期，说明该值在当天之前从未出现，标记为New，否则标记为Returning

增量调度优化方案 #

如果是日常每日新增数据处理，不需要每次全表扫描，可以预先维护一张存储每个Value首次出现日期的映射表value_first_day（字段为Value、first_day），每日处理新增数据时关联该表即可：

WITH daily_data AS (
  -- 取当日新增数据
  SELECT * FROM `你的增量业务表` WHERE Day = '待处理的日期'
)
SELECT
  d.ID,
  d.Day,
  d.Value,
  IF(f.Value IS NULL, 'New', 'Returning') AS Type
FROM daily_data d
LEFT JOIN `value_first_day` f 
  ON d.Value = f.Value

处理完当日数据后，把当日新出现的Value（上述查询中Type为New的Value）同步更新到value_first_day映射表即可。

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