SQL实现事件重复统计及按7天/30天/超30天聚合的技术求助

核心思路拆解 #

不需要依赖循环逻辑，用SQL窗口函数+条件聚合就能完成需求，核心步骤：

1. 标记每个事件的首次发生日期
2. 计算后续事件与首次事件的间隔天数
3. 按时间区间统计重复次数，按周期段聚合事件组数

具体SQL代码实现 #

假设你的数据表名为event_logs，包含字段event_id（事件唯一标识，如A、B）、event_date（事件发生日期）：

第一步：计算首次事件日期及间隔天数 #

WITH event_with_first_date AS (
    SELECT 
        event_id,
        event_date,
        -- 获取当前事件的首次发生日期
        FIRST_VALUE(event_date) OVER (PARTITION BY event_id ORDER BY event_date) AS first_event_date,
        -- 计算与首次事件的间隔天数
        DATEDIFF(event_date, FIRST_VALUE(event_date) OVER (PARTITION BY event_id ORDER BY event_date)) AS days_since_first
    FROM event_logs
)

第二步：按事件聚合统计各维度数据 #

SELECT 
    event_id AS 唯一事件,
    COUNT(*) AS 总事件数,
    -- 3天内重复次数（排除首次事件）
    SUM(CASE WHEN days_since_first > 0 AND days_since_first <=3 THEN 1 ELSE 0 END) AS 3天内重复数,
    -- 7天内重复次数（间隔1-7天）
    SUM(CASE WHEN days_since_first > 0 AND days_since_first <=7 THEN 1 ELSE 0 END) AS 7天内重复数,
    -- 30天内重复次数（间隔1-30天）
    SUM(CASE WHEN days_since_first > 0 AND days_since_first <=30 THEN 1 ELSE 0 END) AS 30天内重复数,
    -- 超过30天重复次数（间隔>30天）
    SUM(CASE WHEN days_since_first >30 THEN 1 ELSE 0 END) AS 超30天重复数,
    -- 7天周期组数：首次事件后每7天为一个周期的事件分组数
    COUNT(DISTINCT CASE WHEN days_since_first <=7 THEN FLOOR(days_since_first/7) ELSE NULL END) AS 7天周期组数,
    -- 30天周期组数：首次事件后每30天为一个周期的事件分组数
    COUNT(DISTINCT CASE WHEN days_since_first <=30 THEN FLOOR(days_since_first/30) ELSE NULL END) AS 30天周期组数,
    -- 超30天周期组数：首次事件后超过30天的部分，每30天为一个周期的分组数
    COUNT(DISTINCT CASE WHEN days_since_first >30 THEN FLOOR((days_since_first-30)/30) +1 ELSE NULL END) AS 超30天周期组数
FROM event_with_first_date
GROUP BY event_id
ORDER BY event_id;

关键逻辑说明 #

• 用窗口函数FIRST_VALUE()替代循环，一次性获取每个事件的首次日期，避免逐行处理
• DATEDIFF()用于计算间隔天数，不同数据库函数可能有差异（比如PostgreSQL用AGE()或DATE_PART()）
• 条件聚合SUM(CASE...)精准统计各时间区间的重复次数
• COUNT(DISTINCT FLOOR(...))实现按周期段分组统计，比如每7天为一个独立周期

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