I want to be able to perform an avg() on a column after removing 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非常高的一些值。 I.e - (1,2,1,3,12311)而不是(122,124,111,212)。由于我们的监控软件的限制,我还需要通过SQL查询来实现这一点。
4 个解决方案
#1
9
You can use row_number
to find the top 5 values, and filter them out in a where
clause:
您可以使用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
#2
2
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 null
value if there is/are any in the column column_name
)
(如果column_name列中有/,则完成nvl以省略空值)
#3
0
Well, the most efficient way to do it would be to calculate (sum(all values) - sum(top 5 values)) / (row_count - 5)
那么,最有效的方法是计算(总和(所有值) - 总和(前5个值))/(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
#4
-1
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)
#1
9
You can use row_number
to find the top 5 values, and filter them out in a where
clause:
您可以使用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
#2
2
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 null
value if there is/are any in the column column_name
)
(如果column_name列中有/,则完成nvl以省略空值)
#3
0
Well, the most efficient way to do it would be to calculate (sum(all values) - sum(top 5 values)) / (row_count - 5)
那么,最有效的方法是计算(总和(所有值) - 总和(前5个值))/(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
#4
-1
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)