Discussion :

[Domi2]

Bonjour,

J'ai donc une une table `immeuble` à laquelle est liée une table `logement`.

J'arrive à compter le nombre de logements par nombre de pièces pour chaque immeuble et je désire encore en faire le total.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT imm.NUMERO_DOSSIER
    , imm.LETTRE_DOSSIER
    , COUNT(CASE NOMBRE_PIECE WHEN 1 THEN NOMBRE_PIECE END) as 1p
    , COUNT(CASE NOMBRE_PIECE WHEN 2 THEN NOMBRE_PIECE END) as 2p
    , COUNT(CASE NOMBRE_PIECE WHEN 3 THEN NOMBRE_PIECE END) as 3p
    , COUNT(CASE NOMBRE_PIECE WHEN 4 THEN NOMBRE_PIECE END) as 4p
    , COUNT(CASE NOMBRE_PIECE WHEN 5 THEN NOMBRE_PIECE END) as 5p    
    , COUNT(log.ID ) as Total  
FROM immeuble as imm
JOIN logement as log
ON imm.ID = log.IMMEUBLE_ID_FK
WHERE NOMBRE_PIECE BETWEEN 1 AND 5
GROUP BY  imm.NUMERO_DOSSIER
    , imm.LETTRE_DOSSIER;

En ligne huit, j'ai donc ajouté :

Code :

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

, COUNT(log.ID ) as Total

qui comptent bien le nombre total de logements par immeubles.

Mais je pensais pouvoir le faire différemment, en additionnant simplement le résultat de mes différentes colonnes, quelque chose comme :

Code :

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

, 1p + 2p +3p + 4p + 5p as Total

Mais j'ai une erreur de syntaxe.

Est-il de possible de faire une telle addition ?

Merci d'avance.

[Waldar]

C'est possible, mais uniquement dans une requête englobant celle-ci.
En effet, les alias de colonnes ne sont transcris qu'en toute fin d'analyse de la requête par le moteur du SGBD.

Il faudrait donc écrire :

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
select NUMERO_DOSSIER
     , LETTRE_DOSSIER
     , 1p, 2p, 3p, 4p, 5p
     , 1p + 2p + 3p + 4p + 5p as Total
  from ( SELECT imm.NUMERO_DOSSIER
              , imm.LETTRE_DOSSIER
              , COUNT(CASE log.NOMBRE_PIECE WHEN 1 THEN 1 END) AS 1p
              , COUNT(CASE log.NOMBRE_PIECE WHEN 2 THEN 1 END) AS 2p
              , COUNT(CASE log.NOMBRE_PIECE WHEN 3 THEN 1 END) AS 3p
              , COUNT(CASE log.NOMBRE_PIECE WHEN 4 THEN 1 END) AS 4p
              , COUNT(CASE log.NOMBRE_PIECE WHEN 5 THEN 1 END) AS 5p    
           FROM immeuble AS imm
           JOIN logement AS log
             ON log.IMMEUBLE_ID_FK = imm.ID
          WHERE log.NOMBRE_PIECE BETWEEN 1 AND 5
       GROUP BY imm.NUMERO_DOSSIER
              , imm.LETTRE_DOSSIER
       ) as SR;

[Domi2]

Bonjour,

Je voyais ça plus simple.

Quelle est la meilleure pratique ?

Faire l'addition des alias ou compter simplement le total de logements ?

Et encore une question. A quoi correspond :

Code :

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

) AS SR;

en fin de requête ?

Sous-Requête ?

Domi2

[Waldar]

Gagné pour SR ! Mais on peut y mettre ce qu'on veut.
Certains SGBD obligent à ce que toute vue imbriquée soit nommée (SQL-Server, PostgreSQL), d'autres non (Oracle Database).

Il faut viser court et parlant - j'utilise des alias à deux ou trois signes mais c'est au bon vouloir de chacun.

Quant aux deux syntaxes, on peut dire que le moteur du SGBD traduira la seconde quasiment comme étant la première...
L'addition est peut-être plus coûteuse que le dénombrement, mais je serai étonné que ce soit perceptible.

Le goulet d'étranglement principal des SGBD ce sont les I/O, loin devant le CPU.
Et dans les deux cas, les I/O seront les mêmes.

[Domi2]

