#1 Fri 22 July 2022 11:53
- upriste
- Participant occasionnel
- Date d'inscription: 14 Feb 2019
- Messages: 43
QGIS 3.20.3: Requete CASE aide a la formulation
Bonjour,
Je n'obtiens pas ce que je veux avec l'opérateur CASE. Je précise que je suis novice en sql.
J'aimerais évaluer des surfaces d'une culture entre 2 dates. Sachant que sur les 2 dates, je peux avoir des données, des données nulles, des données vides ou des données à 0. Il y a 5 évolutions possibles d'une culture :
1/ création
2/ suppression
3/ progression
4/ diminution
5/ stagnation (stable)
Voilà mon essai de requête. Mais je note que ça ne fonctionne que pour les surfaces en stagnation (stable).
Pourriez-vous m'aider à réécrire cette requête ?
CASE
WHEN
"Surf18" = "Surf15" THEN 'surface stable'
WHEN
"Surf18" > "Surf15" AND "Surf15">0 OR "Surf15" <> NULL OR "Surf15" <>''
THEN 'surface en progression'
WHEN
"Surf15" > "Surf18" AND "Surf18">0 OR "Surf18" <> NULL OR "Surf18" <>''
THEN 'surface en régression'
WHEN
"Surf18" IS NULL OR "Surf18" =''
AND "Surf15" <> 0 OR "Surf15" <> NULL OR "Surf15" <>''
THEN 'surface supprimée en 2018'
WHEN
"Surf15" IS NULL OR "Surf15" =''
AND "Surf18" <> 0 OR "Surf18" <> NULL OR "Surf18" <>''
THEN 'surface créee en 2018'
END
Hors ligne
#2 Fri 22 July 2022 12:01
- Miniopterine
- Participant assidu
- Date d'inscription: 29 Nov 2005
- Messages: 278
Re: QGIS 3.20.3: Requete CASE aide a la formulation
Bonjour,
Et en rajoutant des parenthèses comme par exemple ici : "Surf18" > "Surf15" AND ("Surf15">0 OR "Surf15" <> NULL OR "Surf15" <>'')?
Hors ligne
#3 Fri 22 July 2022 12:21
- Sylvain M.
- Participant assidu
- Lieu: Saint-Pierre-des-Nids (53)
- Date d'inscription: 8 Sep 2005
- Messages: 995
Re: QGIS 3.20.3: Requete CASE aide a la formulation
Sans avoir été jusqu'au bout de la recherche, 2 conseils :
- indenter le code, pour qu'il soit plus lisible et facilement modifiable
- je crois que "<> NULL" ne marche pas, il faut remplacer par "IS NOT NULL"
Voici ce que ça donnerait avec mes 2 conseils (non testé) :
Code:
CASE WHEN "Surf18" = "Surf15" THEN 'surface stable' WHEN "Surf18" > "Surf15" AND "Surf15" > 0 OR "Surf15" IS NOT NULL OR "Surf15" <> '' THEN 'surface en progression' WHEN "Surf15" > "Surf18" AND "Surf18" > 0 OR "Surf18" IS NOT NULL OR "Surf18" <> '' THEN 'surface en régression' WHEN "Surf18" IS NULL OR "Surf18" = '' AND "Surf15" <> 0 OR "Surf15" IS NOT NULL OR "Surf15" <> '' THEN 'surface supprimée en 2018' WHEN "Surf15" IS NULL OR "Surf15" = '' AND "Surf18" <> 0 OR "Surf18" IS NOT NULL OR "Surf18" <> '' THEN 'surface créee en 2018' END
Sylvain M.
Hors ligne
#4 Fri 22 July 2022 15:52
- upriste
- Participant occasionnel
- Date d'inscription: 14 Feb 2019
- Messages: 43
Re: QGIS 3.20.3: Requete CASE aide a la formulation
Merci les parenthèse ça semblent fonctionner.
Maintenant je veux ajouter en plus de tout cela une condition. Que toute cette requête ne soit applicable que si le champs "A"=3. Comment faudrait-il procéder?
Hors ligne
#5 Fri 22 July 2022 16:31
- p.jeremie
- Participant assidu
- Lieu: Valence
- Date d'inscription: 10 Sep 2017
- Messages: 427
Re: QGIS 3.20.3: Requete CASE aide a la formulation
Je proposerai d'ajouter un IF englobant le case .. end
Quelque chose du genre :
Code:
if( "A" = 3, CASE ... END, 'A différent de 3')
En modifiant la valeur que vous voulez quand A est différent de 3, j'ai mis 'A différent de 3' dans mon exemple.
Dernière modification par p.jeremie (Fri 22 July 2022 16:31)
Hors ligne
#6 Sat 23 July 2022 19:11
- Mathieu Denat
- Participant actif
- Lieu: Montpellier
- Date d'inscription: 5 May 2010
- Messages: 110
Re: QGIS 3.20.3: Requete CASE aide a la formulation
Bonjour,
Les données en entrée sont au format pgsql ou travaillez vous avec une couche virtuelle?
En complément des réponses précédentes, voici une petite suggestion d'amélioration sur le premier script.
C'est très personnel, mais je préfère utilser des CTE (l'usage de WITH, permet de faire des "requêtes combinées" pour nettoyer les données).
Il peut y avoir plusieurs sources d'erreurs, notamment le fait de comparer du texte alors que vos données sont des chiffres.
Voici une proposition d'amélioration:
Code:
/* pour info sur le SQL on note les commentaires entre */ -- ou avec -- pour ignorer du -- jusqu'en fin de ligne --création d'un jeu de données "propre" with tmp as ( select --nettoyage du champ Surf15 --coalesce permet de remplacer les valeurs nulles par ce qu'on veut (ici 0) coalesce(<champ>,<résutat souhaité>)) coalesce( --nullif permet de remplacer la valeur xxx parce qu'on veut (ici null) nullif(<champ>,<valeur à remplacer par null>) nullif( "Surf15",'' ) ,'0') --::int permet de convertir le texte en nombre (essayer ::real si ça n'est pas du pgsql) ::numeric --on spécifie le nom du champ en miniscules, histoire de s'embêter pour la suite as surf15, --nettoyage du champ Surf18 coalesce(nullif("Surf18",''),'0')::numeric as surf18, * --on récupère les autres champs si besoin, si plein de champs inutiles, prévoir de récupérer uniquement un identifiant unique pour faire une jointure serait peut-être plus from couche ) select CASE WHEN surf18 = surf15 THEN 'surface stable' WHEN surf18 > surf15 THEN 'surface en progression' --coalesce remplace la valeur nulle par 0. nullif remplace la valeur '' par NULL WHEN surf15 > surf18 THEN 'surface en régression' WHEN surf18 = 0 AND surf15 > 0 THEN 'surface supprimée en 2018' WHEN surf15 = 0 AND surf18 > 0 THEN 'surface créee en 2018' ELSE NULL --permet de voir les éventuels enregistrement ne répondant à aucune condition ci-dessus. Pratique pour voir les erreurs! Normalement il devrait pas y en avoir END evolution, * -- autres champs si nécessaires. from tmp
Pour appliquer le case when uniquement en fonction de certains champs j'ajouterai une clause where, ex avec tout le blabla du dessus:
with [...]
from tmp
where "A"=3
Bienvenu dans le mon merveilleux du SQL!
PS: ces requêtes se tapent dans l'éditeur du gestionnaire de BDD, mais je viens de comprendre que vous utilisez un autre moyen pour interroger vos couches! Je laisse ça ici tout de même, on sait jamais si ça peut servir quand même à d'autres...
Dernière modification par Mathieu Denat (Sat 23 July 2022 19:12)
Mathieu
C'est en forgeant qu'on devient forgeron
Hors ligne
#7 Mon 25 July 2022 12:43
- p.jeremie
- Participant assidu
- Lieu: Valence
- Date d'inscription: 10 Sep 2017
- Messages: 427
Re: QGIS 3.20.3: Requete CASE aide a la formulation
Très bonne idéee @Mathieu Denat l'utilisation du nullif et du coalesce pour rendre plus facile les tests.
Dans QGIS, les deux fonctions existent donc @upriste peut les utiliser. Ce qui n'est pas le cas de la CTE avec WITH.
L'expression pour la création du champ d'évaluation d'évolution des surfaces donnerait quelque chose comme :
Code:
if( "A" = 3, CASE WHEN coalesce(nullif("Surf18",''),0) = 0 AND coalesce(nullif("Surf15",''),0) > 0 THEN 'surface supprimée en 2018' WHEN coalesce(nullif("Surf15",''),0) = 0 AND coalesce(nullif("Surf18",''),0) > 0 THEN 'surface créee en 2018' WHEN coalesce(nullif("Surf18",''),0) = coalesce(nullif("Surf15",''),0) THEN 'surface stable' WHEN coalesce(nullif("Surf18",''),0) > coalesce(nullif("Surf15",''),0) THEN 'surface en progression' WHEN coalesce(nullif("Surf15",''),0) > coalesce(nullif("Surf18",''),0) THEN 'surface en régression' ELSE NULL END, 'A différent de 3')
A noter : j'ai placé les tests pour suppression et création de surface en premier, pour éviter d'avoir à ajouter les test AND Surfxx > 0 pour les tests progression et régressions.
Également, je doute de l'utilité du nullif dans le cas où les champs sont déclarés en tant que nombre numérique (entier, réel...).
Hors ligne
#8 Mon 25 July 2022 13:58
- upriste
- Participant occasionnel
- Date d'inscription: 14 Feb 2019
- Messages: 43
Re: QGIS 3.20.3: Requete CASE aide a la formulation
Effectivement le case when avec le requeteur qgis a ses limites. Je n'ai pas réussi à faire ce que je voulais. Va falloir envisager d'utiliser l'editeur de la bdd et pour cela approfondir le sql. En attendant, j'ai filtré ma table selon les critères souhaités et j'ai tapé mes expressions en fonction. C'est pas terrible terrible en terme de productivité mais ça fait le travail tant que la complexité est faible.
Hors ligne
#9 Mon 25 July 2022 14:10
- SANTANNA
- Moderateur
- Lieu: Angers
- Date d'inscription: 18 Jan 2008
- Messages: 3930
Re: QGIS 3.20.3: Requete CASE aide a la formulation
Bonjour,
Dans QGIS, les deux fonctions existent donc @upriste peut les utiliser. Ce qui n'est pas le cas de la CTE avec WITH.
Hmmm... Pas vraiment. La fonction with_variable joue ce rôle.
Hors ligne
#10 Mon 25 July 2022 14:12
- SANTANNA
- Moderateur
- Lieu: Angers
- Date d'inscription: 18 Jan 2008
- Messages: 3930
Re: QGIS 3.20.3: Requete CASE aide a la formulation
Effectivement le case when avec le requeteur qgis a ses limites. Je n'ai pas réussi à faire ce que je voulais.
Sans avoir essayé de formuler votre situation en code (je le précise), je doute néanmoins que votre situation soit trop complexe pour être gérée par un CASE WHEN. La solution de Jeremie ne fait pas le taf? Qu'est-ce qui ne marcherait pas?
Hors ligne
#11 Mon 25 July 2022 15:59
- upriste
- Participant occasionnel
- Date d'inscription: 14 Feb 2019
- Messages: 43
Re: QGIS 3.20.3: Requete CASE aide a la formulation
Je n'avais pas vu le message de Jérémie (merci au passage). J'ai testé et en fait j'ai quelques aberrations qui ne sont pas liées au code mais au fait que je n'ai pas 1 mais 2 conditions préalables.
Plus précisément, j'ai une colonne "A" qui est le type de culture en 2018 et la colonne "B" qui est le type de culture en 2020.
Le fait de savoir si c'est stable, en progression, en regression,... ne m'intéresse que pour 2 types de cultures : A IN (1,3) et B IN (1,3).
Du coup, le code formulé ici décrit bien les augmentations, diminutions,... mais il le fait dans toutes situations y compris quand A ou B = 2,4,5...
Or j'aimerais avoir la valeur "pas de culture" pour pour tout ce qui n'est pas A IN (1,3) et B IN (1,3).
Un exemple : si la colonne A = 2 et que la colonne B = 1 et que la surface de B est supérieure à A le code essayé ici me donne la valeur "surface en progression". Or ce n'est pas exact puisqu'on compare2 cultures différentes.
Dernière modification par upriste (Mon 25 July 2022 16:01)
Hors ligne
#12 Mon 25 July 2022 21:39
- p.jeremie
- Participant assidu
- Lieu: Valence
- Date d'inscription: 10 Sep 2017
- Messages: 427
Re: QGIS 3.20.3: Requete CASE aide a la formulation
Dans ce cas il faut modifier la condition du if.
Mais je ne suis pas sûr de comprendre exactement ce que vous voulez faire.
Par exemple si A est 1 ou 3 mais B est 2, il faut quoi comme résultat ? "pas de culture" ? Et même chose pour le cas inverse A est 2 et B est 1 ou 3 ?
Ci-dessous, je prend en compte uniquement les cas où A et B sont 1 ou 3 et qu'ils sont identiques A=B.
Code:
if( "A" in (1,3) AND "A"="B", CASE WHEN coalesce(nullif("Surf18",''),0) = 0 AND coalesce(nullif("Surf15",''),0) > 0 THEN 'surface supprimée en 2018' WHEN coalesce(nullif("Surf15",''),0) = 0 AND coalesce(nullif("Surf18",''),0) > 0 THEN 'surface créee en 2018' WHEN coalesce(nullif("Surf18",''),0) = coalesce(nullif("Surf15",''),0) THEN 'surface stable' WHEN coalesce(nullif("Surf18",''),0) > coalesce(nullif("Surf15",''),0) THEN 'surface en progression' WHEN coalesce(nullif("Surf15",''),0) > coalesce(nullif("Surf18",''),0) THEN 'surface en régression' ELSE NULL END, 'Pas étudié')
Hors ligne
#13 Mon 25 July 2022 21:43
- p.jeremie
- Participant assidu
- Lieu: Valence
- Date d'inscription: 10 Sep 2017
- Messages: 427
Re: QGIS 3.20.3: Requete CASE aide a la formulation
Bonjour,Pas vraiment. La fonction with_variable joue ce rôle.
Intéressant le with_variable, en effet il peut très bien remplacer le WITH. Je n'ai pas testé pour le cas qui nous concerne ici par manque de temps.
Hors ligne
#14 Wed 27 July 2022 08:54
- upriste
- Participant occasionnel
- Date d'inscription: 14 Feb 2019
- Messages: 43
Re: QGIS 3.20.3: Requete CASE aide a la formulation
Bonjour, désolé je n'ai pas pu tester le code car j'étais parti sur une fausse piste. Cependant, votre code m'est utile pour autre chose, j'ai essayé de l'adapter mais en vain. La requete suivante que j'ai faite fonctionne sauf lorsque je rencontre une valeur null. Comment faire en sorte que le code fonctionne y compris lorsqu'une des 2 valeurs est nulles? Je précise que les 2 champs CodC contiennent des valeurs qui peuvent être 1,2,3,4,5..., NULL.
CASE
WHEN
"CodC18"IN ('1','3') AND"CodC15"IN ('1','3')
THEN 'culture 2015-2018'
WHEN "CodC18"IN ('1','3') AND "CodC15" NOT IN ('1','3')
THEN 'surface créée en 2018'
WHEN "CodC18" NOT IN ('1','3') AND "CodC15" IN ('1','3')
THEN 'surface supprimée en 2018'
WHEN "CodC18" NOT IN ('1','3') AND "CodC15" NOT IN ('1','3')
THEN 'pas de culture'
END
Merci de votre aide et de m'initier ainsi au sql.
Dernière modification par upriste (Wed 27 July 2022 09:15)
Hors ligne
#15 Wed 27 July 2022 10:14
- upriste
- Participant occasionnel
- Date d'inscription: 14 Feb 2019
- Messages: 43
Re: QGIS 3.20.3: Requete CASE aide a la formulation
Voilà, j'ai trouvé. Le caolesce peut-il fonctionner ici ?
CASE
WHEN
"CodC15"IN ('1','3') and "CodC18"IN ('1','3')
THEN 'culture 2015-2018'
WHEN
"CodC15"='1'OR "CodC15"='3' AND "CodC18" NOT IN ('1','3')
THEN 'surface supprimée en 2018'
WHEN
"CodC18" NOT IN('1','3') AND "CodC15"IN ('1','3')
THEN 'surface supprimée en 2018'
WHEN
"CodC18"='1'OR "CodC18"='3' AND "CodC15" NOT IN ('1','3')
THEN 'surface créée en 2018'
WHEN
"CodC15" NOT IN('1','3') AND "CodC18"IN ('1','3')
THEN 'surface créée en 2018'
WHEN
"CodC18" NOT IN('1','3') AND "CodC15" IS NULL
THEN 'pas de culture'
WHEN
"CodC15" NOT IN('1','3') AND "CodC18" IS NULL
THEN 'pas de culture'
END
Dernière modification par upriste (Wed 27 July 2022 12:40)
Hors ligne
#16 Wed 27 July 2022 17:32
- p.jeremie
- Participant assidu
- Lieu: Valence
- Date d'inscription: 10 Sep 2017
- Messages: 427
Re: QGIS 3.20.3: Requete CASE aide a la formulation
Bonjour,
Premièrement, vous devriez utiliser les balises [ code ] et [ /code ] pour afficher votre expression plus distinctement dans vos messages.
Il suffit de clique sur le bouton Code au dessus de la zone de saisie du message et de mettre votre expression entre les deux crochets : [ code ] ici [ /code ]
Également, essayez d'indenter le code, c'est à dire ajouter des tabulations ou espaces au début des lignes pour comprendre quels blocs vont ensemble. Dans ce cas, décaler (indenter) les WHEN et THEN un niveau sous le CASE pour comprendre que ce sont les tests du CASE.
Concernant votre besoin, il peut être pratique d'utiliser le with_variable proposé par SANTANNA. Mais ça complique un peu la compréhension de l'expression donc je vais faire sans dans un premier temps.
Le coalesce peut en effet être une bonne solution à votre problème, pour remplacer les null par une valeur texte ou numérique, par exemple 0. Ce qui permet de pouvoir fonctionner avec les test = ou <> et IN () ou NOT IN ()
Si je reprend votre expression (qui d'ailleurs n'est pas exactement du SQL même si certaines fonctions sont les mêmes entre SQL et expressions dans QGIS).
Code:
CASE -- coalesce permet de récupérer la valeur de CodC15, ou alors 0 s'il est null / idem pour CodC18 WHEN coalesce("CodC15",0) IN ('1','3') and coalesce("CodC18",0) IN ('1','3') THEN 'culture 2015-2018' WHEN coalesce("CodC15",0)='1'OR "CodC15"='3' AND coalesce("CodC18",0) NOT IN ('1','3') THEN 'surface supprimée en 2018' WHEN coalesce("CodC18",0) NOT IN('1','3') AND coalesce("CodC15",0) IN ('1','3') THEN 'surface supprimée en 2018' WHEN coalesce("CodC18",0)='1' OR coalesce("CodC18",0)='3' AND coalesce("CodC15",0) NOT IN ('1','3') THEN 'surface créée en 2018' WHEN coalesce("CodC15",0) NOT IN('1','3') AND coalesce("CodC18",0) IN ('1','3') THEN 'surface créée en 2018' WHEN coalesce("CodC18",0) NOT IN('1','3') AND coalesce("CodC15",0) IS NULL THEN 'pas de culture' WHEN coalesce("CodC15",0) NOT IN('1','3') AND coalesce("CodC18",0) IS NULL THEN 'pas de culture' END
Attention sur les tests à la priorité des opérateur logique AND et OR : AND est prioritaire sur OR, donc le test
Code:
"CodC18"='1' OR "CodC18"='3' AND "CodC15" NOT IN ('1','3')
sera être vrai :
> quand CodeC18=1
OU
> quand CodC18=3 ET que CodC15 pas dans la liste : ('1','3')
Je ne sais pas si c'est ce que vous voulez ?
Dans tous les cas il vaut mieux mettre des parenthèses pour regrouper les tests et s'assurer qu'ils sont correctement faits, par exemple si vous vouliez que le test soit vrai
> quand CodC18=1 ou CodC18=3
ET CodC15 pas dans la liste ('1','3')
Alors il faudrait écrire
Code:
( "CodC18"='1' OR "CodC18"='3' ) AND "CodC15" NOT IN ('1','3')
Notez les parenthèses qui entourent les deux test d'égalité de valeur pour CodC18.
Avec la fonction with_variable(), ça donnerait quelque chose comme :
Code:
-- définition de la variable v_codc15 avec la valeur de l'expression coalesce("CodC15",0) with_variable('v_codc15', coalesce("CodC15",0), -- définition de la variable v_codc18 avec la valeur de l'expression coalesce("CodC18",0) with_variable('v_codc18', coalesce("CodC18",0), -- utilisation des variable avec @nom_variable CASE WHEN @v_codc15 IN ('1','3') and @v_codc18 IN ('1','3') THEN 'culture 2015-2018' WHEN @v_codc15='1'OR @v_codc15='3' AND @v_codc18 NOT IN ('1','3') THEN 'surface supprimée en 2018' WHEN @v_codc18 NOT IN('1','3') AND @v_codc15 IN ('1','3') THEN 'surface supprimée en 2018' WHEN @v_codc18='1' OR @v_codc18='3' AND @v_codc15 NOT IN ('1','3') THEN 'surface créée en 2018' WHEN @v_codc15 NOT IN('1','3') AND @v_codc18 IN ('1','3') THEN 'surface créée en 2018' WHEN @v_codc18 NOT IN('1','3') AND @v_codc15 IS NULL THEN 'pas de culture' WHEN @v_codc15 NOT IN('1','3') AND @v_codc18 IS NULL THEN 'pas de culture' END
Hors ligne