banniere

Le portail francophone de la géomatique


Toujours pas inscrit ? Mot de passe oublié ?
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

#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! smile

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

upriste a écrit:

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

SANTANNA a écrit:

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

 

Pied de page des forums

Powered by FluxBB