PostgreSQL：如何统计每月完整实习周期的人员数量？

假设你的实习表名为 internships，字段对应为 user_id、start_date（开始日期）、end_date（结束日期），以下是直接解决需求的完整方案：

完整SQL代码 #

WITH adjusted_dates AS (
    SELECT
        user_id,
        -- 调整开始日期：仅保留完整参与的月份，非月初开始则从下月第一天算起
        CASE
            WHEN start_date = date_trunc('month', start_date)::date THEN start_date
            ELSE (date_trunc('month', start_date) + INTERVAL '1 month')::date
        END AS valid_start,
        -- 调整结束日期：仅保留完整参与的月份，非月末结束则统计到上月为止
        CASE
            WHEN end_date = (date_trunc('month', end_date) + INTERVAL '1 month - 1 day')::date 
            THEN (date_trunc('month', end_date) + INTERVAL '1 month')::date
            ELSE date_trunc('month', end_date)::date
        END AS valid_end
    FROM internships
),
user_months AS (
    SELECT
        user_id,
        date_trunc('month', generate_series)::date AS month
    FROM adjusted_dates
    -- 生成有效区间内的所有完整月份（不包含valid_end节点）
    CROSS JOIN generate_series(valid_start, valid_end - INTERVAL '1 day', INTERVAL '1 month')
)
SELECT
    month,
    COUNT(DISTINCT user_id) AS intern_count
FROM user_months
GROUP BY month
ORDER BY month;

代码分步解释 #

1. 调整有效统计区间（adjusted_dates CTE） #

• valid_start：如果实习开始日是当月第一天，直接使用；否则自动跳至下一月第一天，确保只统计用户完整参与的月份。比如2019-12-22会被调整为2020-01-01。
• valid_end：如果实习结束日是当月最后一天，自动跳至下一月第一天；否则取当月第一天。这样后续生成序列时会自动排除结束日所在的不完整月份，比如2020-06-29会被调整为2020-06-01，最终统计截止到2020-05。

2. 生成用户的有效月份序列（user_months CTE） #

• 使用PostgreSQL内置的generate_series函数，按月份生成valid_start到valid_end前一天的所有日期节点，再通过date_trunc('month')统一转为当月第一天作为统计维度。
• CROSS JOIN会为每个用户生成其所有有效实习月份的独立记录，替代了你原本想的"重塑表格"操作，逻辑更直接高效。

3. 分组统计人数 #

• 按month分组，用COUNT(DISTINCT user_id)统计每个月份的实习人数（避免同一用户被重复计数），最后按月份排序得到有序结果。

测试数据的预期结果 #

针对你提供的3条测试数据，运行后会得到如下结果：

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