如何在SQL中基于50天日期范围设置用户标识（Flag）

样本表（customer）数据 #

现有查询的问题 #

以下查询尝试生成带行号和标识的结果，但逻辑存在明显错误：

select
    RecID, createdDate, UserID,
    row_number() over (partition by UserID order by UserID) as "ROWNUMBER",
    toCount
from (
    select
       *,
       (case when datediff(day, lag(createdDate,50,createdDate) over (partition by UserID order by UserID), createdDate) <= 1 
             then 'true'
             else 'false' 
        end) as toCount
    from customer
) t

错误点：

• 行号排序依据为UserID，无法按注册时间区分记录新旧；
• lag函数使用固定偏移量50，取的是当前行往前第50条记录的日期，而非当前记录的上一条注册日期；
• 日期差判断条件为<=1，与需求的50天间隔完全不符。

需求说明 #

生成标识toCount，规则如下：

• 若某条记录是用户的最新注册记录，且该用户在这条记录的50天内有其他注册记录，则toCount为false；
• 其他所有情况（非最新记录、最新记录但50天内无其他注册、仅一条注册记录），toCount为true。

期望结果 #

修正后的SQL #

select
    RecID, createdDate, UserID,
    ROWNUMBER,
    case
        -- 最新记录且上一条注册在50天内 → false
        when ROWNUMBER = 1 and datediff(day, prev_createdDate, createdDate) <= 50 then 'false'
        -- 其他情况统一标记为true
        else 'true'
    end as toCount
from (
    select
        *,
        row_number() over (partition by UserID order by createdDate desc) as ROWNUMBER,
        lag(createdDate) over (partition by UserID order by createdDate desc) as prev_createdDate
    from customer
) t

逻辑说明 #

1. 子查询按UserID分组，按createdDate降序生成行号，确保行号1对应用户的最新注册记录；
2. 使用lag函数获取每条记录的上一条（更晚的）注册日期；
3. 外层查询根据规则判断标识值，完全匹配期望结果。

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