如何批量查找并补全快照表中缺失的月度记录？

我有一张存储大量Activation_ID的快照表，规则是：当记录根据Valid_Until字段过期后，对应月份的快照会消失；订阅重新激活后，快照会恢复。比如示例中2022-06-01的快照缺失，需要手动补全为指定格式的记录。但现有SQL代码只能处理指定月份（通过dateadd('month', 0, date_trunc('month', current_date))过滤），无法高效遍历所有快照批量处理，求优化方法。

输入表示例 #

期望输出表示例 #

现有代码（仅处理指定月份） #

with last_snapshot as (
    select
        ls.*
    from {{ ref('BRDG_FLEXERA_ANALYSIS_MONTHLY_BASE') }} ls
    --Applies the filter to get only the current month to load
    where ls.SNAPSHOT_DATE = dateadd('month', 0, date_trunc('month', current_date))
), 
--Joins and adds in the previous periods columns which are needed for PIT compare
current_snapshot as (
    select 
        --Columns to be added from the previous snapshot
        ps.FLEXERA_ACTIVATION_ID "PREVIOUS_FLEXERA_ACTIVATION_ID_CTE",
        ps.SNAPSHOT_DATE "PREVIOUS_SNAPSHOT_DATE_CTE",
        ps.CURRENT_LICENCE_STATUS "PREVIOUS_LICENCE_STATUS_CTE",
        ps.REPORTING_STATUS "PREVIOUS_REPORTING_STATUS_CTE",
        ps.COUNT "COUNT_CTE",
        ls.*
    from last_snapshot ls

    left join {{ ref('BRDG_FLEXERA_ANALYSIS_MONTHLY_BASE') }} ps
        on ps.FLEXERA_ACTIVATION_ID = ls.FLEXERA_ACTIVATION_ID
        and ps.SNAPSHOT_DATE = ls.PREVIOUS_SNAPSHOT_DATE
),
--This helps us to determine if a record dropped off from the last snapshot to the latest one.
--For ones missing we create a new Churn record
previous_snapshots as (
     select
        --Builds the columns to identify the previous snapshot
        dateadd('month', 0, date_trunc('month', current_date)) "CURRENT_MONTH_DATE_NEW",
        add_months(CURRENT_MONTH_DATE_NEW, -1) "PREVIOUS_MONTH_DATE",
        ps.*
    from {{ ref('BRDG_FLEXERA_ANALYSIS_MONTHLY_BASE') }} ps
    
    --Joins the current data to the previous snapshot to flag what exists in the current which was in the previous
    left join current_snapshot cs
        on ps.FLEXERA_ACTIVATION_ID = cs.FLEXERA_ACTIVATION_ID

    where ps.SNAPSHOT_DATE = PREVIOUS_MONTH_DATE     --Filters to get the previous snapshot
        and ps.CURRENT_LICENCE_STATUS != 'Expired'   --Filters to get the previous snapshot
        and cs.FLEXERA_ACTIVATION_ID is NULL         --Filters to get only those rows which were in the last snapshot but not the current
)

优化方案：批量补全所有缺失月份 #

核心思路是：为每个Activation_ID生成其生命周期内的完整月份序列，再与原表关联找出缺失的月份，最后生成补全记录并合并原数据。

完整优化SQL代码 #

with activation_date_ranges as (
    -- 找出每个Activation_ID的最早和最晚快照日期，确定需要覆盖的月份范围
    select
        Activation_ID,
        date_trunc('month', min(Snapshot_Date)) as earliest_snapshot_month,
        date_trunc('month', max(Snapshot_Date)) as latest_snapshot_month
    from {{ ref('BRDG_FLEXERA_ANALYSIS_MONTHLY_BASE') }}
    group by Activation_ID
),
monthly_series as (
    -- 为每个Activation_ID生成完整的月份序列
    select
        adr.Activation_ID,
        dateadd('month', n, adr.earliest_snapshot_month) as Snapshot_Date
    from activation_date_ranges adr
    cross join (
        -- 生成足够多的月份偏移量，覆盖最大可能的时间范围（这里假设最多100个月，可按需调整）
        select row_number() over(order by 1) - 1 as n
        from table(generator(rowcount => 100))
    ) months
    where dateadd('month', n, adr.earliest_snapshot_month) <= adr.latest_snapshot_month
),
existing_snapshots as (
    -- 原表数据，保留所有字段
    select *
    from {{ ref('BRDG_FLEXERA_ANALYSIS_MONTHLY_BASE') }}
),
missing_snapshots as (
    -- 找出缺失的月份快照，并获取该Activation_ID过期前的Valid_Until
    select
        ms.Activation_ID,
        ms.Snapshot_Date,
        'Manual generation' as Row_Generation,
        'Expired' as Status,
        -- 获取该Activation_ID在缺失月份之前最近的Valid_Until
        last_value(es.Valid_Until) over(
            partition by ms.Activation_ID 
            order by ms.Snapshot_Date 
            rows between unbounded preceding and current row
        ) as Valid_Until
    from monthly_series ms
    left join existing_snapshots es
        on ms.Activation_ID = es.Activation_ID
        and ms.Snapshot_Date = es.Snapshot_Date
    where es.Activation_ID is null
),
final_snapshots as (
    -- 合并原表数据和补全的缺失数据
    select * from existing_snapshots
    union all
    select * from missing_snapshots
)
select * from final_snapshots
order by Activation_ID, Snapshot_Date desc;

代码说明 #

1. activation_date_ranges：统计每个Activation_ID的快照时间范围，确定需要生成的月份区间。
2. monthly_series：通过交叉连接生成每个Activation_ID在时间范围内的所有月份快照日期。
3. existing_snapshots：直接引用原表数据。
4. missing_snapshots：关联完整月份序列和原表，找出缺失的记录，并继承该Activation_ID最近一次的Valid_Until值，同时设置补全记录的固定字段值。
5. final_snapshots：合并原数据和补全数据，得到完整的快照表。

注意事项 #

• 生成月份偏移量的generator(rowcount => 100)可根据实际数据的时间跨度调整数值，确保覆盖所有需要的月份。
• 若使用的SQL方言不支持generator，可替换为其他生成连续数字的方式（比如递归CTE）。
• 确保Activation_ID和Snapshot_Date的索引合理，提升关联查询效率。

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