请参阅下面的查询，我从直接添加中获得声誉值，而无需函数。
SELECT
"User"."id",
"User"."name",
"User"."title",
"User"."about",
"User"."location",
"User"."isModerator",
"User"."createdAt",
(
-- question vote
COALESCE(SUM("PostVote"."value" * 10) FILTER (WHERE "value" = 1 AND "Post"."questionId" IS NULL), 0)::int
+
COALESCE(SUM("PostVote"."value" * 2) FILTER (WHERE "value" = -1 AND "Post"."questionId" IS NULL), 0)::int
+
-- answer vote
COALESCE(SUM("PostVote"."value" * 10) FILTER (WHERE "value" = 1 AND "Post"."questionId" IS NOT NULL), 0)::int
+
COALESCE(SUM("PostVote"."value" * 2) FILTER (WHERE "value" = -1 AND "Post"."questionId" IS NOT NULL), 0)::int
+
-- accepted answer
(COALESCE(COUNT("answer"."id"), 0)::int * 15)
) as reputation
FROM "User"
LEFT JOIN "Post" ON "Post"."authorId" = "User"."id"
LEFT JOIN "PostVote" ON "PostVote"."postId" = "Post"."id"
LEFT JOIN "Post" as answer ON "Post"."acceptedAnswerId" = "answer"."id"
GROUP BY "User"."id"
ORDER BY reputation DESC, id字符串
我这样做是因为SUM不能嵌套，我最初想做这样的事情。
-- ...
SUM(
-- question vote
COALESCE(SUM("PostVote"."value" * 10) FILTER (WHERE "value" = 1 AND "Post"."questionId" IS NULL), 0)::int
+
COALESCE(SUM("PostVote"."value" * 2) FILTER (WHERE "value" = -1 AND "Post"."questionId" IS NULL), 0)::int
+
-- answer vote
COALESCE(SUM("PostVote"."value" * 10) FILTER (WHERE "value" = 1 AND "Post"."questionId" IS NOT NULL), 0)::int
+
COALESCE(SUM("PostVote"."value" * 2) FILTER (WHERE "value" = -1 AND "Post"."questionId" IS NOT NULL), 0)::int
+
-- accepted answer
(COALESCE(COUNT("answer"."id"), 0)::int * 15)
) as reputation
-- ...型
我很好奇是否有任何可能导致不正确值的性能影响或求和？我已经用负值进行了测试，它工作正常，也许我错过了一些东西。
我本可以通过嵌套查询来使用SUM函数，但我认为这样做太冗长了。
2条答案
按热度按时间5rgfhyps1#
我可以通过嵌套查询来使用SUM函数.
不，你不可能有。你忽略了聚合函数（
sum()）和普通运算符（+）之间的根本区别。ruakh的答案已经详细说明了。但是既然你表达了对性能影响的关注：你可以优化
count()的使用，它 * 从不 * 返回null。请参阅：假设引用完整性，实际上不需要连接到接受的答案。只需计算
p."acceptedAnswerId"中的非空值。（我不相信你一开始就正确地计算了“接受”，但我坚持你的逻辑。）实际上，你可以用更快的
count()替换所有sum()的示例。因此，你根本不需要COALESCE：字符串
umuewwlo2#
不同之处在于SUM是一个聚合函数-它的参数是一个表达式，对每个相关行进行计算，并给出所有结果的总和（如果没有结果，则为NULL）-而
+是一个普通的二元运算符：它出现在一个表达式中，它的操作数是两个子表达式，对于任何给定的行，它给出两个子表达式的和。此外，它们处理NULL的方式也有点不同;
+在任何一个子表达式给出NULL时给出NULL，而SUM跳过NULL（只有在接收到 no 非NULL值时才返回NULL）。我认为讨论相对性能没有意义，因为它们的使用方式非常不同，所以使用一个查询与使用另一个查询没有区别。