BigQuery中能否合并并拆分重叠的区间数组？

完全可以在BigQuery中实现该需求，以下是具体的实现方案：

核心思路 #

1. 提取所有关键日期：将两张表的StartDate和EndDate全部收集起来，按ID分组后排序，这些日期就是拆分区间的边界点。
2. 生成连续子区间：通过窗口函数将相邻的日期配对，形成一个个不重叠的子区间。
3. 判断区间类型：对每个子区间，检查它是否被苹果区间覆盖、香蕉区间覆盖，从而标记为A（仅苹果）、B（仅香蕉）或AB（两者都有）。
4. 过滤无效区间：剔除起始日期大于等于结束日期的无效区间。

示例实现代码 #

假设苹果表名为apple_table，香蕉表名为banana_table，执行以下SQL：

WITH all_dates AS (
    -- 收集所有ID对应的关键日期
    SELECT ID, PARSE_DATE('%d/%m/%y', StartDate) AS date FROM apple_table
    UNION ALL
    SELECT ID, PARSE_DATE('%d/%m/%y', EndDate) AS date FROM apple_table
    UNION ALL
    SELECT ID, PARSE_DATE('%d/%m/%y', StartDate) AS date FROM banana_table
    UNION ALL
    SELECT ID, PARSE_DATE('%d/%m/%y', EndDate) AS date FROM banana_table
),
sorted_dates AS (
    -- 按ID和日期排序，生成相邻日期对
    SELECT 
        ID,
        date AS start_date,
        LEAD(date) OVER (PARTITION BY ID ORDER BY date) AS end_date
    FROM all_dates
),
valid_intervals AS (
    -- 过滤无效区间
    SELECT ID, start_date, end_date
    FROM sorted_dates
    WHERE end_date IS NOT NULL AND start_date < end_date
),
interval_types AS (
    -- 判断每个区间的类型
    SELECT
        vi.ID,
        vi.start_date,
        vi.end_date,
        CONCAT(
            IF(EXISTS(
                SELECT 1 FROM apple_table a
                WHERE a.ID = vi.ID
                AND PARSE_DATE('%d/%m/%y', a.StartDate) <= vi.start_date
                AND PARSE_DATE('%d/%m/%y', a.EndDate) >= vi.end_date
            ), 'A', ''),
            IF(EXISTS(
                SELECT 1 FROM banana_table b
                WHERE b.ID = vi.ID
                AND PARSE_DATE('%d/%m/%y', b.StartDate) <= vi.start_date
                AND PARSE_DATE('%d/%m/%y', b.EndDate) >= vi.end_date
            ), 'B', '')
        ) AS type
    FROM valid_intervals vi
)
-- 输出最终结果，格式化日期为原格式
SELECT
    ID,
    FORMAT_DATE('%d/%m/%y', start_date) AS StartDate,
    FORMAT_DATE('%d/%m/%y', end_date) AS EndDate,
    type
FROM interval_types
ORDER BY ID, start_date;

输入示例 #

苹果食用区间表 #

香蕉食用区间表 #

输出结果 #

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