Oracle SQL如何对相邻相同Value值的记录分组求和Delay时长

这个需求的核心是给连续相邻的同Value块分配唯一的分组ID，之后按这个分组ID+Value聚合求Delay总和即可，不会把非连续的同Value记录合并。

1. SQL 实现（支持Oracle/MySQL 8.0+/PostgreSQL等带窗口函数的数据库） #

核心逻辑为两次窗口函数计算：

1. 用LAG()取上一行的Value值，标记当前行和上一行Value是否不同
2. 对标记做累加得到连续块的唯一分组ID，分组求和即可

示例代码（表名假设为your_table，默认按Timestamp时间顺序判断相邻关系）：

WITH step1 AS (
    SELECT 
        *,
        CASE WHEN Value = LAG(Value) OVER (ORDER BY Timestamp) THEN 0 ELSE 1 END AS block_flag
    FROM your_table
),
step2 AS (
    SELECT 
        *,
        SUM(block_flag) OVER (ORDER BY Timestamp) AS block_id
    FROM step1
)
SELECT 
    block_id,
    Value,
    MIN(Timestamp) AS block_start_time,
    MAX(Timestamp) AS block_end_time,
    SUM(`Delay(s)`) AS total_delay
FROM step2
GROUP BY block_id, Value
ORDER BY block_id;

样例数据运行结果完全符合需求：

2. Python Pandas 实现 #

逻辑和SQL一致，用shift()判断前后行Value差异，累加生成分组ID：

import pandas as pd

# 假设数据已读入df，先按时间排序保证相邻关系正确
df = df.sort_values('Timestamp').reset_index(drop=True)
# 生成连续块分组ID
df['block_id'] = (df['Value'] != df['Value'].shift()).cumsum()
# 分组求和得到结果
result = df.groupby(['block_id', 'Value'], as_index=False)['Delay(s)'].sum()

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