如何按天分组对列中连续相同状态的status_minutes求和直至状态变更

核心逻辑 #

你需要的是典型的「间隙与岛屿（Gaps and Islands）」场景统计，规则可拆解为：

• 分组维度为site_id、location_id、date_id，跨天、跨站点/点位的状态不合并
• 同分组内按时间顺序排列，连续相同的status归为同一个「状态段」
• 每个状态段的总时长为该段所有行status_minutes的求和值

SQL 实现（支持窗口函数的数据库通用，适配MySQL8.0+、PostgreSQL、Hive等） #

WITH step1 AS (
    -- 第一步：按时间顺序排序，判断当前行是否是新状态段的起始
    SELECT 
        *,
        CASE WHEN 
            status != LAG(status,1) OVER(PARTITION BY site_id, location_id, date_id ORDER BY hour_id, id)
            OR LAG(status,1) OVER(PARTITION BY site_id, location_id, date_id ORDER BY hour_id, id) IS NULL
        THEN 1 ELSE 0 END AS is_new_segment
    FROM your_table_name
),
step2 AS (
    -- 第二步：累加新段标记，生成唯一的状态段组号
    SELECT 
        *,
        SUM(is_new_segment) OVER(PARTITION BY site_id, location_id, date_id ORDER BY hour_id, id) AS segment_group
    FROM step1
)
-- 第三步：按状态段分组求和得到最终结果
SELECT 
    site_id,
    date_id,
    location_id,
    status,
    SUM(status_minutes) AS status_minutes
FROM step2
GROUP BY site_id, date_id, location_id, segment_group, status
ORDER BY site_id, date_id, location_id, segment_group;

Python Pandas 实现 #

import pandas as pd

# 读取数据到df，此处替换为你自己的数据源读取逻辑
df = pd.read_csv('your_data_path.csv')

# 1. 按时间顺序排序，保证状态连续判断的准确性
df = df.sort_values(by=['site_id', 'location_id', 'date_id', 'hour_id', 'id']).reset_index(drop=True)

# 2. 生成状态段组号：同分组内，状态变化或日期变化时组号+1
df['is_new_segment'] = (
    (df['status'] != df.groupby(['site_id', 'location_id', 'date_id'])['status'].shift(1))
    | (df['date_id'] != df.groupby(['site_id', 'location_id'])['date_id'].shift(1))
).cumsum()

# 3. 分组求和得到最终结果
result = df.groupby(
    ['site_id', 'date_id', 'location_id', 'is_new_segment', 'status'],
    as_index=False
)['status_minutes'].sum().drop('is_new_segment', axis=1)

结果验证 #

该逻辑计算结果和你给出的预期输出完全匹配：

• 20210101前2行Offline求和60+57=117
• 后续2行Available求和3+20=23
• 再后1行Offline单独统计为40
• 20210102的Offline不会和前一天的Offline合并，单独统计为23

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