DB2需求：标记用户首次访问及距上次标记超60天的访问

问题回顾 #

给定用户访问数据，需要标记两类访问为NewVisit=1：

• 用户的首次访问
• 距离该用户上次被标记为NewVisit的日期超过60天的访问
  其余访问标记为NewVisit=0

原始数据集：

核心问题分析 #

单纯用LAG()函数只能获取上一行的访问日期，但我们需要跟踪的是最近一次被标记为NewVisit的日期，而非上一行的日期——这就是LAG()无法满足需求的原因。

实现代码（以MySQL为例） #

-- 第一步：转换日期格式并排序
WITH formatted_visits AS (
    SELECT 
        UserID,
        STR_TO_DATE(VisitDate, '%m/%d/%y') AS VisitDate
    FROM your_table
    ORDER BY UserID, VisitDate
),
-- 第二步：标记NewVisit
flagged_visits AS (
    SELECT 
        *,
        CASE
            -- 首次访问直接标记为1
            WHEN ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY VisitDate) = 1 THEN 1
            -- 计算当前日期与最近的NewVisit日期的差值，超过60天则标记为1
            WHEN DATEDIFF(VisitDate,
                          MAX(CASE WHEN NewVisit = 1 THEN VisitDate END)
                          OVER (PARTITION BY UserID ORDER BY VisitDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)) > 60 THEN 1
            ELSE 0
        END AS NewVisit
    FROM formatted_visits
)
-- 第三步：输出结果，转换回原日期格式
SELECT
    UserID,
    DATE_FORMAT(VisitDate, '%m/%d/%y') AS VisitDate,
    NewVisit
FROM flagged_visits;

适配其他SQL方言的说明 #

• PostgreSQL：将STR_TO_DATE替换为TO_DATE(VisitDate, 'MM/DD/YY')，DATEDIFF替换为VisitDate - 最近标记日期（直接日期减法得到天数）
• SQL Server：将STR_TO_DATE替换为CONVERT(date, VisitDate, 101)，DATEDIFF语法改为DATEDIFF(day, 最近标记日期, VisitDate)

逻辑解释 #

1. 先将字符串类型的VisitDate转换为日期类型，确保日期差值计算准确
2. 按UserID分组、VisitDate排序后，用ROW_NUMBER()标记首次访问
3. 用窗口函数MAX(CASE ...)获取当前记录之前最近的NewVisit日期，计算与当前日期的差值，超过60天则标记为1

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