Le portail francophone de la géomatique

BenB · Tue 08 January 2019 12:11

Bonjour,

j'ai une table d'observation de la faune, une table des protections et une table de l'INPN pour les taxons.

Je dois faire une vue résumant le nombre d'observation par groupe en prenant en comptes les protections.

Soit deux colonnes : une avec la somme total des observations par groupe et une avec la somme des espèce protégées par groupe.

Je dois donc faire une sous requête.

Code:

SELECT 
t.group2_inpn,
count(t.group2_inpn) as "nombre observation total",

(SELECT  
count(t.group2_inpn) 
FROM "observatoire".t_observation_faune f
LEFT JOIN ref_inpn.taxref_faune t ON f.cd_nom::text = t.cd_nom::text
LEFT JOIN ref_inpn.liste_protection p ON f.cd_nom::text = p.cd_nom::text
WHERE p.article_national IS NOT NULL AND t.group2_inpn::text <> 'Oiseaux'::text 
GROUP BY t.group2_inpn )
as "nombre observation protégée"


 FROM "observatoire".t_observation_faune f
 LEFT JOIN ref_inpn.taxref_faune t ON f.cd_nom::text = t.cd_nom::text
  WHERE t.group2_inpn::text <> 'Oiseaux'::text
GROUP BY t.group2_inpn 

ORDER BY 1

j'ai le message suivant :
ERREUR: plus d'une ligne renvoyée par une sous-requête utilisée comme une expression

En enlevant le GROUP BY de la sous-requête j'ai bien un résultat avec la somme total des espèces protégés. Pourquoi le GROUP BY pose-t-il problème ?

En vous remerciant .

Nicolas Ribot · Tue 08 January 2019 12:38

Bonjour,

Pour ce genre d'analyse multi dimensions (deux ici), PG dispose de la fonctionnalité "GROUPING SETS", qui permet de faire des group by sur plusieurs colonnes dans la même requete, en se passant de requêtes intermédiaires:
https://www.postgresql.org/docs/11/quer … UPING-SETS

Concernant le message d'erreur: tout est dit : vous utilisez un select dans une partie qui attend une seule valeur, or votre select en renvoie plusieurs.
En enlevant le group by, vous faites un "select count(*) from matable", ce qui renvoie bien une seule valeur.

Si vous voulez passer par des tables intermédiaires, faites les deux synthèses dans deux CTE, puis le select final affiche les colonnes que vous voulez.

Nicolas

tumasgiu · Tue 08 January 2019 12:43

Salut,

Je crois que vous vous compliquez pas mal la vie.

Si j'ai bien saisi votre MCD et votre demande, on peut simplifier comme suit :

Code:

SELECT
    group2_inpn,
    count(*) nb_total_obs,
    count(article_national) nb_protege,
FROM "observatoire".t_observation_faune f
JOIN ref_inpn.taxref_faune t 
    ON f.cd_nom::text = t.cd_nom::text
LEFT JOIN ref_inpn.liste_protection p 
    ON f.cd_nom::text = p.cd_nom::text 
    AND p.article_national IS NOT NULL 
WHERE 
    t.group2_inpn::text <> 'Oiseaux'::text
GROUP BY group2_inpn

L'astuce est d’utiliser le fait que count ne comptablise
que les valeurs non nulles.

[edit]
La suggestion de Nicolas est tout aussi valide,
et certainement plus claire, et plus modulable.

Dernière modification par tumasgiu (Tue 08 January 2019 13:57)

Nicolas Ribot · Tue 08 January 2019 12:51

tumasgiu a écrit:

Salut,

L'astuce est d’utiliser le fait que count ne comptablise
que les valeurs non nulles.

Ca me plait bien cet usage. Pas d'étape intermédiaire.

Nicolas

BenB · Tue 08 January 2019 14:21

Merci pour vos réponses,

la technique de tumasgiu fonctionne très bien, je ne sais pas pourquoi je me suis lancé dans les sous requêtes...

Je retiens une chose :

p.article_national IS NOT NULL placé dans le JOIN et non dans le WHERE

De plus, j'ai continué en ajoutant de nouvelles jointures et count sur les listes rouges avec la même écriture. C'est parfait

Code:

SELECT
    group2_inpn,
    count(*) nb_total_obs,
    count(article_national) nb_protege,
    count (lr.statut) nb_lr_departemental,
    count(a.statut) nb_lr_regional
    
FROM "observatoire".t_observation_faune f
LEFT JOIN ref_inpn.taxref_faune t 
    ON f.cd_nom::text = t.cd_nom::text

LEFT JOIN ref_inpn.liste_protection p 
    ON f.cd_nom::text = p.cd_nom::text 
   AND p.article_national IS NOT NULL

