Conception et requêtage
de la base de données SQL
khalid OURO-ADOYI
Contexte
Réalisation de projet de Proof of Concept pour
un réseau national d’agences immobilières,
Laplace Immo en tant que Data Engineer.
• Collecte et Structure de données
• Implémentation de BDD SQL
• Analyse du marché (Biens Immobiliers)
1 ‹#›
La stratégie de sauvegarde et
la conformité RGPD
• la transformation des données ;
• un extrait du dictionnaire des données ;
• le schéma relationnel normalisé ;
• une capture d’écran de la base de données avec les tables créées et
les données chargées ;
• le code SQL des requêtes et leurs résultats permettant de répondre
aux besoins de l’agence (qui vous seront présentés à l’étape 2)
Ajouter un pied de page ‹#›
Les données initiales
• Données communes :
CODREG,CODDEP,CODARR,CODCAN,CODCOM,COM,PMUN,PCAP,PTOT
• Données Réference geographiques :
regrgp_nom,reg_nom,reg_nom_old,aca_nom,dep_nom,com_code,com_code1,com_code2,com_id,com_no
m_maj_court,com_nom_maj,com_nom,uu_code,uu_id,uucr_id,uucr_nom,ze_id,dep_code,dep_id,dep_no
m_num,dep_num_nom,aca_code,aca_id,reg_code,reg_id,reg_code_old,reg_id_old,fd_id,fr_id,fe_id,uu_id_
99,au_code,au_id,auc_id,auc_nom,uu_id_10,geolocalisation
• Données Valeur fonciéres :
Date mutation,Nature mutation,Valeur fonciere,No voie,B/T/Q,Code type de voie,Type de voie,Code
voie,Voie,Code ID commune,Code postal,Commune,Code departement,Code commune,Préfixe de
section,Section,No plan,No Volume,1er lot,Surface Carrez du 1er lot,2eme lot,Surface Carrez du 2eme
lot,3eme lot,Surface Carrez du 3eme lot,4eme lot,Surface Carrez du 4eme lot,5eme lot,Surface Carrez du
5eme lot,Nombre de lots,Code type local,Type local,Identifiant local,Surface reelle bati,Nombre pieces
principales,Nature culture,Nature culture speciale,Surface terrain,Nom de l'acquereur ‹#›
L’extrait du dictionnaire des
données
• Donnees communes :
Ajouter un pied de page ‹#›
• Donnees Valeurs foncières :
Ajouter un pied de page ‹#›
• Données Referentiels géographiques :
‹#›
Le schéma relationnel normalisé
Ajouter un pied de page ‹#›
Les données retenues
• Table Region (Stocke les informations des régions)
Attribut Type Contraintes Signification
Id_region INTEGER UNSIGNED NOT NULL, PRIMARY KEY Identifiant unique de la région
Nom_Region VARCHAR(50) NOT NULL Nom de la région
Nom du regroupement de la région (ex : grande zone
Nom_regroup VARCHAR(50) NOT NULL
administrative)
‹#›
• Table Commune (Stocke les informations des communes)
Attribut Type Contraintes Signification
id_codedep_codecommu Identifiant unique de la commune (concaténation du code
VARCHAR(50) NOT NULL, PRIMARY KEY
ne département et du code commune)
NOT NULL, FOREIGN KEY
Id_region INTEGER UNSIGNED Identifiant de la région à laquelle appartient la commune
(Region)
Code_departement VARCHAR(10) NOT NULL Code du département de la commune
Code_commune INTEGER UNSIGNED NOT NULL Code unique de la commune dans le département
Nom_commune VARCHAR(50) NOT NULL Nom de la commune
Nbre_habitant_2019 INTEGER UNSIGNED NOT NULL Nombre d’habitants dans la commune en 2019
‹#›
• Table Bien (Stocke les informations des biens immobiliers)
Attribut Type Contraintes Signification
Id_bien INTEGER UNSIGNED NOT NULL, PRIMARY KEY Identifiant unique du bien immobilier
NOT NULL, FOREIGN KEY
id_codedep_codecommune VARCHAR(50) Référence à la commune où se trouve le bien
(Commune)
No_voie VARCHAR(50) DEFAULT '' Numéro de la voie du bien
BTQ VARCHAR(1) DEFAULT '' Boîte aux lettres
Type_voie VARCHAR(4) DEFAULT '' Type de voie
Voie VARCHAR(50) DEFAULT '' Nom de la voie
Total_piece INTEGER UNSIGNED NULL Nombre total de pièces dans le bien
Surface_carrez FLOAT UNSIGNED NULL Surface habitable en loi Carrez
Surface_local INTEGER UNSIGNED NULL Surface totale du bien
Type_local VARCHAR(50) NULL Type de bien (appartement, maison, local commercial, etc.)
‹#›
• Table Vente (Stocke les informations des transactions immobilières)
Attribut Type Contraintes Signification
NOT NULL, PRIMARY Identifiant unique de la
Id_vente INTEGER UNSIGNED
KEY vente
NOT NULL, FOREIGN
Id_bien INTEGER UNSIGNED Référence au bien vendu
KEY (Bien)
Date DATE NOT NULL Date de la vente
Prix de vente du bien en
Valeur INTEGER UNSIGNED NULL
euros
‹#›
Création de la base [immobilier] et des tables
Ajouter un pied de page ‹#›
Ajouter un pied de page ‹#›
Chargement des données
Ajouter un pied de page ‹#›
vente region
commune
bien
Requêtes SQL et résultats
(Etude de marché)
Requête 1
Nombre total d’appartements vendus au 1er semestre 2020.
SELECT COUNT(*) AS Nb_appartements_vendus
FROM vente v
JOIN bien b ON v.Id_bien = b.Id_bien
WHERE [Link] BETWEEN '2020-01-01' AND '2020-06-30'
AND b.Type_local = 'Appartement';
Ajouter un pied de page ‹#›
Requête 2
Le nombre de ventes d’appartement par région pour le 1er semestre 2020.
SELECT r.Nom_region, COUNT(*) AS Nb_ventes
FROM vente v
JOIN bien b ON v.Id_bien = b.Id_bien
JOIN commune c ON b.id_codedep_codecommune =
c.id_codedep_codecommune
JOIN region r ON c.Id_region = r.Id_region
WHERE [Link] BETWEEN '2020-01-01' AND '2020-06-30'
AND b.Type_local = 'Appartement'
GROUP BY r.Nom_region
ORDER BY Nb_ventes DESC;
Ajouter un pied de page ‹#›
Requête 3
Proportion des ventes d’appartements par le nombre de pièces.
SELECT b.Total_piece, COUNT(*) * 100.0 / (SELECT COUNT(*)
FROM vente v JOIN bien b ON v.Id_bien = b.Id_bien
WHERE b.Type_local = 'Appartement') AS Proportion
FROM vente v
JOIN bien b ON v.Id_bien = b.Id_bien
WHERE b.Type_local = 'Appartement'
GROUP BY b.Total_piece
ORDER BY Total_piece ASC;
Ajouter un pied de page ‹#›
Requête 4
Liste des 10 départements où le prix du mètre
carré est le plus élevé.
SELECT c.Code_departement, AVG([Link] /
b.Surface_carrez) AS Prix_m2
FROM vente v
JOIN bien b ON v.Id_bien = b.Id_bien
JOIN commune c ON b.id_codedep_codecommune =
c.id_codedep_codecommune
WHERE b.Surface_carrez > 0
GROUP BY c.Code_departement
ORDER BY Prix_m2 DESC
LIMIT 10;
‹#›
Requête 5
Prix moyen du mètre carré d’une maison en Île-de-France.
SELECT AVG([Link] / b.Surface_carrez) AS prix_m2_moyen
FROM vente v
JOIN bien b ON v.Id_bien = b.Id_bien
JOIN commune c ON b.Id_codedep_codecommune =
c.Id_codedep_codecommune
JOIN region r ON c.Id_region = r.Id_region
WHERE b.Type_local = 'maison'
AND r.Nom_Region = 'Île-de-France';
‹#›
Requête 6
Liste des 10 appartements les plus chers avec la région et le
nombre de mètres carrés.
SELECT b.Id_bien, [Link], b.Surface_carrez,
r.Nom_region
FROM vente v
JOIN bien b ON v.Id_bien = b.Id_bien
JOIN Commune c ON
b.id_codedep_codecommune =
c.id_codedep_codecommune
JOIN region r ON c.Id_region = r.Id_region
WHERE b.Type_local = 'Appartement'
ORDER BY [Link] DESC
LIMIT 10;
‹#›
Requête 7
Taux d’évolution du nombre de ventes entre le premier et le second trimestre de 2020.
SELECT
(COUNT(CASE WHEN [Link] BETWEEN '2020-04-01' AND '2020-06-30'
THEN 1 END) * 100) /
COUNT(CASE WHEN [Link] BETWEEN '2020-01-01' AND '2020-03-31'
THEN 1 END) AS Evolution
FROM vente v
WHERE [Link] BETWEEN '2020-01-01' AND '2020-06-30';
‹#›
Requête 8
Le classement des régions par rapport au prix au mètre carré des
appartement de plus de 4 pièces.
SELECT r.Nom_region, AVG([Link] /
b.Surface_carrez) AS Prix_m2
FROM vente v
JOIN bien b ON v.Id_bien = b.Id_bien
JOIN commune c ON b.id_codedep_codecommune
= c.id_codedep_codecommune
JOIN region r ON c.Id_region = r.Id_region
WHERE b.Type_local = 'Appartement'
AND b.Total_piece > 4
AND b.Surface_carrez > 0
GROUP BY r.Nom_region
ORDER BY Prix_m2 DESC;
Ajouter un pied de page ‹#›
Requête 9
Liste des communes ayant eu au moins 50 ventes au 1er trimestre
SELECT c.Nom_commune, COUNT(*) AS Nb_ventes
FROM vente v
JOIN bien b ON v.Id_bien = b.Id_bien
JOIN commune c ON b.id_codedep_codecommune =
c.id_codedep_codecommune
WHERE [Link] BETWEEN '2020-01-01' AND '2020-03-31'
GROUP BY c.Nom_commune
HAVING COUNT(*) >= 50;
‹#›
Requête 10
Différence en pourcentage du prix au mètre carré entre un appartement de 2 pièces et un
appartement de 3 pièces.
WITH Prix_2_pieces AS (
SELECT AVG([Link] / b.Surface_carrez) AS Prix_m2_2_pieces
FROM vente v
JOIN bien b ON v.Id_bien = b.Id_bien
WHERE b.Type_local = 'Appartement' AND b.Total_piece = 2 AND b.Surface_carrez > 0
),
Prix_3_pieces AS (
SELECT AVG([Link] / b.Surface_carrez) AS Prix_m2_3_pieces
FROM vente v
JOIN bien b ON v.Id_bien = b.Id_bien
WHERE b.Type_local = 'Appartement' AND b.Total_piece = 3 AND b.Surface_carrez > 0
)
SELECT
((Prix_3_pieces.Prix_m2_3_pieces - Prix_2_pieces.Prix_m2_2_pieces) /
Prix_2_pieces.Prix_m2_2_pieces) * 100 AS Diff_percentage
FROM Prix_2_pieces, Prix_3_pieces;
‹#›
Requête 11
Les moyennes de valeurs foncières pour le top 3 des communes des départements 6, 13, 33,
59 et 69.
SELECT
c.Code_departement,
c.Nom_commune,
AVG([Link]) AS Moyenne_valeur_fonciere
FROM
Vente v
JOIN
Bien b ON v.Id_bien = b.Id_bien
JOIN
Commune c ON b.id_codedep_codecommune = c.id_codedep_codecommune
WHERE
c.Code_departement IN ('06', '13', '33', '59', '69')
GROUP BY
c.Code_departement, c.Nom_commune
ORDER BY
Moyenne_valeur_fonciere DESC
LIMIT 3;
‹#›
Requête 12
Les 20 communes avec le plus de transactions pour 1000
habitants pour les communes qui dépassent les 10 000 habitants.
SELECT c.Nom_commune, COUNT(*) * 1000.0 /
c.Nbre_habitant_2019 AS
Transactions_par_1000_habitants
FROM vente v
JOIN bien b ON v.Id_bien = b.Id_bien
JOIN commune c ON b.id_codedep_codecommune =
c.id_codedep_codecommune
WHERE c.Nbre_habitant_2019 > 10000
GROUP BY c.Nom_commune, c.Nbre_habitant_2019
ORDER BY Transactions_par_1000_habitants DESC
LIMIT 20;
Ajouter un pied de page ‹#›
Merci !