Discussion :

[Tristavirs]

Bonjour,

Je dispose de la table en pièce-jointe sur laquelle je dois réaliser des requêtes statistiques dans le cadre d'une étude marché immobilier.

Sur la table désirée, je parviens à obtenir les totaux, par commune pour le nb et la surface moyenne des logements par typologie de logements. la moyenne générale du montant des transactions et le prix moyen toutes typologies de logements confondues.

Par contre je suis incapable d'obtenir la moyenne du m² bâti par type de logement (colonne e_m2_bati_hab).

J'ai recherché sur le forum, google etc mais n'ai rien trouvé.

Voici ma requête telle qu'elle existe actuellement. Je vous passe mes différentes tentatives pour obtenir le résultat escompté tel qu'il apparait en pièce-jointe :

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
33
SELECT 
 coalesce (geo_mutationtip2.commune, 'Total général'),
 
  sum(geo_mutationtip2.nbapt1pp) as nbapt1pp ,   round(avg(geo_mutationtip2.sapt1pp),2)as surf_moy1p,
 
-- endroit où je voudrais insérer une colonne avec la moyenne de e_m2_bati_hab pour les appartements d'une pièce
-- et bien-sûr, la même pour les types de logements suivants
 
  sum(geo_mutationtip2.nbapt2pp)as nbapt2pp,     round(avg(geo_mutationtip2.sapt2pp),2)as surf_moy2p, 
  sum(geo_mutationtip2.nbapt3pp)as nbapt3pp,  round(avg(geo_mutationtip2.sapt3pp),2) as surf_moy3p, 
  sum(geo_mutationtip2.nbapt4pp)as nbapt4pp, round(avg(geo_mutationtip2.sapt4pp),2)as surf_moy4p,
  sum(geo_mutationtip2.nbapt5pp)as nbapt5pp,    round(avg( geo_mutationtip2.sapt5pp),2)as surf_moy5p, 
  sum(geo_mutationtip2.nbmai1pp)as nbmai1pp, round(avg(geo_mutationtip2.smai1pp),2)as sur_moy1p, 
  sum(geo_mutationtip2.nbmai2pp)as nbmai2pp,    round(avg(geo_mutationtip2.smai2pp),2)as sur_moy2p, 
  sum(geo_mutationtip2.nbmai3pp)as nbmai3pp,   round(avg(geo_mutationtip2.smai3pp),2)as sur_moy3p, 
  sum(geo_mutationtip2.nbmai4pp)as nbmai4pp,  round(avg(geo_mutationtip2.smai4pp),2)as sur_moy4p, 
  sum(geo_mutationtip2.nbmai5pp)as nbmai5pp,  round(avg(geo_mutationtip2.smai5pp),2) as sur_moy5p, 
 
    round(avg(geo_mutationtip2.e_m2_bati_hab):: numeric,2) as prix_m2hab,   
    round(avg(geo_mutationtip2.valeurfonc),2) as moy_prixha
FROM 
 
  dvf2018.geo_mutationtip2
 
 where e_m2_bati_hab is not null -- ceci me permet de ne sélectionner que les biens bâtis et dédiés à l'habitat
 
  group by rollup  
 
   (geo_mutationtip2.commune)
 
 
ORDER BY
  geo_mutationtip2.commune ASC;

Je suis sur postrgresql 9.5 avec PGADMIN III sur une base locale et bien-sûr, je suis un grand débutant en sql et tout nouveau sur ce forum.

Désolé si je ne respecte pas les protocoles :-)

[escartefigue]

Bonjour,

Quelle est la règle à appliquer pour calculer "la moyenne du m² bâti par type de logement "
- Il manque la description des tables en entrée, vous ne communiquez que la table résultante
- Quelle colonne de quelle table donne le type du logement
- Quelle colonne de quelle table donne la surface du logement
- Y a -t- il des filtres à appliquer (types à ne pas prendre en compte par exemple)

[Tristavirs]

Bonjour, et merci pour votre retour.

Quelle est la règle à appliquer pour calculer "la moyenne du m² bâti par type de logement "
la colonne à prendre en compte est e_m2_bati_hab

- Il manque la description des tables en entrée, vous ne communiquez que la table résultante
Dans la pièce jointe, le 1er tableau correspond à la table sur laquelle j'effectue les requêtes
Le second tableau correspond à ce que je souhaiterai obtenir.