Merci pour toutes ces précisions.

Donc, j'ai maintenant essayé d'intégrer ces petites améliorations dans ma requête finale. La première fonctionne :

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
SELECT cal.ANNEE_REF
    , COUNT(CASE NOMBRE_PIECE WHEN 1 THEN NOMBRE_PIECE END) AS 1p
    , COUNT(CASE NOMBRE_PIECE WHEN 2 THEN NOMBRE_PIECE END) AS 2p
    , COUNT(CASE NOMBRE_PIECE WHEN 3 THEN NOMBRE_PIECE END) AS 3p
    , COUNT(CASE NOMBRE_PIECE WHEN 4 THEN NOMBRE_PIECE END) AS 4p
    , COUNT(CASE NOMBRE_PIECE WHEN 5 THEN NOMBRE_PIECE END) AS 5p    
    , COUNT(log.ID ) as Total
FROM (SELECT DISTINCT EXTRACT(YEAR FROM DATE) AS ANNEE_REF FROM ipc) AS cal
JOIN immeuble AS imm
INNER JOIN logement AS log
ON log.IMMEUBLE_ID_FK = imm.ID AND cal.ANNEE_REF >= EXTRACT(YEAR FROM log.DATE_DEBUT)
    AND (cal.ANNEE_REF <= EXTRACT(YEAR FROM log.DATE_FIN)-1 OR log.DATE_FIN IS NULL)
WHERE imm.TYPE_STATUT_IMMEUBLE_ID_FK >= 3 AND NOMBRE_PIECE BETWEEN 1 AND 5
GROUP BY cal.ANNEE_REF
ORDER BY cal.ANNEE_REF ASC;

Par contre, j'ai encore un souci avec la deuxième méthode.

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
SELECT cal.ANNEE_REF
    , 1p, 2p, 2p, 4p, 5p
    , 1p + 2p + 3p + 4p + 5p AS Total    
	FROM (SELECT COUNT(CASE NOMBRE_PIECE WHEN 1 THEN NOMBRE_PIECE END) AS 1p
        , COUNT(CASE NOMBRE_PIECE WHEN 2 THEN NOMBRE_PIECE END) AS 2p
        , COUNT(CASE NOMBRE_PIECE WHEN 3 THEN NOMBRE_PIECE END) AS 3p
        , COUNT(CASE NOMBRE_PIECE WHEN 4 THEN NOMBRE_PIECE END) AS 4p
        , COUNT(CASE NOMBRE_PIECE WHEN 5 THEN NOMBRE_PIECE END) AS 5p
     FROM (SELECT DISTINCT EXTRACT(YEAR FROM DATE) AS ANNEE_REF FROM ipc) AS cal 
    JOIN immeuble AS imm
    INNER JOIN logement AS log
    ON log.IMMEUBLE_ID_FK = imm.ID AND cal.ANNEE_REF >= EXTRACT(YEAR FROM log.DATE_DEBUT)
        AND (cal.ANNEE_REF <= EXTRACT(YEAR FROM log.DATE_FIN)-1 OR log.DATE_FIN IS NULL)
    WHERE imm.TYPE_STATUT_IMMEUBLE_ID_FK >= 3 AND NOMBRE_PIECE BETWEEN 1 AND 5
    GROUP BY cal.ANNEE_REF
    ORDER BY cal.ANNEE_REF ASC
    ) AS SR;

En effet, avec la modification apportée, cal.ANNEE_REF n'est maintenant plus reconnu.

J'ai essayé diverses choses, mais sans succès.

[Waldar]

C'est normal, les alias de tables ne "remontent" pas (mais ils peuvent descendre dans certaines conditions).

Dans la seconde requête, à l'extérieur, il n'existe plus qu'une seule vue qui s'appelle SR.
La première colonne cherche à récupérer cal.ANNEE_REF, mais point de cal ici.
Il suffit donc de supprimer le cal. ou de le remplacer par SR. ; la suppression est envisageable car à l'extérieur il n'y a qu'une source de données => pas de confusion possible.

À noter aussi que les ORDER BY intermédiaires peuvent parfaitement être ignorés par le SGBD.
Le seul qui compte, c'est celui à l'extérieur.

[Domi2]

