Evolution d'une population

**Nick_59** · 18/07/2006, 12h12

Salut à tous,

je cherche à visualiser l'évolution d'une population dans un groupe de commune sur une période donnée.
j'ai d'un côté une table des groupes, l'autre une table des communes et leur nombre d'habitant :

GROUPE { id_groupe, nom }
COMMUNES { id_commune, nbhab,date }
GROUPE_COMMUNE { id_groupe,id_commune }

ex pour COMMUNES :
1 | 100000 | 2006-02-20
1 | 250000 | 2006-10-20
2 | 15000 | 2006-5-15
2 | 25000 | 2006-9-20

ex pour GROUPE
1 | groupe1

ex pour GROUPE_COMMUNE
1 | 1
1 | 2

je voudrais l'evolution du nombre d'habitant du groupe1 sur la periode (par ex) du 01/02/2006 au 20/11/2006

merci pour votre aide

**al1_24** · 18/07/2006, 12h24

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT  g.nom
    ,   c.date
    ,   SUM(c.nbhab)
FROM    groupe  AS g
  INNER JOIN  groupe_commune  AS gc
    ON  g.id_groupe = gc.id_commune
  INNER JOIN  communes AS c
    ON  gc.id_commune = c.id_commune
WHERE   g.id_groupe = 1
  AND   c.date BETWEEN  CAST('01/02/2006' AS DATE)  AND CAST('20/11/2006' AS DATE)
GROUP BY  g.nom, c.date
ORDER BY  g.nom, c.date
;

Attention toutefois que date est un mot réservé de SQL et ne devrait pas être utilisé comme nom de colonne...

**Médiat** · 18/07/2006, 12h47

Est-ce que le résultat attendu est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
du 2006-02-20 au 2006-05-15 --> 100 000
du 2006-05-15 au 2006-09-20 --> 115 000
du 2006-09-20 au 2006-10-20 --> 125 000
à partir du 2006-10-20      --> 275 000

**Nick_59** · 18/07/2006, 13h09

la reponse attendue est bien celle-ci

**Médiat** · 18/07/2006, 13h30

Alors c'est un peu plus compliqué, je n'ai pas pris en compte les groupes, cette partie étant facile à ajouter :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
select Debut, Fin , sum(nbhab)
from (select a.ladate as Debut, min(b.ladate) as Fin
      from laTable a left join laTable b
                          on a.ladate < b.ladate
      group by a.ladate) c left join laTable d
                                  on d.ladate < coalesce(Fin, to_date('01/01/2999', 'DD/MM/YYYY'))
where not exists (select null
                  from laTable e
                  where d.id_commune = e.id_commune
                    and d.ladate < e.ladate
                    and e.ladate < coalesce(Fin, to_date('01/01/2999', 'DD/MM/YYYY')))
group by Debut, Fin ;

J'ai testé, cela donne bien le bon résultat avec ce jeu d'essai.

**Nick_59** · 18/07/2006, 14h17

j'ai oublié de préciser, j'utilise Mysql
la fonction coalesce fonctionne t elle ?

**Médiat** · 18/07/2006, 14h19

Je ne sais pas, mais c'est facile à tester, et il doit exister l'équivalent.

**TheLeadingEdge** · 18/07/2006, 14h43

Bonjour,