- Quelle colonne de quelle table donne le type du logement
les colonnes définissant le type de logement sont nbapt1pp (qui signifie nombre d'appartements 1 pièce vendus à l'occasion de la mutation) de 1pp (1 pièce) à 5pp (pièces).
Pour les maison, la colonne se nomme nbmaixpp où x représente le nombre de pièces,
- Quelle colonne de quelle table donne la surface du logement
pour les surfaces, le nom de colonne reprend la même logique avec un préfixe "s" à la place de "nb" sapt1pp, smai1pp...

- Y a -t- il des filtres à appliquer (types à ne pas prendre en compte par exemple)
oui, et c'est justement là où je coince pour obtenir la moyenne de e_m2_bati_hab pour les lignes où la valeur de la colonne nbapt1pp n'est pas nulle. (et ensuite pour la colonne nbapt2pp à nbapt5pp, et nbmai1pp à 5pp)
J'ai tenté de mettre le where dans la fonction avg() mais au lieu de me renvoyer une moyenne de e_m2_bati_hab par commune, il me renvoie pour chaque commune la même valeur qui correspond à la moyenne toutes communes confondues.

Merci en tous cas de prendre la peine de vous intéresser à la demande,

[SQLpro]

La formule générale de cette moyenne est :

Code :

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

SUM(prix de vente) / SUM(m²)

A +

[Tristavirs]

Bonjour SQLpro,

Il s'agit de l'une des solutions que j'avais tentées, mais je n'obtiens pas le résultat escompté.

J'ai intégré cette ligne en ligne 4 de ma requête de départ :

Code :

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

1
2
3
 
 
 (select sum (valeurfonc)/sum (sapt1pp) from dvf2018.geo_mutationtip2 where nbapt1pp is not null )as prixm2a1pp,

Le problème, c'est que j'obtiens un résultat qui correspond à la moyenne générale pour l'ensemble des communes mais pas ventilé par commune.
Si je tente de rajouter un group by après le where, j'ai logiquement un message d'erreur : plus d'une ligne renvoyée par une sous-requête utilisée comme une expression.

J'ai testé sur une table filtrée où je n'ai que les lignes où la valeur de la colonne nbapt1pp n'est pas nulle...la requête fonctionne comme je le souhaiterais, avec la moyenne par commune.


Je SECHE...

[SQLpro]

Utilisez une fonction de fenêtrage :

Code :

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

1
2
3
select sum (valeurfonc) OVER (PARTITION BY commune_id) /sum (sapt1pp) OVER (PARTITION BY commune_id)
from dvf2018.geo_mutationtip2 
where nbapt1pp is not null

A +

[Tristavirs]

Bonjour SQLpro et merci pour votre retour,
Merci également pour la fonction de fenêtrage que j'avais bien des difficultés à comprendre en consultant la documentation de postgresql et que j’appréhende beaucoup mieux appliquée à un cas concret.

La requête fonctionne bien si je l'exécute seule, mais si je l’intègre en tant que sous-requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT 
coalesce (geo_mutationtip2.commune, 'Total général'),
sum(geo_mutationtip2.nbapt1pp) as nbapt1pp ,   round(avg(geo_mutationtip2.sapt1pp),2)as surf_moy1p,
(select distinct  sum (valeurfonc) over (partition by commune)/sum (sapt1pp) over (partition by commune) from dvf2018.geo_mutationtip2 where nbapt1pp is not null) -- j'ai ajouté le distinct pour n'obtenir qu'une ligne par commune-- 
FROM 
 
dvf2018.geo_mutationtip2
 
group by  ou group by rollup pour avoir une ligne de totaux
 
(geo_mutationtip2.commune)

J'obtiens toujours le message d'erreur :

ERREUR: plus d'une ligne renvoyée par une sous-requête utilisée comme une expression
********** Erreur **********

ERREUR: plus d'une ligne renvoyée par une sous-requête utilisée comme une expression
État SQL :21000

Pensez-vous qu'il me reste un espoir d'obtenir le résultat escompté ?

[Tristavirs]

Bonjour,

Je m'engage à créer un poème pour celui qui parviendra à me sortir de l'impasse.

Pour vous garantir la qualité qui peut en être attendue, sachez que j'ai eu 19/20 en CM1 sur un exercice de composition poétique.

Je sens que les contributeurs vont se battre pour éclairer ma lanterne.

Plus sérieusement, s'il vous faut des informations ou pièces-jointes complémentaires pour répondre à mon problème, je serai très heureux de vous les fournir.

Vous remerciant par avance,

Tristavirs

[SQLpro]

Pourqui mettre cela dans une sous requête ? C'est stupide !

Code :

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

1
2
3
4
5
6
SELECT geo_mutationtip2.commune,
       sum(geo_mutationtip2.nbapt1pp) as nbapt1pp ,   
	   round(avg(geo_mutationtip2.sapt1pp),2) as surf_moy1p,
       sum(valeurfonc) over (partition by commune) / sum(sapt1pp) over (partition by commune) AS moyenne