J'ai donc essayé ceci :

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
SELECT SR.ANNEE_REF
    , 1p, 2p, 2p, 4p, 5p
    , 1p + 2p + 3p + 4p + 5p AS Total    
	FROM (SELECT COUNT(CASE NOMBRE_PIECE WHEN 1 THEN NOMBRE_PIECE END) AS 1p
        , COUNT(CASE NOMBRE_PIECE WHEN 2 THEN NOMBRE_PIECE END) AS 2p
        , COUNT(CASE NOMBRE_PIECE WHEN 3 THEN NOMBRE_PIECE END) AS 3p
        , COUNT(CASE NOMBRE_PIECE WHEN 4 THEN NOMBRE_PIECE END) AS 4p
        , COUNT(CASE NOMBRE_PIECE WHEN 5 THEN NOMBRE_PIECE END) AS 5p
     FROM (SELECT DISTINCT EXTRACT(YEAR FROM DATE) AS ANNEE_REF FROM ipc) AS cal 
    JOIN immeuble AS imm
    INNER JOIN logement AS log
    ON log.IMMEUBLE_ID_FK = imm.ID AND cal.ANNEE_REF >= EXTRACT(YEAR FROM log.DATE_DEBUT)
        AND (cal.ANNEE_REF <= EXTRACT(YEAR FROM log.DATE_FIN)-1 OR log.DATE_FIN IS NULL)
    WHERE imm.TYPE_STATUT_IMMEUBLE_ID_FK >= 3 AND NOMBRE_PIECE BETWEEN 1 AND 5
    GROUP BY cal.ANNEE_REF
    ORDER BY cal.ANNEE_REF ASC
    ) AS SR;

Egalement, j'ai essayé :

Code :

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

SELECT ANNEE_REF

Mais j'ai toujours une erreur.

[Waldar]

Je ne vois pas d'erreur là pourtant... Même message en sortant le ORDER BY ?

Edit : Ah si, la colonne ANNEE_REF n'est pas dans la vue imbriquée.
Il faut qu'elle soit présente pour être lue au-dessus.

Donc SR.ANNEE_REF à l'extérieur et cal.ANNEE_REF à l'intérieur !

[Domi2]

Si tu parles simplement de supprimer la clause, oui, le résultat est identique.

Error Code: 1054. Unknown column 'SR.ANNEE_REF' in 'field list'

[Waldar]

Comme ceci :

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 ANNEE_REF
       , 1p, 2p, 2p, 4p, 5p
       , 1p + 2p + 3p + 4p + 5p AS Total    
    FROM (   SELECT cal.ANNEE_REF
                  , COUNT(CASE log.NOMBRE_PIECE WHEN 1 THEN 1 END) AS 1p
                  , COUNT(CASE log.NOMBRE_PIECE WHEN 2 THEN 1 END) AS 2p
                  , COUNT(CASE log.NOMBRE_PIECE WHEN 3 THEN 1 END) AS 3p
                  , COUNT(CASE log.NOMBRE_PIECE WHEN 4 THEN 1 END) AS 4p
                  , COUNT(CASE log.NOMBRE_PIECE WHEN 5 THEN 1 END) AS 5p
               FROM (SELECT DISTINCT EXTRACT(YEAR FROM DATE) AS ANNEE_REF FROM ipc) AS cal 
         CROSS JOIN immeuble AS imm
         INNER JOIN logement AS log
                 ON log.IMMEUBLE_ID_FK = imm.ID
                AND cal.ANNEE_REF >= EXTRACT(YEAR FROM log.DATE_DEBUT)
                AND (cal.ANNEE_REF <= EXTRACT(YEAR FROM log.DATE_FIN)-1 OR log.DATE_FIN IS NULL)
              WHERE imm.TYPE_STATUT_IMMEUBLE_ID_FK >= 3
                AND log.NOMBRE_PIECE BETWEEN 1 AND 5
           GROUP BY cal.ANNEE_REF
         ) AS SR
ORDER BY ANNEE_REF ASC;

[Theophane631]

Salut,

Y a sans doute un truc que j'ai pas compris, mais est-il vraiment nécessaire pour toi d'avoir une sortie en colonne plutôt qu'en ligne ? Parce qu'un simple COUNT(NOMBRE_PIECE) avec un WITH ROLLUP dans le GROUP BY ferait l'affaire pour avoir le total.
Et tu peux rajouter NOMBRE_PIECE dans le ORDER BY pour être sûr de récupérer tes lignes dans l'ordre 1, 2, 3, 4, 5.

