Suppression de point aberrants

Version imprimable

07/08/2014, 15h31
ELKHECHAFI

Suppression de point aberrants

Bonjour,

je dois supprimer de ma base toute valeur supérieur à (Moyenne+5*écart type) ou inférieur à (Moyenne-5*écart type) et ceci pour toute les colonnes de la base de données.

Merci
07/08/2014, 16h07
Lyche

C'est sur que comme ça, sans DDL, sans la moindre information sur la base, on va pouvoir aider et répondre juste à 100%.

Dans le doute, un NOT BETWEEN pourrait répondre à la solution côté bornes + et -

Pour le reste, sans modèle de données, et avec si peu d'indication, s'aventurer à donner une réponse me semble compromis.

Cordialement,
Lyche

Bonjour,

il manque une question à ton post, et d'autres informations qui pourraient être importantes, mais je suppose que tu veux faire quelque chose comme ça.
Code:

1 2 3 4 5 6 7 8 9 10 WITH SRC AS ( SELECT * , Moyenne = AVG( LaColonne ) OVER ( PARTITION BY ?? ) , Deviation = STDEV( LaColonne) OVER ( PARTITION BY ?? ) FROM LaTable ) DELETE FROM SRC WHERE LaColonne > Moyenne + 5 * Deviation
Les points d'interrogation ?? dépendent de tes critères exacts. Si tu pouvais être plus précis, je pourrais peut être l'être plus. ;)

07/08/2014, 21h05
ELKHECHAFI

Rebonjour,
Alors la base de données est composée de prélèvements d'eau qui ont été effectués dans 4 stations avec 33 paramètres physico-chimiques(manganèse, Sodium, oxygène dissous,...) dans chaque prélèvement: Il s'agit d'une matrice des prélèvements en ligne et des paramètres en colonnes.
Je cherche à supprimer les points aberrants :toute valeur "NOT BETWEEN" (Moyenne(colonne)-5*écart type(colonne)) et (Moyenne(colonne)+5*écart type(colonne)) pour chaque une des 33 colonnes (manganèse, Sodium, oxygène dissous,...).
08/08/2014, 10h30
StringBuilder

Hmmmm, donc si y'a une importante pollution dans la nappe phréatique, on supprime les lignes et on ne lance pas d'alerte ? 8O

Ah ben je comprends mieux certaines choses comme un certain nuage qui n'a pas passé la douane en 1986... :weird:
08/08/2014, 10h55
ELKHECHAFI

Hmmmmm, vous avez tout compris 8-) sauf ma question bien sûr ;)
08/08/2014, 11h33
Laurent C

Questions:
- Hormis les paramêtres physico-chimiques, y a-t-il d'autres colonnes et dans ce cas, lesquelels?
- Si l'on trouve une ligne contenant une valeur aberrante, faut-il juste ignorer la valeur aberrante, ou toute la ligne?
- La table doit-elle vraiment être modifiée, ou bien faut-il juste ignorer les résultats dans la requête finale?
08/08/2014, 11h50
ELKHECHAFI

Oui il existe d'autres paramètres , des colonnes de type chaîne de caractères: nom de station , Type station,..
pour le résultat ,la table doit être modifiée par la suppression de la valeur aberrante uniquement , pas toute la ligne.
08/08/2014, 13h26
hmira

Citation:

Envoyé par ELKHECHAFI

Oui il existe d'autres paramètres , des colonnes de type chaîne de caractères: nom de station , Type station,..
pour le résultat ,la table doit être modifiée par la suppression de la valeur aberrante uniquement , pas toute la ligne.

Il faut être précis sur le vocabulaire !

SQL est une implémentation des langages formels de l'algèbre relationnelle et au calcul relationnel de tuples (lignes).

En SQL on ne supprime pas une valeur ! En SQL, on peut supprimer une ou plusieurs lignes (tuples) d'une table (relation). Pour cela on dispose de l'opérateur DELETE.
De même, en SQL, l'opérateur UPDATE permet de modifier une ou plusieurs lignes d'une table. Pour cela on dispose de l'opérateur UPDATE.

Qu'entendez-vous par "la suppression de la valeur aberrante uniquement" ?
Vous voulez mettre la valeur à NULL (c.à.d. UPDATE ) ? ou bien supprimer la ligne de la table (c.à.d DELETE) ?

A+
08/08/2014, 13h50
ELKHECHAFI

mais on peut bien modifier ou même supprimer une valeur en SQL!!!!
"UPDATE [dbo].[Report3$] SET[ph] = 0 WHERE [ph]<6.5" par exemple
"la suppression de la valeur aberrante uniquement , pas toute la ligne." 8-)
08/08/2014, 14h03
Donpi

Le scrip de Laurent C ne suffit-il pas ?
08/08/2014, 14h43
StringBuilder

Euh... Passer une valeur à 0, c'est tout sauf la suppression de la valeur !

