sql - 另一个表中存在的记录的计算比率

Question

您能否在下面的SQLite语句中为我提供unknownComputation的帮助？

select t1.attr, unknownComputation as ratio
from t1
group by t1.attr
order by ratio

unknownComputation应该为 (count(t1.attr) for which there exists t2.url = t1.link) / count(t1.attr)

并非t1中的所有URL都在t2中，我想找到这些比率并将其按t1分组。

Answer 1

利用您提供的信息，也许应该这样做：

select t1.attr, count(t2.url) / count(t1.attr) as ratio
from t1
left join t2 on t2.url = t1.link
group by t1.attr
order by ratio

但是，这考虑到一个t1可以有0个或多个t2