sql - 是否可以提高此子查询的性能？

对于我的申请，我有一个主题表和一个投票表。投票选项为-1、0和1。我试图找出每个主题被投票的次数-1和1。
为了找出答案，我做了很多子查询，每个子查询都使用find total number of -1 or 1 votes and then divide by the total number of votes算法。
不幸的是，我想的很慢。部分原因是我计算了两次计数，但我不确定是否可以从外部查询引用它。
这个性能能提高吗？

SELECT title,
  (SELECT COUNT(vote)::float8 FROM vote WHERE topic_id = v1.topic_id AND vote_choice = -1)
/ (SELECT COUNT(vote)::float8 FROM vote WHERE topic_id = v1.topic_id) as lp,
  (SELECT COUNT(vote)::float8 FROM vote WHERE topic_id = v1.topic_id AND vote_choice = 1)
/ (SELECT COUNT(vote)::float8 FROM vote WHERE topic_id = v1.topic_id) as rp
FROM topic
JOIN vote v1 ON topic.id = v1.topic_id
GROUP BY v1.topic_id, topic.title;

最佳答案

我将使用FILTER而不是相关的子查询：

SELECT
    title,
    1.0 * COUNT(*) FILTER(WHERE vote_choice = -1) / COUNT(*) as lp,
    1.0 * COUNT(*) FILTER(WHERE vote_choice = 1)  / COUNT(*) as rp
FROM topic
JOIN vote v1 ON topic.id = v1.topic_id
GROUP BY v1.topic_id, topic.title;

关于sql - 是否可以提高此子查询的性能？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57646584/