如何获取多段数据的ID范围（含连续ErrorLineFlag=1区间）

可以实现该需求，下面提供两种常用的实现方式：

一、SQL实现（以MySQL为例） #

核心思路是通过窗口函数识别连续的ErrorLineFlag=1分组，提取每个分组的最小/最大LogFileTextID作为上下界，最后合并所有分段并生成序号。

原始数据 #

实现代码 #

WITH grouped_data AS (
    SELECT 
        LogFileTextID,
        ErrorLineFlag,
        -- 标记连续错误行的分组：当前行是错误且上一行不是错误时，生成新分组ID
        SUM(CASE WHEN ErrorLineFlag=1 AND LAG(ErrorLineFlag,1,0) OVER(ORDER BY LogFileTextID)!=1 THEN 1 ELSE 0 END) 
            OVER(ORDER BY LogFileTextID) AS error_group_id
    FROM your_table_name
),
error_ranges AS (
    SELECT 
        error_group_id,
        MIN(LogFileTextID) AS LowerID,
        MAX(LogFileTextID) AS UpperID
    FROM grouped_data
    WHERE ErrorLineFlag=1
    GROUP BY error_group_id
),
all_segments AS (
    -- 加入所有非错误行（单条记录，UpperID为空）
    SELECT 
        LogFileTextID AS LowerID,
        NULL AS UpperID
    FROM grouped_data
    WHERE ErrorLineFlag=0
    UNION ALL
    -- 加入连续错误行的分组记录
    SELECT LowerID, UpperID FROM error_ranges
)
-- 排序并生成InstanceNum
SELECT 
    ROW_NUMBER() OVER(ORDER BY LowerID) AS InstanceNum,
    LowerID,
    UpperID
FROM all_segments
ORDER BY InstanceNum;

执行结果 #

二、Python实现（使用Pandas） #

利用Pandas的分组功能识别连续错误行，合并非错误单条记录与错误分组范围，最终生成目标格式结果。

实现代码 #

import pandas as pd

# 构造原始数据
data = {
    'LogFileTextID': [88182351,88182352,88182353,88182354,88182355,88182356,88182357,
                      88182358,88182359,88182360,88182361,88182362,88182363,88182364,
                      88182365,88182366,88182367,88182368,88182369,88182370,88182371,
                      88182372,88182373,88182374,88182375,88182376,88182377,88182378,
                      88182379,88182380],
    'ErrorLineFlag': [0,0,0,0,1,1,0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0,0,0]
}
df = pd.DataFrame(data)

# 标记连续错误行的分组ID
df['group_id'] = (df['ErrorLineFlag'] != df['ErrorLineFlag'].shift(1)).cumsum()

# 提取错误分组的上下界
error_groups = df[df['ErrorLineFlag'] == 1].groupby('group_id').agg(
    LowerID=('LogFileTextID', 'min'),
    UpperID=('LogFileTextID', 'max')
).reset_index(drop=True)

# 处理非错误行：单条记录，UpperID设为空
non_error_rows = df[df['ErrorLineFlag'] == 0].rename(columns={'LogFileTextID': 'LowerID'})
non_error_rows['UpperID'] = None

# 合并所有分段并排序
all_segments = pd.concat([non_error_rows[['LowerID', 'UpperID']], error_groups], ignore_index=True)
all_segments = all_segments.sort_values('LowerID').reset_index(drop=True)

# 生成InstanceNum序号
all_segments['InstanceNum'] = all_segments.index + 1

# 调整列顺序并输出
result = all_segments[['InstanceNum', 'LowerID', 'UpperID']]
print(result)

输出结果 #

InstanceNum  LowerID   UpperID
0             1  88182351       NaN
1             2  88182352       NaN
2             3  88182353       NaN
3             4  88182354       NaN
4             5  88182355  88182356
5             6  88182357       NaN
6             7  88182358  88182372
7             8  88182373       NaN
8             9  88182374       NaN
9            10  88182375       NaN
10           11  88182376       NaN
11           12  88182377       NaN
12           13  88182378       NaN
13           14  88182379       NaN
14           15  88182380       NaN

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