Le portail francophone de la géomatique

white-shadow90 · Mon 22 February 2016 15:59

Bonjour,

Dans le cadre du développement d'un outil de sectorisation, je cherche à calculer la somme des effectifs des différents niveaux.

J'ai tout d'abord créé une vue qui me donne 3 colonnes (le nom du secteur, le niveau et les effectifs)

Code:

CREATE VIEW secto_elem_result
AS
(SELECT "secteurs_elementaires.nom_secteur, effectifs_scol.niveau, count(*)
FROM effectifs_scol
INNER JOIN secteurs_elementaires
ON ST_WITHIN(effectifs_scol.geom, secteurs_elementaires.geom)
GROUP BY secteurs_elementaires.nom_secteur, effectifs_scol.niveau
ORDER BY secteurs_elementaires.nom_secteur, effectifs_scol.niveau)

Puis je créé une autre vue sous la forme d'un tableau en utilisant l'extension tablefunc

Code:

CREATE VIEW secto_elem_tableau
AS SELECT *
FROM crosstab(
'select nom_secteur, niveau, count 
from secto_elem_result 
where niveau = ''CP'' OR niveau  = ''CE1'' OR niveau  = ''CE2'' OR niveau  = ''CM1'' OR niveau  = ''CM2''
ORDER BY 1')
AS 
(id character varying (25),
CP bigint, 
CE1 bigint, 
CE2 bigint, 
CM1 bigint, 
CM2 bigint);

Et j'aimerais ajouter dans ce tableau récapitulatif la somme des effectifs de CP, CE1, CE2, CM1 et CM2 pour m'assurer que l'on ne dépasse pas la capacité d'accueil des différents établissements. Mais je ne sais pas comment écrire la requête dans le cadre d'un tablefunc...

Auriez-vous une solution à proposer ?

Je vous remercie par avance pour l'aide apportée.

gustavecoste · Tue 23 February 2016 05:48

Avez-vous pensé à faire un UNION de la vue secto_elem_tableau et d'une requête sommant les colonnes de secto_elem_tableau?

white-shadow90 · Tue 23 February 2016 08:10

Bonjour,

Je vais regarder votre piste et je me suis par ailleurs rendu compte d'une erreur car les valeurs ne sortent pas dans l'ordre souhaité.
Je reposterai quand j'aurai une solution.

MathieuB · Tue 23 February 2016 09:20

Bonjour,

pas sûr que tablefunc soit utile.

Selon la version de PostgreSQL utilisée, vous pouvez faire des sommes conditionnelles :

à l'"ancienne" :

Code:

SELECT nom_secteur, sum(CASE WHEN niveau = 'CP' THEN effectif ELSE 0 END) AS cp, 
sum(CASE WHEN niveau = 'CE1' THEN effectif ELSE 0 END) AS ce1, 
sum(CASE WHEN niveau = 'CE2' THEN effectif ELSE 0 END) AS ce2, 
sum(CASE WHEN niveau = 'CM1' THEN effectif ELSE 0 END) AS cm1, 
sum(CASE WHEN niveau = 'CM2' THEN effectif ELSE 0 END) AS cm2
FROM toto
GROUP BY nom_secteur

et depuis la version 9.4 je crois :

Code:

SELECT nom_secteur, sum(effectif) FILTER (WHERE niveau' = 'CP') AS cp, 
sum(effectif) FILTER (WHERE niveau = 'CE1') AS ce1, 
sum(effectif) FILTER (WHERE niveau = 'CE2') AS ce2, 
sum(effectif) FILTER (WHERE niveau = 'CM1') AS cm1, 
sum(effectif) FILTER (WHERE niveau = 'CM2') AS cm2 
FROM toto
GROUP BY nom_secteur

white-shadow90 · Tue 23 February 2016 10:06

Bonjour,

La version la plus ancienne de postgresql que j'utilise est la version 8.4

J'ai corrigé l'ordre de progression des niveaux de la manière suivante :

Code:

CREATE OR REPLACE VIEW secto_elem_global_result (nom_sect, niveau, effectifs)
AS
(SELECT t.nom_sect, t.niveau, t.effectifs
FROM (SELECT 
secteurs_elementaires.nom_secteur AS nom_sect,
CASE
    WHEN effectifs_scol.niveau = 'CP' THEN 'niveau_1_CP'
    WHEN effectifs_scol.niveau = 'CE1' THEN 'niveau_2_CE1'
    WHEN effectifs_scol.niveau = 'CE2' THEN 'niveau_3_CE2'
    WHEN effectifs_scol.niveau = 'CM1' THEN 'niveau_4_CM1'
    WHEN effectifs_scol.niveau = 'CM2' THEN 'niveau_5_CM2'
    ELSE 'non_elem'
end AS niveau,
count(*) AS effectifs
FROM secteurs_elementaires
INNER JOIN effectifs_scol
ON ST_WITHIN(effectifs_scol.geometry, secteurs_elementaires.geometry2)
GROUP BY secteurs_elementaires.nom_secteur, effectifs_scol.niveau) t
GROUP BY t.nom_sect, t.niveau, t.effectifs
ORDER BY t.nom_sect, t.niveau);

Et j'ai essayé la méthode proposée sur http://stackoverflow.com/questions/2856 … postgresql

et ça donnerait ça mais ça ne fonctionne pas sachant que le total que je cherche à récupérer est la somme des effectifs de CP+CE1+CE2+CM1+CM2

Code:

CREATE OR REPLACE VIEW secto_elem_global_tableau
AS SELECT *
FROM crosstab(
'select nom_sect, sum(sum(effectifs)) OVER (PARTITION BY nom_sect), niveau, effectifs 
from secto_elem_global_result
where niveau = ''niveau_1_CP'' OR niveau = ''niveau_2_CE1'' OR niveau = ''niveau_3_CE2'' OR niveau = ''niveau_4_CM1'' OR niveau = ''niveau_5_CM2''
ORDER BY 1')
AS 
(id character varying (25),
total bigint,
niveau_1_CP bigint, 
niveau_2_CE1 bigint, 
niveau_3_CE2 bigint, 
niveau_4_CM1 bigint, 
niveau_5_CM2 bigint);

Dernière modification par white-shadow90 (Tue 23 February 2016 10:10)

white-shadow90 · Tue 23 February 2016 11:47

@MathieuB, sauf erreur de compréhension de ma part, la requête que vous proposez renvoie la somme par niveau et non la somme de tous les niveaux, non ?

@gustavecoste, j'ai essayé quelque chose qui ressemble à ce qu'on peut lire ici : http://dba.stackexchange.com/questions/ … gresql-9-0 mais sans succès. Pourriez-vous donner davantage de précisions svp ?

Code:

SELECT * FROM crosstab(
  $$WITH cte AS (
      SELECT nom_sect, niveau, effectifs AS ct
      FROM   secto_elem_global_result
      GROUP  BY 1, 2, 3
      )
   TABLE  cte
   UNION ALL  
   SELECT nom_sect, 'total' AS niveau, sum(ct) AS ct
   FROM   cte
   GROUP  BY 1
   ORDER  BY 1$$  

   ,$$VALUES ('niveau_1_CP'::text), ('niveau_2_CE1'), ('niveau_3_CE2'), ('niveau_4_CM1'), ('niveau_5_CM2')
           , ('total')$$
   )
AS t("nom_sect" text
   , "niveau_1_CP" bigint, "niveau_2_CE1" bigint,"niveau_3_CE2" bigint, "niveau_4_CM1" bigint, "niveau_5_CM2" bigint
   , "total" bigint);

Le problème de cette requête est de donner la somme de tous les effectifs (incluant les maternelles) et pas seulement les CP+CE1+CE2+CM1+CM2

Dernière modification par white-shadow90 (Tue 23 February 2016 12:01)

MathieuB · Tue 23 February 2016 11:58

white-shadow90 a écrit:

@MathieuB, sauf erreur de compréhension de ma part, la requête que vous proposez renvoie la somme par niveau et non la somme de tous les niveaux, non ?

Oui c'est bien cela, vous voulez en plus pour chaque secteur le total des effectifs des différents niveaux c'est ça?

white-shadow90 · Tue 23 February 2016 12:02

Oui, il me reste à trouver le moyen de faire la somme conditionnelle à la place de sum(ct)...

Voilà qui est fait :

Code:

CREATE OR REPLACE VIEW secto_elem_global_tableau
AS SELECT * FROM crosstab(
  $$WITH cte AS (
      SELECT nom_sect, niveau, effectifs AS ct
      FROM   secto_elem_global_result
      GROUP  BY 1, 2, 3
      )
   TABLE  cte
   UNION ALL  
   SELECT nom_sect, 'total' AS niveau, sum(ct) AS ct
   FROM   cte
   WHERE niveau <> 'non_elem'
   GROUP  BY 1
   ORDER  BY 1$$  

   ,$$VALUES ('niveau_1_CP'::text), ('niveau_2_CE1'), ('niveau_3_CE2'), ('niveau_4_CM1'), ('niveau_5_CM2')
           , ('total')$$
   )
AS t("nom_sect" text
   , "niveau_1_CP" bigint, "niveau_2_CE1" bigint,"niveau_3_CE2" bigint, "niveau_4_CM1" bigint, "niveau_5_CM2" bigint
   , "total" bigint);

Dernière modification par white-shadow90 (Tue 23 February 2016 12:13)

MathieuB · Tue 23 February 2016 12:11

Dans ce cas, si je comprends bien, il suffit de compléter la requête avec la somme des effectifs des différents niveaux du secteur :

Code:

SELECT nom_secteur, sum(CASE WHEN niveau = 'CP' THEN effectif ELSE 0 END) AS cp, 
sum(CASE WHEN niveau = 'CE1' THEN effectif ELSE 0 END) AS ce1, 
sum(CASE WHEN niveau = 'CE2' THEN effectif ELSE 0 END) AS ce2, 
sum(CASE WHEN niveau = 'CM1' THEN effectif ELSE 0 END) AS cm1, 
sum(CASE WHEN niveau = 'CM2' THEN effectif ELSE 0 END) AS cm2, 
sum(CASE WHEN niveau IN ('CP','CE1','CE2', 'CM1', 'CM2') THEN effectif ELSE 0 END) AS total
FROM toto
GROUP BY nom_secteur

Avec peu de colonnes, cette manière de créer une matrice est pour moi plus simple que d'utiliser tablefunc.

white-shadow90 · Tue 23 February 2016 12:15

Effectivement, j'ai écrasé une mouche avec un bulldozer... Votre solution semble bien plus simple...

MathieuB · Tue 23 February 2016 13:25

Parfois ça permet aussi de pratiquer un peu le bulldozer et de le ressortir à bon escient plus tard ;-)

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 Mon 22 February 2016 15:59

[Posgresql] Somme dans un tablefunc pour sectorisation scolaire

Code:

Code:

#2 Tue 23 February 2016 05:48

Re: [Posgresql] Somme dans un tablefunc pour sectorisation scolaire

#3 Tue 23 February 2016 08:10

Re: [Posgresql] Somme dans un tablefunc pour sectorisation scolaire

#4 Tue 23 February 2016 09:20

Re: [Posgresql] Somme dans un tablefunc pour sectorisation scolaire

Code:

Code:

#5 Tue 23 February 2016 10:06

Re: [Posgresql] Somme dans un tablefunc pour sectorisation scolaire

Code:

Code:

#6 Tue 23 February 2016 11:47

Re: [Posgresql] Somme dans un tablefunc pour sectorisation scolaire

Code:

#7 Tue 23 February 2016 11:58

Re: [Posgresql] Somme dans un tablefunc pour sectorisation scolaire

#8 Tue 23 February 2016 12:02

Re: [Posgresql] Somme dans un tablefunc pour sectorisation scolaire

Code:

#9 Tue 23 February 2016 12:11

Re: [Posgresql] Somme dans un tablefunc pour sectorisation scolaire

Code:

#10 Tue 23 February 2016 12:15

Re: [Posgresql] Somme dans un tablefunc pour sectorisation scolaire

#11 Tue 23 February 2016 13:25

Re: [Posgresql] Somme dans un tablefunc pour sectorisation scolaire

Pied de page des forums