Jointure tables
chaldeen
Messages postés
14
Statut
Membre
-
yg_be Messages postés 24281 Date d'inscription Statut Contributeur Dernière intervention -
yg_be Messages postés 24281 Date d'inscription Statut Contributeur Dernière intervention -
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 !
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 !
A voir également:
- Jointure tables
- Tables des matières word - Guide
- Tables ascii - Guide
- Tables des annexes ✓ - Forum Word
- Oracle liste des tables - Forum Oracle
- Voici une base de données présentant la programmation d'un théâtre. elle comporte 4 tables : piece, seance, categorie, tarif. combien de pièces de danse et de pièces de musique, en tout, ont un tarif réduit strictement inférieur à 15 euros ? - Forum Python
2 réponses
yg_be
Messages postés
24281
Date d'inscription
Statut
Contributeur
Dernière intervention
Ambassadeur
1 585
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
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
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 à
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
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_heureet
decoupage_mesuredirectement dans la table
mesure, car si ma compréhension est correcte, une mesure impliquera toujours (ou presque toujours) un
decoupage_heureet 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
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
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
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...
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...
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.
'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 !
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.
Merci en tout cas