使用 SQLite 将组中的行相乘 [英] Multiply rows in group with SQLite
本文介绍了使用 SQLite 将组中的行相乘的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个查询,它返回某个标记具有特定分类的概率.
I have a query that returns the probability that a token has a certain classification.
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
的概率.
I need to aggregate the probabilities of each class
by multiplying them together.
-- 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
或变量之类的功能 - 解决方案 在其他问题中提到.
How can I do this in SQLite? SQLite doesn't have features like LOG
/EXP
or variables - solutions mentioned in other questions.
推荐答案
您可以计算行数,然后使用递归 cte 进行乘法.然后获取包含乘法最终结果的每个类的最大 rnum(计算的 row_number)值.
You can calculate row numbers and then use a recursive cte for multiplication. Then get the max rnum (calculated row_number) value for each class which contains the final result of multiplication.
--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
这篇关于使用 SQLite 将组中的行相乘的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文