如何按小时统计状态时长占比并聚合表格数据

我有一个包含三列的表格，原始数据结构如下：

我希望将每小时内的所有状态进行聚合，得到如下格式的结果表格：

判断标准为选取该小时内占用时长最多的状态。问题在于每小时的行数不固定，可能为3行或更多，需要通过计算实现，请问该如何操作？

这是个很典型的时间序列聚合问题，我来给你分几种常用工具的实现方法，你可以根据自己的情况选：

解决方案：分工具实现 #

1. 使用Python Pandas处理 #

这是处理大量数据最灵活的方式，咱们一步步来：

步骤1：数据准备与转换

先把日期和时间合并成完整的datetime对象，再计算每个状态的持续时长：

import pandas as pd

# 构造原始数据（如果是从文件读取，用pd.read_csv即可）
df = pd.DataFrame({
    'Date': ['23/05']*13,
    'Hour': ['12:00','12:20','12:40','13:00','13:06','13:15','13:20','13:40','14:00','14:01','14:20','14:40','15:00'],
    'Status': ['Stop','Stop','Running','Running','Stop','Running','Running','Running','Running','Other','Other','Other','Other']
})

# 合并Date和Hour为datetime格式
df['Datetime'] = pd.to_datetime(df['Date'] + ' ' + df['Hour'], format='%d/%m %H:%M')

# 计算每个状态的持续时长：用下一行的时间减去当前行时间
df['Duration'] = df['Datetime'].shift(-1) - df['Datetime']

# 处理最后一行：假设它持续到当前小时结束，时长设为1小时
df.loc[df.index[-1], 'Duration'] = pd.Timedelta(hours=1)

步骤2：按小时分组，选出时长最长的状态

# 提取小时分组键（格式和你想要的结果一致）
df['Hour_Group'] = df['Datetime'].dt.floor('H').dt.strftime('%d/%m %H:00')
# 拆分分组键为Date和Hour列
df[['Group_Date', 'Group_Hour']] = df['Hour_Group'].str.split(' ', expand=True)

# 按分组和状态聚合，计算总时长
duration_summary = df.groupby(['Group_Date', 'Group_Hour', 'Status'])['Duration'].sum().reset_index()

# 对每个小时分组，选出时长最大的状态
result = duration_summary.sort_values('Duration', ascending=False).groupby(['Group_Date', 'Group_Hour']).first().reset_index()

# 整理成目标格式
result = result.rename(columns={'Group_Date':'Date', 'Group_Hour':'Hour'})[['Date', 'Hour', 'Status']]

print(result)

运行后就能得到你想要的聚合结果。

2. 使用Excel处理 #

如果更习惯用Excel操作，也可以这样实现：

步骤1：计算每个状态的持续时长

• 新增一列Duration，在第二行（假设数据从第二行开始）输入公式：

  =IF(AND(A2=A3,B2<B3), TIMEVALUE(B3)-TIMEVALUE(B2), 1/24)
  

  这个公式的逻辑是：如果同一日期下一行有时间记录，就用下一行时间减当前时间得到时长；如果是最后一行，默认它持续1小时（1/24代表一天的1小时）。
• 把公式下拉填充到所有行。

步骤2：用数据透视表+公式选出最长时长的状态

• 选中数据区域，插入数据透视表：
  • 行标签：Date和Hour（右键点击Hour字段→分组→选择“小时”）
  • 值：Duration（汇总方式选“求和”）
  • 列标签：Status
• 透视表会显示每个小时各状态的总时长，接下来用INDEX+MATCH+MAX组合公式选出每个小时时长最大的状态，比如在新表格中输入：

  =INDEX($C:$C,MATCH(MAX(透视表对应小时的时长区域),透视表对应小时的时长区域,0))
  

  或者用Power Query的分组功能，直接按小时分组后筛选最大时长的状态，操作更简便。

3. 使用SQL处理 #

如果数据存在数据库里，用SQL也能轻松搞定：

步骤1：计算每个状态的持续时长

先计算每条记录的结束时间，再算出持续分钟数：

WITH time_with_end AS (
    SELECT 
        Date,
        Hour,
        Status,
        STR_TO_DATE(CONCAT(Date, ' ', Hour), '%d/%m %H:%i') AS start_time,
        LEAD(STR_TO_DATE(CONCAT(Date, ' ', Hour), '%d/%m %H:%i'), 1) OVER (PARTITION BY Date ORDER BY Hour) AS end_time
    FROM your_table
),
duration_calculated AS (
    SELECT 
        Date,
        DATE_FORMAT(start_time, '%H:00') AS Hour_Group,
        Status,
        TIMESTAMPDIFF(MINUTE, start_time, COALESCE(end_time, DATE_ADD(start_time, INTERVAL 1 HOUR))) AS duration_minutes
    FROM time_with_end
)

步骤2：分组选出最长时长的状态

SELECT 
    Date,
    Hour_Group AS Hour,
    Status
FROM (
    SELECT 
        Date,
        Hour_Group,
        Status,
        SUM(duration_minutes) AS total_minutes,
        ROW_NUMBER() OVER (PARTITION BY Date, Hour_Group ORDER BY SUM(duration_minutes) DESC) AS rn
    FROM duration_calculated
    GROUP BY Date, Hour_Group, Status
) t
WHERE rn = 1;

这段SQL会先计算每个状态的持续分钟数，再按小时分组，选出总时长最大的状态。

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