基于SQL Server计算指定保单过去三年车辆总数的SQL查询求助

示例数据表 #

需求说明 #

传入参数 policy_number=1234、policy_eff_dt=2022-10-12、lob_cd=AUTO 时，需筛选出**过去三年（2019、2020、2021年）**的记录，将对应 Number_of_vehicles 求和（6+3+2=11），命名为 PREV3_TOTAL_NBR_OF_VEHICLES。

核心过滤逻辑需满足：

in_policy_number = policy_number 
and policy_eff_dt < in_policy_eff_dt  -- 排除当前年份的记录，精准匹配过去三年
and in_lob_cd = lob_cd

现有查询的问题 #

初始查询问题 #

初始查询存在硬编码年份、范围不准确的问题：

1. 固定写死年份值 '2022'，无法根据传入的 policy_eff_dt 参数动态计算时间范围
2. 条件包含了2022年的记录，不符合“过去三年”的需求

select 
   policy_num, lob_cd, sum(number_of_vehicles) 
from table1 
where policy_num='S 2455350'  
    and lob_cd = 'AU'  
    and year(policy_eff_dt) >= '2022'-3  
    and year(policy_eff_dt) <= '2022' 
group by policy_num , lob_cd

新编写的SQL问题 #

新查询存在语法错误（如 curr. A.policy_eff_dt 格式错误、子查询结构不完整），且通过CASE逐个判断年份差的方式冗余复杂，逻辑效率低。

select A.policy_num, A.lob_cd, A.policy_eff_dt,sum(NUM1+NUM2+NUM3) as PREV3_NUMBER_OF_VEHICLES from (select  policy_num, lob_cd,curr. A.policy_eff_dt,  case when year (curr.policy_eff_dt) - year (prev.policy_eff_dt) =1 then prev.number_of_vehicles else 0 end) as NUM1, case when year (curr.policy_eff_dt) - year (prev.policy_eff_dt) =2 then prev.number_of_vehicles else 0 end as NUM2, case when year(curr.policy_eff_dt) - year (prev.policy_eff_dt) =3 then prev.number_of_vehicles else 0 end as NUM3 from table1 curr 
     left join table1 prev  on curr.policy_num=prev.policy_num  and curr.lob_cd = prev.lob_cd where curr.policy_num='S 2455350'  
    and curr.lob_cd = 'AU'  ) A
group by  A.policy_num, A.lob_cd, A.policy_eff_dt

正确的SQL实现 #

方式一：直接过滤（推荐） #

利用传入参数动态计算时间范围，避免硬编码，精准筛选目标数据：

-- 定义传入参数
DECLARE @in_policy_number VARCHAR(50) = '1234'
DECLARE @in_policy_eff_dt DATE = '2022-10-12'
DECLARE @in_lob_cd VARCHAR(10) = 'AUTO'

SELECT 
    @in_policy_number AS policy_number,
    @in_lob_cd AS lob_cd,
    SUM(Number_of_vehicles) AS PREV3_TOTAL_NBR_OF_VEHICLES
FROM table1
WHERE 
    policy_number = @in_policy_number
    AND lob_cd = @in_lob_cd
    -- 筛选早于传入日期，且年份在传入日期前三年范围内的记录
    AND policy_eff_dt < @in_policy_eff_dt
    AND YEAR(policy_eff_dt) >= YEAR(@in_policy_eff_dt) - 3

方式二：子查询方式 #

如果需要通过子查询单独提取过去三年的行数据，可采用以下写法：

-- 定义传入参数
DECLARE @in_policy_number VARCHAR(50) = '1234'
DECLARE @in_policy_eff_dt DATE = '2022-10-12'
DECLARE @in_lob_cd VARCHAR(10) = 'AUTO'

SELECT 
    policy_number,
    lob_cd,
    SUM(Number_of_vehicles) AS PREV3_TOTAL_NBR_OF_VEHICLES
FROM (
    -- 子查询筛选过去三年的记录
    SELECT *
    FROM table1
    WHERE 
        policy_number = @in_policy_number
        AND lob_cd = @in_lob_cd
        AND policy_eff_dt < @in_policy_eff_dt
        AND YEAR(policy_eff_dt) >= YEAR(@in_policy_eff_dt) - 3
) AS prev_three_years
GROUP BY policy_number, lob_cd

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