如何使用Pandas计算自上一个峰值以来的行数

需求说明 #

Value | Rows since Peak #

1 0
3 0
1 1
2 2
1 3
4 0
6 0
5 1

峰值定义：当前值大于此前所有值的最大值时，视为新峰值，此时行数统计重置为0；非峰值行统计距离上一个峰值的行数（从1开始递增）

实现方案 #

1. Python Pandas 实现 #

import pandas as pd

# 构造示例数据
df = pd.DataFrame({'Value': [1, 3, 1, 2, 1, 4, 6, 5]})

# 标记峰值：当前值大于之前的累计最大值
df['is_peak'] = df['Value'] > df['Value'].cummax().shift(fill_value=-float('inf'))

# 按峰值分组，计算组内行数偏移
df['Rows since Peak'] = df.groupby(df['is_peak'].cumsum()).cumcount()

# 输出结果
print(df[['Value', 'Rows since Peak']])

执行后输出：

Value  Rows since Peak
0      1                0
1      3                0
2      1                1
3      2                2
4      1                3
5      4                0
6      6                0
7      5                1

2. SQL 实现（MySQL 8.0+） #

WITH ranked_data AS (
    SELECT 
        Value,
        -- 计算截至当前行的累计最大值
        MAX(Value) OVER (ORDER BY ROW_NUMBER() OVER ()) AS running_max,
        -- 标记是否为新峰值
        CASE 
            WHEN Value > LAG(MAX(Value) OVER (ORDER BY ROW_NUMBER() OVER ())) OVER () THEN 1
            ELSE 0
        END AS is_peak
    FROM your_table
),
peak_groups AS (
    SELECT 
        Value,
        -- 累计峰值数量作为分组ID
        SUM(is_peak) OVER (ORDER BY ROW_NUMBER() OVER ()) AS group_id
    FROM ranked_data
)
SELECT 
    Value,
    -- 组内行数偏移（从0开始）
    ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY ROW_NUMBER() OVER ()) - 1 AS `Rows since Peak`
FROM peak_groups;

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