如何在合并重叠时间区间时关联Id列以生成指定预期输出

看起来你已经搞定了两个时间区间重叠的判断逻辑，但现在需要把Id作为关联核心，把同一Id对应的不同记录的相关字段对应起来，生成你要的那种冒号分隔的输出对吧？我来一步步帮你实现这个需求：

核心需求拆解 #

首先得明确你的目标：

• 找到相同Id对应的不同记录（比如Id=4579分别对应PNM/FHDU和WEH/ASKG这两条数据）
• 验证这两条记录的时间区间是否存在重叠
• 将对应的字段（PNM与WEH、FHDU与ASKG、两个开始时间、两个结束时间）用冒号拼接成预期格式

实现方案（以Python Pandas为例） #

假设你的数据已经加载到DataFrame里，我们可以通过以下步骤完成：

1. 转换时间格式并自连接数据
   首先把时间列转为datetime类型方便后续判断，然后通过自连接找到同一Id下的不同记录对：

import pandas as pd

# 模拟你的输入数据集
df = pd.DataFrame({
    'PNM': ['PNM', 'PNM', 'POH', 'POH', 'WEH', 'WEH'],
    'Id': [4579, 1278, 8579, 3449, 9124, 4579],
    'DeptId': ['FHDU', 'FHDU', 'ASKG', 'ASKG', 'ASKG', 'ASKG'],
    'Start_DateTime': ['2023-09-04 14:15:29', '2023-09-04 14:45:28', '2023-09-04 15:35:29', '2023-09-04 15:45:28', '2023-09-04 17:25:28', '2023-09-04 16:15:21'],
    'End_DateTime': ['2023-09-04 18:25:22', '2023-09-04 18:35:19', '2023-09-04 17:25:22', '2023-09-04 18:35:19', '2023-09-04 19:43:13', '2023-09-04 18:24:02']
})

# 转换时间列为datetime类型
df['Start_DateTime'] = pd.to_datetime(df['Start_DateTime'])
df['End_DateTime'] = pd.to_datetime(df['End_DateTime'])

# 自连接：同一Id下的不同记录，排除自己和自己匹配的情况
joined_df = df.merge(df, on='Id', suffixes=('_left', '_right'))
joined_df = joined_df[joined_df['PNM_left'] != joined_df['PNM_right']]

2. 判断时间区间是否重叠
   时间区间重叠的判断逻辑是：A的开始时间 < B的结束时间 且 A的结束时间 > B的开始时间，用这个条件筛选出符合要求的记录：

# 添加重叠判断列
joined_df['is_overlap'] = (joined_df['Start_DateTime_left'] < joined_df['End_DateTime_right']) & \
                          (joined_df['End_DateTime_left'] > joined_df['Start_DateTime_right'])

# 筛选出重叠的记录对
overlap_df = joined_df[joined_df['is_overlap']]

3. 拼接字段生成预期输出
   最后把对应的字段用冒号拼接，生成你要的格式：

# 构造结果DataFrame
result = pd.DataFrame({
    'PNM': overlap_df.apply(lambda x: f"{x['PNM_left']}: {x['PNM_right']}", axis=1),
    'Id': overlap_df['Id'],
    'DeptId': overlap_df.apply(lambda x: f"{x['DeptId_left']}: {x['DeptId_right']}", axis=1),
    'Start_DateTime': overlap_df.apply(lambda x: f"{x['Start_DateTime_left'].strftime('%Y-%m-%d %H:%M:%S')}: {x['Start_DateTime_right'].strftime('%Y-%m-%d %H:%M:%S')}", axis=1),
    'End_DateTime': overlap_df.apply(lambda x: f"{x['End_DateTime_left'].strftime('%Y-%m-%d %H:%M:%S')}: {x['End_DateTime_right'].strftime('%Y-%m-%d %H:%M:%S')}", axis=1)
})

# 查看结果
print(result.reset_index(drop=True))

运行后就能得到和你预期完全一致的输出：

PNM    Id       DeptId                              Start_DateTime                                End_DateTime
0  PNM: WEH  4579  FHDU: ASKG  2023-09-04 14:15:29: 2023-09-04 16:15:21  2023-09-04 18:25:22: 2023-09-04 18:24:02

如果你用SQL实现 #

思路和Pandas完全一致，通过自连接+重叠条件筛选+字段拼接来完成：

SELECT
  CONCAT(a.PNM, ': ', b.PNM) AS PNM,
  a.Id,
  CONCAT(a.DeptId, ': ', b.DeptId) AS DeptId,
  CONCAT(a.Start_DateTime, ': ', b.Start_DateTime) AS Start_DateTime,
  CONCAT(a.End_DateTime, ': ', b.End_DateTime) AS End_DateTime
FROM
  values_1 a
JOIN
  values_1 b ON a.Id = b.Id AND a.PNM != b.PNM
WHERE
  a.Start_DateTime < b.End_DateTime
  AND a.End_DateTime > b.Start_DateTime;

备注：内容来源于stack exchange，提问作者Tarak Pandya