条件计数:使用 SUM() 与 COUNT() 的性能差异? [英] Conditional counting: Performance differences in using SUM() vs COUNT()?

问题描述

举一个非常简单的例子，假设我有一个表 test 和这样的示例数据:

Just as a very simple example, let's say I have table test with sample data like so:

a     |     b      
-------------
1     |    18
1     |    24
1     |    64
1     |    82
1     |    10
1     |     7
2     |     5
2     |    18
2     |    66
2     |    72
3     |    81
3     |    97

对于每个 a，我要计算有多少 b 是 <50. 结果如下:

And for each a, I'm to get the count of how many b's there are that are < 50. The result would look like:

a     |   bcnt
--------------
1     |      4
2     |      2
3     |      0

现在我可以通过以下两种方式中的任意一种来实现这一结果:

Now I could achieve this result in either of two ways:

SELECT a, COUNT(CASE WHEN b < 50 THEN 1 ELSE NULL END) AS bcnt
FROM test
GROUP BY a

或者:

SELECT a, SUM(CASE WHEN b < 50 THEN 1 ELSE 0 END) AS bcnt
FROM test
GROUP BY a

我知道这似乎是一件微不足道的小事，但我的问题是在以下方面使用一种方法是否比另一种方法有任何优势(尽管如此微不足道):性能?...他们还有多少其他 DBMS会起作用吗?...声明的清晰度?...等

I know this may seem like such an insignificant trivial matter, but my question is would there be any advantage (however so slight) in using one approach over the other in terms of: Performance?... How many other DBMSs they would work in?... Clarity of statement?... etc.

推荐答案

性能?

哦，如果有的话，差异很小，我敢肯定.我没什么可担心的.

Oh, the difference, if any, would be marginal, I'm sure. It would be nothing for me to worry about.

他们会在多少其他 DBMS 中工作?

How many other DBMSs they would work in?

我毫不怀疑这两者至少可以在任何主要 SQL 产品中工作，因此，同样，这不是一个问题，反正对我来说不是.

I've no doubt both would work in any major SQL product at least, so, again, this wouldn't be a matter of concern, not to me anyway.

陈述是否清晰?

当然，COUNT 更清楚地表达了您想要计数 的事情，而不是加起来 一些任意值.使用SUM，只有在浏览条件后到达THEN 1部分，您才会意识到实际意图.

Certainly COUNT expresses it clearer that you want to count things, not to add up some arbitrary values. With SUM, you would realise the actual intention only upon reaching the THEN 1 part after skimming through the condition.

此外，如果我使用 COUNT 我可以省略 ELSE NULL 部分，因为当 ELSE 不存在时，这就是隐含的内容.如果我在 SUM 表达式中省略 ELSE 0，我可能会得到 NULL 结果而不是可能预期的 0代码>.

Also, if I use COUNT I can omit the ELSE NULL part, because that's what is implied when ELSE is absent. If I omit ELSE 0 in the SUM expression, I may end up with a NULL result instead of the probably expected 0.

另一方面，可能在完全相反的情况下，返回 NULL 而不是 0 会更方便的计数.所以，如果我使用 COUNT，我将不得不做类似 NULLIF(COUNT(CASE ...), 0) 的事情，而使用 SUM(CASE ...) 省略 ELSE 子句就足够了.但即使在那种情况下，我可能仍然更喜欢更长的清晰度而不是稍微模糊的简洁(其他条件相同).

On the other hand, there may be quite opposite situations where it would be more convenient to return NULL instead of 0 as a result of counting. So, if I used COUNT, I would have to do something like NULLIF(COUNT(CASE ...), 0), while with SUM(CASE ...) it would be just enough to leave out the ELSE clause. But even in that case I might still prefer the somewhat longer clarity to the slightly more obscure brevity (other things being equal).

这篇关于条件计数:使用 SUM() 与 COUNT() 的性能差异?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！