编写SQL代码实现同星期几近6周数据求和及数值预测

需要编写SQL代码，实现对相同星期几的最近6周数据的num字段求和，并生成下一个同星期几的预测数值。

测试数据创建语句 #

create table #t (dy varchar(10), dt datetime, code varchar(4), wd varchar(10), num int)
insert into #t values
('星期三', '2023-03-01','c','101A',5),
('星期三', '2023-03-08','c','101A',3),
('星期三', '2023-03-15','c','101A',1),
('星期三', '2023-03-22','c','101A',1),
('星期三', '2023-03-29','c','101A',3),
('星期三', '2023-04-05','c','101A',2),
('星期三', '2023-04-12','c','101A',5),
('星期三', '2023-04-19','c','101A',2),
('星期三', '2023-04-26','c','101A',0),
('星期三', '2023-05-03','c','101A',6),
('星期三', '2023-05-10','c','101A',4),
('星期三', '2023-05-17','c','101A',3),
('星期三', '2023-05-24','c','101A',5),
('星期三', '2023-05-31','c','101A',1),
('星期三', '2023-05-07','c','101A',0),

('星期三', '2023-03-01','c','102A',4),
('星期三', '2023-03-08','c','102A',6),
('星期三', '2023-03-15','c','102A',4),
('星期三', '2023-03-22','c','102A',3),
('星期三', '2023-03-29','c','102A',6),
('星期三', '2023-04-05','c','102A',2),
('星期三', '2023-04-12','c','102A',1),
('星期三', '2023-04-19','c','102A',2),
('星期三', '2023-04-26','c','102A',3),
('星期三', '2023-05-03','c','102A',2),
('星期三', '2023-05-10','c','102A',5),
('星期三', '2023-05-17','c','102A',0),
('星期三', '2023-05-24','c','102A',1),
('星期三', '2023-05-31','c','102A',1),
('星期三', '2023-06-07','c','102A',0),

('星期二', '2023-02-28','d','101A',2),
('星期二', '2023-03-07','d','101A',3),
('星期二', '2023-03-14','d','101A',8),
('星期二', '2023-03-21','d','101A',10),
('星期二', '2023-03-28','d','101A',3),
('星期二', '2023-04-04','d','101A',2),
('星期二', '2023-04-11','d','101A',5),
('星期二', '2023-04-18','d','101A',2),
('星期二', '2023-04-25','d','101A',4),
('星期二', '2023-05-02','d','101A',6),
('星期二', '2023-05-09','d','101A',3),
('星期二', '2023-05-16','d','101A',4),
('星期二', '2023-05-23','d','101A',5),
('星期二', '2023-05-30','d','101A',1),
('星期二', '2023-06-06','d','101A',6),

('星期二', '2023-02-28','cd','103',5),
('星期二', '2023-03-07','cd','103',5),
('星期二', '2023-03-14','cd','103',4),
('星期二', '2023-03-21','cd','103',1),
('星期二', '2023-03-28','cd','103',3),
('星期二', '2023-04-04','cd','103',2),
('星期二', '2023-04-11','cd','103',4),
('星期二', '2023-04-18','cd','103',2),
('星期二', '2023-04-25','cd','103',0),
('星期二', '2023-05-02','cd','103',3),
('星期二', '2023-05-09','cd','103',2),
('星期二', '2023-05-16','cd','103',1),
('星期二', '2023-05-23','cd','103',5),
('星期二', '2023-05-30','cd','103',1),
('星期二', '2023-06-06','cd','103',0)

例如：针对2023-06-13，需要求和最近6条同星期几的数据（2023-06-06、2023-05-30、2023-05-23、2023-05-16、2023-05-09、2023-05-02）的num值

预期结果 #

create table #t_result (dy varchar(10), dt datetime, code varchar(4), wd varchar(10), num int)

insert into #t_result values
('星期二', '2023-06-13','cd','103',12),
('星期二', '2023-06-06','cd','103',12),
('星期二', '2023-05-30','cd','103',13),
('星期二', '2023-05-23','cd','103',12),
('星期二', '2023-05-16','cd','103',13),
('星期二', '2023-05-09','cd','103',14),
('星期二', '2023-05-02','cd','103',12),

('星期二', '2023-06-13','d','101A',25),
('星期二', '2023-06-06','d','101A',23),
('星期二', '2023-05-30','d','101A',24),
('星期二', '2023-05-23','d','101A',24),
('星期二', '2023-05-16','d','101A',22),
('星期二', '2023-05-09','d','101A',22),
('星期二', '2023-05-02','d','101A',26)

解决方案SQL代码 #

WITH ranked_data AS (
    SELECT 
        dy,
        dt,
        code,
        wd,
        num,
        -- 按分组内日期倒序排名
        ROW_NUMBER() OVER (PARTITION BY dy, code, wd ORDER BY dt DESC) AS rn,
        -- 获取分组内最新日期，用于生成下一个同星期几的日期
        MAX(dt) OVER (PARTITION BY dy, code, wd) AS max_dt
    FROM #t
),
summed_data AS (
    SELECT 
        dy,
        dt,
        code,
        wd,
        -- 对当前及后续5条（共6条）数据的num求和
        SUM(num) OVER (PARTITION BY dy, code, wd ORDER BY dt DESC ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING) AS sum_num
    FROM ranked_data
    WHERE rn <= 7 -- 保留足够数据以生成有效求和结果
)
-- 合并原有日期的求和结果与预测记录
SELECT 
    dy,
    dt,
    code,
    wd,
    sum_num AS num
FROM summed_data
WHERE sum_num IS NOT NULL -- 过滤数据不足6条的无效记录
UNION ALL
-- 生成下一个同星期几的预测记录
SELECT 
    dy,
    DATEADD(week, 1, max_dt) AS dt,
    code,
    wd,
    SUM(num) AS num
FROM ranked_data
WHERE rn <= 6 -- 取最近6条数据求和
GROUP BY dy, code, wd, max_dt
ORDER BY dy, code, wd, dt DESC;

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