在R中绘制按星期小时拆分的跨时段机器负载直方图

你的思路方向完全正确，核心问题是补全无任务时段的0值，这可以通过生成机器-星期-小时的全量维度组合，再和任务数据关联来解决。下面提供SQL（数据库场景）和Python Pandas（本地数据处理）两种常用实现方案：

方案一：SQL实现（适用于数据库存储的任务数据） #

步骤1：生成全量维度表 #

先构建包含所有机器、星期、小时的完整组合，这是补全0值的基础：

-- 提取所有唯一机器编号
WITH positions AS (
    SELECT DISTINCT position FROM your_task_table
),
-- 生成星期维度（0=周日，1=周一…6=周六，可根据需求调整）
week_days AS (
    SELECT generate_series(0,6) AS week_day
),
-- 生成小时维度（0-23点）
hour_ranges AS (
    SELECT generate_series(0,23) AS hour_of_day
),
-- 生成机器-星期-小时的全量笛卡尔积
all_dimensions AS (
    SELECT p.position, w.week_day, h.hour_of_day
    FROM positions p
    CROSS JOIN week_days w
    CROSS JOIN hour_ranges h
)

步骤2：拆分跨小时任务并计算负载占比 #

将每个任务拆分为以整点为边界的分段，计算各分段的负载占比（有效时长/3600秒）：

, split_task_segments AS (
    SELECT
        position,
        -- 提取任务开始时间对应的星期
        EXTRACT(DOW FROM job_started_at) AS week_day,
        -- 生成任务覆盖的所有整点小时
        generate_series(
            DATE_TRUNC('hour', job_started_at),
            DATE_TRUNC('hour', job_finished_at),
            INTERVAL '1 hour'
        ) AS hour_start,
        -- 计算分段实际开始时间（取任务开始和整点的最大值）
        GREATEST(job_started_at, DATE_TRUNC('hour', job_started_at)) AS segment_start,
        -- 计算分段实际结束时间（取任务结束和下一个整点的最小值）
        LEAST(job_finished_at, DATE_TRUNC('hour', job_started_at) + INTERVAL '1 hour') AS segment_end
    FROM your_task_table
)
, load_ratio_calc AS (
    SELECT
        position,
        week_day,
        EXTRACT(HOUR FROM hour_start) AS hour_of_day,
        -- 计算该分段的负载占比
        EXTRACT(EPOCH FROM (segment_end - segment_start)) / 3600 AS load_ratio
    FROM split_task_segments
)

步骤3：关联维度表补0，计算均值 #

将负载数据与全量维度左连接，缺失值填充0，最后按维度分组计算平均负载：

SELECT
    ad.position,
    ad.week_day,
    ad.hour_of_day,
    -- 无任务时段填充0，计算同一维度下的平均负载
    COALESCE(AVG(lrc.load_ratio), 0) AS avg_load_ratio
FROM all_dimensions ad
LEFT JOIN load_ratio_calc lrc
    ON ad.position = lrc.position
    AND ad.week_day = lrc.week_day
    AND ad.hour_of_day = lrc.hour_of_day
GROUP BY ad.position, ad.week_day, ad.hour_of_day
ORDER BY ad.week_day, ad.position, ad.hour_of_day;

方案二：Python Pandas实现（适用于本地CSV/Excel数据） #

步骤1：加载数据并预处理 #

import pandas as pd
import numpy as np

# 加载数据，自动解析时间列
df = pd.read_csv('your_task_data.csv', parse_dates=['job_started_at', 'job_finished_at'])
# 添加星期列（0=周一，6=周日，可通过isoweekday()调整为1=周一）
df['week_day'] = df['job_started_at'].dt.weekday

步骤2：拆分跨小时任务 #

# 定义拆分单个任务的函数
def split_single_job(row):
    start = row['job_started_at']
    end = row['job_finished_at']
    position = row['position']
    
    # 生成任务覆盖的所有整点小时
    hour_list = pd.date_range(start=start.floor('H'), end=end.floor('H'), freq='H')
    segments = []
    for hour_start in hour_list:
        seg_start = max(start, hour_start)
        seg_end = min(end, hour_start + pd.Timedelta(hours=1))
        # 计算负载占比
        load_ratio = (seg_end - seg_start).total_seconds() / 3600
        # 处理跨天任务的星期变化
        seg_week_day = seg_start.weekday()
        segments.append({
            'position': position,
            'week_day': seg_week_day,
            'hour_of_day': hour_start.hour,
            'load_ratio': load_ratio
        })
    return pd.DataFrame(segments)

# 应用函数拆分所有任务
split_df = pd.concat(df.apply(split_single_job, axis=1).tolist(), ignore_index=True)

步骤3：生成全量维度并补0 #

# 生成所有机器、星期、小时的组合
positions = df['position'].unique()
week_days = range(7)
hours = range(24)
all_dimensions = pd.MultiIndex.from_product(
    [positions, week_days, hours],
    names=['position', 'week_day', 'hour_of_day']
).to_frame(index=False)

# 左连接补0并计算平均负载
result = all_dimensions.merge(
    split_df,
    on=['position', 'week_day', 'hour_of_day'],
    how='left'
).fillna(0)

# 按维度分组计算均值
result = result.groupby(['position', 'week_day', 'hour_of_day'])['load_ratio'].mean().reset_index()

步骤4：绘制分星期直方图 #

import matplotlib.pyplot as plt

# 按星期拆分绘图
for day in range(7):
    day_data = result[result['week_day'] == day]
    plt.figure(figsize=(12, 6))
    # 为每台机器绘制小时负载
    for pos in positions:
        pos_data = day_data[day_data['position'] == pos]
        plt.bar(pos_data['hour_of_day'], pos_data['load_ratio'], label=f'Machine {pos}', alpha=0.7)
    plt.title(f'Hourly Load Distribution - Week Day {day} (0=Monday)')
    plt.xlabel('Hour of Day')
    plt.ylabel('Average Load Ratio')
    plt.xticks(range(24))
    plt.legend()
    plt.show()

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