T-SQL для нормализованного среднего

Я ищу способ вычисления полезного среднего для заданного набора значений, который может содержать огромные всплески. (например, 21, 54, 34, 14, 20, 300, 23 или 1, 1, 1, 1, 200, 1, 100) шипы могут отбрасывать вещи при использовании стандартного среднего расчета.

Я изучил использование медианы, но это не дает желаемого результата.

Я хотел бы реализовать это в T-SQL

Есть идеи?

Таким образом, вы можете отнять самый высокий и самый низкий 25%, прежде чем вычислять результат.

declare @t table (col1 int) insert @t select 21 union all select 54 union all select 34 union all select 14 union all select 20 union all select 300 union all select 23 union all select 1 union all select 1 union all select 1 union all select 1 union all select 200 union all select 1 union all select 100 select avg(col1) from ( select top 67 percent col1 from ( select top 75 percent col1 from @t order by col1 ) a order by col1 desc) b 

Использовать срединный фильтр:

 SELECT AVG(value) FROM ( SELECT TOP 1 value AS median FROM ( SELECT TOP 50 PERCENT value FROM mytable ORDER BY value ) q ORDER BY value DESC ) q JOIN mytable m ON ABS(LOG10(value) - LOG10(median)) <= @filter_level 
  1. Создайте GROUP BY по логарифмическому правилу (например, разница между числом не превышает 10 раз или любой другой базой журнала)
  2. Создайте фильтрацию (используя HAVING ) нерепрезентативными группами (например, менее 3)

Опасность в этом заключается в том, что вы не можете быть уверены в том, что все эти шипы незначительны и отбрасываются. Шум одного человека – черный лебедь другого человека.

Если вы беспокоитесь о больших значениях, искажающих ваше мнение о данных, вам будет лучше использовать меру, такую ​​как медиана, которая менее чувствительна к выбросам. Это сложнее рассчитать, чем означать, но это даст вам определенную центральность, которая не пошатнулась шипами.

Вы можете использовать функцию окна, такую ​​как OVER / PARTITION BY. Это позволит вам точно настраивать исключения внутри определенных групп строк (например, по имени, дате или часу). В этом примере я беру строки из примера t-clausen.dk и расширяю, добавляя имя, чтобы мы могли продемонстрировать окно.

 - Установить границы, например, TOP PERCENT, используемые в вышеупомянутом примере
 DECLARE @UBOUND FLOAT, @LBOUND FLOAT 

SET @UBOUND = 0,8 – (80%)
SET @LBOUND = 0,2 – (20%)

– Создать таблицу CTE
; С tb_example AS (
выберите [Val] = 21, [fname] = 'Bill' union all
выберите 54, 'Tom' union all
выберите 34, 'Tom' union all
выберите 14, «Bill» union all
выберите 20, «Bill» union all
выберите 300, «Tom» union all
выберите 23, «Bill» union all
выберите 1, 'Tom' union all
выберите 1, 'Tom' union all
выберите 1, «Bill» union all
выберите 1, 'Tom' union all
выберите 200, «Bill» union all
выберите 1, 'Tom' union all
выберите 12, 'Tom' union all
выберите 8, 'Tom' union all
выберите 11, «Bill» union all
выберите 100, «Билл»
)

– Внешний запрос применяет критерии по вашему выбору для удаления шипов
SELECT fname, AVG (Val) FROM (
– Внутренний запрос применяет агрегированные значения окна для внешней обработки запросов
ВЫБРАТЬ *
, ROW_NUMBER () OVER (PARTITION by fname order by Val) RowNum
, COUNT (*) OVER (PARTITION by fname) RowCnt
, MAX (Val) OVER (PARTITION by fname) MaxVal
, MIN (Val) OVER (PARTITION by fname) MinVal
FROM tb_example
) ТБ
ГДЕ
– Вы можете использовать границы для исключения верхних и нижних 20%
RowNum BETWEEN (RowCnt * @ LBOUND) и (RowCnt * @ UBOUND) – окно с ограничениями
– Или вы можете просто исключить значения Max и MIN
ИЛИ (Val> MinVal AND Val <MaxVal) – удаляет самые низкие и самые высокие значения
GROUP BY fname

В этом случае я использую оба критерия и AVG val по имени fname. Но небо – это предел того, как вы решили смягчить всплески этой техникой.

Interesting Posts
Давайте будем гением компьютера.