您可以计算行号,然后使用递归cte进行乘法。然后获取每个包含乘法最终结果的类的最大rnum(计算的row_number)值。
--Calculating row numbers
with rownums as (select t1.*,
(select count(*) from t t2 where t2.token<=t1.token and t1.class=t2.class) as rnum
from t t1)
--Getting the max rnum for each class
,max_rownums as (select class,max(rnum) as max_rnum from rownums group by class)
--Recursive cte starts here
,cte(class,rnum,probPaired,running_mul) as
(select class,rnum,probPaired,probPaired as running_mul from rownums where rnum=1
union all
select t.class,t.rnum,t.probPaired,c.running_mul*t.probPaired
from cte c
join rownums t on t.class=c.class and t.rnum=c.rnum+1)
--Final value selection
select c.class,c.running_mul
from cte c
join max_rownums m on m.max_rnum=c.rnum and m.class=c.class