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
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
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
Rejoignez la communauté du chat et partagez vos connaissances ou vos questions avec nous
Mon Tutoriel pour apprendre les Agregations
Consultez mon Blog SQL destiné aux débutants
Pensez à FAQ SQL Server Ainsi qu'aux Cours et Tuto SQL Server
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.
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.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
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,...).
Hmmmm, donc si y'a une importante pollution dans la nappe phréatique, on supprime les lignes et on ne lance pas d'alerte ?
Ah ben je comprends mieux certaines choses comme un certain nuage qui n'a pas passé la douane en 1986...
On ne jouit bien que de ce qu’on partage.
Hmmmmm, vous avez tout compris sauf ma question bien sûr
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?
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+
"Une idée mal écrite est une idée fausse !"
http://hamid-mira.blogspot.com
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."
Le scrip de Laurent C ne suffit-il pas ?
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.
On ne jouit bien que de ce qu’on partage.
Tiens, Celko parle français?
S'il s'agit d'ignorer les valeurs aberrantes, alors cela devrait plutôt ressembler à cela.
Edit: j'avais oublié la partie ELSE dans le CASE WHEN.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
Edit2: changement de LaColonne < Moyenne + 5 * Deviation en LaColonne < Moyenne - 5 * Deviation et LaColonne2 < Moyenne + 5 * Deviation en LaColonne < Moyenne - 5 * Deviation.
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 SQL : Sélectionner tout - Visualiser dans une fenêtre à part
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+
"Une idée mal écrite est une idée fausse !"
http://hamid-mira.blogspot.com
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.
- 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+
"Une idée mal écrite est une idée fausse !"
http://hamid-mira.blogspot.com
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 SQL : Sélectionner tout - Visualiser dans une fenêtre à part
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
Résultat :
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2 SELECT * FROM dbo.TVA SELECT * FROM dbo.TARIF
Contenu de la Table TVA :
Contenu de Table dbo.TARIF (Avant Update de la CTE )
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3 IdTVA DateApp TauxTva 1 2013-01-01 7.000 1 2014-01-01 10.000
Lancez l'update CTE de la CTE
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2 IdTarif MontantHT IdTVA MontantTTC 65214 500.000 1 0.000
vérifiez le contenu de la table TARIF
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
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);
Résultat :
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part SELECT * FROM dbo.TARIF
Contenu de la Table dbo.TARIF (Après Update de la CTE )
Remarquez que c'est le premier Taux de TVA (7%) qui a été appliqué pour le calcul du montant TTC.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2 IdTarif MontantHT IdTVA MontantTTC 65214 500.000 1 535.000
Maintenant rajouter un index non cluster sur la table dbo.TVA comme suit
Remarquez la clause DESC (DESCENDANT) sur la colonne DateApp (Date application)
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7 CREATE NONCLUSTERED INDEX IX_TVA_IdTva_DatteApp ON dbo.TVA ( IdTva ASC, DateApp DESC ) ON [PRIMARY] GO
Maintenant lancez à nouveau le même Update de la CTE ci-dessus :
Puis vérifiez à nouveau le contenu de la table dbo.TARIF
Résultat :
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part SELECT * FROM dbo.TARIF
Table Tarif (après ajout de l'index et Update de la CTE)
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.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2 IdTarif MontantHT IdTVA MontantTTC 65214 500.000 1 550.000
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+
"Une idée mal écrite est une idée fausse !"
http://hamid-mira.blogspot.com
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.
++
Vous avez un bloqueur de publicités installé.
Le Club Developpez.com n'affiche que des publicités IT, discrètes et non intrusives.
Afin que nous puissions continuer à vous fournir gratuitement du contenu de qualité, merci de nous soutenir en désactivant votre bloqueur de publicités sur Developpez.com.
Partager