[Domi2]

Bonjour,

@Waldar

Oui, ok, cette fois cela fonctionne.

Dans mes essais, je crois d'ailleurs que je n'étais pas loin, c'est je pense le ORDER BY qui posait problème.

Maintenant, j'ai vu encore deux choses :

Tu as modifié la jointure de la table `immeuble`, là j'ai encore de la lecture en cours, puis surtout la clause WHERE, en retirant :

Code :

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

AND NOMBRE_PIECE BETWEEN 1 AND 5

Une raison à cela ?

@Theophane631

Ben, comme dit, je débute. J'ai vu qu'il y avait sans doute plusieurs façons d'obtenir ce que je recherche, j'ai essayé de choisir une solution à ma portée et qui semble bien adaptée.

[Waldar]

puis surtout la clause WHERE, en retirant :

Code :

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

AND NOMBRE_PIECE BETWEEN 1 AND 5

Je l'ai simplement mis à la ligne n°17

[Theophane631]

Si j'ai bien compris, tu cherches à savoir par année, le nombre de logement 1 à 5 pièces de tous tes immeubles répondant au critère "WHERE imm.TYPE_STATUT_IMMEUBLE_ID_FK >= 3" ?

Pour le AND de la clause WHERE, il est bien là, il a juste fait un retour à la ligne pour une meilleur visibilité

Pour ma part, je ferrai plutôt un compte en ligne. J'imagine que c'est sans doute une question de préférence. Dans ma tête, chaque colonne est associée à une sémantique particulière, et ce sont les lignes qui font les dérivées de cette sémantique. Là, tu as 5 pièces max, mais si tout d'un coup tu ne veux que 4 et 5, tu dois tout réécrire. Le principale défaut que je vois à cette requête, c'est qu'elle n'est pas modulaire.

Par exemple, tu ne peux pas en faire une procédure avec en paramètre le nombre de pièces. SQL peut gère les lignes selon ces besoins, alors que les colonnes sont figées. En plus, tu es obligé de faire une sous-requête pour convertir les lignes que te donne SQL avec

Code :

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

1
2
3
(SELECT DISTINCT EXTRACT(YEAR FROM DATE) AS ANNEE_REF FROM ipc) AS cal 
         CROSS JOIN immeuble AS imm
         INNER JOIN logement AS log

en colonne, alors que COUNT les comptera directement en ligne.

Comme je le précisais, il existe une clause "WITH ROLLUP" qui associé à un GROUP BY fait les sommes correspondant à tous les groupes.

J'aurais donc plutôt fait une requête du genre :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT COALESCE(ANNEE_REF, 'Totaux globaux'), COALESCE(log.NOMBRE_PIECE, 'Total'), COUNT(log.NOMBRE_PIECE)   
    FROM (SELECT DISTINCT EXTRACT(YEAR FROM DATE) AS ANNEE_REF FROM ipc) AS cal
    CROSS JOIN immeuble AS imm
    INNER JOIN logement AS log
      ON log.IMMEUBLE_ID_FK = imm.ID
        AND cal.ANNEE_REF >= EXTRACT(YEAR FROM log.DATE_DEBUT)
        AND (cal.ANNEE_REF <= EXTRACT(YEAR FROM log.DATE_FIN)-1 OR log.DATE_FIN IS NULL)
    WHERE imm.TYPE_STATUT_IMMEUBLE_ID_FK >= 3
      AND log.NOMBRE_PIECE BETWEEN 1 AND 5
    GROUP BY cal.ANNEE_REF, log.NOMBRE_PIECE WITH ROLLUP
    ORDER BY ANNEE_REF ASC;

A moins que je ne me sois complètement foiré, ça devrait te donner la même chose. Le problème potentiel est que si tu as une année où il n'existe aucun logement d'un nombre de pièces donné, au lieu d'avoir 0, la ligne pour ce nombre de pièces n’apparaîtra pas. A voir si c'est un problème à résoudre.

PS : la fonction COALESCE permet de transformer les NULL sur les lignes de sommes par le mot 'Total'.

[alassanediakite]

