在Snowflake与dbt中拼接时间区间并筛选高优先级机器状态

我有一张SQL表，记录每台机器的状态及其对应的开始、结束时间，表中时间存在重叠情况，同一时间点可能对应多个状态。需要生成连续时间序列，每个区间内仅保留最高优先级（数值越小优先级越高）的状态。该表约有1100万行数据，状态持续时间可达数小时，曾尝试交叉连接但性能极差。

示例数据 #

期望输出：生成最小时间区间，每个区间内为最高优先级的状态。

当前查询语句 #

with main as (
select
    machine_id,
    state,
    priority,
    start_date_time,
    end_date_time
from pseimenis.public_manufacturing.test),
-- Create an interval set
intervals as (
    select
        machine_id,
        start_date_time as date_time
    from main
    union
    select
        machine_id,
        end_date_time as date_time
    from main
),
interval_times as 
(select 
    machine_id,
    date_time as start_date_time,
    lead(date_time) over (partition by machine_id order by start_date_time) as end_date_time
from intervals
)
select it.*,
    m.state,
    m.priority
from interval_times it 
left join main m on it.machine_id = m.machine_id
and it.start_date_time >= m.start_date_time and m.end_date_time >= it.end_date_time
qualify row_number() over (partition by it.machine_id, it.start_date_time order by m.priority) = 1
order by it.start_date_time, priority

执行计划 #

GlobalStats:
    partitionsTotal=8
    partitionsAssigned=8
    bytesAssigned=27251200
Operations:
1:0     ->Result  UNION_ALL(MAIN.MACHINE_ID, MAIN.MACHINE_ID), UNION_ALL(MAIN.START_DATE_TIME, MAIN.END_DATE_TIME), LEAD(UNION_ALL(MAIN.START_DATE_TIME, MAIN.END_DATE_TIME)) OVER (PARTITION BY UNION_ALL(MAIN.MACHINE_ID, MAIN.MACHINE_ID) ORDER BY UNION_ALL(MAIN.START_DATE_TIME, MAIN.END_DATE_TIME) ASC NULLS LAST), SYS_VW.STATE_4, SYS_VW.PRIORITY_2  
1:1          ->Sort  UNION_ALL(MAIN.START_DATE_TIME, MAIN.END_DATE_TIME) ASC NULLS LAST, SYS_VW.PRIORITY_2 ASC NULLS LAST  
1:2               ->Filter  ROW_NUMBER() OVER (PARTITION BY UNION_ALL(MAIN.MACHINE_ID, MAIN.MACHINE_ID), UNION_ALL(MAIN.START_DATE_TIME, MAIN.END_DATE_TIME) ORDER BY SYS_VW.PRIORITY_2 ASC NULLS LAST) = 1  
1:3                    ->WindowFunction  ROW_NUMBER() OVER (PARTITION BY UNION_ALL(MAIN.MACHINE_ID, MAIN.MACHINE_ID), UNION_ALL(MAIN.START_DATE_TIME, MAIN.END_DATE_TIME) ORDER BY SYS_VW.PRIORITY_2 ASC NULLS LAST)  
1:4                         ->LeftOuterJoin  joinKey: (UNION_ALL(MAIN.MACHINE_ID, MAIN.MACHINE_ID) = SYS_VW.MACHINE_ID_1), joinFilter: ((UNION_ALL(MAIN.START_DATE_TIME, MAIN.END_DATE_TIME)) >= SYS_VW.START_DATE_TIME_3) AND (SYS_VW.END_DATE_TIME_0 >= LEAD(UNION_ALL(MAIN.START_DATE_TIME, MAIN.END_DATE_TIME)) OVER (PARTITION BY UNION_ALL(MAIN.MACHINE_ID, MAIN.MACHINE_ID) ORDER BY UNION_ALL(MAIN.START_DATE_TIME, MAIN.END_DATE_TIME) ASC NULLS LAST))  
1:5                              ->WindowFunction  LEAD(UNION_ALL(MAIN.START_DATE_TIME, MAIN.END_DATE_TIME)) OVER (PARTITION BY UNION_ALL(MAIN.MACHINE_ID, MAIN.MACHINE_ID) ORDER BY UNION_ALL(MAIN.START_DATE_TIME, MAIN.END_DATE_TIME) ASC NULLS LAST)  
1:6                                   ->Aggregate  groupKeys: [UNION_ALL(MAIN.MACHINE_ID, MAIN.MACHINE_ID), UNION_ALL(MAIN.START_DATE_TIME, MAIN.END_DATE_TIME)]  
1:7                                        ->UnionAll  
1:8                                             ->WithReference  
1:9                                                  ->WithClause  MAIN  
1:10                                                       ->TableScan  PSEIMENIS.PUBLIC_MANUFACTURING.TEST  MACHINE_ID, STATE, STATE_ID_PRIORITY, START_DATE_TIME, END_DATE_TIME  {partitionsTotal=8, partitionsAssigned=8, bytesAssigned=27251200}
1:11                                             ->WithReference  
1:12                              ->Filter  M.MACHINE_ID IS NOT NULL  
1:13                                   ->JoinFilter  joinKey: (UNION_ALL(MAIN.MACHINE_ID, MAIN.MACHINE_ID) = SYS_VW.MACHINE_ID_1), joinFilter: ((UNION_ALL(MAIN.START_DATE_TIME, MAIN.END_DATE_TIME)) >= SYS_VW.START_DATE_TIME_3) AND (SYS_VW.END_DATE_TIME_0 >= LEAD(UNION_ALL(MAIN.START_DATE_TIME, MAIN.END_DATE_TIME)) OVER (PARTITION BY UNION_ALL(MAIN.MACHINE_ID, MAIN.MACHINE_ID) ORDER BY UNION_ALL(MAIN.START_DATE_TIME, MAIN.END_DATE_TIME) ASC NULLS LAST))  
1:14                                        ->WithReference  M  

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