在BigQuery中按日期和用户ID获取每日首个用户事件的方法

原SQL错误原因 #

• 分区逻辑错误：partition by date是按天全局分组，若要实现「每个用户单日」维度分组，分区键需要同时包含userId和date两个字段
• 排序逻辑错误：order by userId是按用户ID排序，无法体现事件发生的先后顺序，需要使用事件精确时间戳（通常事件表会有event_time/event_ts这类存储时分秒的时间字段，下文统一用event_time指代）作为排序依据，才能匹配到单日最早的事件
• 去重逻辑缺失：仅用FIRST_VALUE仅会新增一列标记值，不会自动过滤多余记录，需要额外加过滤条件保留首条记录

实现方案 #

方案1：标准SQL通用写法（兼容所有支持窗口函数的SQL引擎） #

WITH event_rank AS (
  SELECT 
    *,
    ROW_NUMBER() OVER(
      PARTITION BY userId, date 
      ORDER BY event_time ASC -- 时间正序，最早发生的事件排在第一位
    ) AS row_num
  FROM `你的项目.你的数据集.你的表名`
  -- 可按需加过滤条件：WHERE date BETWEEN '2018-09-29' AND '2018-09-30'
)
SELECT * EXCEPT(row_num) -- 排除序号列，保留原始字段
FROM event_rank
WHERE row_num = 1 -- 仅保留每个用户每日第一条记录

方案2：BigQuery专用简洁写法（使用QUALIFY语法） #

BigQuery原生支持QUALIFY语法，可以省去子查询，代码更简洁：

SELECT *
FROM `你的项目.你的数据集.你的表名`
-- WHERE date BETWEEN '2018-09-29' AND '2018-09-30'
QUALIFY ROW_NUMBER() OVER(PARTITION BY userId, date ORDER BY event_time ASC) = 1

补充说明：如果你的表没有单独的精确时间字段，event字段本身存储了可标识事件先后的有序值，把ORDER BY后的event_time替换为对应的可排序字段即可。

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