如何用SQL实现每周用户分群（Cohort）的用户类型统计？

问题核心 #

原SQL仅针对单一固定周的分群计算用户类型，无法动态遍历所有目标周、关联每个分群的历史得分并按周统计。要实现需求，需重构逻辑，从生成动态分群周、预处理用户访问数据、分群内初步判定、累计历史得分到按周统计全流程拆解。

分步解决方案 #

1. 生成所有目标分群周列表 #

先确定统计的时间范围（比如近4个月的所有周），生成每个周作为分群的终点周：

WITH cohort_weeks AS (
    SELECT 
        date_trunc('week', generate_series(
            date_add('week', -16, current_date), -- 覆盖近4个月（约16周）
            current_date,
            INTERVAL '1 week'
        )) - INTERVAL '1 day' AS cohort_end_week -- 统一周结束日（例：周日）
),

2. 预处理用户周访问记录 #

提取每个用户每周是否有访问，生成唯一的user_id + 访问周记录：

user_weekly_visits AS (
    SELECT DISTINCT
        user_id,
        date_trunc('week', CAST(visits."timestamp" AS timestamp)) - INTERVAL '1 day' AS visit_week
    FROM Visits
    WHERE visits."timestamp" >= date_add('week', -23, current_date) -- 覆盖最早分群的回溯范围（8个分群+每个分群7周=23周）
),

3. 为每个分群周计算用户初步类型 #

对每个分群周，统计用户在[cohort_end_week - 7周, cohort_end_week]范围内的访问情况：

cohort_user_initial_types AS (
    SELECT
        c.cohort_end_week,
        u.user_id,
        COUNT(DISTINCT uv.visit_week) AS total_visit_weeks,
        -- 判断是否有连续4周访问：通过窗口函数标记连续周段，统计最长连续长度
        CASE 
            WHEN MAX(continuous_weeks) >= 4 THEN 'A'
            WHEN COUNT(DISTINCT uv.visit_week) >=3 THEN 'B'
            ELSE 'C'
        END AS initial_type
    FROM cohort_weeks c
    CROSS JOIN (SELECT DISTINCT user_id FROM user_weekly_visits) u
    LEFT JOIN user_weekly_visits uv
        ON uv.user_id = u.user_id
        AND uv.visit_week BETWEEN date_add('week', -7, c.cohort_end_week) AND c.cohort_end_week
    -- 子查询：计算用户连续访问周数
    LEFT JOIN (
        SELECT
            user_id,
            visit_week,
            COUNT(*) OVER (
                PARTITION BY user_id, (visit_week - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY visit_week) * INTERVAL '1 week')
            ) AS continuous_weeks
        FROM user_weekly_visits
    ) cont
        ON cont.user_id = u.user_id
        AND cont.visit_week BETWEEN date_add('week', -7, c.cohort_end_week) AND c.cohort_end_week
    GROUP BY c.cohort_end_week, u.user_id
),

4. 计算每个分群周的用户得分 #

根据初步类型，加上过去4周访问的额外加分：

cohort_user_scores AS (
    SELECT
        cohort_end_week,
        user_id,
        initial_type,
        CASE
            WHEN initial_type = 'A' THEN 
                3 + CASE WHEN EXISTS (
                    SELECT 1 FROM user_weekly_visits uv
                    WHERE uv.user_id = cus.user_id
                    AND uv.visit_week BETWEEN date_add('week', -4, cus.cohort_end_week) AND cus.cohort_end_week
                ) THEN 0.5 ELSE 0 END
            WHEN initial_type = 'B' THEN
                1 + CASE WHEN EXISTS (
                    SELECT 1 FROM user_weekly_visits uv
                    WHERE uv.user_id = cus.user_id
                    AND uv.visit_week BETWEEN date_add('week', -4, cus.cohort_end_week) AND cus.cohort_end_week
                ) THEN 0.5 ELSE 0 END
            ELSE -1
        END AS cohort_score
    FROM cohort_user_initial_types cus
),

5. 累计过去8个分群的得分并判定最终类型 #

对每个用户，按分群周排序，累计最近8个分群的得分：

user_final_types_by_cohort AS (
    SELECT
        cohort_end_week,
        user_id,
        SUM(cohort_score) OVER (
            PARTITION BY user_id
            ORDER BY cohort_end_week
            ROWS BETWEEN 7 PRECEDING AND CURRENT ROW -- 取当前分群及过去7个，共8个分群
        ) AS total_score,
        CASE
            WHEN SUM(cohort_score) OVER (
                PARTITION BY user_id
                ORDER BY cohort_end_week
                ROWS BETWEEN 7 PRECEDING AND CURRENT ROW
            ) >=12 THEN 'A'
            WHEN SUM(cohort_score) OVER (
                PARTITION BY user_id
                ORDER BY cohort_end_week
                ROWS BETWEEN 7 PRECEDING AND CURRENT ROW
            ) >=4 THEN 'B'
            ELSE 'C'
        END AS final_user_type
    FROM cohort_user_scores cus
    -- 仅保留有至少8个分群数据的记录（可根据需求调整）
    WHERE (SELECT COUNT(*) FROM cohort_weeks c WHERE c.cohort_end_week <= cus.cohort_end_week) >=8
),

6. 按周统计各类型数量 #

最后按分群周和用户类型分组计数：

final_stats AS (
    SELECT
        cohort_end_week,
        final_user_type,
        COUNT(user_id) AS type_count
    FROM user_final_types_by_cohort
    GROUP BY cohort_end_week, final_user_type
    ORDER BY cohort_end_week DESC, final_user_type
)
SELECT * FROM final_stats;

关键优化点 #

• 动态分群周：用generate_series生成所有需要统计的周，避免硬编码固定时间范围
• 连续周判定：通过visit_week - 行号*周间隔的分组逻辑，精准计算用户连续访问周数
• 滑动窗口累计：用ROWS BETWEEN窗口子句自动累计过去8个分群的得分，无需手动关联历史数据
• 性能优化：预处理用户周访问记录，减少重复计算；用EXISTS替代嵌套子查询提升查询效率

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