使用SQL计算分组内项目月度进度百分比的技术问题

看起来你需要给每个项目的存续月份计算递增的进度百分比，核心挑战是没有项目ID来关联同一个项目的不同月份。下面我会给出具体的SQL实现方案，分步骤拆解逻辑：

前提假设 #

先明确几个必要的前提（如果你的数据集有差异，可以灵活调整）：

• 原始表名为 project_registrations
• 表中有 registration_month 字段（日期类型，存储项目注册的月份，比如 '2011-06-01'）
• 表中有 duration_months 字段（存储项目的持续月数N，比如4表示该项目持续4个月）
• 如果没有duration_months，你需要补充这个字段（比如通过项目结束日期计算：DATEDIFF(month, registration_month, end_month) + 1）

步骤1：给每个项目生成临时唯一ID #

因为没有项目ID，我们需要先给每条注册记录分配一个临时ID，用来唯一标识每个项目：

WITH project_with_id AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY registration_month) AS temp_project_id,
        registration_month,
        duration_months
    FROM project_registrations
)

步骤2：生成每个项目的所有存续月份 #

用递归CTE来自动生成每个项目从注册月开始的N个连续月份：

, project_months AS (
    -- 递归起始：每个项目的第一个月（注册月）
    SELECT
        temp_project_id,
        registration_month AS project_month,
        1 AS month_sequence,
        duration_months
    FROM project_with_id

    UNION ALL

    -- 递归生成后续月份（不同SQL方言语法略有差异）
    SELECT
        pm.temp_project_id,
        -- PostgreSQL: (pm.project_month + INTERVAL '1 month')::DATE
        -- SQL Server: DATEADD(month, 1, pm.project_month)
        -- MySQL: DATE_ADD(pm.project_month, INTERVAL 1 MONTH)
        DATEADD(month, 1, pm.project_month) AS project_month,
        pm.month_sequence + 1 AS month_sequence,
        pm.duration_months
    FROM project_months pm
    JOIN project_with_id pwi ON pm.temp_project_id = pwi.temp_project_id
    WHERE pm.month_sequence < pwi.duration_months
)

步骤3：计算进度百分比 #

最后在生成的月份序列基础上，计算每个月份的进度：

SELECT
    temp_project_id,
    project_month,
    ROUND((month_sequence * 100.0 / duration_months), 2) AS percentage
FROM project_months
ORDER BY temp_project_id, project_month;

完整SQL示例（以SQL Server为例） #

把上面的部分整合起来，直接可用：

WITH project_with_id AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY registration_month) AS temp_project_id,
        registration_month,
        duration_months
    FROM project_registrations
),
project_months AS (
    SELECT
        temp_project_id,
        registration_month AS project_month,
        1 AS month_sequence,
        duration_months
    FROM project_with_id

    UNION ALL

    SELECT
        pm.temp_project_id,
        DATEADD(month, 1, pm.project_month) AS project_month,
        pm.month_sequence + 1 AS month_sequence,
        pm.duration_months
    FROM project_months pm
    JOIN project_with_id pwi ON pm.temp_project_id = pwi.temp_project_id
    WHERE pm.month_sequence < pwi.duration_months
)
SELECT
    temp_project_id,
    project_month,
    ROUND((month_sequence * 100.0 / duration_months), 2) AS percentage
FROM project_months
ORDER BY temp_project_id, project_month;

关键说明 #

• 临时ID的作用：通过ROW_NUMBER()生成的临时ID，解决了无项目ID无法分组的问题，确保每个项目的月份序列能正确关联。
• 递归CTE的作用：自动生成每个项目的所有存续月份，避免手动维护月份记录，适合批量处理。
• 进度计算逻辑：用当前月份在项目周期内的序号（month_sequence）除以总持续月数，再乘以100得到百分比，ROUND()用来控制小数位数，让结果更整洁。
• 方言适配：如果使用PostgreSQL或MySQL，只需要修改日期递增的语法（注释里已经标注对应写法）。

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