在PostgreSQL中将重叠的区间拆分成更小、更贴切的区间

-- 创建一个包含重叠区间的表
CREATE TABLE intervals (
   id SERIAL PRIMARY KEY,
   range INTERVAL[]
);

-- 插入一些示例数据
INSERT INTO intervals (range) VALUES
   (ARRAY['[2019-01-01, 2019-01-10]', '[2019-01-05, 2019-01-15]']),
   (ARRAY['[2019-02-01, 2019-02-10]', '[2019-02-05, 2019-02-15]']);

-- 使用unnest函数将数组展开为一组行
SELECT id, unnest(range) AS interval
FROM intervals;

-- 使用generate_series函数生成连续的序列，并与原始区间进行交集运算
SELECT id, generate_series(lower(interval), upper(interval), '1 day') AS new_interval
FROM (
   SELECT id, unnest(range) AS interval
   FROM intervals
) AS subquery;

-- 使用unnest和generate_subscripts函数将数组展开为一组行，并生成子脚标序列
SELECT id, range[i] AS interval
FROM (
   SELECT id, unnest(range) AS range, generate_subscripts(range, 1) AS i
   FROM intervals
) AS subquery;