1 autre ...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SQL> SELECT     g.id_groupe,
  2     d.dat,
  3     MIN(f.dat),
  4     SUM (d.nbhab) / COUNT(1)
  5  FROM       groupe_commune g
  6  INNER JOIN commune d
  7  ON id_commune = d.id
  8  LEFT OUTER JOIN commune f
  9  ON id_commune IN
 10  (
 11  SELECT     id_commune
 12  FROM       groupe_commune
 13  WHERE      id_groupe = g.id_groupe
 14  )
 15  AND        d.dat < f.dat
 16  GROUP BY g.id_groupe,
 17     d.dat
 18  ORDER BY g.id_groupe,
 19     d.dat,
 20     MIN(f.dat)
 21  ;
 
 ID_GROUPE DAT      MIN(F.DA SUM(D.NBHAB)/COUNT(1)
---------- -------- -------- ---------------------
         1 20/02/06 15/05/06                100000
         1 15/05/06 20/09/06                 15000
         1 20/09/06 20/10/06                 25000
         1 20/10/06                         250000

**Nick_59** · 18/07/2006, 14h47

merci pour ta requete,
mais il me semble qu'elle affiche plusieurs fois le même nombre d'habitant pour des dates différentes qui n'ont pas de references dans la table.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
1  	2006-03-01 00:00:00  	2006-05-18 00:00:00  	100000
1 	2006-05-18 00:00:00 	2006-07-03 00:00:00 	850000
1 	2006-07-03 00:00:00 	2006-07-05 00:00:00 	850000
1 	2006-07-05 00:00:00 	2006-07-07 00:00:00 	850000
1 	2006-07-07 00:00:00 	2006-07-10 00:00:00 	850000
1 	2006-07-10 00:00:00 	2006-07-19 00:00:00 	850000
1 	2006-07-19 00:00:00 	2006-07-21 00:00:00 	850000
1 	2006-07-21 00:00:00 	2006-07-25 00:00:00 	850000
1 	2006-07-25 00:00:00 	2006-07-28 00:00:00 	850000
1 	2006-07-28 00:00:00 	2006-07-29 00:00:00 	850000
1 	2006-07-29 00:00:00 	NULL 	850000
4 	2006-03-01 00:00:00 	2006-05-18 00:00:00 	120000
4 	2006-05-18 00:00:00 	2006-07-03 00:00:00 	120000
4 	2006-07-03 00:00:00 	2006-07-05 00:00:00 	120000

En base , j'ai ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
1	150000	10/07/2004
1	350000	10/07/2005
1	100000	01/03/2006
1	850000	18/05/2006
4	120000	01/01/2006

voici le code utilisé :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
select id_commune, Debut, Fin , sum(NBHAB)  from (<div style="margin-left:40px">
select a.DATE_VALIDITE as Debut, min(b.DATE_VALIDITE) as Fin 
from formulaire_formCCI_C a left join formulaire_formCCI_C b  on a.DATE_VALIDITE < b.DATE_VALIDITE 
where a.DATE_VALIDITE>'2006-2-20'  group by a.DATE_VALIDITE) c</div>left join formulaire_formCCI_C d on d.DATE_VALIDITE < coalesce(Fin, '2006-11-20')
where not exists (<div style="margin-left:40px">
select null   from formulaire_formCCI_C e 
where d.id_commune = e.id_commune  and d.DATE_VALIDITE < e.DATE_VALIDITE 
and e.DATE_VALIDITE < coalesce(Fin, '2006-11-20')</div>)
group by id_commune, Debut, Fin

**Médiat** · 18/07/2006, 14h54

Dans la requête qui calcule les dates, il faut ajouter l'id du groupe, sinon tu prends toutes les dates de la base dont certaines n'ont pas d'impact sur un groupe donné, d'où cet effet de répétition.

Je n'ai pas le temps cet après-midi, mais si tu ne t'en sors pas, poste les ordres de création de tes tables et les inserts (en pièce jointe, c'est plus propre), et je regarderai ce soir.

**Médiat** · 18/07/2006, 14h56

Envoyé par TheLeadingEdge

1 autre ...

Mais ce n'est pas le résultat attendu

**Nick_59** · 18/07/2006, 15h06

merci Mediat...
je cherche de mon côté, mais je te laisse tout de même les tables .. (le teste sur les dates se fait sur DATE_VALIDITE)

**Médiat** · 18/07/2006, 15h57

Envoyé par Nick_59

je cherche de mon côté, mais je te laisse tout de même les tables ..

Il faudrait que tu les postes

, sous forme de create et d'insert.

**TheLeadingEdge** · 18/07/2006, 16h12

re

Envoyé par Médiat

Citation:
TheLeadingEdge a écrit :
1 autre ...

Mais ce n'est pas le résultat attendu

désolé ... j'ai 1 peu 'boulettisé' * le copié/collé. Il manquait 1 étage pour faire les cumuls.
Mais finalement je ne capte pas le résultat à obtenir,
voila ce que j'avais ...

(* boulettiser qui vient de boulette hein... )

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
    GROUPE DEBUT    FIN      SUM(NBHAB)
---------- -------- -------- ----------
         1 20/02/06 15/05/06     100000
         1 15/05/06 20/09/06     115000
         1 20/09/06 20/10/06     140000
         1 20/10/06              390000
 
SQL>

**Médiat** · 18/07/2006, 17h24

Envoyé par TheLeadingEdge

Mais finalement je ne capte pas le résultat à obtenir,

du 2006-02-20 au 2006-05-15 --> seule la commune 1 existe et a 100 000 habitants
du 2006-05-15 au 2006-09-20 --> la commune 1 a toujours 100 000 habitants et la 2 en a 15 000, total 115 000
du 2006-09-20 au 2006-10-20 --> la commune 1 a toujours 100 000 habitants et la 2 en a 25 000, total 125 000
à partir du 2006-10-20 --> la commune 1 a 250 000 habitants et la 2 en a toujours 25 000, total 275 000

**Médiat** · 18/07/2006, 18h12

Avec les données suivantes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
GROUPE  COMMUNE
1       1
1       2
2       3
2       4

et

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
COMMUNE  NBHAB    DATE_VALIDITE
1        100000   20/02/06
1        250000   20/10/06
2        15000    15/05/06
2        25000    20/09/06
3        25000    23/03/06
3        15000    20/09/06
4        5000     12/05/06
4        2000     20/07/06

et la requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
select c.Id_Groupe, Debut, Fin , sum(nbhab) as Nombre
from (select g1.Id_Groupe, a.date_validite as Debut, min(b.date_validite) as Fin
      from dvp_COMMUNE a inner join dvp_GROUPE_COMMUNE g1
                             on g1.Id_commune = a.Id_Commune
                     inner join dvp_GROUPE_COMMUNE g2
                             on g1.Id_groupe = g2.Id_Groupe
                     left  join dvp_COMMUNE b
                             on a.date_validite < b.date_validite
                            and g2.Id_Commune = b.Id_commune
      group by g1.Id_Groupe, a.date_validite) c left join dvp_GROUPE_COMMUNE g
                                                       on c.Id_groupe = g.Id_Groupe
                                                left join dvp_COMMUNE d
                                                       on d.Id_commune = g.Id_commune
                                                      and d.date_validite < coalesce(Fin, to_date('01/01/2999', 'DD/MM/YYYY'))
where not exists (select null
                  from dvp_COMMUNE e
                  where d.id_commune = e.id_commune
                    and d.date_validite < e.date_validite
                    and e.date_validite < coalesce(Fin, to_date('01/01/2999', 'DD/MM/YYYY')))
group by c.Id_Groupe, Debut, Fin ;

J'obtiens

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
GROUPE	DEBUT          FIN        NOMBRE
1       20/02/06       15/05/06	  100000
1       15/05/06       20/09/06   115000
1       20/09/06       20/10/06   125000
1       20/10/06                  275000
2       23/03/06       12/05/06    25000
2       12/05/06       20/07/06    30000
2       20/07/06       20/09/06    27000
2       20/09/06                   17000

**Nick_59** · 18/07/2006, 19h28

dsl,
il me semblait l'avoir poster.

**Nick_59** · 18/07/2006, 19h54

j'ai testé ta requête, et elle semble fonctionner.
mais elle affiche l'evolution sur toutes les dates.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
1  	Communauté lilloise  	2004-07-10 00:00:00  	2005-07-10 00:00:00  	150000
1 	Communauté lilloise 	2005-07-10 00:00:00 	2006-01-01 00:00:00 	350000
1 	Communauté lilloise 	2006-01-01 00:00:00 	2006-03-01 00:00:00 	470000
1 	Communauté lilloise 	2006-03-01 00:00:00 	2006-05-18 00:00:00 	220000
1 	Communauté lilloise 	2006-05-18 00:00:00 	2006-07-10 00:00:00 	970000
1 	Communauté lilloise 	2006-07-10 00:00:00 	NULL 	860000
4 	G10 	2005-07-07 00:00:00 	2006-07-03 00:00:00 	65000
4 	G10 	2006-07-03 00:00:00 	2006-07-05 00:00:00 	95000
4 	G10 	2006-07-05 00:00:00 	2006-07-19 00:00:00 	96000
4 	G10 	2006-07-19 00:00:00 	NULL 	81000

je l'ai modifié pour afficher l'evolution sur un periode du type 12/2/2006 au 12/11/2006
mais ça ne fonctionne pas, j'ai du mettre mes critéres au mauvaise endroit !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
select c.id_groupe,c.GROUPE, Debut, Fin , sum(NBHAB) as Nombre
from (
select g1.id_groupe,gg.GROUPE , a.DATE_VALIDITE as Debut, min(b.DATE_VALIDITE) as Fin
from formulaire_formCCI_C a 
inner join formulaire_formGroupCom_commune g1  on g1.Id_commune = a.id_commune
inner join formulaire_formGroupCom gg on gg.id_groupe=g1.id_groupe 
inner join formulaire_formGroupCom_commune g2  on gg.id_groupe = g2.id_groupe  
left  join  formulaire_formCCI_C b   on a.DATE_VALIDITE < b.DATE_VALIDITE
and g2.id_commune = b.id_commune
group by g1.id_groupe, a.DATE_VALIDITE) c left join formulaire_formGroupCom_commune g
                                                       on c.id_groupe = g.id_groupe
                                                left join formulaire_formCCI_C d
                                                       on d.id_commune = g.id_commune
                                                      and d.DATE_VALIDITE < coalesce(Fin, '2999-12-31')
where not exists (select null
                  from formulaire_formCCI_C e
                  where d.id_commune = e.id_commune
                    and d.DATE_VALIDITE < e.DATE_VALIDITE
                    and e.DATE_VALIDITE < coalesce(Fin, '2999-12-31'))
group by c.id_groupe, Debut, Fin

**Nick_59** · 18/07/2006, 20h53

c'est plutot ce code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
select c.id_groupe,c.GROUPE, Debut, Fin , sum(NBHAB) as Nombre
from (
select g1.id_groupe,gg.GROUPE , a.DATE_VALIDITE as Debut, min(b.DATE_VALIDITE) as Fin
from formulaire_formCCI_C a 
inner join formulaire_formGroupCom_commune g1  on g1.Id_commune = a.id_commune
inner join formulaire_formGroupCom gg on gg.id_groupe=g1.id_groupe 
inner join formulaire_formGroupCom_commune g2  on gg.id_groupe = g2.id_groupe  
left  join  formulaire_formCCI_C b   on a.DATE_VALIDITE < b.DATE_VALIDITE
and g2.id_commune = b.id_commune
and a.DATE_VALIDITE between ('2006-2-12') AND ('2006-11-12')
and gg.DATE_VALIDITE between ('2006-2-12') AND ('2006-11-12')
group by g1.id_groupe, a.DATE_VALIDITE) c left join formulaire_formGroupCom_commune g
                                                       on c.id_groupe = g.id_groupe
                                                left join formulaire_formCCI_C d
                                                       on d.id_commune = g.id_commune
                                                      and d.DATE_VALIDITE < coalesce(Fin, '2999-12-31')
where not exists (select null
                  from formulaire_formCCI_C e
                  where d.id_commune = e.id_commune
                    and d.DATE_VALIDITE < e.DATE_VALIDITE
                    and e.DATE_VALIDITE < coalesce(Fin, '2999-12-31'))
group by c.id_groupe, Debut, Fin

**Médiat** · 19/07/2006, 05h36

Avec les données précédentes et la requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
select c.Id_Groupe, 
       case when Debut < to_date('14/05/2006','DD/MM/YYYY')
            then to_date('14/05/2006','DD/MM/YYYY')
            else Debut
       end as Debut, 
       case when coalesce(Fin, to_date('01/01/2999', 'DD/MM/YYYY')) > to_date('30/09/2006','DD/MM/YYYY')
            then to_date('30/09/2006','DD/MM/YYYY')
            else Fin
       end as Fin, 
       sum(nbhab) as Nombre
from (select g1.Id_Groupe, a.date_validite as Debut, min(b.date_validite) as Fin
      from dvp_COMMUNE a inner join dvp_GROUPE_COMMUNE g1
                             on g1.Id_commune = a.Id_Commune
                     inner join dvp_GROUPE_COMMUNE g2
                             on g1.Id_groupe = g2.Id_Groupe
                     left  join dvp_COMMUNE b
                             on a.date_validite < b.date_validite
                            and g2.Id_Commune = b.Id_commune
      where a.date_validite <= to_date('30/09/2006','DD/MM/YYYY')
      group by g1.Id_Groupe, a.date_validite
      having coalesce(min(b.date_validite), to_date('01/01/2999', 'DD/MM/YYYY')) > to_date('14/05/2006','DD/MM/YYYY')) c 
          left join dvp_GROUPE_COMMUNE g
                 on c.Id_groupe = g.Id_Groupe
          left join dvp_COMMUNE d
                 on d.Id_commune = g.Id_commune
                and d.date_validite < coalesce(Fin, to_date('01/01/2999', 'DD/MM/YYYY'))
where not exists (select null
                  from dvp_COMMUNE e
                  where d.id_commune = e.id_commune
                    and d.date_validite < e.date_validite
                    and e.date_validite < coalesce(Fin, to_date('01/01/2999', 'DD/MM/YYYY')))
group by c.Id_Groupe, Debut, Fin;

j'obtiens le résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
GROUPE  DEBUT      FIN       NOMBRE
1       14/05/06   15/05/06  100000
1       15/05/06   20/09/06  115000
1       20/09/06   30/09/06  125000
2       14/05/06   20/07/06  30000
2       20/07/06   20/09/06  27000
2       20/09/06   30/09/06  17000

J'ai vu que tu avais bien interprété le to_date de ORACLE, donc j'ai continué à l'utiliser.
Pour éviter tous les coalesce sur Fin, il est possible de remplacer min(b.date_validite) as Fin par coalesce(min(b.date_validite), '2999-12-31') as Fin, mais cela veut dire que tu auras une date bidon comme date de fin au lieu de null, à toi de voir.

Evolution d'une population

Langage SQL

Discussions similaires

Partager

Partager