您可以通过使用 'PA' . 我推荐 countif() :

select i.*, row_number() over (partition by grp order by date) as ranking
from (select i.*,
             countif(type = 'PA') over (order by date) as grp
      from input i
     ) i;

如果你已经有了 rank 作为一列，还可以减去 'PA' . 那就是：

select t.*,
       (ranking + 1 -
        coalesce(max(case when type = 'PA' then ranking end) over (order by ranking), 1)
       ) as new_ranking
from input

您可以使用pa创建一个虚拟组。因此，每当新pa行出现时，组索引将增加1。我把那列命名为reset\u group。
然后，我将此列用作分区列，并使用row\ u number（）计算组内的排名。

WITH
data AS
(
  SELECT 'PA' as Type, '2000-01-01' as DATE UNION ALL
  SELECT 'ON' as Type, '2000-01-02' as DATE UNION ALL
  SELECT 'Acq' as Type, '2000-01-04' as DATE UNION ALL
  SELECT 'PA' as Type, '2000-02-01' as DATE UNION ALL
  SELECT 'ON' as Type, '2000-03-02' as DATE UNION ALL
  SELECT 'ON' as Type, '2000-06-03' as DATE UNION ALL
  SELECT 'PA' as Type, '2000-06-11' as DATE
),
reset_groups AS
(
  select *, SUM(IF(Type = 'PA', 1, 0)) OVER (ORDER BY Date) as reset_group
  from data
)
select *, ROW_NUMBER() OVER (PARTITION BY reset_group ORDER BY Date) as rank
from reset_groups