SQL实现年度累计值预测：基于历史周均值补全全年数据

现有正确代码：计算当前年度周度累计值 #

已通过以下SQL正确计算出当前年度截至有效数据周的周度累计值（Running_Count）：

SELECT 
    count(policynumber) as policies,
    DATEPART(WEEK, DATEADD(DAY, 7-(DATEPART(WEEKDAY, issuedate)), issuedate)) as weekofyear,
    year(issuedate) as year,
    sum(count(policynumber)) OVER (PARTITION BY year(issuedate) ORDER BY DATEPART(WEEK, DATEADD(DAY, 7-(DATEPART(WEEKDAY, issuedate)), issuedate))) as Running_Count
FROM 
    insure
where
year(issuedate) in (select max(year(issuedate)) as year from insure) and policyname like ('%contents%')

group by year(issuedate),DATEPART(WEEK, DATEADD(DAY, 7-(DATEPART(WEEKDAY, issuedate)), issuedate))

当前问题 #

将上述结果关联到包含全年所有周的LOOKUP_CALENDAR日历表后，年度剩余周的Running_Count显示为0。

核心需求 #

取本年度的周平均值，通过累加方式将其添加到最后一个有效累计值中，补全至年末，规则如下：

• 第37周：最后一个有效累计值24623 + 周均值683 = 25303
• 第38周：24623 + 683×2 = 25986
• 以此类推，直到第52/53周

要求仅在无有效Running_Count的周，生成符合上述累加规则的预测值。

当前错误查询代码 #

当前完整查询的Test列计算逻辑错误（出现重复计数），代码如下：

select
subg.year,
subg.weekofyear,
subg.running_count,
case
    when subg.average=subg.running_count then 
        sum(subg.running_count) OVER (PARTITION BY subg.year ORDER BY subg.weekofyear) else 0 end as test
from(
/*Returns full year dates with year to date results*/
SELECT distinct
      DATEPART(WEEK, DATEADD(DAY, 7-(DATEPART(WEEKDAY, cal.date)), cal.date)) as weekofyear
    , cal.year
    , syear.average
    , case when ins.Running_Count is null then syear.average else ins.Running_Count end as running_count
FROM
    LOOKUP_CALENDAR cal left outer join
    (SELECT 
    count(policynumber) as policies,
    DATEPART(WEEK, DATEADD(DAY, 7-(DATEPART(WEEKDAY, issuedate)), issuedate)) as weekofyear,
    year(issuedate) as year,
    sum(count(policynumber)) OVER (PARTITION BY year(issuedate) ORDER BY DATEPART(WEEK, DATEADD(DAY, 7-(DATEPART(WEEKDAY, issuedate)), issuedate))) as Running_Count
FROM 
    insure
where
year(issuedate) in (select max(year(issuedate)) as year from insure) and policyname like ('%contents%')

group by year(issuedate),DATEPART(WEEK, DATEADD(DAY, 7-(DATEPART(WEEKDAY, issuedate)), issuedate))
    ) ins on ins.weekofyear= DATEPART(WEEK, DATEADD(DAY, 7-(DATEPART(WEEKDAY, cal.date)), cal.date)) left outer join
/*Returns Current year to date values*/
    (select
sub.sub_year,
avg(sub.policies) as average
from
(
SELECT 
year(issuedate) as sub_year,
      count(policynumber) as policies
FROM 
    insure
where
year(issuedate)  in (select max(year(issuedate)) as year from INS_MERGE_1) and
policyname like ('%contents%')
group by year(issuedate),DATEPART(WEEK, DATEADD(DAY, 7-(DATEPART(WEEKDAY, issuedate)), issuedate))) sub

group by sub.sub_year) syear on syear.sub_year=cal.year
/*Returns Current year to date values*/
where 
    cal.year in (select max(year(issuedate)) as year from insure)) as subg
/*Returns full year dates with year to date results*/

中间结果示例 #

年度周平均值为683，剩余周的Running_Count为0。

修正后的SQL代码 #

WITH yearly_data AS (
    -- 计算当前年度的周度数据及累计值
    SELECT 
        COUNT(policynumber) AS policies,
        DATEPART(WEEK, DATEADD(DAY, 7-(DATEPART(WEEKDAY, issuedate)), issuedate)) AS weekofyear,
        YEAR(issuedate) AS year,
        SUM(COUNT(policynumber)) OVER (PARTITION BY YEAR(issuedate) ORDER BY DATEPART(WEEK, DATEADD(DAY, 7-(DATEPART(WEEKDAY, issuedate)), issuedate))) AS Running_Count
    FROM 
        insure
    WHERE
        YEAR(issuedate) = (SELECT MAX(YEAR(issuedate)) FROM insure) 
        AND policyname LIKE '%contents%'
    GROUP BY YEAR(issuedate), DATEPART(WEEK, DATEADD(DAY, 7-(DATEPART(WEEKDAY, issuedate)), issuedate))
),
weekly_avg AS (
    -- 计算当前年度的周平均新增数
    SELECT 
        sub_year,
        AVG(policies) AS average
    FROM (
        SELECT 
            YEAR(issuedate) AS sub_year,
            COUNT(policynumber) AS policies
        FROM 
            insure
        WHERE
            YEAR(issuedate) = (SELECT MAX(YEAR(issuedate)) FROM insure) 
            AND policyname LIKE '%contents%'
        GROUP BY YEAR(issuedate), DATEPART(WEEK, DATEADD(DAY, 7-(DATEPART(WEEKDAY, issuedate)), issuedate))
    ) sub
    GROUP BY sub_year
),
full_year_weeks AS (
    -- 获取全年所有周，去重
    SELECT DISTINCT
        DATEPART(WEEK, DATEADD(DAY, 7-(DATEPART(WEEKDAY, cal.date)), cal.date)) AS weekofyear,
        cal.year
    FROM
        LOOKUP_CALENDAR cal
    WHERE
        cal.year = (SELECT MAX(YEAR(issuedate)) FROM insure)
),
final_base AS (
    -- 关联数据，获取每个周的实际累计值，同时计算最后一个有效累计值和间隔周数
    SELECT 
        f.weekofyear,
        f.year,
        w.average,
        y.Running_Count,
        -- 年度最后一个有效累计值
        MAX(y.Running_Count) OVER (PARTITION BY f.year) AS last_valid_running_count,
        -- 当前周与最后一个有效周的间隔数
        f.weekofyear - MAX(CASE WHEN y.Running_Count IS NOT NULL THEN f.weekofyear END) OVER (PARTITION BY f.year) AS weeks_since_last_valid
    FROM 
        full_year_weeks f
    LEFT JOIN yearly_data y ON f.weekofyear = y.weekofyear AND f.year = y.year
    LEFT JOIN weekly_avg w ON f.year = w.sub_year
)
-- 生成最终结果：实际值或预测累加值
SELECT 
    year,
    weekofyear,
    CASE 
        WHEN Running_Count IS NOT NULL THEN Running_Count 
        ELSE last_valid_running_count + (average * weeks_since_last_valid)
    END AS final_running_count
FROM 
    final_base
ORDER BY 
    year, weekofyear;

修正说明 #

1. 使用CTE拆分逻辑，代码结构更清晰易维护；
2. 新增last_valid_running_count获取年度最后一个有效累计值；
3. 新增weeks_since_last_valid计算当前周与最后有效周的间隔数；
4. 对无有效Running_Count的周，用最后有效累计值 + 周均值 × 间隔周数生成符合规则的预测值；
5. 彻底修正原代码中Test列的重复计数问题，确保仅在缺失数据的周生成预测值。

内容的提问来源于stack exchange，提问作者A.Steer