不同城市事件发生时间不同时如何计算事件后月度人口变化平均值

核心逻辑为给每个城市绑定独立的事件发生基准时间，将绝对日期转换为相对事件的偏移时间维度后再做聚合计算，具体实现步骤如下：

• 第一步：整理城市事件映射表
  先输出一份结构为[城市ID, 城市名称, 事件发生年月]的配置表，确保每个城市仅对应唯一的事件发生时间，无重复/缺失记录。
• 第二步：关联计算相对时间偏移
  将原始月度人口表和上述映射表按城市维度关联，用每条记录的统计年月减去对应城市的事件发生年月，得到相对事件月份（rel_month）：事件发生当月对应rel_month=0，事件后第一个月对应rel_month=1，若需要统计事件前基准数据，事件前第一个月对应rel_month=-1。
• 第三步：计算单城市月度人口增量
  按城市分组，对每个城市的所有记录按绝对年月升序排序，通过偏移窗口函数取相邻月份的人口差值，得到每个<城市, 统计年月>对应的月度人口增减量pop_delta。
• 第四步：按相对时间聚合统计
  过滤出需要分析的相对时间范围（比如仅统计事件发生后0~12个月的数据），按rel_month分组对所有城市的pop_delta取平均值，即可得到对齐事件时间后的各月平均人口增减规模。

以下是标准SQL实现示例，可根据自己使用的数据库语法调整细节：

WITH city_event_map AS (
    -- 城市事件发生时间映射表，可替换为实际表名
    SELECT city_id, city_name, event_yr_month FROM city_event_config
),
pop_with_rel_month AS (
    -- 关联原始表生成相对月份
    SELECT 
        t1.city_id,
        t1.yr_month,
        t1.population,
        -- 年月格式为'YYYYMM'字符串/整数的计算逻辑，DATE格式可直接用对应数据库的月份差函数
        (CAST(SUBSTR(t1.yr_month,1,4) AS INT) - CAST(SUBSTR(t2.event_yr_month,1,4) AS INT))*12 
        + (CAST(SUBSTR(t1.yr_month,5,2) AS INT) - CAST(SUBSTR(t2.event_yr_month,5,2) AS INT)) AS rel_month
    FROM monthly_pop_table t1
    JOIN city_event_map t2 ON t1.city_id = t2.city_id
),
pop_delta_cal AS (
    -- 计算单城市每月人口增减量
    SELECT 
        *,
        population - LAG(population,1) OVER(PARTITION BY city_id ORDER BY yr_month ASC) AS pop_delta
    FROM pop_with_rel_month
)
-- 按相对月份聚合得到最终统计结果
SELECT 
    rel_month,
    AVG(pop_delta) AS avg_pop_change,
    COUNT(DISTINCT city_id) AS valid_city_cnt -- 可选字段，标注每个相对月份的有效样本量
FROM pop_delta_cal
WHERE rel_month >= 0 -- 仅统计事件发生后的数据，可按需调整范围
GROUP BY rel_month
ORDER BY rel_month ASC;

注意事项 #

• 若部分城市在指定统计周期内存在月份数据缺失，可根据分析需求选择剔除该城市对应周期的所有数据，避免样本不均导致的统计偏差
• 若需要分城市等级、所在区域等维度做细分统计，在最终聚合步骤增加对应分组字段即可
• 若统计年月为DATE类型，可直接使用数据库内置函数计算月份差：如MySQL用PERIOD_DIFF、Hive/Spark SQL用DATE_DIFF，无需手动拆分年月计算

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