如何在Snowflake中识别首次访问用户并添加标记列

首先，你的核心需求是为每个访客的首次访问标记为1，后续所有访问标记为0。你之前使用ROW_NUMBER()时遇到的问题，是没有按VisitorID进行分区处理，导致行号是全局排序，而非针对每个访客单独计算访问顺序。

原始数据表格 #

正确的Snowflake SQL查询 #

SELECT
  VisitorID,
  Date,
  CASE
    WHEN ROW_NUMBER() OVER (PARTITION BY VisitorID ORDER BY Date ASC) = 1 THEN 1
    ELSE 0
  END AS is_first_visit
FROM PRODUCT_VISITS_UNIQ
ORDER BY VisitorID, Date;

代码逻辑解释 #

• PARTITION BY VisitorID：将数据按访客ID拆分分组，确保每个访客的访问记录单独计算行号，不会和其他访客的记录混排。
• ORDER BY Date ASC：在每个访客的分组内，按访问日期升序排列，保证首次访问的记录会被分配到行号1的位置。
• CASE条件判断：当行号为1时标记为1（代表首次访问），其余情况标记为0（代表重复访客）。

查询结果示例 #

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