如何在Power BI中计算任务运行的重叠时间及相关详情

实现思路 #

将任务的时间字符串转换为可计算的时间对象，遍历所有任务对判断是否存在时间重叠；若存在，则计算具体的重叠时段和持续时长。

示例代码（Python） #

from datetime import datetime

# 任务原始数据
jobs = [
    ("A", "08:30:01 PM", "09:41:05 PM"),
    ("B", "09:00:00 PM", "09:02:14 PM"),
    ("C", "09:30:01 PM", "09:30:11 PM"),
    ("D", "10:00:00 PM", "10:05:07 PM"),
    ("E", "10:30:00 PM", "10:36:56 PM"),
    ("F", "10:45:01 PM", "10:56:50 PM")
]

# 定义时间解析格式
time_format = "%I:%M:%S %p"

# 转换时间字符串为可计算的datetime对象（默认所有任务在同一天）
processed_jobs = []
for name, start_str, end_str in jobs:
    start_time = datetime.strptime(start_str, time_format)
    end_time = datetime.strptime(end_str, time_format)
    processed_jobs.append((name, start_time, end_time))

# 检测重叠任务对并计算相关信息
overlap_details = []
for i in range(len(processed_jobs)):
    job1_name, job1_start, job1_end = processed_jobs[i]
    # 避免重复检测同一任务对（如A&B和B&A）
    for j in range(i + 1, len(processed_jobs)):
        job2_name, job2_start, job2_end = processed_jobs[j]
        # 判断时间重叠条件：任务1开始早于任务2结束，且任务2开始早于任务1结束
        if job1_start < job2_end and job2_start < job1_end:
            # 计算重叠时段的起止时间
            overlap_start = max(job1_start, job2_start)
            overlap_end = min(job1_end, job2_end)
            # 计算持续时长并格式化
            duration = overlap_end - overlap_start
            duration_str = str(duration).split(".")[0]  # 忽略微秒部分
            # 保存结果
            overlap_details.append({
                "任务对": f"{job1_name}与{job2_name}",
                "重叠时间段": f"{overlap_start.strftime(time_format)}至{overlap_end.strftime(time_format)}",
                "持续时长": duration_str
            })

# 输出检测结果
for detail in overlap_details:
    print(f"{detail['任务对']}: 重叠时间段{detail['重叠时间段']}，持续时长{detail['持续时长']}")

运行输出 #

A与B: 重叠时间段09:00:00 PM至09:02:14 PM，持续时长00:02:14
A与C: 重叠时间段09:30:01 PM至09:30:11 PM，持续时长00:00:10

注：你提供的示例中E与F无时间重叠（E结束于10:36:56 PM，F开始于10:45:01 PM），因此未被检测到；若需调整任务时间，只需修改jobs列表中的对应数据即可。

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