在同一个 SELECT sql 查询中从 SUM() 计算百分比 [英] Compute percents from SUM() in the same SELECT sql query

问题描述

在表 my_obj 中有两个整数字段:

In the table my_obj there are two integer fields:

(value_a integer, value_b integer);

我尝试计算多少次value_a = value_b，我想用百分比表示这个比率.这是我试过的代码:

I try to compute how many time value_a = value_b, and I want to express this ratio in percents. This is the code I have tried:

select sum(case when o.value_a = o.value_b then 1 else 0 end) as nb_ok,
       sum(case when o.value_a != o.value_b then 1 else 0 end) as nb_not_ok,
       compute_percent(nb_ok,nb_not_ok)
from  my_obj as o
group by o.property_name;

compute_percent 是一个只执行 (a * 100)/(a + b)

但是 PostgreSQL 抱怨列 nb_ok 不存在.
你会如何正确地做到这一点?

But PostgreSQL complains that the column nb_ok doesn't exist.
How would you do that properly ?

我在 Ubuntu 12.04 上使用 PostgreSQL 9.1.

I use PostgreSQL 9.1 with Ubuntu 12.04.

推荐答案

这个问题比看起来的要多.

There is more to this question than it may seem.

这更快、更简单:

SELECT property_name
      ,(count(value_a = value_b OR NULL) * 100) / count(*) AS pct
FROM   my_obj
GROUP  BY 1;

结果:

property_name | pct
--------------+----
 prop_1       | 17
 prop_2       | 43

怎么样?

• 您根本不需要此功能.

• You don't need a function for this at all.

不要计算 value_b(您不需要开始)并计算总数，而是使用 count(*) 作为总数.更快、更简单.

Instead of counting value_b (which you don't need to begin with) and calculating the total, use count(*) for the total. Faster, simpler.

这假设您没有 NULL 值.IE.两列都定义为 NOT NULL.您的问题中缺少信息.
如果不是，则您的原始查询可能没有按照您的想法行事.如果任何值为 NULL，则您的版本根本不计算该行.您甚至可以通过这种方式引发被零除异常.
此版本也适用于 NULL.count(*) 生成所有行的计数，而不考虑值.

This assumes you don't have NULL values. I.e. both columns are defined NOT NULL. The information is missing in your question.
If not, your original query is probably not doing what you think it does. If any of the values is NULL, your version does not count that row at all. You could even provoke a division-by-zero exception this way.
This version works with NULL, too. count(*) produces the count of all rows, regardless of values.

计数的工作原理如下:

 TRUE  OR NULL = TRUE
 FALSE OR NULL = NULL

count() 忽略 NULL 值.瞧.

count() ignores NULL values. Voilá.

运算符优先级 控制 = 在 OR 之前绑定.您可以添加括号以使其更清楚:

Operator precedence governs that = binds before OR. You could add parentheses to make it clearer:

count ((value_a = value_b) OR FALSE)

你可以用

You can do the same with

count NULLIF(<expression>, FALSE)

count() 的结果类型默认为 bigint.
除法bigint/bigint，截断小数位.

The result type of count() is bigint by default.
A division bigint / bigint, truncates fractional digits.

使用100.0(带小数位数)强制计算为数字，从而保留小数位数.
您可能想要使用 round() 用这个:

Use 100.0 (with fractional digit) to force the calculation to be numeric and thereby preserve fractional digits.
You may want to use round() with this:

SELECT property_name
      ,round((count(value_a = value_b OR NULL) * 100.0) / count(*), 2) AS pct
FROM   my_obj
GROUP  BY 1;

结果:

property_name | pct
--------------+-------
 prop_1       | 17.23
 prop_2       | 43.09

顺便说一句:
我使用 value_a 而不是 valueA.不要在 PostgreSQL 中使用不带引号的大小写混合标识符.我已经看到太多绝望的问题来自这种愚蠢的行为.如果您想知道我在说什么，请阅读 手册中的标识符和关键字.

As an aside:
I use value_a instead of valueA. Don't use unquoted mixed-case identifiers in PostgreSQL. I have seen too many desperate question coming from this folly. If you wonder what I am talking about, read the chapter Identifiers and Key Words in the manual.

这篇关于在同一个 SELECT sql 查询中从 SUM() 计算百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！