SELECT last_name, 
       year, 
       increment, 
       SUM(CASE WHEN increment < 0 THEN 1 ELSE 0 END) OVER (PARTITION BY last_name ORDER BY year) AS partition
FROM your_table
ORDER BY last_name, year;

看起来你想把连续的正/负值分组在一起，一个选项是使用两个row_number函数之间的差，这将使分区具有无序的组编号。

select *,
  row_number() over (partition by last_name order by year) -
  row_number() over (partition by last_name,
    case when increment>=0 then 1 else 2 end order by year) as prt
from tbl 
order by last_name, year

如果希望分区按顺序排列（1、2、3...），可以尝试使用滞后和运行求和的另一种方法，如下所示：

select last_name, year, increment,
  1 + sum(case when sign(increment) <> sign(pre_inc) then 1 else 0 end) over
  (partition by last_name order by year) as prt
from
(
  select *,
    lag(increment, 1 , increment) over
    (partition by last_name order by year) pre_inc
  from tbl
) t
order by last_name, year

See demo

如果增量列在列年内没有增加，则标记为1;否则，它将被标记为0。然后，我们使用“LAG“对连续的数据进行分组，而不管增量是正的还是负的。

with cte as (
  select * ,
  row_number() over (partition by last_name order by year) as row_num,
  case when increment >= LAG(increment,1,0) over (partition by last_name order by year) 
  then 1 else 0 end rank_num
  from mytable
),
cte2  as (
  select *, LAG(rank_num,1,1) over (partition by last_name order by year) as pre 
  from cte
  order by year
)
select last_name, year, increment, 1+sum(case when pre <> rank_num then 1 else 0 end) over
    (partition by last_name order by year) as partition
from cte2;