oracle 从 SQL 表中找到前 5 个 MAX() 值，然后在没有它们的情况下对该表执行 AVG()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6483586/
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
Find the top 5 MAX() values from an SQL table and then performi an AVG() on that table without them
提问by Arkadi Y.
I want to be able to perform an avg() on a column afterremoving the 5 highest values in it and see that the stddev is not above a certain number. This has to be done entirely as a PL/SQL query.
我希望能够在删除列中的 5 个最高值后对列执行 avg()并看到 stddev 不超过某个数字。这必须完全作为 PL/SQL 查询来完成。
EDIT: To clarify, I have a data set that contains values in a certain range and tracks latency. I want to know whether the AVG() of those values is due to a general rise in latency, or due to a few values with a very high stddev. I.e - (1, 2, 1, 3, 12311) as opposed to (122, 124, 111, 212). I also need to achieve this via an SQL query due to our monitoring software's limitations.
编辑：澄清一下，我有一个包含特定范围内的值并跟踪延迟的数据集。我想知道这些值的 AVG() 是由于延迟的普遍上升，还是由于一些具有非常高的 stddev 的值。即 - (1, 2, 1, 3, 12311) 与 (122, 124, 111, 212) 相对。由于我们的监控软件的限制，我还需要通过 SQL 查询来实现这一点。
回答by Andomar
You can use row_numberto find the top 5 values, and filter them out in a whereclause:
您可以使用row_number查找前 5 个值，并在where子句中将它们过滤掉：
select avg(col1)
from (
select row_number() over (order by col1 desc) as rn
, *
from YourTable
) as SubQueryAlias
where rn > 5
回答by Souvik Saha
select column_name1 from
(
select column_name1 from table_name order by nvl(column_name,0) desc
)a
where rownum<6
(the nvl is done to omit the nullvalue if there is/are any in the column column_name)
（null如果列中有/有任何值，则执行 nvl 以省略该值column_name）
回答by Peter Alexander
Well, the most efficient way to do it would be to calculate (sum(all values) - sum(top 5 values)) / (row_count - 5)
那么，最有效的方法是计算 (sum(all values) - sum(top 5 values)) / (row_count - 5)
SELECT SUM(val) AS top5sum FROM table ORDER BY val DESC LIMIT 5
SELECT SUM(val) AS allsum FROM table
SELECT (COUNT(*) - 5) AS bottomCount FROM table
The average is then (allsum - top5sum) / bottomCount
那么平均值是 (allsum - top5sum) / bottomCount
回答by Martin Booth
First, get the MAX 5 values:
首先，获取 MAX 5 值：
SELECT TOP 5 RowId FROM Table ORDER BY Column
Now use this in your main statement:
现在在你的主语句中使用它：
SELECT AVG(Column) FROM Table WHERE RowId NOT IN (SELECT TOP 5 RowId FROM Table ORDER BY Column)
