SQL Server中如何将不可拆分季度目标转为周目标生成含实绩的周视图

现有数据 #

• Actuals表：按日存储销售人员目标完成实绩，已聚合生成周度实绩视图vw_Actuals
• Goals表：按年度+季度存储每个销售人员2021年前两个季度的季度目标
• 已创建周维度表WeekTable，包含weekstart、weekend、year、quarter字段

需求 #

生成带周起始、周结束日期的周度视图，同时展示每个销售人员的周度实绩与整数型周度目标（不可拆分，无小数）

原方案问题 #

你之前写的WHILE循环无限运行的核心原因是：@totalOppor、@iParEnd、@qtrlyGoal等核心变量仅在循环外初始化一次，循环内插入数据后没有重新计算这些变量的值，导致跳出条件永远无法触发，同时循环遍历的方式性能极低，完全可以用集合运算替代。

无需循环，直接通过窗口函数实现整数型季度目标的周度拆分，逻辑如下：

WITH sales_quarter_weeks AS (
    -- 生成每个销售人员、每个季度对应的所有周映射，标记季度内周序号、季度总周数
    SELECT
        g.salespersonID,
        g.salespersonName,
        g.Qtr,
        g.Year,
        g.QtrlyGoals,
        w.WeekStart,
        w.WeekEnd,
        COUNT(*) OVER(PARTITION BY g.salespersonID, g.Year, g.Qtr) AS quarter_total_weeks,
        ROW_NUMBER() OVER(PARTITION BY g.salespersonID, g.Year, g.Qtr ORDER BY w.WeekStart) AS week_in_quarter_rank
    FROM Goals g
    JOIN WeekTable w ON g.Year = w.year AND g.Qtr = w.quarter
),
weekly_goals AS (
    -- 计算整数周目标：基础值+余数分配，总和严格等于季度目标
    SELECT
        salespersonID,
        salespersonName,
        Qtr,
        Year,
        WeekStart,
        WeekEnd,
        QtrlyGoals / quarter_total_weeks 
        + CASE WHEN week_in_quarter_rank <= QtrlyGoals % quarter_total_weeks THEN 1 ELSE 0 END AS WeeklyGoal
    FROM sales_quarter_weeks
)
-- 关联周度实绩生成最终视图
SELECT
    wg.salespersonID,
    wg.salespersonName,
    wg.Year,
    wg.Qtr,
    wg.WeekStart,
    wg.WeekEnd,
    wg.WeeklyGoal,
    ISNULL(va.ActualValue, 0) AS WeeklyActual
FROM weekly_goals wg
LEFT JOIN vw_Actuals va 
    ON wg.salespersonID = va.salespersonID 
    AND wg.WeekStart = va.WeekStart
ORDER BY wg.salespersonID, wg.WeekStart

方案说明 #

• 自动保证所有周目标总和严格等于季度目标，且所有周目标均为正整数，符合不可拆分实体的要求
• 如果需要调整周目标分配规则，比如优先分配给历史产能更高的周，仅需要修改ROW_NUMBER的排序逻辑即可
• 纯集合运算，性能远高于循环遍历插入方案

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