BigQuery如何查询每个用户首次点击的营销广告活动

注意：你表中的event_date为dd-mm-yyyy格式的字符串，直接按字符串排序会出现逻辑错误，需要先转换为DATE类型再做排序判断。

方案1：通用窗口函数写法（兼容性最强） #

使用ROW_NUMBER()窗口函数按用户分组后取最早日期的记录：

WITH ranked_records AS (
  SELECT 
    user_id,
    event_date,
    `mkt campaign`,
    -- 按用户分组，组内按触达日期升序排序打行号
    ROW_NUMBER() OVER (
      PARTITION BY user_id 
      ORDER BY PARSE_DATE('%d-%m-%Y', event_date) ASC
    ) AS rn
  FROM `你的表名`
)
SELECT user_id, event_date, `mkt campaign`
FROM ranked_records
WHERE rn = 1 -- 取每个用户的第一条记录即首次触达

逻辑说明：PARTITION BY user_id将所有数据按用户ID拆分分组，每组内按转换后的日期从小到大排序，行号为1的就是该用户最早的触达记录。

方案2：BigQuery专有简化写法（性能更高、代码更短） #

利用BigQuery的ARRAY_AGG聚合函数直接取分组内的第一条记录：

SELECT 
  ARRAY_AGG(
    STRUCT(user_id, event_date, `mkt campaign`)
    ORDER BY PARSE_DATE('%d-%m-%Y', event_date) ASC
    LIMIT 1
  )[OFFSET(0)].*
FROM `你的表名`
GROUP BY user_id

逻辑说明：按user_id分组后，每组内按日期升序将记录聚合为数组，取数组第一个元素即为该用户最早的触达记录。

两种方案输出结果均和你提供的预期结果完全一致。

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