PostgreSQL统计指定周期供应商累计数及周环比增长问题求助

需求说明 #

需要统计指定日期及之后有课程运行的供应商数量，且供应商仅在其课程覆盖的周内被计入统计；同时计算周环比（WoW）增长。例如：若供应商课程在1月16日当周至2月6日当周运行，则仅在这几周的统计行中包含该供应商，其他周不计入。

原代码问题分析 #

原代码存在两个核心问题：

• 错误地以ms.created_at（供应商创建时间）作为周分组维度，而非课程实际运行的周
• 累计逻辑基于供应商创建时间，不符合“仅在课程运行周计入供应商”的需求
• 第8行的会话筛选条件未明确关联课程运行周的逻辑

修正后的SQL实现 #

-- 步骤1：获取每个有效活动的会话时间范围及供应商关联
WITH activity_sessions AS (
    SELECT
        ma.id AS activity_id,
        ma.supplier_id,
        MIN(s.date) AS first_session_date,
        MAX(s.date) AS last_session_date,
        COUNT(DISTINCT s.id) AS bookable_sessions
    FROM marketplace_activity ma
    JOIN marketplace_activitysession s 
        ON s.activity_id = ma.id
        AND s.is_deleted = FALSE  -- 筛选未删除的有效会话，可添加其他会话条件
    WHERE
        ma.booking_type = 'instant'
        AND ma.status = 'PUBLISHED'
    GROUP BY ma.id, ma.supplier_id
    HAVING COUNT(DISTINCT s.id) > 0  -- 仅保留有有效会话的活动
),
-- 步骤2：生成每个活动覆盖的所有周（从首次会话周到末次会话周）
activity_weeks AS (
    SELECT
        supplier_id,
        date_trunc('week', generate_series(
            date_trunc('week', first_session_date),
            date_trunc('week', last_session_date),
            INTERVAL '1 week'
        ))::DATE AS week_start
    FROM activity_sessions
    -- 可选：添加指定起始日期条件，比如 WHERE first_session_date >= '2024-01-01'
),
-- 步骤3：按周统计活跃供应商数及累计数
weekly_suppliers AS (
    SELECT
        week_start,
        COUNT(DISTINCT supplier_id) AS weekly_active_suppliers,
        -- 累计活跃供应商：到当前周为止所有曾活跃的供应商总数（去重）
        COUNT(DISTINCT supplier_id) OVER (
            ORDER BY week_start
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_active_suppliers
    FROM activity_weeks
    GROUP BY week_start
    ORDER BY week_start
)
-- 最终查询：计算周环比增长
SELECT
    week_start,
    weekly_active_suppliers,
    cumulative_active_suppliers,
    -- 处理第一周无环比的情况，结果保留两位小数
    CASE
        WHEN LAG(weekly_active_suppliers) OVER (ORDER BY week_start) IS NULL THEN NULL
        ELSE ROUND(
            (weekly_active_suppliers - LAG(weekly_active_suppliers) OVER (ORDER BY week_start))::NUMERIC
            / LAG(weekly_active_suppliers) OVER (ORDER BY week_start)::NUMERIC * 100,
            2
        )
    END AS wow_growth_percent
FROM weekly_suppliers
ORDER BY week_start DESC;

关键逻辑说明 #

1. 有效活动筛选：先过滤出已发布、支持即时预订且有未删除会话的活动，获取每个活动的首次/末次会话日期
2. 生成覆盖周：使用generate_series生成每个活动从首次到末次会话的所有周，确保供应商仅在课程运行的周内被计入
3. 周维度统计：按周分组统计活跃供应商数（去重，避免同一供应商多个活动重复计数），同时计算累计活跃供应商数
4. 周环比计算：使用LAG函数获取上周的活跃供应商数，计算环比增长率，处理第一周无数据的情况

自定义调整点 #

• 如果需要指定统计的起始日期，在activity_weeks的FROM子句后添加WHERE first_session_date >= 'YYYY-MM-DD'
• 如果有其他会话筛选条件（比如会话状态、特定日期范围），可在activity_sessions的JOIN条件中补充（例如AND s.date >= '2024-01-01'）

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