使用SQLite将组中的行相乘

admin

使用SQLite将组中的行相乘

sql

我有一个查询，该查询返回令牌具有特定分类的概率。

token       class       probPaired
----------  ----------  ----------
potato      A           0.5
potato      B           0.5
potato      C           1.0
potato      D           0.5
time        A           0.5
time        B           1.0
time        C           0.5

我需要class通过将它们相乘来汇总它们的概率。

-- Imaginary MUL operator
select class, MUL(probPaired) from myTable group by class;

class       probability
----------  ----------
A           0.25
B           0.5
C           0.5
D           0.5

如何在SQLite中做到这一点？SQLite没有像LOG/EXP或变量这样的功能-其他问题中提到的解决方案。

阅读 164

2021-07-01

共1个答案

admin

您可以计算行号，然后使用递归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

SQL Fiddle

2021-07-01