Salut
Quelque chose m'échappe. Comment log.NOMBRE_PIECE vient-il de...

Comme ceci :

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 ANNEE_REF
       , 1p, 2p, 2p, 4p, 5p
       , 1p + 2p + 3p + 4p + 5p AS Total    
    FROM (   SELECT cal.ANNEE_REF
                  , COUNT(CASE log.NOMBRE_PIECE WHEN 1 THEN 1 END) AS 1p
                  , COUNT(CASE log.NOMBRE_PIECE WHEN 2 THEN 1 END) AS 2p
                  , COUNT(CASE log.NOMBRE_PIECE WHEN 3 THEN 1 END) AS 3p
                  , COUNT(CASE log.NOMBRE_PIECE WHEN 4 THEN 1 END) AS 4p
                  , COUNT(CASE log.NOMBRE_PIECE WHEN 5 THEN 1 END) AS 5p
               FROM (SELECT DISTINCT EXTRACT(YEAR FROM DATE) AS ANNEE_REF FROM ipc) AS cal 
         CROSS JOIN immeuble AS imm
         INNER JOIN logement AS log
                 ON log.IMMEUBLE_ID_FK = imm.ID
                AND cal.ANNEE_REF >= EXTRACT(YEAR FROM log.DATE_DEBUT)
                AND (cal.ANNEE_REF <= EXTRACT(YEAR FROM log.DATE_FIN)-1 OR log.DATE_FIN IS NULL)
              WHERE imm.TYPE_STATUT_IMMEUBLE_ID_FK >= 3
                AND log.NOMBRE_PIECE BETWEEN 1 AND 5
           GROUP BY cal.ANNEE_REF
         ) AS SR
ORDER BY ANNEE_REF ASC;

@+

[alassanediakite]

Ok, j'ai vu
Il fallait juste lire jusqu'à ...

Code :

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

1
2
CROSS JOIN immeuble AS imm
         INNER JOIN logement AS log....

Toutes mes excuses.
@+

[Domi2]

Je l'ai simplement mis à la ligne n°17

Au temps pour moi.

@Theophane631

ROLLUP n'est pas reconnu par MySQL, la requête que tu proposes ne fonctionne donc pas.

Mais ta remarque concernant l'absence de résultat si tous les types de logements ne sont pas présent une année donnée m'interpelle.

Je vais essayer de vérifier cela demain matin.

[Theophane631]

Re,
Le coup de l'absence de résultat en cas de manque de type de logement, c'est avec ma méthode et non la tienne

Pour ROLLUP, ça existe dans MySQL depuis la version 4.1.1 (voir ici). Tu utilises quelle version ?
Par contre, c'est vrai que ça n'a pas l'air trop répandu ailleurs. J'ai vu que dans Oracle, il ne faut pas mettre WITH avant ROLLUP et le mettre avant les attributs : GROUP BY ROLLUP attribut1, etc...

A+

[Domi2]

Bonjour,

J'utilise une version 5.5.24.

En fait, je me suis basé sur ce tutoriel pour voir les différentes fonctions disponibles. Il n'est peut-être plus vraiment à jour.

Sinon, j'ai quand même une erreur avec ta requête :

Error Code: 1221. Incorrect usage of CUBE/ROLLUP and ORDER BY

Comme j'ai déjà deux requêtes qui fonctionnent, je pense m'arrêter là pour l'instant, j'ai encore beaucoup de lecture et d'essais en perspective.

Merci à tous.

[Theophane631]

Salut. Pardon c'est de ma faute. On ne peut pas utiliser ordre by avec rollup puisque celui-ci fait déjà un tri. Tu peux rajouter les ASC et DESC directement dans le group by. En virant le order by, la requête devrait maintenant fonctionner.
Je te conseille quand meme de l'essayer pour voir l'allure des résultats avec un rollup, malgré le peu de temps dont tu disposes pour d'autres tests

Enfin, n'oublie pas le principal défaut que je vois à ta façon de faire : ta commande n'offre pas de souplesse, car si tu veux faire 4p et 5p seulement , où même passer à l'ajout des 6p tu seras obligé de réécrire toute ta requête, et tu ne peux par exemple pas faire une fonction tri_ensemble_nbr_pieces_par_an(nbr_pieces_min, nbr_pieces_max).

Bonne continuation !