BigQuery中基于起止日期重叠拆分数据行的实现方案问询

原始数据 #

期望输出 #

可行SQL查询方案 #

可以用CTE+窗口函数实现，核心思路是提取所有关键分割日期，生成连续时间区间后匹配对应优先级的percent值：

通用SQL（适用于PostgreSQL等支持标准日期函数的数据库） #

WITH key_dates AS (
    -- 收集所有用于分割的关键日期点
    SELECT id, start_date AS date FROM your_table
    UNION ALL
    SELECT id, end_date + INTERVAL '1 day' AS date FROM your_table
    UNION ALL
    SELECT id, (SELECT MIN(start_date) FROM your_table WHERE priority = 1) - INTERVAL '1 day' AS date FROM your_table WHERE priority = 2
),
sorted_dates AS (
    -- 按ID和日期排序，用LEAD生成连续的区间起止对
    SELECT 
        id,
        date AS start_date,
        LEAD(date) OVER (PARTITION BY id ORDER BY date) AS end_date
    FROM key_dates
    WHERE date <= (SELECT MAX(end_date) FROM your_table)
),
priority_ranges AS (
    -- 单独提取高优先级的时间区间
    SELECT id, percent, start_date, end_date FROM your_table WHERE priority = 1
)
-- 匹配每个区间对应的percent值，调整日期确保无重叠
SELECT 
    sd.id,
    COALESCE(pr.percent, (SELECT percent FROM your_table WHERE priority = 2 AND id = sd.id)) AS percent,
    sd.start_date,
    CASE 
        WHEN sd.end_date IS NOT NULL THEN sd.end_date - INTERVAL '1 day'
        ELSE (SELECT end_date FROM your_table WHERE priority = 2 AND id = sd.id)
    END AS end_date
FROM sorted_dates sd
LEFT JOIN priority_ranges pr 
    ON sd.id = pr.id 
    AND sd.start_date >= pr.start_date 
    AND sd.end_date <= pr.end_date + INTERVAL '1 day'
WHERE sd.end_date IS NOT NULL
ORDER BY sd.id, sd.start_date;

MySQL适配版 #

将日期函数替换为MySQL支持的语法：

WITH key_dates AS (
    SELECT id, start_date AS date FROM your_table
    UNION ALL
    SELECT id, DATE_ADD(end_date, INTERVAL 1 DAY) AS date FROM your_table
    UNION ALL
    SELECT id, DATE_SUB((SELECT MIN(start_date) FROM your_table WHERE priority = 1), INTERVAL 1 DAY) AS date FROM your_table WHERE priority = 2
),
sorted_dates AS (
    SELECT 
        id,
        date AS start_date,
        LEAD(date) OVER (PARTITION BY id ORDER BY date) AS end_date
    FROM key_dates
    WHERE date <= (SELECT MAX(end_date) FROM your_table)
),
priority_ranges AS (
    SELECT id, percent, start_date, end_date FROM your_table WHERE priority = 1
)
SELECT 
    sd.id,
    COALESCE(pr.percent, (SELECT percent FROM your_table WHERE priority = 2 AND id = sd.id)) AS percent,
    sd.start_date,
    CASE 
        WHEN sd.end_date IS NOT NULL THEN DATE_SUB(sd.end_date, INTERVAL 1 DAY)
        ELSE (SELECT end_date FROM your_table WHERE priority = 2 AND id = sd.id)
    END AS end_date
FROM sorted_dates sd
LEFT JOIN priority_ranges pr 
    ON sd.id = pr.id 
    AND sd.start_date >= pr.start_date 
    AND sd.end_date <= DATE_ADD(pr.end_date, INTERVAL 1 DAY)
WHERE sd.end_date IS NOT NULL
ORDER BY sd.id, sd.start_date;

逻辑说明 #

1. key_dates：收集所有必要的分割点，包括原始区间起止、高优先级区间的前后一天，确保分割覆盖所有需要拆分的位置。
2. sorted_dates：对每个ID的日期排序，用LEAD函数生成下一个日期作为区间结束，形成连续无重叠的日期段。
3. priority_ranges：单独提取高优先级区间，方便后续匹配。
4. 最后通过关联匹配，优先使用高优先级的percent值，否则用低优先级值，同时调整结束日期保证区间无重叠。

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