问如何在Google-Bigquery中添加基于条件和条件重置的自动递增计数器

EN

select t.*,
       (case when row_number() over (partition by grp order by month, week) = 1
             then 'Good'
             else concat('Stage ', row_number() over (partition by grp order by month, week) - 1)
        end) as level
from (select t.*,
             countif(score > 0.95) over (order by month, week) as grp
      from t
     ) t;

select * except(grp),
  (case when Average_score >= 95 and 1 = row_number() over grps then 'Good'
    else format('Stage %i', row_number() over grps - sign(grp))
   end) as Level
from (
  select *, countif(Average_score >= 95) over (order by Month, Week) as grp
  from `project.dataset.table`
)
window grps as (partition by grp order by Month, Week)