LEFT JOIN ref_inpn.lr_74 lr
 ON   lr.cd_nom=f.cd_nom 
 AND lr.statut IN ('VU','EN')

 LEFT JOIN ref_inpn.lr_ra a
 ON   a.cd_nom=f.cd_nom 
 AND a.statut IN ('VU','EN')


WHERE t.group2_inpn::text <> 'Oiseaux'::text
GROUP BY group2_inpn

Encore merci

tumasgiu · Tue 08 January 2019 15:35

BenB a écrit:

p.article_national IS NOT NULL placé dans le JOIN et non dans le WHERE

Oui, si cette condition était dans le WHERE, les éléments de la relation à gauche de la jointure
n'ayant pas de correspondance avec la relation droite ne serait pas retenu pour le comptage.

Il y a des cas ou les deux notations sont équivalentes mais attention,
il est possible que cela change le plan de la requête.

Dernière modification par tumasgiu (Tue 08 January 2019 15:40)

BenB · Tue 08 January 2019 15:54

Ok,

La requête compte le nombre d'observation total pour chaque espèce, mais si je veux simplement le nombre d’espèce par groupe je dois utiliser un count (distinct cd_nom)

Le problème est pour mes attributs de protection le count (distinct) compte les récurrences des articles ou des statuts des listes rouges et non le nombre d’espèces différentes concernées par ces protections.

la solution est-elle du côté de "GROUPING SETS" ?

tumasgiu · Tue 08 January 2019 16:14

Salut,

jamais utilisé les GROUPING SETS, je pense que c'est sans doute possible.

Sinon, je pense que vous pouvez tricher avec le même genre de requête,
en changeant l'expression des counts, exemple:

Code:

count(DISTINCT 
        CASE WHEN article_national IS NULL 
            THEN NULL 
            ELSE cd_nom 
        END)

BenB · Tue 08 January 2019 17:03

yes cela fonctionne

En gros j'avais ceci

nom protection
grenouille rousse article 1
Triton article 2
grenouille commune article 1

Résultat avant le DISCTINCT :

Groupe nombre observé protégé
Amphibiens 3 2

Le count compte les nombre d'article different dans la colonne protégé (article 1 et 2)

Avec le DISCTINCT

Groupe nombre observé protégé
Amphibiens 3 3

par contre je ne comprend pas l'astuce du CASE WHEN THEN qui permet ce résultat

tumasgiu · Tue 08 January 2019 17:26

l'argument des fonctions d'agrégat, donc fait partie count(),
n'est pas forcement une colonne mais peut très bien être une expression.

Ici, vous voulez compter le nombre d'espèce protégées, autrement dit
le nombre de cd_nom distinct.
Une espèce est protégée uniquement si article_national est non NULL,
On l'a vu, count() ne prends pas en compte les occurences nulles, donc
l'idée est de "transmettre" à l'aide du CASE la "nullité"
de article_national à cd_nom, puis de compter le nombre distinct
de cette expression.

Exemple si on a les données suivantes,

cd_nom;article

ligne 1 : grenouille;article1
ligne 2 : grenouille;article1
ligne 3 : licorne;NULL
ligne 4 : balbuzard;article3

l'expression count(DISTINCT cd_nom) comptera les données

grenouille
licorne
balbuzard

Tandis que le count avec le CASE, cela se passe comme çà
(en décomposé):

premiere étape de transmission de la nullité

ligne 1 : article1 -> grenouille
ligne 2 : article1 -> grenouille
ligne 3 : NULL -> NULL
ligne 4 : article3 -> balbuzard

seconde etape distinct :

grenouille
balbuzard

Dernière modification par tumasgiu (Tue 08 January 2019 17:39)

MathieuB · Tue 08 January 2019 18:23

Bonsoir à tous,

une clause FILTER suivant le COUNT ne suffit-elle pas ?
L'exemple ci-dessous est adapté à mon modèle de données :

Code:

SELECT group2_inpn, count(distinct id_obs), count (distinct taxref.cd_ref) nb_especes, count (distinct taxref.cd_ref) FILTER(WHERE type = 'Protection' AND niveau='national') as nb_especes_prot_nat
  FROM saisie.saisie_observation
  JOIN inpn.taxref_v12 taxref USING(cd_nom)
  LEFT JOIN inpn.protection_especes_11 ON protection_especes_11.cd_nom::text = taxref.cd_nom
  JOIN inpn.protection_especes_type_11 USING(cd_protection)
  WHERE group2_inpn<>'oiseaux'
  GROUP BY group2_inpn

Je compte pour chaque groupe (hors oiseaux) le nombre d’observations que j'ai en base, le nombre total d’espèces observées et parmi elles le nombre d'espèces faisant l’objet d'une protection nationale.

tumasgiu · Tue 08 January 2019 19:34

