Conditional counting: Performance differences in u

问题:

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.

回答1:

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).

回答2:

Personally, I would use

select a, count(b)
  from test
 where b < 50
 group by a

Clear, concise and according to this SQL fiddle a tiny bit quicker than the others (needs less data according to the execution plan, though with a table that small you won't notice a difference):

回答3:

Whats wrong with a where clause:

select a, count(b)
from test
where b < 50
group by a

回答4:

With COUNT, you count the elements, using SUM you add numbers (positive, negative or zero) for a result that can be negative.