Jointure tables

Signaler
Messages postés
10
Date d'inscription
mardi 13 avril 2021
Statut
Membre
Dernière intervention
3 mai 2021
-
Messages postés
15622
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
16 mai 2021
-
Bonjour,

Je vous expose mon problème, je souhaite créer une base de données qui permettrait d'analyser les comptages voiture dans une ville à partir de bornes.
Pour simplifier, j'ai pris des données fictives et supprimé certaines colonnes inutiles pour la lisibilité des données, et juste modifié les noms des id de chaque table pour éviter les ambiguïtés dans mon explication.

Pour mieux se représenter les choses et comprendre la suite, voici la structure de ma BDD :


J'ai en gros ces données en entrée, qui proviennent d'un fichier csv, les noms de colonnes, de 0 à 23, correspondent aux heures de la journée et les valeurs aux nombres de voitures détectées :


J'ai alors créé une table Mesure, un id Mesure correspond à une station à un jour donné, j'ai réalisé un total quotidien par station que j'ai concaténé à un autre fichier (qui ne possédait que les totaux quotidiens et non pas par heure). En bref, le df final, que j'ai exporté en csv puis insérer dans la BDD, ressemble à cela :


J'aimerais maintenant relier une table 'Découpage_heure' afin d'avoir une granularité horaire pour les mesures qui possèdent cette précision. En bref, j'aimerais que la table 'Découpage_heure' ressemble à cela :


C'est après réflexion la structure de la BDD qui me semble la plus logique et facile à exploiter.
Seulement, je rencontre 2 problèmes :
-Comment récupérer les données horaires initiales (1ère image) et les faire correspondre à l'id_mesure, étant donné que dans la création et l'insertion des données Mesures, j'ai totalement laissé de côté les données horaires pour me concentrer sur les totaux quotidiens ? (Faudrait-il, au lieu de générer un identifiant unique 'id_mesure' dans la table Mesures, créer une double clé primaire associée id_station/date et ajouter ces 2 données comme attributs dans la table Découpage_heure, ainsi il détecterait la station/date correspondante ?)
-Comment insérer les données horaires dans la table 'Découpage_heure', les 0, 1, 2 ..., 23 passant de nom de colonnes d'un fichier csv à valeur dans une table ?

J'ai bien conscience que c'est un pavé et peut-être pas clair par moment, je suis tout-à-fait prêt à fournir plus d'explications si besoin.
En tout cas, un énorme merci à toute personne qui pourra m'aiguiller sur ce sujet.

Bonne journée !

2 réponses

Messages postés
15622
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
16 mai 2021
855
bonjour,
essaie de donner plus de texte, et moins d'images.
quand tu écris "1ère image", je suppose qu'il s'agit de la deuxième, la première étant la structure de tes tables.

avant d'importer les données, concentrons-nous sur la conception des tables.
"comptage", dans la table "mesure", c'est le total quotidien?
c'est quoi les champs "mesure" et valeur" dans la table decoupage_heure?
c'est quoi la table decoupage_type? d'où vient ce "type"?

voyant tes données, je pense qu'il faut une table station,
et une table mesure, avec 4 champs, id_station, date, heure, et mesure
Messages postés
15622
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
16 mai 2021
855
pour l'importation, je pense à deux techniques possibles:
1) en python, tu "analyses" les données, et tu fais l'insertion des enregistrements avec la bonne heure
2) tu importes les données brutes dans une table intermédiaire à 26 champs, tu exécutes une requête SQL qui importe les données de la table intermédiaire dans la table mesure, et tu vides la table intermédiaire.
Messages postés
10
Date d'inscription
mardi 13 avril 2021
Statut
Membre
Dernière intervention
3 mai 2021

Bonjour et merci pour votre réponse,
'Comptage' représente effectivement le total quotidien, qui était directement disponible dans le fichier csv jusqu'à 2019, et que j'ai du calculer manuellement dans le fichier à partir de 2020.
Dans la table 'découpage_heure', mesure représente l'id_mesure (correspond à chaque station/jour) et valeur au nombre de voitures par heure (cf 2ème image, sous la structure des tables).
Idem pour la table découpage_type, même structure, mais à la place des heures, chaque colonne du fichier csv représente un type de véhicule (électrique, location, etc). Le fichier csv a la même organisation de celui des heures (2ème image).
Merci beaucoup !
Messages postés
15622
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
16 mai 2021
855 >
Messages postés
10
Date d'inscription
mardi 13 avril 2021
Statut
Membre
Dernière intervention
3 mai 2021