Et vous allez à la fois introduire de nouvelles valeurs aberrantes (un PH à 0, perso, j'évite de me doucher avec) mais aussi fausser vos moyennes et écarts types.

Si vous voulez "supprimer" une valeur, alors on doit la passer à NULL !
A ce moment, elle sera naturellement écartée de toutes les fonctions analytiques.

Tiens, Celko parle français? ;)

S'il s'agit d'ignorer les valeurs aberrantes, alors cela devrait plutôt ressembler à cela.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
;WITH 
	SRC AS ( 
    SELECT * 
         , Moyenne = 
            AVG( LaColonne ) OVER ( PARTITION BY (SELECT NULL) ) 
         , Deviation = 
           STDEV( LaColonne) OVER ( PARTITION BY (SELECT NULL) ) 
		 , Moyenne2 = 
            AVG( LaColonne2 ) OVER ( PARTITION BY (SELECT NULL) ) 
         , Deviation2 = 
           STDEV( LaColonne2) OVER ( PARTITION BY (SELECT NULL) ) 
 
 
    FROM LaTable
) 
UPDATE SRC
	SET   LaColonne = CASE 
						WHEN LaColonne > Moyenne + 5 * Deviation THEN NULL
						WHEN LaColonne < Moyenne - 5 * Deviation THEN NULL
						ELSE LaColonne
					END
        , LaColonne2 = CASE 
						WHEN LaColonne2 > Moyenne2 + 5 * Deviation2 THEN NULL
						WHEN LaColonne2 < Moyenne2 - 5 * Deviation2 THEN NULL
						ELSE Lacolonne2
					END

Edit: j'avais oublié la partie ELSE dans le CASE WHEN.
Edit2: changement de LaColonne < Moyenne + 5 * Deviation en LaColonne < Moyenne - 5 * Deviation et LaColonne2 < Moyenne + 5 * Deviation en LaColonne < Moyenne - 5 * Deviation.

08/08/2014, 16h05
ELKHECHAFI

Merci Laurent pour le code

Citation:

Envoyé par Laurent C

S'il s'agit d'ignorer les valeurs aberrantes, alors cela devrait plutôt ressembler à cela.

Avez-vous au moins tester votre requête avec jeu d'essai ? Votre requête présente plusieurs anomalies !

1 - Votre requête ne compile pas !
2 - Ce n'est pas la CTE (SRC) qu'il faut "updater" mais la table (Latable)
3 - Pour pouvoir atteindre la CTE (SRC), il faut établir un lien entre la CTE (SRC) et la table (LaTable) (càd une jointure entre la CTE et la Table LaTable) !

Ci-dessous la requête revue et corrigée.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 WITH CTE_Min_Max AS ( SELECT AVG(Col1) - (5 * STDEV(col1) ) AS Min_Col1 , AVG(col1) + (5 * STDEV(Col1)) As Max_Col1 , AVG(Col2) - (5 * STDEV(col2) ) AS Min_Col2 , AVG(col2) + (5 * STDEV(Col2)) As Max_Col2 -- .... , AVG(ColN) - (5 * STDEV(ColN) ) AS Min_ColN , AVG(ColN) + (5 * STDEV(ColN)) As Max_ColN FROm MaTable ) UPDATE MaTable SET Col1 = CASE WHEN Col1 NOT BETWEEN CTE_Min_Max.Min_Col1 AND CTE_Min_Max.Max_Col1 THEN NULL ELSE Col1 END , Col2 = CASE WHEN Col2 NOT BETWEEN CTE_Min_Max.Min_Col2 AND CTE_Min_Max.Max_Col2 THEN NULL ELSE Col2 END -- .... , ColN = CASE WHEN ColN NOT BETWEEN CTE_Min_Max.Min_ColN AND CTE_Min_Max.Max_ColN THEN NULL ELSE ColN END FROM MaTable INNER JOIN CTE_Min_Max ON 1 = 1
A+

08/08/2014, 17h17
Laurent C

Citation:

Envoyé par hmira

Avez-vous au moins tester votre requête avec jeu d'essai ? Votre requête présente plusieurs anomalies !

1 - Votre requête ne compile pas !
2 - Ce n'est pas la CTE (SRC) qu'il faut "updater" mais la table (Latable)
3 - Pour pouvoir atteindre la CTE (SRC), il faut établir un lien entre la CTE (SRC) et la table (LaTable) (càd une jointure entre la CTE et la Table LaTable) !

Non, je n'ai pas fait de test sur un jeu de données.

1 - C'était vrai pour la toute première version, raison pour laquelle j'ai édité mon post.
2 - 3 - Une CTE peut être "updatée" comme on "update" une vue. Evidemment, c'est un abus de langage, et c'est bien la table qui est mise à jour. Une jointure n'est donc pas forcément requise pour un update.

Ceci étant dit, j'ai laissé une erreur de signe dans le deuxième WHEN.
09/08/2014, 11h31
hmira

Citation:

Envoyé par Laurent C

Non, je n'ai pas fait de test sur un jeu de données.

