您可以计算累积最大值，然后
row_number()在其上使用。
所以：
select t.*, row_number() over (partition by running_max order by date) as rows_since_last_maxfrom (select t.*, max(amount) over (order by date rows between 5 preceding and current row) as running_max from tbl t ) t;
我认为这适用于您的示例数据。如果您有重复项，则可能不起作用。
在这种情况下，您可以使用日期算术：
select t.*, datediff(day, max(date) over (partition by running_max order by date), date ) as days_since_most_recent_max5from (select t.*, max(amount) over (order by date rows between 5 preceding and current row) as running_max from tbl t ) t;
编辑：
这是使用行号的示例：
select t.*, (seqnum - max(case when amount = running_amount then seqnum end) over (partition by running_max order by date)) as rows_since_most_recent_max5from (select t.*, max(amount) over (order by date rows between 5 preceding and current row) as running_max, row_number() over (order by date) as seqnum from tbl t ) t;