ce qui confirme ce que j'avais supposé.

donc, une table station,
et une table mesure, avec 5 champs: id_station, date, heure, mesure et type

en effet, dans la table mesure, 24 enregistrements pour les dates possédant la granularité horaire et seulement 1 pour les date ne possédant que le total quotidien (tout cela sans tenir compte du type, à propos duquel tu donnes peu d'information factuelle.

tout le reste tu le calculeras avec des requêtes.
Messages postés
10
Date d'inscription
mardi 13 avril 2021
Statut
Membre
Dernière intervention
3 mai 2021
>
Messages postés
15622
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
16 mai 2021

Je pense que c'est jouable, je vais en discuter avec mon lead dev, ce qui m'embêtait était surtout de mélanger dans une même colonne des valeurs horaires et d'autres quotidiennes, mais en laissant la colonne 'heure' vide pour les données quotidiennes, ça pourrait effectivement fonctionner.
Merci en tout cas
Messages postés
29801
Date d'inscription
jeudi 12 mai 2005
Statut
Modérateur
Dernière intervention
12 mai 2021
7 092
Bonjour,

Personnellement ça me paraît être un schéma de base de donnée bien compliqué pour pas grand chose, et surtout qui risque d'être coûteux à exploiter, car il va t'amener à faire plein de jointures. Or les jointures, ça passe mal à l'échelle : une jointure entre deux tables de tailles m et n revient à faire leur produit cartésien en ne conservant que les records qui se rejoignent, ce qui se fait en O(m.n). Si m et n sont grands, ça ne pas passera donc pas à l'échelle.

Dans ton cas, j'aurais adopté un schéma plus simple : j'aurais mis les champs associés à
decoupage_heure
et
decoupage_mesure
directement dans la table
mesure
, car si ma compréhension est correcte, une mesure impliquera toujours (ou presque toujours) un
decoupage_heure
et un
decoupage_type
. Si ce que je dis est correct, l'espace "perdu" pour les cas où ces champs ne seraient pas exploités serait donc minime.

Si tu as un gros volume de mesures (ce qui finit par arriver inévitablement si on attend assez longtemps), il sera alors plus simple de partitionner ta table mesure (e.g. par mois ou par année). Ainsi en fonction de la requête, tu pourras chercher les mesures dans les tables qui couvrent un intervalle de temps pertinent.

Bonne chance
Messages postés
10
Date d'inscription
mardi 13 avril 2021
Statut
Membre
Dernière intervention
3 mai 2021

Bonjour et merci pour votre réponse,
Malheureusement, je ne possède que des données quotidiennes de 2014 à 2020, et ce n'est que depuis 2020 que je possède une granularité horaire, idem pour les types, ce qui laisserait vraiment beaucoup de valeurs vides.
Dans votre explication, comment auriez-vous vu les choses ? Vous auriez par exemple mis 24 attributs correspondant aux heures dans la table Mesure ? Ou alors mis une ligne (enregistrement) par mesure (station/date) et par heure, ce qui fait qu'il y aurait eu 24 enregistrements pour les dates possédant la granularité horaire et seulement 1 pour les date ne possédant que le total quotidien ?
Merci à vous
Messages postés
29801
Date d'inscription
jeudi 12 mai 2005
Statut
Modérateur
Dernière intervention
12 mai 2021
7 092 >
Messages postés
10
Date d'inscription
mardi 13 avril 2021
Statut
Membre
Dernière intervention
3 mai 2021

Je pensais que la plupart de tes mesures avait une granularité horaire, donc je pensais que le problème de place perdue n'était pas très important.

Vu que les nature des mesures semble dépendre de l'année, si tu fais une partition par année (cf ma discussion sur le passage à l'échelle) et avoir un schéma de table différent pour les années pré-2020 et post-2020. Ensuite, à l'aide d'une vue et/ou avec une procédure stockée, tu peux interroger les bons champs des bonnes partitions et réunir les résultats extraits de chaque partition. Après les partitions ne deviennent intéressantes que si tu manipules de gros volumes de données. Bref c'est peut être overkill dans ton cas...
Messages postés
15622
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
16 mai 2021
855 >
Messages postés
10
Date d'inscription
mardi 13 avril 2021
Statut
Membre
Dernière intervention
3 mai 2021

peux-tu donner suite, ou marquer comme résolu?