Exact Mathieu, je crois même que c'est une des raisons
pour laquelle cette clause FILTER a été créée,
pour éviter de faire des CASE WHEN dans des agrégats.
Elle a été introduite dans la 9.4, ce qui date un peu,
donc on peut supposer qu'elle peut être conseillée
à la majorité des utilisateurs.

le prédicat de la clause FILTER devrait être évaluer à FAUX,
quand les colonnes type et niveau sont NULLS
(
NULL = 'Protégé -> NULL, NULL = 'National' -> NULL
=>
NULL AND NULL -> NULL
=>
un prédicat avec une valeur NULL est évalué à FAUX
)
et donc écarter ces lignes de l'agrégat.

Je pensais à tort que la clause FILTER et la clause DISTINCT
dans une fonction d'agrégat étaient mutuellement exclusives,
mais je crois que j'ai confondu avec la clause ORDER BY.

Dernière modification par tumasgiu (Tue 08 January 2019 20:17)

tumasgiu · Tue 08 January 2019 19:40

Sans pour autant essayer de me justifier,
j'ajoute qu'il me semble que cette clause peut engendrer
une pénalité en terme de performance
(j'ai du lire çà dans la doc ou la mailing list...)

De plus, il est bon de savoir utiliser les deux "techniques",
pour pouvoir simplifier les choses quand c'est possible,
ou faire le même genre de requête quand la fonctionnalité
n'est pas implémentée dans le SGBDR qu'on utilise.

tumasgiu · Tue 08 January 2019 19:50

En fait quand ORDER BY et DISTINCT sont utilisée
dans un agrégat, les colonnes qui servent à ordonner
doivent apparaitre dans la clause DISTINCT,
donc c'est pas mutuellement exclusif,
donc voilà...

bon maintenant, j’arrête...

tumasgiu · Tue 08 January 2019 20:06

Si, allez ! Une dernière :
dans mon exemple, on doit pouvoir
remplacer le CASE par :

Code:

cd_nom || article_national

Tout çà pour dire, si la version de Postgres est sup. ou égale à 9.4,
préférez la solution de Mathieu.

Dernière modification par tumasgiu (Tue 08 January 2019 20:15)

BenB · Thu 10 January 2019 16:08

Très intéressant tout ça.

Pour l'instant mes vues de "stats" tournent avec CASE WHEN.

Je vais essayer avec FILTER et ||

Communauté	Ressources	L'emploi	Le marché	L'association	Fils RSS
Forum Blogs Wiki Planet Sites amis	Agenda Biblio Fiches AMO-CNIG Paris PCGIS Téléchargements	Offres d'emploi Banque de CV Formations Démarche Métiers	Geo-Entreprises Geo-Communiqué Appels d'offres	L'association Qui sommes-nous ? Nous aider Bienfaiteurs Partenaires Historique Statuts	Tout le site Job Géomatique Services GeoBlogs ...	Faire un don Mentions légales

Le portail francophone de la géomatique

Annonce

#1 Tue 08 January 2019 12:11

[PostgreSQL] Sous-requêtes et GROUP BY

Code:

#2 Tue 08 January 2019 12:38

Re: [PostgreSQL] Sous-requêtes et GROUP BY

#3 Tue 08 January 2019 12:43

Re: [PostgreSQL] Sous-requêtes et GROUP BY

Code:

#4 Tue 08 January 2019 12:51

Re: [PostgreSQL] Sous-requêtes et GROUP BY

#5 Tue 08 January 2019 14:21

Re: [PostgreSQL] Sous-requêtes et GROUP BY

Code:

#6 Tue 08 January 2019 15:35

Re: [PostgreSQL] Sous-requêtes et GROUP BY

#7 Tue 08 January 2019 15:54

Re: [PostgreSQL] Sous-requêtes et GROUP BY

#8 Tue 08 January 2019 16:14

Re: [PostgreSQL] Sous-requêtes et GROUP BY

Code:

#9 Tue 08 January 2019 17:03

Re: [PostgreSQL] Sous-requêtes et GROUP BY

#10 Tue 08 January 2019 17:26

Re: [PostgreSQL] Sous-requêtes et GROUP BY

#11 Tue 08 January 2019 18:23

Re: [PostgreSQL] Sous-requêtes et GROUP BY

Code:

#12 Tue 08 January 2019 19:34

Re: [PostgreSQL] Sous-requêtes et GROUP BY

#13 Tue 08 January 2019 19:40

Re: [PostgreSQL] Sous-requêtes et GROUP BY

#14 Tue 08 January 2019 19:50

Re: [PostgreSQL] Sous-requêtes et GROUP BY

#15 Tue 08 January 2019 20:06

Re: [PostgreSQL] Sous-requêtes et GROUP BY

Code:

#16 Thu 10 January 2019 16:08

Re: [PostgreSQL] Sous-requêtes et GROUP BY

Pied de page des forums