如何用SQL实现7天时间窗口重置的用户会话事件去重计数

核心思路 #

这类窗口属于**"会话式滑动窗口"**，区别于固定周期窗口，它由事件触发启动：每个窗口从当前事件开始向后延伸7天，后续事件若落在当前窗口范围内则归为同一组，超出范围则触发新窗口。核心是通过递归或窗口函数标记每个事件所属的窗口分组。

解决方案（以PostgreSQL为例） #

1. 准备示例数据 #

假设存在用户登录表user_logins，结构及测试数据如下：

CREATE TABLE user_logins (
    user_id INT,
    login_date DATE
);

INSERT INTO user_logins VALUES
(1, '2024-01-01'),
(1, '2024-01-03'),
(1, '2024-01-10'),
(1, '2024-01-16'),
(1, '2024-01-25'),
(2, '2024-01-05'),
(2, '2024-01-12');

2. 递归CTE实现窗口分组 #

递归CTE是处理这类动态窗口的常用方案，步骤如下：

• 先按用户和登录日期排序，为每个事件分配行号
• 递归遍历每个事件，判断当前事件是否落在上一个窗口的7天范围内，是则归为同一组，否则开启新窗口

WITH ordered_logs AS (
    SELECT 
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM user_logins
),
window_groups AS (
    -- 递归起始点：每个用户的第一个事件作为首个窗口
    SELECT 
        user_id,
        login_date,
        rn,
        login_date AS window_start,
        login_date + INTERVAL '7 days' - INTERVAL '1 day' AS window_end,
        1 AS window_group
    FROM ordered_logs
    WHERE rn = 1

    UNION ALL

    -- 递归逻辑：判断当前事件是否属于上一窗口
    SELECT 
        ol.user_id,
        ol.login_date,
        ol.rn,
        CASE WHEN ol.login_date <= wg.window_end THEN wg.window_start ELSE ol.login_date END,
        CASE WHEN ol.login_date <= wg.window_end THEN wg.window_end ELSE ol.login_date + INTERVAL '7 days' - INTERVAL '1 day' END,
        CASE WHEN ol.login_date <= wg.window_end THEN wg.window_group ELSE wg.window_group + 1 END
    FROM ordered_logs ol
    JOIN window_groups wg ON ol.user_id = wg.user_id AND ol.rn = wg.rn + 1
)
SELECT 
    user_id,
    login_date,
    window_start::DATE,
    window_end::DATE,
    window_group
FROM window_groups
ORDER BY user_id, login_date;

3. 输出结果说明 #

执行上述SQL后，示例数据的输出如下：

可以看到：

• 用户1的1日、3日登录属于同一窗口（1-7日）
• 10日登录超出上一窗口范围，开启新窗口（10-16日），16日登录落在该窗口内，归为同一组
• 25日登录再次超出范围，开启第三组窗口

4. 基于窗口组的事件计数 #

若需统计每个窗口内的事件数（窗口内仅计1次），可基于上述结果分组聚合：

WITH ordered_logs AS (
    SELECT 
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM user_logins
),
window_groups AS (
    SELECT 
        user_id,
        login_date,
        rn,
        login_date AS window_start,
        login_date + INTERVAL '7 days' - INTERVAL '1 day' AS window_end,
        1 AS window_group
    FROM ordered_logs
    WHERE rn = 1

    UNION ALL

    SELECT 
        ol.user_id,
        ol.login_date,
        ol.rn,
        CASE WHEN ol.login_date <= wg.window_end THEN wg.window_start ELSE ol.login_date END,
        CASE WHEN ol.login_date <= wg.window_end THEN wg.window_end ELSE ol.login_date + INTERVAL '7 days' - INTERVAL '1 day' END,
        CASE WHEN ol.login_date <= wg.window_end THEN wg.window_group ELSE wg.window_group + 1 END
    FROM ordered_logs ol
    JOIN window_groups wg ON ol.user_id = wg.user_id AND ol.rn = wg.rn + 1
)
SELECT 
    user_id,
    window_start::DATE,
    window_end::DATE,
    COUNT(DISTINCT login_date) AS event_count -- 窗口内事件仅计1次
FROM window_groups
GROUP BY user_id, window_start, window_end
ORDER BY user_id, window_start;

其他数据库适配说明 #

• MySQL 8.0+：支持递归CTE，日期计算调整为DATE_ADD(login_date, INTERVAL 6 DAY)替代login_date + INTERVAL '7 days' - INTERVAL '1 day'
• SQL Server：递归CTE语法类似，日期计算使用DATEADD(day, 6, login_date)
• BigQuery：不支持递归CTE，可通过窗口函数结合累计最大值实现：

WITH ordered_logs AS (
    SELECT 
        user_id,
        login_date,
        LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_login
    FROM user_logins
),
window_markers AS (
    SELECT 
        user_id,
        login_date,
        CASE WHEN prev_login IS NULL OR login_date > DATE_ADD(prev_login, INTERVAL 7 DAY) THEN 1 ELSE 0 END AS new_window
    FROM ordered_logs
),
window_groups AS (
    SELECT 
        user_id,
        login_date,
        SUM(new_window) OVER (PARTITION BY user_id ORDER BY login_date) AS window_group
    FROM window_markers
)
SELECT 
    user_id,
    MIN(login_date) AS window_start,
    MIN(login_date) + INTERVAL 6 DAY AS window_end,
    COUNT(DISTINCT login_date) AS event_count
FROM window_groups
GROUP BY user_id, window_group
ORDER BY user_id, window_start;

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