Filter rejecting record with sql
We have a dataset such that we need to get the average of a column. a select avg(x) from y
does the trick. However, we need a more accurate figure.
I realized that there must be a way to filter records with too high or too low values (spikes) so that we can exclude them when calculating the average.
source to share
There are three types of averages, and initially you use an average - the sum of all values divided by the number of values.
You may find it more useful to get the mode - the most common value:
select name,
(select top 1 h.run_duration
from sysjobhistory h
where h.step_id = 0
and h.job_id = j.job_id
group by h.run_duration
order by count(*) desc) run_duration
from sysjobs j
If you want to get rid of any values outside the original standard deviation, you can find the mean and standard deviation in a subquery, eliminate those outside the range: mean + - standard deviation, then do the mean of the remaining values, but you start the risk have meaningless meanings:
select oh.job_id, avg(oh.run_duration) from sysjobhistory oh
inner join (select job_id, avg(h.run_duration) avgduration,
stdev(h.run_duration) stdev_duration
from sysjobhistory h
group by job_id) as m on m.job_id = oh.job_id
where oh.step_id = 0
and abs(oh.run_duration - m.avgduration) < m.stdev_duration
group by oh.job_id
source to share