如何使用HiveQL合并两个SCD-2表的区间并生成符合要求的结果表？

看起来你需要合并两个SCD-2类型的维度表，核心是把两个表的时间区间做精细化拆分，让每个小时间段内同时保留两个表的状态值，包括其中一个表状态结束后的空白期。我之前处理过类似的需求，给你分享一个可行的思路和HiveQL代码：

核心思路 #

SCD-2表的合并关键在于提取所有时间节点，生成最小的连续时间段，再分别匹配两个表的状态。具体来说：

• 收集两个表中所有的start_dt和end_dt，按PK分组去重排序，得到所有需要拆分的时间点；
• 把这些时间点两两配对，生成一系列无重叠、无间隙的小时间段；
• 每个小时间段分别关联两个表，获取对应的var1和var2状态（如果时间段落在某个表的区间内，就取对应值，否则为NULL）；
• 最后合并结果，得到符合要求的输出。

完整HiveQL代码 #

with 
-- 第一步：收集两个表的所有时间节点（start_dt和end_dt）
all_time_nodes as (
    select pk, start_dt as dt from table1
    union all
    select pk, end_dt as dt from table1
    union all
    select pk, start_dt as dt from table2
    union all
    select pk, end_dt as dt from table2
),
-- 第二步：对每个PK的时间节点去重、排序，生成连续的小时间段
split_ranges as (
    select 
        pk,
        dt as segment_start,
        -- 用lead获取下一个时间节点，处理最后一个区间的end_dt为9999-12-31
        case 
            when next_dt = '9999-12-31' then next_dt
            else next_dt
        end as segment_end
    from (
        select 
            pk,
            dt,
            lead(dt, 1, '9999-12-31') over (partition by pk order by dt) as next_dt
        from (
            -- 去重避免重复的时间节点
            select distinct pk, dt from all_time_nodes
        ) distinct_dates
    ) ordered_dates
    -- 过滤掉最后一个节点（因为lead已经处理到9999-12-31）
    where dt != '9999-12-31'
),
-- 第三步：匹配每个时间段对应的table1的var1状态
t1_segment_status as (
    select 
        sr.pk,
        sr.segment_start as start_dt,
        sr.segment_end as end_dt,
        t1.var1
    from split_ranges sr
    left join table1 t1
        on sr.pk = t1.pk
        -- 判断时间段是否完全落在table1的区间内（左闭右闭）
        and sr.segment_start >= t1.start_dt
        and sr.segment_end <= t1.end_dt
),
-- 第四步：匹配每个时间段对应的table2的var2状态
t2_segment_status as (
    select 
        sr.pk,
        sr.segment_start as start_dt,
        sr.segment_end as end_dt,
        t2.var2
    from split_ranges sr
    left join table2 t2
        on sr.pk = t2.pk
        and sr.segment_start >= t2.start_dt
        and sr.segment_end <= t2.end_dt
)
-- 第五步：合并两个状态表，得到最终结果
select 
    t1.pk,
    t1.var1,
    t2.var2,
    t1.start_dt,
    t1.end_dt
from t1_segment_status t1
join t2_segment_status t2
    on t1.pk = t2.pk
    and t1.start_dt = t2.start_dt
    and t1.end_dt = t2.end_dt
-- 过滤掉无效的时间段（比如start_dt > end_dt，可能因为时间节点顺序问题）
where t1.start_dt <= t1.end_dt
order by t1.pk, t1.start_dt;

代码说明 #

• all_time_nodes：把两个表的所有起始和结束日期都收集起来，确保不会漏掉任何需要拆分的时间点；
• split_ranges：用lead()窗口函数生成每个时间节点的下一个节点，从而得到最小的时间段。这里特意处理了最后一个区间的结束日期为9999-12-31，符合SCD-2的默认结束值规范；
• t1_segment_status/t2_segment_status：通过左连接原表，确保每个时间段都能匹配到对应的状态值，如果时间段不在原表的任何区间内，就返回NULL；
• 最后合并两个状态表，按PK和起始日期排序，得到最终的拆分结果。

针对你例子的验证 #

拿你给出的PK=123的情况来说，这个代码会：

1. 收集到所有时间节点：2010-01-15、2015-01-15、2015-01-16、2025-05-02、2025-05-03、9999-12-31、2015-05-27（假设这里是笔误，应该是2025-05-27，否则会生成无效区间被过滤）；
2. 生成的有效时间段会完全匹配你期望的拆分逻辑，每个时间段分别对应table1的var1和table2的var2状态，包括其中一个表状态结束后的空白期。

内容来源于stack exchange