动态起始10天滑动时间窗口的SQL计数查询实现求助

这个需求属于动态会话划分问题，窗口起点依赖前一个有效事件的时间块，无法用普通滑动窗口函数实现，推荐用递归CTE（Common Table Expression）逐行追踪每个ID的时间块范围。

假设你的数据表名为event_table，包含字段id（用户ID）和event_date（事件日期，需确保为日期类型而非字符串），以下是适配主流数据库的SQL实现：

通用逻辑（以PostgreSQL为例） #

WITH ordered_events AS (
    -- 给每个ID的事件按日期排序，生成行号
    SELECT 
        id,
        event_date,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY event_date) AS row_num
    FROM event_table
),
session_tracking AS (
    -- 初始行：每个ID的第一条事件，标记为新事件（Count=1），初始化时间块
    SELECT
        id,
        event_date,
        1 AS count,
        event_date AS block_start,
        event_date + INTERVAL '10 days' AS block_end
    FROM ordered_events
    WHERE row_num = 1
    
    UNION ALL
    
    -- 递归处理后续事件
    SELECT
        oe.id,
        oe.event_date,
        -- 判断当前事件是否超出上一个时间块，是则标记为新事件
        CASE WHEN oe.event_date > st.block_end THEN 1 ELSE 0 END AS count,
        -- 超出则更新时间块起点，否则沿用原起点
        CASE WHEN oe.event_date > st.block_end THEN oe.event_date ELSE st.block_start END AS block_start,
        -- 同步更新时间块终点
        CASE WHEN oe.event_date > st.block_end THEN oe.event_date + INTERVAL '10 days' ELSE st.block_end END AS block_end
    FROM session_tracking st
    JOIN ordered_events oe 
        ON st.id = oe.id 
        AND oe.row_num = st.row_num + 1
)
-- 格式化输出结果
SELECT
    id AS "ID",
    TO_CHAR(event_date, 'MM/DD/YY') AS "Date",
    count AS "Count"
FROM session_tracking
ORDER BY id, event_date;

不同数据库的适配调整 #

• MySQL：将INTERVAL '10 days'替换为INTERVAL 10 DAY，日期格式化用DATE_FORMAT(event_date, '%m/%d/%y')，递归CTE写法一致。
• SQL Server：将INTERVAL '10 days'替换为DATEADD(day, 10, event_date)，日期格式化用FORMAT(event_date, 'MM/dd/yy')，递归CTE写法一致。
• Oracle：将INTERVAL '10 days'替换为event_date + 10，日期格式化用TO_CHAR(event_date, 'MM/DD/YY')，递归CTE需用Oracle 11g及以上支持的语法。

逻辑说明 #

1. ordered_events：按ID分组、日期排序，给每个事件生成行号，确保递归时能按顺序处理每个ID的事件。
2. session_tracking：
   • 初始部分取每个ID的第一条事件，标记为Count=1，并设置第一个10天时间块的起止时间。
   • 递归部分逐行处理后续事件：如果当前事件日期超出上一个时间块的终点，就标记为新事件（Count=1），同时更新时间块的起止；否则标记为Count=0，沿用原时间块。
3. 最后格式化日期为示例要求的MM/DD/YY格式，输出结果。

示例输出验证 #

针对你给出的示例数据，执行后会得到完全匹配的结果：

（注：示例中的*仅用于标记新事件起点，对应SQL中Count=1的行）

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