Nous utilisons des cookies pour vous garantir la meilleure expérience sur notre site. Si vous continuez à utiliser ce dernier, nous considèrerons que vous acceptez l'utilisation des cookies. J'ai compris ! ou En savoir plus !.
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é ?

Annonce

Suite à un problème technique intervenu entre le 22 et le 23 mars, nous avons du procéder dans la soirée du 25 mars, à la restauration de la base de données du 24 mars (matinée).

En clair, nous avons perdu vos contributions et inscriptions du dimanche 24 et du lundi 25 mars.
Nous vous prions de nous excuser.

#1 Tue 08 January 2019 12:11

BenB
Participant actif
Date d'inscription: 29 Dec 2015
Messages: 120

[PostgreSQL] Sous-requêtes et GROUP BY

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 .

Hors ligne

 

#2 Tue 08 January 2019 12:38

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1534

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

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 wink : 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

Hors ligne

 

#3 Tue 08 January 2019 12:43

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

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

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)

Hors ligne

 

#4 Tue 08 January 2019 12:51

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1534

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

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

Hors ligne

 

#5 Tue 08 January 2019 14:21

BenB
Participant actif
Date d'inscription: 29 Dec 2015
Messages: 120

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

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

Hors ligne

 

#6 Tue 08 January 2019 15:35

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

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

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)

Hors ligne

 

#7 Tue 08 January 2019 15:54

BenB
Participant actif
Date d'inscription: 29 Dec 2015
Messages: 120

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

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" ?

Hors ligne

 

#8 Tue 08 January 2019 16:14

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

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

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)

Hors ligne

 

#9 Tue 08 January 2019 17:03

BenB
Participant actif
Date d'inscription: 29 Dec 2015
Messages: 120

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

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

Hors ligne

 

#10 Tue 08 January 2019 17:26

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

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

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)

Hors ligne

 

#11 Tue 08 January 2019 18:23

MathieuB
Membre du bureau
Lieu: Montpellier
Date d'inscription: 18 Jan 2006
Messages: 1220
Site web

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

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.


Mathieu BOSSAERT
Association GeoRezo

Hors ligne

 

#12 Tue 08 January 2019 19:34

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

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

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)

Hors ligne

 

#13 Tue 08 January 2019 19:40

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

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

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.

Hors ligne

 

#14 Tue 08 January 2019 19:50

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

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

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...

Hors ligne

 

#15 Tue 08 January 2019 20:06

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

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

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)

Hors ligne

 

#16 Thu 10 January 2019 16:08

BenB
Participant actif
Date d'inscription: 29 Dec 2015
Messages: 120

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

Très intéressant tout ça.

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

Je vais essayer avec FILTER et ||

Hors ligne

 

Pied de page des forums

Powered by FluxBB