如何使用任何 Oracle 函数在 Oracle 中选择计数占总数的百分比？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6613156/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select count as a percentage over the total in Oracle using any Oracle function?
提问by Oh Chin Boon
I have an SQL statement that counts over the total number of rows active packages whose end date is null. I am currently doing this using (x/y) * 100:
我有一个 SQL 语句，它计算结束日期为空的活动包的总行数。我目前正在使用(x/y) * 100：
SELECT (SELECT COUNT(*)
FROM packages
WHERE end_dt IS NULL) / (SELECT COUNT(*)
FROM packages) * 100
FROM DUAL;
I wonder if there is a way to make use of any Oracle function to express this more easily?
我想知道是否有一种方法可以使用任何 Oracle 函数来更轻松地表达这一点？
回答by OMG Ponies
There's no functionality I'm aware of, but you could simply the query to be:
我不知道有什么功能，但您可以简单地将查询设为：
SELECT SUM(CASE WHEN p.end_dt IS NULL THEN 1 ELSE 0 END) / COUNT(*) * 100
FROM PACKAGES p
回答by Andriy M
So, basically the formula is
所以，基本上公式是
COUNT(NULL-valued "end_dt") / COUNT(*) * 100
Now, COUNT(NULL-valued "end_dt")is syntactically wrong, but it can be represented as COUNT(*) - COUNT(end_dt). So, the formula can be like this:
现在，COUNT(NULL-valued "end_dt")在语法上是错误的，但它可以表示为COUNT(*) - COUNT(end_dt). 所以，公式可以是这样的：
(COUNT(*) - COUNT(end_dt)) / COUNT(*) * 100
If we just simplify it a little, we'll get this:
如果我们稍微简化一下，我们会得到这个：
SELECT (1 - COUNT(end_dt) * 1.0 / COUNT(*)) * 100 AS Percent
FROM packages
The * 1.0bit converts the integer result of COUNTto a non-integer value so make the division non-integer too.
该* 1.0位将 的整数结果转换COUNT为非整数值，因此也将除法设为非整数。
The above sentence and the corresponding part of the script turned out to be complete rubbish. Unlike some other database servers, Oracle does not perform integer division, even if both operands are integers. This doc pagecontains no hint of such behaviour of the division operator.
上面的句子和脚本的相应部分结果完全是垃圾。与其他一些数据库服务器不同，即使两个操作数都是整数，Oracle 也不执行整数除法。此文档页面不包含除法运算符的此类行为的提示。
回答by RooZA
The original post is a little long in the tooth but this should work, using the function "ratio_to_report" that's been available since Oracle 8i:
原来的帖子有点长，但这应该可以工作，使用自 Oracle 8i 以来可用的函数“ ratio_to_report”：
SELECT
NVL2(END_DT, 'NOT NULL', 'NULL') END_DT,
RATIO_TO_REPORT(COUNT(*)) OVER () AS PCT_TOTAL
FROM
PACKAGES
GROUP BY
NVL2(END_DT, 'NOT NULL', 'NULL');
