Oracle转SQL Server：窗口函数日期范围累计金额实现求助

原Oracle实现代码 #

Oracle通过窗口函数的RANGE BETWEEN INTERVAL语法，直接计算分组内过去6个月和1年的累计金额：

select
     a,b,c,d,e,trunc(record_date, 'MM'),
        sum(sum(amount)) over(
            partition by a, b, c, d 
            order by trunc(record_date, 'MM')
            range between interval 5 month preceding and current row) semester_amount,
        sum(sum(amount)) over(
            partition by a, b, c, d
            order by trunc(record_date, 'MM')
            range between interval '11' month preceding and current row) year_amount
from table
group by
      a,b,c,d,e,trunc(record_date, 'MM')

测试表结构及数据 #

用于验证的SQL Server测试表：

drop table if exists dbo.test_window_frames;
create table dbo.test_window_frames (
    fielda char(1),
    fieldb char(1),
    fieldc char(2),
    fieldd char(2),
    fielde char(2),
    amount int,
    record_date date);
insert into dbo.test_window_frames (fielda, fieldb, fieldc, fieldd, fielde, amount, record_date)
values
    ('A', 'X', 'C1', 'D2', 'E2', 74, '2024-03-10'),
    ('A', 'X', 'C1', 'D2', 'E2', 34, '2024-03-12'),
    ('A', 'X', 'C1', 'D2', 'E2', 24, '2024-03-12'),
    ('A', 'X', 'C1', 'D2', 'E1', 24, '2024-03-25'),
    ('A', 'X', 'C1', 'D2', 'E1', 95, '2024-06-20'),
    ('A', 'X', 'C1', 'D2', 'E1', 81, '2024-06-27'),
    ('A', 'X', 'C1', 'D2', 'E1', 83, '2024-07-10'),
    ('A', 'X', 'C1', 'D2', 'E2', 73, '2024-07-27'),
    ('A', 'X', 'C1', 'D2', 'E2', 60, '2024-10-07'),
    ('A', 'X', 'C1', 'D2', 'E2', 50, '2024-10-07'),
    ('A', 'X', 'C2', 'D1', 'E1', 67, '2024-04-02'),
    ('A', 'X', 'C2', 'D1', 'E1', 69, '2024-12-04'),
    ('B', 'X', 'C1', 'D1', 'E1', 50, '2024-01-15'),
    ('B', 'X', 'C1', 'D1', 'E2', 30, '2024-02-20'),
    ('B', 'X', 'C2', 'D2', 'E1', 80, '2024-03-22'),
    ('B', 'X', 'C2', 'D2', 'E2', 90, '2024-05-10'),
    ('B', 'X', 'C1', 'D1', 'E1', 55, '2024-06-01'),
    ('B', 'X', 'C2', 'D2', 'E2', 20, '2024-07-19'),
    ('B', 'X', 'C1', 'D1', 'E2', 40, '2024-08-14'),
    ('B', 'X', 'C1', 'D1', 'E2', 5, '2024-08-14'),
    ('B', 'X', 'C1', 'D2', 'E1', 65, '2024-09-25'),
    ('B', 'X', 'C2', 'D1', 'E2', 35, '2024-10-12'),
    ('B', 'X', 'C2', 'D2', 'E1', 75, '2024-11-05');

遇到的问题及错误尝试 #

SQL Server不支持窗口函数中基于日期的RANGE BETWEEN INTERVAL语法，尝试用子查询实现时出现以下错误：

Column 'dbo.test_window_frames.record_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

错误的尝试代码：

select 
    t1.fielda,
    t1.fieldb,
    t1.fieldc,
    t1.fieldd,
    t1.fielde,
    dateadd(month, datediff(month, 0, t1.record_date), 0) as reference_date,
    (
        select sum(t2.amount)
        from dbo.test_window_frames t2
        where t2.fielda = t1.fielda
          and t2.fieldb = t1.fieldb
          and t2.fieldc = t1.fieldc
          and t2.fieldd = t1.fieldd
          and t2.record_date between dateadd(month, -5, t1.record_date) and t1.record_date
    ) as semester_amount,
    (
        select sum(t2.amount)
        from dbo.test_window_frames t2
        where t2.fielda = t1.fielda
          and t2.fieldb = t1.fieldb
          and t2.fieldc = t1.fieldc
          and t2.fieldd = t1.fieldd
          and t2.record_date between dateadd(month, -11, t1.record_date) and t1.record_date
    ) as year_amount
from 
    dbo.test_window_frames t1
group by 
    t1.fielda, t1.fieldb, t1.fieldc, t1.fieldd, t1.fielde, dateadd(month, datediff(month, 0, t1.record_date), 0);

错误原因：外层GROUP BY仅包含截断后的日期，但子查询中引用了原始的t1.record_date，该字段未在GROUP BY或聚合函数中，SQL Server无法确定取哪一行的日期值。

预期结果（FieldA='A'时） #

正确的SQL Server实现方案 #

先预聚合到每个月的分组金额，再基于聚合结果计算累计，避免日期字段的歧义：

WITH monthly_agg AS (
    -- 第一步：按分组和月份聚合，得到每月的总金额
    SELECT
        fielda,
        fieldb,
        fieldc,
        fieldd,
        fielde,
        DATEADD(month, DATEDIFF(month, 0, record_date), 0) AS reference_date,
        SUM(amount) AS monthly_amount
    FROM dbo.test_window_frames
    GROUP BY fielda, fieldb, fieldc, fieldd, fielde, DATEADD(month, DATEDIFF(month, 0, record_date), 0)
)
SELECT
    ma.fielda,
    ma.fieldb,
    ma.fieldc,
    ma.fieldd,
    ma.fielde,
    ma.reference_date,
    -- 计算过去6个月（当前月+前5个月）的累计金额
    (SELECT SUM(ma2.monthly_amount)
     FROM monthly_agg ma2
     WHERE ma2.fielda = ma.fielda
       AND ma2.fieldb = ma.fieldb
       AND ma2.fieldc = ma.fieldc
       AND ma2.fieldd = ma.fieldd
       AND ma2.reference_date BETWEEN DATEADD(month, -5, ma.reference_date) AND ma.reference_date) AS semester_amount,
    -- 计算过去1年（当前月+前11个月）的累计金额
    (SELECT SUM(ma2.monthly_amount)
     FROM monthly_agg ma2
     WHERE ma2.fielda = ma.fielda
       AND ma2.fieldb = ma.fieldb
       AND ma2.fieldc = ma.fieldc
       AND ma2.fieldd = ma.fieldd
       AND ma2.reference_date BETWEEN DATEADD(month, -11, ma.reference_date) AND ma.reference_date) AS year_amount
FROM monthly_agg ma
ORDER BY ma.fielda, ma.fieldb, ma.fieldc, ma.fieldd, ma.fielde, ma.reference_date;

方案说明 #

1. 预聚合阶段：先将原始数据按fielda, fieldb, fieldc, fieldd, fielde和截断后的月份分组，计算每个月的总金额，确保每个行的日期是唯一的月份起始日。
2. 累计计算阶段：基于预聚合后的结果，通过关联子查询筛选同分组下日期在目标范围内的所有月份金额，求和得到累计值，完全匹配原Oracle代码的逻辑。

内容的提问来源于stack exchange，提问作者E. Gptreas