L'élimination des valeurs aberrantes par l'écart type dans SQL Server

Je suis d'essayer d'éliminer les valeurs aberrantes dans SQL Server 2008 par l'écart-type. Je voudrais seulement les enregistrements qui contiennent une valeur dans une colonne spécifique à l'intérieur de +/- 1 écart-type de la colonne moyenne.

Comment puis-je y arriver?

OriginalL'auteur David Pfeffer | 2010-06-19

  1. 16

    Si vous êtes en supposant une distribution en cloche des événements, alors seulement 68% des valeurs au sein de 1 écart-type l'écart à la moyenne (95% sont couverts par 2 écarts-types).

    Je voudrais charger une variable avec l'écart-type de votre gamme (obtenues à l'aide des ecartype /ecartypep de la fonction sql), puis sélectionnez les valeurs dans le nombre d'écarts-types.

    declare @stdtest table (colname varchar(20), colvalue int)
insert into @stdtest (colname, colvalue) values ('a', 2)
insert into @stdtest (colname, colvalue) values ('b', 4)
insert into @stdtest (colname, colvalue) values ('c', 4)
insert into @stdtest (colname, colvalue) values ('d', 4)
insert into @stdtest (colname, colvalue) values ('e', 5)
insert into @stdtest (colname, colvalue) values ('f', 5)
insert into @stdtest (colname, colvalue) values ('g', 7)
insert into @stdtest (colname, colvalue) values ('h', 9)
declare @std decimal
declare @mean decimal
declare @lower decimal
declare @higher decimal
declare @noofstds int
select @std = STDEV(colvalue), @mean = AVG(colvalue) from @stdtest
--68%
set @noofstds = 1
select @lower = @mean - (@noofstds * @std)
select @higher = @mean + (@noofstds * @std)
select @lower, @higher, * from @stdtest where colvalue between @lower and @higher
--returns rows with a colvalue between 3 and 7 inclusive
--95%
set @noofstds = 2
select @lower = @mean - (@noofstds * @std)
select @higher = @mean + (@noofstds * @std)
select @lower, @higher, * from @stdtest where colvalue between @lower and @higher
--returns rows with a colvalue between 1 and 9 inclusive
    99% sont couverts par plus ou moins de trois écarts-types, mais c'est valable uniquement si votre population est vraiment représentée par une distribution normale. Les valeurs en dehors de la période de six sigma gamme sont présumés rares, mais dans la vraie vie, ce n'est souvent pas le cas.
    Je suis d'accord et je suis d'accord avec vos commentaires ci-dessous (+1), et c'est pourquoi j'ai été prudent de mentionner une "courbe en cloche de la distribution des événements" - une distribution normale. Si la distribution est asymétrique alors évidemment, cela change les choses.
    Mais la distribution normale est toujours une idéalisation, parce que vous ne pouvez jamais savoir quand un cygne noir aberrantes est à venir le long d'à côté.
    il y a assez de moutons noirs sur sans des cygnes noirs se joindre à eux!
    Dites-le à Taleb: fooledbyrandomness.com

    OriginalL'auteur amelvin

  2. 4

    Il y a une fonction d'agrégation appelé la fonction ECARTYPE dans SQL qui va vous donner l'écart-type. C'est la partie la plus difficile - puis il suffit de connaître la distance entre la moyenne et de +/- un ECARTYPE valeur.

    De cette manière, vous pouvez aller sur le faire -

        create table #test
(
testNumber int
)
INSERT INTO #test (testNumber)
SELECT  2
UNION ALL 
SELECT 4
UNION ALL 
SELECT 4
UNION ALL 
SELECT 4
UNION ALL 
SELECT 5
UNION ALL 
SELECT 5
UNION ALL 
SELECT 7
UNION ALL 
SELECT 9
SELECT testNumber FROM #test t
JOIN (
SELECT STDEV (testnumber) as [STDEV], AVG(testnumber) as mean
FROM #test
) X on t.testNumber >= X.mean - X.STDEV AND t.testNumber <= X.mean + X.STDEV
    Notez qu'au-dessus de la fonction ECARTYPE est un peu plus de deux et la requête renvoie les valeurs 4, 4, 4, 5, 5, 7

    OriginalL'auteur Mike M.

  3. 0

    Je serais prudent et réfléchissez à ce que vous êtes en train de faire. Jeter les valeurs aberrantes peut dire que vous êtes jeter de l'information qui ne peut pas s'en pré-conçue de vue sur le monde qui pourrait être tout à fait tort. Ces valeurs aberrantes peut-être "cygnes noirs" qui sont rares, mais pas aussi rares que vous le croyez, et tout à fait significatif.

    Vous donner aucun contexte ni explication de ce que vous faites. Il est facile de citer une fonction ou une technique qui leur permettront de répondre aux besoins de votre cas particulier, mais j'ai pensé qu'il était approprié de poster la prudence jusqu'à ce que des renseignements supplémentaires sont fournis.

    Il en va de même pour tout type de tâche d'analyse. Même si j'apprécie vos idées (et après avoir lu Taleb du livre 😉 ), ce que vous avez posté n'est pas une réponse à la question...
    Voyant que la réponse a été donnée il y a deux ans, et que vous avez offert absolument rien sur cette question et peu sur autre chose, je voudrais vous demander si errer de la SORTE et en laissant trollish commentaires est une utilisation constructive de votre temps.

    OriginalL'auteur duffymo