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

问题描述

作为一个非常简单的例子，假设我有一个表格 test ，样本数据如下：

  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 m来获得有多少 b 的计数， 50.结果将如下所示：

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

现在我可以通过以下两种方法之一实现这个结果：

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的工作方式？...语句的清晰度...等。

解决方案

blockquote>

性能？

哦，差别，如果有的话， 。

还有多少其他DBMS？

我毫不怀疑这两种方法至少可以在任何主要的 SQL产品中工作，因此，这不会是一个关注的问题

$ b

$ b $ b

当然 COUNT 表示您想要计数，而不是添加值。使用 SUM ，只有在跳过条件后到达 THEN 1 部分，才会意识到实际意图。另外，如果我使用 COUNT ，我可以省略 ELSE NULL 部分，因为这是当 ELSE 不存在时的含义。如果在 SUM 表达式中省略 ELSE 0 ，我可能会得到 NULL 。

另一方面， > 可能会在相反的情况下更方便的返回 NULL 而不是 0 计数结果。所以，如果我使用 COUNT ，我会做一些像 NULLIF（COUNT（CASE ...），0），而使用 SUM（CASE ...）就足以省略 ELSE 子句。但即使在这种情况下，我可能仍然更喜欢稍微更加模糊的简洁（其他事情是平等的）。

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

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

Or:

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

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.

解决方案

Performance?

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

How many other DBMSs they would work in?

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.

Clarity of statement?

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.

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.

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屋！