SQL按项目分月统计活动起止数及累计值实现方法

之前的写法存在两个核心错误：

• 窗口函数分区规则错误：将具体的Start Date/End Date放入PARTITION BY子句，会导致统计粒度落到单日而非要求的月份维度，累计计算的分组边界完全错误
• 未拆分事件类型：单条活动记录的开始、结束时间可能落在不同月份，直接在原表上同时计数开始、结束事件，会出现跨月活动统计错位、无事件月份漏统计的问题

核心逻辑是先拆分开始/结束两类事件，聚合到项目-月份粒度后再做累计计算，标准SQL写法如下（兼容PostgreSQL、Spark SQL、BigQuery等主流引擎）：

WITH event_unpivot AS (
    -- 拆分每条活动的开始、结束事件，统一关联到对应月份
    SELECT
        project,
        DATE_TRUNC('month', "Start Date") AS event_month,
        1 AS start_flag,
        0 AS stop_flag
    FROM project_activities
    UNION ALL
    SELECT
        project,
        DATE_TRUNC('month', "End Date") AS event_month,
        0 AS start_flag,
        1 AS stop_flag
    FROM project_activities
),
monthly_aggregate AS (
    -- 按项目+月份聚合，计算当月开始、结束的活动数
    SELECT
        project,
        event_month AS Month,
        SUM(start_flag) AS Monthly_Starts,
        SUM(stop_flag) AS Monthly_Stops
    FROM event_unpivot
    GROUP BY project, event_month
)
-- 按项目分区、按月份排序计算累计值
SELECT
    project,
    Month,
    Monthly_Starts,
    SUM(Monthly_Starts) OVER (
        PARTITION BY project
        ORDER BY Month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Cumulative_Starts,
    Monthly_Stops,
    SUM(Monthly_Stops) OVER (
        PARTITION BY project
        ORDER BY Month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Cumulative_Stops
FROM monthly_aggregate
ORDER BY project, Month;

• 如果需要输出项目全周期内连续无中断的月份（包括没有任何活动开始/结束的月份），可以先生成项目-月份的全量笛卡尔基表，再左关联上述聚合结果，将空值用COALESCE(统计字段, 0)补0即可
• 如果使用MySQL等不支持DATE_TRUNC函数的引擎，可以用DATE_FORMAT(event_date, '%Y-%m-01')替代实现月份截断效果
• 窗口函数中显式声明ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，是为了避免部分引擎默认窗口范围异常导致累计值计算错误

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