SQL Server中如何将t1表end_date列值批量代入查询计算平均费率

我来帮你搞定这个需求——你需要把t1表中每个机构对应的end_date作为时间参数，分别计算该时间范围内（end_date往前推6个月到end_date）的平均费率对吧？这里有两种简洁高效的实现方式，适配不同的场景：

方法1：通过JOIN关联t1表实现批量计算 #

这种方法最直接，把t1表作为参数源，和你的table_a、rates表关联，一次性完成所有end_date的计算：

WITH averages AS (
    SELECT 
        t1.agency AS t1_agency, -- 明确标记来自t1的机构
        t1.end_date,
        a.id AS agency,
        c.rate,
        AVG(c.rate) OVER (PARTITION BY t1.agency, t1.end_date) AS avg_cost -- 按t1的机构+时间范围分组计算平均
    FROM t1
    JOIN table_a AS a ON t1.agency = a.id -- 关联机构表
    JOIN rates c 
        ON a.rate_id = c.id 
        AND c.end_date = t1.end_date -- 使用t1的end_date作为参数
        AND c.start_date = DATEADD(month, -6, t1.end_date) -- 自动计算起始日期
)
SELECT DISTINCT 
    t1_agency,
    end_date,
    avg_cost
FROM averages
ORDER BY t1_agency, end_date;

关键调整说明： #

• 把t1表加入查询，作为所有时间参数的来源，每个t1的行都会触发一次对应时间范围的计算
• 窗口函数的PARTITION BY改为t1.agency, t1.end_date，确保每个机构+时间范围的平均费率是独立计算的
• 保留end_date在结果中，方便你区分不同时间范围的计算结果

方法2：使用CROSS APPLY（适合复杂子查询场景） #

如果你的后续逻辑需要更灵活的子查询处理，CROSS APPLY是更好的选择，它可以针对t1的每一行执行一次子查询：

SELECT 
    t1.agency,
    t1.end_date,
    sub.avg_cost
FROM t1
CROSS APPLY (
    SELECT AVG(c.rate) AS avg_cost
    FROM table_a AS a
    JOIN rates c 
        ON a.rate_id = c.id 
        AND c.end_date = t1.end_date
        AND c.start_date = DATEADD(month, -6, t1.end_date)
    WHERE a.id = t1.agency
) AS sub
ORDER BY t1.agency, t1.end_date;

优势： #

• 子查询逻辑独立，更容易维护和修改
• 避免了CTE中可能出现的重复数据问题，直接返回每个机构+时间范围的平均结果

注意事项： #

• 检查t1表中的笔误：2017-12-01l应该是2017-12-01，避免日期格式错误
• 如果rates表中存在同一机构同一时间范围的多条费率记录，两种方法都会正确计算平均值
• 如果需要排除某些无匹配数据的机构+时间组合，可以把CROSS APPLY改为OUTER APPLY，保留这些行的avg_cost为NULL

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