在Amazon Redshift中基于相对日期统计群组活动数

嘿，我完全懂你的困扰——固定日期范围的统计顺手拈来，但要基于每个群组自己的创建时间来划分30天周期统计活动数，确实需要换个思路。咱们用Redshift自带的日期函数和分组逻辑就能轻松搞定，下面一步步来：

先明确假设的表结构（你可以根据实际调整） #

因为你没给出具体表结构，我先按常见的场景假设：

• groups表：存储群组基础信息，核心字段group_id（群组唯一ID）、created_at（群组创建时间）
• activities表：存储群组活动记录，核心字段activity_id（活动唯一ID）、group_id（关联的群组ID）、activity_time（活动发生时间）

基础版：统计有活动的周期（仅显示存在活动的周期） #

这个方案适合只需要看有活动产生的周期统计，SQL逻辑如下：

SELECT
    g.group_id,
    -- 计算活动所属的30天周期编号（从创建后第1天开始，周期1对应0-29天，周期2对应30-59天...）
    FLOOR(DATEDIFF(day, g.created_at, a.activity_time) / 30) + 1 AS cycle_number,
    -- 生成友好的周期名称，方便阅读
    CONCAT('创建后', (FLOOR(DATEDIFF(day, g.created_at, a.activity_time) / 30) * 30), '-', 
           (FLOOR(DATEDIFF(day, g.created_at, a.activity_time) / 30) * 30 + 29), '天') AS cycle_name,
    COUNT(a.activity_id) AS activity_count
FROM
    groups g
JOIN
    activities a ON g.group_id = a.group_id
WHERE
    -- 过滤掉异常数据：活动时间早于群组创建时间的记录
    a.activity_time >= g.created_at
GROUP BY
    g.group_id,
    cycle_number,
    cycle_name
ORDER BY
    g.group_id,
    cycle_number;

关键逻辑解释 #

• DATEDIFF(day, g.created_at, a.activity_time)：计算活动发生时间与群组创建时间的天数差
• FLOOR(天数差 / 30) + 1：把天数差转换成周期编号，确保周期从1开始计数（避免出现0编号）
• 周期名称的拼接是为了让结果更直观，如果你只需要编号，可以直接去掉这一列

进阶版：显示所有周期（包括无活动的周期） #

如果需要统计每个群组的每一个30天周期，哪怕该周期没有活动也要显示0，可以用CTE生成预设周期再关联活动数据：

-- 第一步：生成每个群组的预设周期（这里假设统计到创建后180天，共6个周期，你可以按需扩展）
WITH group_cycles AS (
    SELECT
        group_id,
        created_at,
        cycle_number,
        CONCAT('创建后', (cycle_number-1)*30, '-', cycle_number*30-1, '天') AS cycle_name
    FROM
        groups
    CROSS JOIN
        -- 生成1到6的周期编号，需要更多周期就继续加UNION ALL SELECT N
        (SELECT 1 AS cycle_number UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) cycles
),
-- 第二步：统计每个群组每个周期的实际活动数
activity_counts AS (
    SELECT
        g.group_id,
        FLOOR(DATEDIFF(day, g.created_at, a.activity_time) / 30) + 1 AS cycle_number,
        COUNT(a.activity_id) AS activity_count
    FROM
        groups g
    JOIN
        activities a ON g.group_id = a.group_id
    WHERE
        a.activity_time >= g.created_at
    GROUP BY
        g.group_id,
        cycle_number
)
-- 第三步：关联预设周期和活动统计，无活动的周期用COALESCE补0
SELECT
    gc.group_id,
    gc.cycle_name,
    COALESCE(ac.activity_count, 0) AS activity_count
FROM
    group_cycles gc
LEFT JOIN
    activity_counts ac ON gc.group_id = ac.group_id AND gc.cycle_number = ac.cycle_number
ORDER BY
    gc.group_id,
    gc.cycle_number;

调整说明 #

• 如果你的表字段名和假设的不一样（比如活动时间叫event_time），直接替换对应的字段名即可
• 要是想把周期改成其他长度（比如7天周度统计），只需要把代码里的30换成对应的数字就行
• 预设周期的数量可以根据业务需求调整，比如要统计1年的周期，就生成12个周期（12*30=360天）

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