基于时间范围的数据重组与聚合方案咨询（Pandas/SQL实现）

Got it, let's tackle this problem step by step. You want to split the timeline from the earliest job start to the latest job end into intervals based on all the start/end points of your tasks, then count how many jobs are active in each interval. Below are two robust approaches—one using MySQL directly (great for large datasets that stay in the database) and another with Pandas (perfect for local data or post-processing workflows).

MySQL Solution #

The core idea here is to first collect all unique time points from your job data, turn them into contiguous intervals, then count overlapping jobs for each interval. Here's the full query with breakdowns:

WITH all_times AS (
    -- Gather every start and end time from the jobs table
    SELECT time_start AS point FROM jobs
    UNION ALL
    SELECT time_end AS point FROM jobs
),
sorted_times AS (
    -- Sort the points and use LEAD to get the next time point for each entry
    SELECT 
        point,
        LEAD(point) OVER (ORDER BY point) AS next_point
    FROM all_times
    ORDER BY point
),
time_intervals AS (
    -- Create valid time intervals (skip entries where there's no next point)
    SELECT 
        point AS time_start,
        next_point AS time_end
    FROM sorted_times
    WHERE next_point IS NOT NULL
)
-- Count active jobs for each interval
SELECT 
    ti.time_start,
    ti.time_end,
    COUNT(j.job_id) AS active_jobs
FROM time_intervals ti
LEFT JOIN jobs j 
    -- A job is active if its start is before/at the interval's end, and its end is after/at the interval's start
    ON j.time_start <= ti.time_end 
    AND j.time_end >= ti.time_start
GROUP BY ti.time_start, ti.time_end
ORDER BY ti.time_start;

How this works: #

1. all_times: Pulls every start and end time from your jobs table (using UNION ALL to keep duplicates temporarily, though they'll get handled in the next step).
2. sorted_times: Sorts all time points and uses the LEAD() window function to pair each point with the next one in sequence—this is how we build our intervals.
3. time_intervals: Filters out any incomplete intervals (where next_point is null, which only happens for the very last time point) to get clean start/end pairs.
4. Final join & count: We left join the intervals with the original jobs table using a condition that checks for overlapping time ranges, then count how many jobs match each interval.

Pandas Solution #

If you're working with data locally, Pandas gives you flexible tools to do the same logic. Let's walk through it with your sample data:

import pandas as pd

# Sample data (replace with your actual data import, e.g., pd.read_sql())
data = {
    'job_id': [1, 2, 3],
    'time_start': ['00:00', '02:00', '06:00'],
    'time_end': ['04:00', '05:00', '07:00']
}
df = pd.DataFrame(data)

# Convert time strings to datetime objects (critical for accurate comparisons)
df['time_start'] = pd.to_datetime(df['time_start'], format='%H:%M')
df['time_end'] = pd.to_datetime(df['time_end'], format='%H:%M')

# Collect all unique time points and sort them
all_time_points = pd.concat([df['time_start'], df['time_end']]).sort_values().unique()

# Create intervals by pairing each point with the next one
time_intervals = pd.DataFrame({
    'time_start': all_time_points[:-1],
    'time_end': all_time_points[1:]
})

# Function to count active jobs for a single interval
def count_active(row):
    # Check if a job overlaps with the current interval
    is_active = (df['time_start'] <= row['time_end']) & (df['time_end'] >= row['time_start'])
    return is_active.sum()

# Apply the function to every interval
time_intervals['active_jobs'] = time_intervals.apply(count_active, axis=1)

# Optional: Convert datetime back to string format for readability
time_intervals['time_start'] = time_intervals['time_start'].dt.strftime('%H:%M')
time_intervals['time_end'] = time_intervals['time_end'].dt.strftime('%H:%M')

print(time_intervals)

Output for your sample data: #

time_start time_end  active_jobs
0     00:00    02:00            1
1     02:00    04:00            2
2     04:00    05:00            1
3     05:00    06:00            0
4     06:00    07:00            1

Optimization note for large datasets: #

If you're working with thousands of jobs, the apply() method can be slow. For a faster approach, use vectorized operations with broadcasting:

# Vectorized version (faster for large data)
starts = time_intervals['time_start'].values[:, None]
ends = time_intervals['time_end'].values[:, None]

job_starts = df['time_start'].values
job_ends = df['time_end'].values

# Create a boolean matrix where each row is an interval, each column is a job
overlaps = (job_starts <= ends) & (job_ends >= starts)

# Sum across columns to get active job count per interval
time_intervals['active_jobs'] = overlaps.sum(axis=1)

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