PostgreSQL输入末尾语法错误排查及场馆事件时间区间合并查询求助

首先是建表及插入数据：

create table hall_events
(
hall_id integer,
start_date date,
end_date date
);

delete from hall_events;

insert into hall_events values 
(1,'2023-01-13','2023-01-14')
,(1,'2023-01-14','2023-01-17')
,(1,'2023-01-15','2023-01-17')
,(1,'2023-01-18','2023-01-25')
,(2,'2022-12-09','2022-12-23')
,(2,'2022-12-13','2022-12-17')
,(3,'2022-12-01','2023-01-30');

select * from hall_events;

期望合并同场馆内重叠或相邻的时间区间，得到如下结果：

hall_id start_date end_date
1       2023-01-13 2023-01-17
1       2023-01-18 2023-01-25
2       2022-12-09 2022-12-17
3       2022-12-01 2023-01-30

但编写的查询语句报错：Postgres Sql syntax error at end of input，错误语句如下：

WITH cte as
(select *,row_number()over(order by hall_id,start_date) as event_id
from(
with recursive r_cte as(select *,1 as flag from cte where event_id=1
              union all
              select cte.hall_id,cte.start_date,cte.end_date,cte.event_id,
              case when cte.hall_id=r_cte.hall_id and 
              (cte.start_date between r_cte.start_date and r_cte.end_date or
              r_cte.start_date between cte.start_date and cte.end_date) 
              then 0 else 1 end +flag
              from r_cte
              inner join cte on r_cte.event_id+1=cte.event_id)
select * from cte)x)

• 循环引用CTE：递归CTE r_cte 中引用了外层尚未定义完成的 cte，这在PostgreSQL中是不允许的，CTE的引用必须遵循定义顺序。
• 缺少最终查询语句：整个代码仅定义了CTE结构，但没有在最后添加实际的SELECT查询来输出结果，导致语法解析到末尾时出错。
• 递归逻辑设计缺陷：当前的递归逻辑无法正确识别并合并重叠/相邻区间，没有维护合并后的区间起止日期。

以下是两种可行的PostgreSQL实现方法：

方法一：使用窗口函数（非递归，性能更优） #

WITH ordered_events AS (
    SELECT 
        hall_id,
        start_date,
        end_date,
        -- 标记是否为新的合并组起点：当前区间的start_date > 前一个区间的end_date则为新组
        SUM(CASE WHEN start_date <= LAG(end_date) OVER (PARTITION BY hall_id ORDER BY start_date) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY hall_id ORDER BY start_date) AS group_id
    FROM hall_events
)
SELECT 
    hall_id,
    MIN(start_date) AS start_date,
    MAX(end_date) AS end_date
FROM ordered_events
GROUP BY hall_id, group_id
ORDER BY hall_id, start_date;

方法二：使用递归CTE #

WITH ordered_events AS (
    SELECT 
        hall_id,
        start_date,
        end_date,
        ROW_NUMBER() OVER (PARTITION BY hall_id ORDER BY start_date) AS rn
    FROM hall_events
),
recursive_merge AS (
    -- 递归起点：每个场馆的第一个事件
    SELECT 
        hall_id,
        start_date,
        end_date,
        rn
    FROM ordered_events
    WHERE rn = 1
    
    UNION ALL
    
    -- 递归逻辑：合并重叠/相邻区间
    SELECT 
        r.hall_id,
        -- 保留最早的start_date
        LEAST(r.start_date, o.start_date),
        -- 取最大的end_date
        GREATEST(r.end_date, o.end_date),
        o.rn
    FROM recursive_merge r
    JOIN ordered_events o 
        ON r.hall_id = o.hall_id 
        AND o.rn = r.rn + 1
        -- 判断当前区间是否与已合并的区间重叠或相邻
        AND o.start_date <= r.end_date + INTERVAL '1 day'
)
SELECT 
    hall_id,
    MIN(start_date) AS start_date,
    MAX(end_date) AS end_date
FROM recursive_merge
GROUP BY hall_id, rn
ORDER BY hall_id, start_date;

两种方法都能得到期望的合并结果，其中窗口函数的方法在数据量较大时性能更优。

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