基于Athena实现自父母生日起至当前时间的月度子女人数统计

问题核心 #

你需要的是每个父母从自身出生月份到当前的所有连续月份，每个月对应子女出生数（无子女则为0），同时附带该父母第一个子女的出生日期。原SQL的问题在于：窗口函数的partition by写法错误（不能用条件分组），且没有生成连续月份的逻辑，导致缺失无子女的月份。

完整SQL实现（基于Trino/Athena） #

WITH monthly_dates AS (
    -- 生成全局连续月份序列：从所有父母中最早的出生月份到当前月
    SELECT date_trunc('month', timestamp 'epoch' + s * interval '1 month') AS month_start
    FROM generate_sequence(
        -- 计算最早父母出生月的起始epoch，转成月份数
        EXTRACT(EPOCH FROM date_trunc('month', (SELECT MIN(parent_dob) FROM "awsdatacatalog"."stackoverflow"."desired_data")))/2592000,
        -- 计算当前月的起始epoch，转成月份数
        EXTRACT(EPOCH FROM date_trunc('month', current_timestamp))/2592000
    ) s
),
parent_base AS (
    -- 预计算每个父母的核心信息：ID、出生日期、第一个子女的出生日期
    SELECT 
        parent_id,
        parent_dob,
        MIN(child_dob) AS first_child_dob
    FROM "awsdatacatalog"."stackoverflow"."desired_data"
    GROUP BY parent_id, parent_dob
),
parent_month_range AS (
    -- 给每个父母匹配其有效时间范围内的所有连续月份
    SELECT 
        pb.parent_id,
        pb.parent_dob,
        pb.first_child_dob,
        md.month_start
    FROM parent_base pb
    CROSS JOIN monthly_dates md
    -- 只保留父母出生月份及之后的月份
    WHERE md.month_start >= date_trunc('month', pb.parent_dob)
),
child_monthly_count AS (
    -- 预统计每个父母每个有子女出生的月份的数量
    SELECT 
        parent_id,
        date_trunc('month', child_dob) AS month_start,
        COUNT(child_id) AS count_of_children_in_month
    FROM "awsdatacatalog"."stackoverflow"."desired_data"
    GROUP BY parent_id, date_trunc('month', child_dob)
)
-- 最终关联，补全无子女月份的0值
SELECT 
    pmr.parent_id,
    pmr.parent_dob,
    pmr.month_start,
    COALESCE(cmc.count_of_children_in_month, 0) AS count_of_children_in_month,
    pmr.first_child_dob
FROM parent_month_range pmr
LEFT JOIN child_monthly_count cmc 
    ON pmr.parent_id = cmc.parent_id 
    AND pmr.month_start = cmc.month_start
ORDER BY pmr.parent_id, pmr.month_start;

关键逻辑说明 #

1. 生成连续月份：用generate_sequence生成月份数序列，再转成对应的月份起始时间，确保覆盖所有需要的时间范围。
2. 预聚合父母信息：提前算出每个父母的第一个子女生日，避免后续重复计算，提升效率。
3. 匹配父母的有效月份：通过笛卡尔积+过滤，确保每个父母只拿到自己出生月份到当前的连续月份。
4. 补全0值：用左连接+COALESCE，把没有子女出生的月份的统计数设为0，满足需求。

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