如何对比表中两列区间值，计算每条预定对应的重叠预定记录

核心判断逻辑 #

两个预订时间段重叠的充要条件为：当前预订开始时间 < 对比预订结束时间，且对比预订开始时间 < 当前预订结束时间，匹配时需排除自身和自身配对的无效结果。

数据库场景实现（SQL） #

假设你的预订表名为reservations，包含字段reservation_id（预订ID）、start_day（起始天数）、end_day（结束天数）：

逐行展示重叠配对 #

SELECT 
    r1.reservation_id AS reservation_id,
    r2.reservation_id AS overlapping_reservation_id
FROM 
    reservations r1
INNER JOIN 
    reservations r2
    ON r1.reservation_id != r2.reservation_id
    AND r1.start_day < r2.end_day
    AND r2.start_day < r1.end_day
ORDER BY 
    r1.reservation_id, r2.reservation_id;

按预订ID聚合展示所有重叠ID #

如果需要每个预订ID对应一行、所有重叠ID合并展示，可以使用聚合函数：

SELECT 
    r1.reservation_id,
    GROUP_CONCAT(r2.reservation_id ORDER BY r2.reservation_id SEPARATOR ',') AS overlapping_reservation_ids
FROM 
    reservations r1
LEFT JOIN 
    reservations r2
    ON r1.reservation_id != r2.reservation_id
    AND r1.start_day < r2.end_day
    AND r2.start_day < r1.end_day
GROUP BY 
    r1.reservation_id
ORDER BY 
    r1.reservation_id;

不同数据库的聚合函数有差异：PostgreSQL用STRING_AGG，SQL Server用STRING_AGG，Oracle用LISTAGG，替换对应函数即可。

Python Pandas场景实现 #

如果你是用Python处理本地数据表：

import pandas as pd

# 替换为你的数据表读取逻辑
df = pd.DataFrame({
    "reservation_id": [1,2,3,4,5],
    "start_day": [2,3,6,7,1],
    "end_day": [5,6,8,9,3]
})

overlap_result = []
for _, r1 in df.iterrows():
    overlaps = df[
        (df["reservation_id"] != r1["reservation_id"]) &
        (r1["start_day"] < df["end_day"]) &
        (df["start_day"] < r1["end_day"])
    ]["reservation_id"].tolist()
    overlap_result.append({
        "reservation_id": r1["reservation_id"],
        "overlapping_reservation_ids": ",".join(map(str, overlaps))
    })

result_df = pd.DataFrame(overlap_result)

上述代码代入你举的示例数据，返回的预订ID1的重叠ID就是2,5，和你要求的结果完全一致。

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