Geek Answers Handbook

Find the top 5 values ​​of MAX () from the SQL table and then execute AVG () on this table without them

I want to be able to execute avg () in a column after deleting the 5 highest values ​​in it and see that stddev does not exceed a certain number. This needs to be done completely as a PL / SQL query.

EDIT: To clarify, I have a dataset that contains values ​​in a specific range and tracks the delay. I want to know if the AVG () of these values ​​is the result of an overall increase in latency or because of several values ​​with a very high stddev level. Ie - (1, 2, 1, 3, 12311) in contrast to (122, 124, 111, 212). I also have to achieve this with an SQL query due to the limitations of the monitoring software.

+5
source share
4 answers

You can use row_numberto find the top 5 values ​​and filter them in a sentence where:

select  avg(col1)
from    (
        select  row_number() over (order by col1 desc) as rn
        ,       *
        from    YourTable
        ) as SubQueryAlias
where   rn > 5
+9
source 
select column_name1 from 
(
  select column_name1 from table_name order by nvl(column_name,0) desc
)a 
where rownum<6

(nvl is done to omit the value nullif column_namethere is / in the column )

+2
source

Well, the most effective way to do this is to calculate (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

Average value then (allsum - top5sum) / bottomCount

0
source

First enter the MAX 5 values:

SELECT TOP 5 RowId FROM Table ORDER BY Column

Now use this in your main application:

SELECT AVG(Column) FROM Table WHERE RowId NOT IN (SELECT TOP 5 RowId FROM Table ORDER BY Column)
-1
source

More articles:


All Articles