如何优化保险保单月度数据生成中的日期范围Join查询？

背景与需求 #

当前项目需生成活跃保险保单的月度汇总信息用于Tableau可视化，核心需求包括：

• 处理保单表中重复的Policy/Policy Sequence #组合，保留每组最新插入的记录
• 将保单的生效-过期日期范围拆分为对应月度行，汇总金额得到月度维度的统计数据

原始保单数据样例 #

Policy  Policy Sequence #    Effective Date    Expiration Date    $ amount     Record Insert Date
 a       0                    Jan-20            May-20             $1,000.00    1/1/2020
 a       0                    Jan-20            May-20             $1,500.00    1/1/2020
 a       1                    Jun-20            Dec-20             $2,000.00    6/1/2020
 a       2                    Jan-21            Feb-21             $2,500.00    1/1/2021

目标输出数据集 #

Month   $ amount
Jan-20  $1,500.00
Feb-20  $1,500.00
Mar-20  $1,500.00
Apr-20  $1,500.00
May-20  $1,500.00
Jun-20  $2,000.00  <- Policy change here
Jul-20  $2,000.00
Aug-20  $2,000.00
Sep-20  $2,000.00
Oct-20  $2,000.00
Nov-20  $2,000.00
Dec-20  $2,000.00
Jan-21  $2,500.00  <- Here
Feb-21  $2,500.00
Mar-21  $2,500.00

当前实现方案 #

通过基础日期表（存储月度起始日期）与处理后的保单表做范围Join来拆分日期，基础日期表样例：

Month
1/1/2020
2/1/2020
3/1/2020
...

完整SQL代码：

select
B.Date1, 
ContractingFirm,
StateCode,
sum(PolicyCount) as PolicyCount, 
sum(DollarAmount) as DollarAmount

from BaseTable B
    join
    (
    select 
    PolicyNumber,
    PolicySequence,
    InsertDate,
    date_trunc(month, EffectiveDate) as EffectiveDate, 
    date_trunc(month, ExpirationDate) as ExpirationDate, 
    sum(1) as PolicyCount, 
    sum(DollarAmount) as DollarAmount, 
   
    from Policy_Table
    group by 
    PolicyNumber, 
    PolicySequence,
    InsertDate,
    EffectiveDate,
    ExpirationDate

    qualify row_number() over (partition by PolicyNumber, PolicySequence order by InsertDate desc) = 1

    ) PolicyTable on Date1 between PolicyTable.EffectiveDate and PolicyTable.ExpirationDate

where B.Date1 between '2020-01-01' and '2021-03-01'

group by 
B.Date1, 
ContractingFirm,
StateCode

性能问题 #

查询运行极慢，单月处理需5-10分钟，推测范围Join操作是核心性能瓶颈，需优化逻辑或更换拆分方法。

优化建议 #

1. 调整子查询逻辑，先过滤再聚合 #

原逻辑先对PolicyNumber, PolicySequence, InsertDate分组聚合，再筛选最新记录，会处理大量冗余数据。调整为先过滤重复记录，再聚合：

select 
    PolicyNumber,
    PolicySequence,
    date_trunc(month, EffectiveDate) as EffectiveDate, 
    date_trunc(month, ExpirationDate) as ExpirationDate, 
    sum(1) as PolicyCount, 
    sum(DollarAmount) as DollarAmount,
    ContractingFirm,
    StateCode
from (
    select 
        *,
        row_number() over (partition by PolicyNumber, PolicySequence order by InsertDate desc) as rn
    from Policy_Table
) t
where rn = 1
group by 
    PolicyNumber, 
    PolicySequence,
    EffectiveDate,
    ExpirationDate,
    ContractingFirm,
    StateCode

此方式大幅减少子查询返回的数据集大小，降低Join阶段的计算量。

2. 替换范围Join，直接生成保单覆盖的月度序列 #

利用数据库内置的日期生成函数（如Snowflake的generator、BigQuery的generate_date_array），为每条保单直接生成其覆盖的月度行，无需依赖基础日期表：
以Snowflake为例：

with filtered_policies as (
    select 
        PolicyNumber,
        PolicySequence,
        date_trunc(month, EffectiveDate) as EffectiveDate, 
        date_trunc(month, ExpirationDate) as ExpirationDate, 
        sum(1) as PolicyCount, 
        sum(DollarAmount) as DollarAmount,
        ContractingFirm,
        StateCode
    from (
        select 
            *,
            row_number() over (partition by PolicyNumber, PolicySequence order by InsertDate desc) as rn
        from Policy_Table
    ) t
    where rn = 1
    group by 
        PolicyNumber, 
        PolicySequence,
        EffectiveDate,
        ExpirationDate,
        ContractingFirm,
        StateCode
),
policy_months as (
    select 
        dateadd(month, seq4(), EffectiveDate) as Date1,
        PolicyCount,
        DollarAmount,
        ContractingFirm,
        StateCode
    from filtered_policies,
    table(generator(rowcount => 120)) -- 生成足够覆盖业务周期的月份数（如10年）
    where dateadd(month, seq4(), EffectiveDate) <= ExpirationDate
)
select 
    Date1,
    ContractingFirm,
    StateCode,
    sum(PolicyCount) as PolicyCount,
    sum(DollarAmount) as DollarAmount
from policy_months
where Date1 between '2020-01-01' and '2021-03-01'
group by Date1, ContractingFirm, StateCode
order by Date1

此方式避免了大表间的范围Join，性能提升显著。

3. 索引与分区优化 #

• 为Policy_Table的PolicyNumber, PolicySequence, InsertDate创建联合索引，加速窗口函数的分区排序计算
• 若数据库支持分区，将Policy_Table按EffectiveDate或InsertDate分区，查询时仅扫描目标时间范围内的分区，减少数据扫描量

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