两张拉链表通过相叠时间关联

生成模拟数据

(
    select
    '1' as user_id,'role' as role,'2023-02-01 08:00:00.000' as start_time,'2023-04-01 08:00:00.000' as end_time
    union ALL
    select
    '1' as user_id,'role1' as role,'2023-04-01 08:00:00.000' as start_time,'2023-06-01 08:00:00.000' as end_time
    union ALL
    select
    '1' as user_id,'role3' as role,'2023-07-01 08:00:00.000' as start_time,'2023-10-01 08:00:00.000' as end_time
    union ALL
    select
    '1' as user_id,'role4' as role,'2023-10-15 08:00:00.000' as start_time,'2023-10-30 08:00:00.000' as end_time

) t1

(
    select
    '1' as user_id,'identity1' as identity,'2023-01-01 08:00:00.000' as start_time,'2023-03-01 08:00:00.000' as end_time
    UNION all
    SELECT
    '1' as user_id,'identity2' as identity,'2023-03-01 08:00:00.000' as start_time,'2023-05-01 08:00:00.000' as end_time
    UNION all
    SELECT
    '1' as user_id,'identity3' as identity,'2023-08-01 08:00:00.000' as start_time,'2023-09-01 08:00:00.000' as end_time
    UNION all
    SELECT
    '1' as user_id,'identity4' as identity,'2023-10-01 08:00:00.000' as start_time,'2023-11-01 08:00:00.000' as end_time
    
) t2

关联两张表 并筛选出有交叠部分的数据

with cross_time as (
    SELECT
        t1.user_id,
        role,
        identity,
        t1.start_time as t1_start_time,
        t1.end_time as t1_end_time,
        t2.start_time as t2_start_time,
        t2.end_time as t2_end_time
    FROM
    (
        select
        '1' as user_id,'role' as role,'2023-02-01 08:00:00.000' as start_time,'2023-04-01 08:00:00.000' as end_time
        union ALL
        select
        '1' as user_id,'role1' as role,'2023-04-01 08:00:00.000' as start_time,'2023-06-01 08:00:00.000' as end_time
        union ALL
        select
        '1' as user_id,'role3' as role,'2023-07-01 08:00:00.000' as start_time,'2023-10-01 08:00:00.000' as end_time
        union ALL
        select
        '1' as user_id,'role4' as role,'2023-10-15 08:00:00.000' as start_time,'2023-10-30 08:00:00.000' as end_time

    ) t1

    join 

    (
        select
        '1' as user_id,'identity1' as identity,'2023-01-01 08:00:00.000' as start_time,'2023-03-01 08:00:00.000' as end_time
        UNION all
        SELECT
        '1' as user_id,'identity2' as identity,'2023-03-01 08:00:00.000' as start_time,'2023-05-01 08:00:00.000' as end_time
        UNION all
        SELECT
        '1' as user_id,'identity3' as identity,'2023-08-01 08:00:00.000' as start_time,'2023-09-01 08:00:00.000' as end_time
        UNION all
        SELECT
        '1' as user_id,'identity4' as identity,'2023-10-01 08:00:00.000' as start_time,'2023-11-01 08:00:00.000' as end_time
        
    ) t2
    on t1.user_id = t2.user_id
    WHERE (t1.start_time <= t2.start_time AND t1.end_time >= t2.start_time) or (t1.start_time >= t2.start_time AND t1.start_time <= t2.end_time)

选出第一张表开始时间更晚的部分

,left_big as (
    select 
        user_id,
        role,
        identity,
        t1_start_time as start_time,
        if(t1_end_time<t2_end_time,t1_end_time,t2_end_time) as end_time
    from cross_time
    where t1_start_time > t2_start_time
)

选出第一张表开始时间更早的部分

,right_big as (
    select 
        user_id,
        role,
        identity,
        t2_start_time as start_time,
        if(t1_end_time<t2_end_time,t1_end_time,t2_end_time) as end_time
    from cross_time
    where t1_start_time < t2_start_time
)

union两部分的数据

SELECT * from left_big

union all

SELECT * from right_big

运行结果

简单测试数据不知道是否有bug,欢迎评论区讨论