1 - C'était vrai pour la toute première version, raison pour laquelle j'ai édité mon post.
2 - 3 - Une CTE peut être "updatée" comme on "update" une vue. Evidemment, c'est un abus de langage, et c'est bien la table qui est mise à jour. Une jointure n'est donc pas forcément requise pour un update.

- La dernière version de votre script est OK
- Je n'ai jamais dis que dans l'absolu une CTE ne peut pas être updatée. Je me referais à votre script tel qu'il a été rédigé dans sa première version.
Évidemment qu'on peut mettre à jour une CTE en prenant quelques précautions particulières.

En tout cas, vous avez fait l'effort de donner une solution au problème, d'apporter une aide à la personne à l'origine de ce poste, et donc je trouve votre contribution globalement positive.

A+

Juste pour Info : Précaution à prendre dans le cas d'une modification d'une CTE.

Ce n'est pas le cas ici (le problème ne se pose dans aucun des exemples de scripts ci-dessus), mais, plus généralement, il faut être vigilant lors de la mise à jour (UPDATE) d'une CTE, plus précisément, lorsque la clause FROM de la CTE contient des jointures avec d'autres tables.

Les résultats d'une instruction UPDATE sont aléatoires et non déterministes ! si la clause FROM ne spécifie pas qu'une seule valeur doit être disponible pour chaque occurrence de colonne mise à jour. L'exemple ci-dessous illustre un cas d'UPDATE hasardeux, non déterministe !

Ne portez pas de jugement sur le modèle de données. Le modèle de données je l'ai voulu volontairement bancal. Il est là juste pour illustrer le problème.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 CREATE TABLE dbo.TVA (IdTVA int NOT NULL, DateApp date NOT NULL, -- Data application TauxTva decimal(10,3) NOT NULL); GO INSERT INTO dbo.TVA (IdTVA, DateApp, TauxTva) VALUES (1, '2013-01-01T00:00:00', 7), (1, '2014-01-01T00:00:00', 10); CREATE TABLE dbo.TARIF (IdTarif int PRIMARY KEY NOT NULL, MontantHT decimal(10,3) NOT NULL, IdTVA Int NOT NULL, MontantTTC decimal(10,3) NOT NULL -- champ calculé stocké ); GO INSERT INTO dbo.TARIF (IdTarif, MontantHT, IdTVA, MontantTTC) VALUES (65214, 500.00, 1, 0 ); GO
vérifiez le contenu des 2 tables dbo.TVA et dbo.TARIF
Code:

1 2 SELECT * FROM dbo.TVA SELECT * FROM dbo.TARIF
Résultat :
Contenu de la Table TVA :
Code:

1 2 3 IdTVA DateApp TauxTva 1 2013-01-01 7.000 1 2014-01-01 10.000
Contenu de Table dbo.TARIF (Avant Update de la CTE )
Code:

1 2 IdTarif MontantHT IdTVA MontantTTC 65214 500.000 1 0.000
Lancez l'update CTE de la CTE
Code:

1 2 3 4 5 6 7 8 ; WITH CTE AS ( SELECT TARIF.*, TVA.TauxTva FROM dbo.TARIF INNER JOIN dbo.TVA ON TARIF.IdTVA = TVA.IdTVA ) UPDATE CTE SET CTE.MontantTTC = CTE.MontantHT * (1.00 + TauxTva/100.00);
vérifiez le contenu de la table TARIF

Code:

SELECT * FROM dbo.TARIF

Résultat :
Contenu de la Table dbo.TARIF (Après Update de la CTE )
Code:

1 2 IdTarif MontantHT IdTVA MontantTTC 65214 500.000 1 535.000
Remarquez que c'est le premier Taux de TVA (7%) qui a été appliqué pour le calcul du montant TTC.

Maintenant rajouter un index non cluster sur la table dbo.TVA comme suit
Code:

1 2 3 4 5 6 7 CREATE NONCLUSTERED INDEX IX_TVA_IdTva_DatteApp ON dbo.TVA ( IdTva ASC, DateApp DESC ) ON [PRIMARY] GO
Remarquez la clause DESC (DESCENDANT) sur la colonne DateApp (Date application)

Maintenant lancez à nouveau le même Update de la CTE ci-dessus :

Puis vérifiez à nouveau le contenu de la table dbo.TARIF

Code:

SELECT * FROM dbo.TARIF

Résultat :
Table Tarif (après ajout de l'index et Update de la CTE)
Code:

1 2 IdTarif MontantHT IdTVA MontantTTC 65214 500.000 1 550.000
Remarquez que c'est fois-ci, c'est le 2ème Taux de TVA (10 %) qui a été appliqué pour le calcul du montant TTC.

Cet exemple simple illustre bien le caractère hasardeux et aléatoire qui peut survenir lors d'un UPDATE d'une CTE, aussi, le développeur doit être vigilant lors de spécification de la clause FROM.

A+

09/08/2014, 13h42
mikedavem

Effectivement à partir du moment où il n'existe pas une seule valeur pour chaque occurrence le résultat peut devenir déterministe. Je ferai peut être une remarque plus générale en disant que cela concerne toute instruction update dans ce cas.

++