Azure Databricks中Cycle、Activity与Delay表的SQL时间线关联实现

问题核心 #

• Cycle与Activity通过CYCLE_OID关联，构成完整周期-活动时间线
• Delay是Activity中延迟活动的子集，但无直接业务关联字段（如ACTIVITY_OID/CYCLE_OID）
• 现有条件关联仅能匹配单个活动内的延迟，无法覆盖单条延迟跨多个Cycle/Activity的场景

核心解决思路 #

由于缺乏直接关联键，需通过时间区间重叠规则将Delay关联到所有与其时间范围有交集的Activity，再通过Activity关联对应Cycle，确保跨周期/活动的延迟记录能匹配到所有相关的周期-活动组合。

示例表结构（基于常规场景补充，可根据实际调整） #

Cycle表

Activity表

Delay表

目标结果表

兼容Azure Databricks的SQL代码 #

SELECT
    c.CYCLE_OID,
    c.CYCLE_START,
    c.CYCLE_END,
    a.ACTIVITY_OID,
    a.ACTIVITY_START,
    a.ACTIVITY_END,
    d.DELAY_OID,
    d.DELAY_START,
    d.DELAY_END,
    d.DELAY_REASON,
    -- 可选：计算延迟在当前活动内的实际有效时间段
    GREATEST(d.DELAY_START, a.ACTIVITY_START) AS ACTUAL_DELAY_START,
    LEAST(d.DELAY_END, a.ACTIVITY_END) AS ACTUAL_DELAY_END
FROM
    Cycle c
JOIN
    Activity a ON c.CYCLE_OID = a.CYCLE_OID
LEFT JOIN
    Delay d 
    ON -- 核心规则：判断延迟与活动的时间区间是否重叠
    d.DELAY_START < a.ACTIVITY_END 
    AND d.DELAY_END > a.ACTIVITY_START
-- 若仅需返回含延迟的记录，取消下面注释
-- WHERE d.DELAY_OID IS NOT NULL
ORDER BY
    c.CYCLE_OID, a.ACTIVITY_START;

逻辑说明 #

1. 基础关联：先通过CYCLE_OID关联Cycle和Activity，构建完整的周期-活动时间线
2. 时间重叠匹配：d.DELAY_START < a.ACTIVITY_END AND d.DELAY_END > a.ACTIVITY_START能覆盖所有时间交集场景：
   • 延迟完全包含在单个活动内
   • 延迟跨多个活动/周期（如示例中D1同时匹配A2和A3）
   • 延迟部分覆盖活动的起始/结束阶段
3. 可选扩展：通过GREATEST/LEAST计算延迟在当前活动内的实际有效时间段，满足精细化统计需求

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