如何通过SQL实现每7天重置用户登录的唯一标记

完全可以通过SQL实现这个需求，核心思路是基于用户首次登录的累计天数差，按7天为周期划分窗口，每个周期内的首次登录标记为1，其余为0。

原始表 #

目标表示例 #

实现SQL（通用窗口函数版本） #

SELECT
    UserID,
    dateLogged,
    DayscumulativeDiff,
    CASE
        -- 首次登录直接标记为1
        WHEN ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY dateLogged) = 1 THEN 1
        -- 对比当前与上一条记录的7天周期分组，不同则标记1
        WHEN FLOOR(COALESCE(DayscumulativeDiff, 0)/7) 
             != FLOOR(COALESCE(LAG(DayscumulativeDiff) OVER (PARTITION BY UserID ORDER BY dateLogged), 0)/7) THEN 1
        ELSE 0
    END AS IsUnique
FROM
    your_table_name
ORDER BY
    UserID, dateLogged;

代码说明 #

• ROW_NUMBER() 识别每个用户的首次登录记录，直接标记为1。
• COALESCE(DayscumulativeDiff, 0) 将首次登录的null天数差转为0，统一周期计算逻辑。
• FLOOR(DayscumulativeDiff/7) 把累计天数按7天分组：0-6天为组0，7-13天为组1，14-20天为组2，以此类推。
• LAG(DayscumulativeDiff) 获取上一条登录的累计天数差，若当前记录的分组与上一条不同，说明进入新的7天周期，标记为1。

无DayscumulativeDiff字段的扩展方案 #

如果表中没有预计算的DayscumulativeDiff，可以通过首次登录日期自行计算：

WITH user_first_login AS (
    SELECT
        UserID,
        MIN(dateLogged) AS first_login_date
    FROM your_table_name
    GROUP BY UserID
)
SELECT
    t.UserID,
    t.dateLogged,
    DATEDIFF(day, u.first_login_date, t.dateLogged) AS DayscumulativeDiff,
    CASE
        WHEN ROW_NUMBER() OVER (PARTITION BY t.UserID ORDER BY t.dateLogged) = 1 THEN 1
        WHEN FLOOR(DATEDIFF(day, u.first_login_date, t.dateLogged)/7)
             != FLOOR(DATEDIFF(day, u.first_login_date, LAG(t.dateLogged) OVER (PARTITION BY t.UserID ORDER BY t.dateLogged))/7) THEN 1
        ELSE 0
    END AS IsUnique
FROM your_table_name t
JOIN user_first_login u ON t.UserID = u.UserID
ORDER BY t.UserID, t.dateLogged;

注：DATEDIFF函数语法因数据库而异，MySQL用DATEDIFF(t.dateLogged, u.first_login_date)，Oracle用TRUNC(t.dateLogged) - TRUNC(u.first_login_date)，需根据实际数据库调整。

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