FROM   dvf2018.geo_mutationtip2
GROUP  BY geo_mutationtip2.commune

A +

[Tristavirs]

Je viens de lancer la requête telle que proposée :
PG m'impose d'ajouter dans le group by "valeurfonc" et "sapt1pp"
Lorsque je les intègre, la requête me renvoie 178 lignes.
L'agrégation par commune ne fonctionne pas et j'ai tenté de comprendre le résultat de la requête

Après vérification, je m'aperçois qu'il me crée une ligne pour "1 transaction a porté sur la vente de 1 appartement de 1 pièce de 9 m² cédé à un prix moyen de 13000 € par m²", et décline comme cela les transactions en fonction du nombre d'appartements 1 pièce vendus lors de la transaction et de leur surface avec par contre la même moyenne des prix au m² est reportée sur chaque ligne .

Par ailleurs, sum(valeurfonc) sans discriminant "where nbapt1pp is not null" m'opère les calculs sur la totalité des valeurs foncières de la table, tous types de biens confondus, ce qui forcément fausse les calculs puisque je divise par la surface des seuls appartements 1 pièce. C'est pour intégrer le discriminent que j'avais opté pour la sous-requête...apparemment, ce n'était pas bien malin :-)

Voici un extrait du résultat de la requête proposée avec les group by imposés "valeurfonc" et "sapt1pp":

commune nbapt1pp surf_moy1p moyenne
ARCAMBAL None None None

BOISSIERES None None None
BOUZIES None None None
CABRERETS None None None

CAHORS 1 9.00 13060.1797806753594116
CAHORS 1 12.00 13060.1797806753594116
CAHORS 1 13.00 13060.1797806753594116
[...]

CAHORS 2 2.00 13060.1797806753594116
CAHORS 2 17.00 13060.1797806753594116
CAHORS 2 18.00 13060.1797806753594116
[...]
CAHORS 3 17.00 13060.1797806753594116
CAHORS 3 18.00 13060.1797806753594116
[...]
[...]
CAHORS 6 174.00 13060.1797806753594116 -- je n'ai qu'une seule transaction sur cette commune où 6 apparts 1 pièce ont été vendus en une seule fois, pareil pour les lignes suivantes --
CAHORS 7 126.00 13060.1797806753594116
CAHORS 9 202.00 13060.1797806753594116
CAHORS 10 180.00 13060.1797806753594116
CAHORS 14 252.00 13060.1797806753594116
CAHORS None None 13060.1797806753594116
[...)
CATUS 1 20.00 237358.550000000000
CATUS None None 237358.550000000000
[...)
VERS 2 96.00 34752.292812500000
VERS None None 34752.292812500000

Merci en tous cas de tenter de me dépatouiller malgré mon manque de bases

Forest Gump

[Tristavirs]

Bonjour,

Je me permets de relancer mon appel à contribution pour obtenir des statistiques sur la table que j'interroge.

Je mets la table requêtée en pièce-jointe, au format xlsx

Le résultat souhaité est le suivant :

__________somme nbapt1pp| surface moyenne sapt1pp | montant moyen e_m2_bâti apt1pp| somme nbapt2pp | surface moyenne sapt2pp | montant moyen e_m2_bati apt2pp...
commune 1--------13----------------------21------------------------430---------------------------------------9----------------------25----------------------------420
commune 2---------0-----------------------0--------------------------0----------------------------------------5----------------------23----------------------------320
commune 3 ...
...

Pour les sommes nbaptxpp (colonnes 1 et 4) et les moyennes de surfaces (colonnes 2 et 5), je n'ai pas de problème.

Par contre, pour obtenir les moyennes de e_m2_bati en colonnes 3 et 6 ci-dessus, qui correspondent à la moyenne du prix au m² des biens vendus, je ne parviens pas à insérer la commande dans ma requête.

L'idée est la suivante, par exemple pour la colonne 3 : select avg(t1.e_m2_bati) from t1 where nbapt1pp is not null group by commune

En exécutant cette seule requête, j'obtiens le résultat escompté. Mais si je veux la mettre à la suite de sum(nbapt1pp), avg(sapt1pp), pour obtenir le résultat souhaité ci-dessus, je n'y parviens pas.

SQLPRO en personne m'a fourni des pistes (messages ci-dessous), mais je ne suis parvenu à en mettre aucune en œuvre.

Espérant que ma demande sera plus facilement compréhensible avec la table en pièce-jointe,

Vous remerciant par avance,

Tristavirs