如何基于另一列最大参考号，用CASE语句统计保单金额及数量？

问题背景 #

需要统计代理的两类数据：Submissions（提交件）和Policies（保单），仅对Policies统计GWP金额。现有SQL可运行，但因alpups表中单个保单存在多条记录，导致Policies数量和GWP金额统计了冗余数据，需仅保留每个保单最大REFERNCE#对应的记录来统计。

现有SQL及问题 #

现有SQL：

select a.agsub# as Code, 
sum(case when a.pmstat in ('R', 'Q') then 1 else 0 end) as Submissions,
sum(case when a.pmstat not in ('R', 'Q') then 1 else 0 end) as Policies,
sum(case when a.pmstat not in ('R', 'Q') then (b.pstmap) else 0 end) as GWP 
from cipomf a
inner join alpups b 
on comp# = psconr
and pmprfx = psprfx
and pmplnr = psplnr
inner join ciprar c
on pmprar = paprar
inner join agagtn d
on a.agsub# = d.agsub#
where (PMPRAR = '057' and a.agsub# = '170129')
and (a.pmstat in ('R', 'Q') or a.pmstat != 'Q')
group by a.agsub#, pmprar
order by pmprar

当前结果：

CODE     SUBMISSIONS    POLICIES    GWP
170129          7           3       43746.00

期望结果：Submissions计数正确（7），Policies应为2，GWP应为24074.00。问题源于alpups表的重复保单记录：

AGSUB#  AMOUNT          REFERNCE#
170129  4232.00     7372236   --保单1的唯一记录
170129  19672.00    7372239   --保单2的初始记录
170129  19842.00    7473004   --保单2的最新记录

需要仅选取保单2的最大REFERNCE#（7473004）对应的记录，忽略较低的7372239。

解决方案 #

方案1：使用CTE+窗口函数筛选最新记录

通过窗口函数给每个保单的记录按REFERNCE#降序编号，只保留编号为1的最新记录：

WITH latest_alpups AS (
    SELECT 
        psconr, psprfx, psplnr, pstmap,
        ROW_NUMBER() OVER (PARTITION BY psconr, psprfx, psplnr ORDER BY REFERNCE# DESC) AS rn
    FROM alpups
)
select a.agsub# as Code, 
sum(case when a.pmstat in ('R', 'Q') then 1 else 0 end) as Submissions,
sum(case when a.pmstat not in ('R', 'Q') then 1 else 0 end) as Policies,
sum(case when a.pmstat not in ('R', 'Q') then (b.pstmap) else 0 end) as GWP 
from cipomf a
inner join latest_alpups b 
on a.comp# = b.psconr
and a.pmprfx = b.psprfx
and a.pmplnr = b.psplnr
and b.rn = 1  -- 仅保留每个保单的最新记录
inner join ciprar c
on a.pmprar = c.paprar
inner join agagtn d
on a.agsub# = d.agsub#
where (a.PMPRAR = '057' and a.agsub# = '170129')
and (a.pmstat in ('R', 'Q') or a.pmstat != 'Q')
group by a.agsub#, a.pmprar
order by a.pmprar

方案2：子查询筛选最大REFERNCE#记录

如果数据库不支持窗口函数，可通过子查询直接匹配每个保单的最大REFERNCE#：

select a.agsub# as Code, 
sum(case when a.pmstat in ('R', 'Q') then 1 else 0 end) as Submissions,
sum(case when a.pmstat not in ('R', 'Q') then 1 else 0 end) as Policies,
sum(case when a.pmstat not in ('R', 'Q') then (b.pstmap) else 0 end) as GWP 
from cipomf a
inner join alpups b 
on a.comp# = b.psconr
and a.pmprfx = b.psprfx
and a.pmplnr = b.psplnr
-- 确保当前记录是该保单的最大REFERNCE#
and b.REFERNCE# = (
    SELECT MAX(REFERNCE#) 
    FROM alpups 
    WHERE psconr = a.comp# 
      AND psprfx = a.pmprfx 
      AND psplnr = a.pmplnr
)
inner join ciprar c
on a.pmprar = c.paprar
inner join agagtn d
on a.agsub# = d.agsub#
where (a.PMPRAR = '057' and a.agsub# = '170129')
and (a.pmstat in ('R', 'Q') or a.pmstat != 'Q')
group by a.agsub#, a.pmprar
order by a.pmprar

说明 #

两种方案均通过提前筛选alpups表中每个保单的最新记录，避免了重复统计。Submissions的统计逻辑保持不变，因为它不受alpups表的重复记录影响。

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